5.1 Define reference relationships

Define reference relationships :

In this tutorial, each dimension of your cube has been directly linked to a table in the fact data table of the measurement value group based on the relationship between the primary key and the foreign key. In Lesson 3rd and lesson 4th, you will not directly link to a table (called a snowflake table) in a fact data table) the dimension attribute in is included in the dimension that is directly linked to the fact data table by pressing its key column. For example, in Lesson 3rdDimgeographyAttribute added to the table"Customer"Dimension.DimgeographyTable attribute usage"Region keyword"Link to the fact table. The key isCustomerAttributes andDimgeographyThe primary key in the table. You can define"Customer region"A hierarchical structure that allows you to define the dimensions of customer sales by region. For more information, see

In the tasks of this topic"Region"A dimension is referenced by""Distributor"Dimension is linked to the fact table of distributor sales. This allows you to define the dimensions of dealers' sales by region.

Define distributor sales dimensions by region
Define distributor sales dimensions by region

2. In Solution Explorer, right-click"Multi-dimensional dataset"FolderAnalysis Services tutorialAnd then click"Browse".

4. Delete"Data"All the hierarchies in the pane, and then make sure that"Distributor sales-sales"The metric value appears in"Data"In the data area of the pane. If this metric value does not appear, add it"Data"Pane.

6. Set"Region"User-defined hierarchies"Metadata"In the pane"Region"Drag dimension"Data"Pane"Drag the row field here"Region.

   Note,"Distributor sales-sales"The metric value does not follow"Region"In the hierarchy"Country/region-region"Attribute members correctly determine the dimension, as shown in.

8. Open the data source view designer for the data source view of adventure works DW.

10. In"Graph OrganizationProgram"In the pane, select"Distributor sales"And viewDimgeographyTable andFactresellersalesRelationship between tables.

    Note that there is no direct link between these tables. HoweverDimresellerTable orDimsalesterritoryIndirect link of the table.

12. Double-clickDimgeographyTable andDimresellerThe arrow of the foreign key-primary key relationship between tables.

    In"Edit link"Note:GeographykeyColumnDimgeographyThe primary key in the table.DimresellerShows the foreign key in the table.

14. Click"Cancel"Switch to the multidimensional cube designer of the Analysis Services tutorial cube, and click"Dimension usage"Tab.

    Note,"Region"The current and"Internet sales"Measurement Value group or"Distributor sales"There is no relationship between measurement groups, as shown in.

16. Click"Customer"Dimension and"Internet sales"The"Full name"The ellipsis button (...).

    In"Define link"In the dialog box, note thatDimcustomerDimension table andFactinternetsalesBetween measurement value groups, according toCustomerkeyColumn defined"Regular"Link. So far, all the relationships you have defined in this tutorial are regular relationships.

    Shown"Define link"Dialog box, where the general link isDimcustomerDimension table andFactinternetsalesRelationship between measurement value groups.

18. Click"Cancel".

20. Click"Region"Dimension and"Distributor sales"The ellipsis button (...).

    In"Define link"In the dialog box, you can view the relationship between the currently undefined "region" cube dimension and the "distributor sales" measurement value group. The general relationship cannot be defined because there is no direct relationship between the dimension table of the region dimension and the fact data table of the distributor sales measurement value group.

22. In"Select link type"List, select"Referenced".

    You can define reference relationships by specifying the dimensions directly connected to the measurement value group table.", Analysis services can use this dimension to link a reference dimension to a fact data table. Then, link the reference dimension to the attributes of the intermediate dimension.

24. In"Intermediate dimension"List, select"Distributor".

    The base table of the region dimension is linked to the fact table through the base table of the distributor dimension.

26. In"Reference dimension attributes"List, select"Region keyword"And then try"Intermediate dimension attributes"Select from the list"Region keyword".

    Note,"Region keyword"Does not appear in"Intermediate dimension attributes"List. This is becauseGeographykeyThe column is not defined"Distributor"Attribute in the dimension.

    Displayed in"Define link"Dialog box,"Region keyword"Unavailable"Distributor"The intermediate dimension attribute of the intermediate dimension.

28. Click"Cancel".

In the next task, you will solve this problem by defining the attributes of the geographykey column in the distributor dimension.

Define intermediate dimension attributes and reference dimension relationships
Define intermediate dimension attributes and reference dimension relationships

2. Open"Distributor"Dimension designer, and then view"Data Source view"In the paneResellerView the columns in the table."Features"In the pane"Distributor"Attributes defined in the dimension.

   Note that although geographykey has been defined as a column in the reseller table, no dimension attribute is defined in the distributor dimension based on this column. Geography is defined as the dimension attribute in the "region" dimension because it links the basic table of the dimension to the key column of the fact data table.

4. Based onGeographykeyColumn direction"Distributor"Add a dimension named"Region keyword"And then define this property as a hidden unordered attribute that is not optimized.

   The region keyword attribute in the distributor dimension can only be used to link the region dimension to the reseller sales fact data table. Because it cannot be used for browsing, no visible value is defined for this attribute hierarchy. In addition, sorting and optimizing the Attribute Hierarchy can only negatively affect the processing performance. However, this attribute must be enabled to serve as a link between two dimensions.

6. Switch to the multidimensional cube designer of the Analysis Services tutorial cube, and click"Dimension usage"Tab, and then click"Distributor sales"Measure Value group and"Region"The ellipsis button (...).

8. In"Select link type"List, select"Referenced".

10. In"Intermediate dimension"List, select"Distributor".

12. In"Reference dimension attributes"List, select"Region keyword"And then"Intermediate dimension attributes"Select from the list"Region keyword".

    Note: Selected"Concrete"Check box. This is the default setting of the molap dimension. During processing, the link of a specific dimension attribute can be embodied in the molap structure of a dimension or store the link value between a fact table and the referenced dimension of each row. This will have little impact on the processing performance and storage requirements, but will enhance the query performance (sometimes significant ).

14. Click"OK".

    Note,"Region"The cube dimension is now linked"Distributor sales"Measurement Value group. This icon indicates that this link is a reference dimension link.

16. In"Dimension usage"Tab"Dimension"List, right-click"Region"And then click"RENAME".

18. Change the dimension name of the cube"Distributor region"And press enter to make the name change take effect.

    The dimension of this cube is now linked"Distributor sales"Therefore, you can benefit from explicitly defining a value group in a multi-dimensional dataset to avoid possible user confusion.

    Displayed in the defined"Region"Multidimensional Dataset dimension and"Distributor sales"After the relationship between measurement value groups"Dimension usage"Tab.

distributor sales dimension defined by region
define distributor sales dimensions by region

1. In"Generate"Click"Deployment Analysis Services tutorial".

2. After the deployment is complete, click"Browser"Tab, and then click"Reconnect".

3. In"Metadata"In the pane, expand"Distributor region", Right-click"Region"And then click"Add to row area".

   Please note that,"Distributor sales-sales"The measurement value is now based on"Region"In the User-Defined hierarchy"Country/region-region"The attribute correctly determines the dimension, as shown in.