Defining a Many-to-Many Relationship

Artigo
12/03/2008

When you define a dimension, typically each fact joins to one and only one dimension member, whereas a single dimension member can be associated with many different facts. For example, each customer can have many orders but each order belongs to a single customer. In relational database terminology, this is referred to as a one-to-many relationship. However, sometimes a single fact can join to multiple dimension members. In relational database terminology, this is referred to as a many-to-many relationship. For example, a customer may have multiple reasons for making a purchase, and a purchase reason can be associated with multiple purchases. A join table is used to define the sales reasons that relate to each purchase. A Sales Reason dimension constructed from such relationships would then have multiple members that relate to a single sales transaction. Many-to-many dimensions expand the dimensional model beyond the classic star schema and support complex analytics when dimensions are not directly related to a fact table.

In Microsoft SQL Server 2005 Analysis Services (SSAS), you define a many-to-many relationship between a dimension and a measure group by specifying an intermediate fact table that is joined to the dimension table. An intermediate fact table is joined, in turn, to an intermediate dimension table to which the fact table is joined. The many-to-many relationships between the intermediate fact table and both the dimension table in the relationship and the intermediate dimension creates the many-to-many relationships between members of the primary dimension and measures in the measure group that is specified by the relationship. In order to define a many-to-many relationship between a dimension and a measure group through an intermediate measure group, the intermediate measure group must share one or more dimensions with the original measure group.

With a many-to-many dimension, values are distinct summed, which means that they do not aggregate more than once to the All member.

Note

In order to support a many-to-many dimension relationship, a primary key–foreign key relationship must be defined in the data source view between all the tables that are involved. Otherwise, you will not be able to select the correct intermediate measure group when you establish the relationship in the Dimension Usage tab of Cube Designer.

For more information, see Dimension Relationships, and Defining a Many-to-Many Relationship and Many-to-Many Relationship Properties.

In the tasks in this topic, you define the Sales Reasons dimension and the Sales Reasons measure group, and you define a many-to-many relationship between the Sales Reasons dimension and the Internet Sales measure group through the Sales Reasons measure group.

Adding Required Tables to the Data Source View

To add required tables to the data source view

Open Data Source View Designer for the Adventure Works DW data source view.
Right-click anywhere in the Diagram Organizer pane, click New Diagram, and specify Internet Sales Order Reasons as the name for this new diagram. For more information, see Working with Diagrams in a Data Source View (Analysis Services).

A new diagram is created with no tables, and appears in the Diagram pane.
Drag the InternetSales (dbo.FactInternetSales) table to the Diagram pane from the Tables pane.
Right-click anywhere in the Diagram pane, and then click Add/Remove Tables.
In the Add/Remove Tables dialog box, add the DimSalesReason table and the FactInternetSalesReason table to the Included objects list, and then click OK.

Notice that the primary key–foreign key relationships between the tables that are involved are established automatically because those relationships are defined in the underlying relational database. If these relationships were not defined in the underlying relational database, you would have to define them in the data source view.
On the Format menu, point to Auto Layout, and then click Diagram.
In the Properties window, change the FriendlyName property of the DimSalesReason table to SalesReason, and then change the FriendlyName property of the FactInternetSalesReason table to InternetSalesReason.
In the Tables pane, expand InternetSalesReason (dbo.FactInternetSalesReason), click SalesOrderNumber, and then review the DataType property for this data column in the Properties window.

Notice that the data type for the SalesOrderNumber column is a string data type.
Review the data types for the other columns in the FactInternetSalesReason table.

Notice that the data types for the other two columns in this table are numeric data types.
In the Tables pane, right-click InternetSalesReason (dbo.FactInternetSalesReason), and then click Explore Data.

Notice that, for each line number within each order, a key value identifies the sales reason for the purchase of that line item, as shown in the following image.

Defining the Intermediate Measure Group

To define the intermediate measure group

Switch to Cube Designer for the Analysis Services Tutorial cube, and then click the Cube Structure tab.
Right-click anywhere in the Measures pane, and then click New Measure Group. For more information, see Defining and Configuring a Measure Group.
In the New Measure Group dialog box, select InternetSalesReason in the Select a table from the data source view list, and then click OK.

Notice that the Internet Sales Reason measure group now appears in the Measures pane.
Expand the Internet Sales Reason measure group.

Notice that only a single measure is defined for this new measure group, the Internet Sales Reason Count measure.
Select Internet Sales Reason Count and review the properties of this measure in the Properties window.

Notice that the AggregateFunction property for this measure is defined as Count instead of Sum. Analysis Services chose Count because the underlying data type was a string data type. The other two columns in the underlying fact table were not selected as measures because Analysis Services detected them as numeric keys instead of as actual measures. For more information, see Defining Semiadditive Behavior.
In the Properties window, change the Visible property of the Internet Sales Reason Count measure to False.

This measure will only be used to join the Sales Reason dimension that you will define next to the Internet Sales measure group. Users will not browse this measure directly.

The following image shows the properties for the Internet Sales Reason Count measure.

Defining the Many-to-Many Dimension

To define the many-to-many dimension

On the Cube Structure tab in Cube Designer for the Analysis Services Tutorial cube, right-click anywhere in the Dimensions pane, and then click Add Cube Dimension.
In the Add Cube Dimension dialog box, click New dimension.

The Dimension Wizard opens.
On the Welcome to the Dimension Wizard page, click Next.
On the Select Data Source View page, click Next to select the data for the dimension from the Adventure Works DW data source view.
On the Select the Dimension Type page, click Next to specify that the dimension will be a standard dimension.
On the Select the Main Dimension Table page, select dbo.DimSalesReason in the Main table list, select SalesReasonName in the Column containing the member name (optional) list, and then click Next.
On the Select Dimension Attributes page, clear the Sales Reason Alternate Key attribute check box, change the name of the Sales Reason Reason Type attribute to Sales Reason Type, and then click Finish.
On the Completing the Wizard page, click Finish to create the Sales Reason dimension, and then click OK to add the newly created database dimension to the Analysis Services Tutorial cube as a cube dimension.
Open Dimension Designer for the Sales Reason dimension.
In the Hierarchies and Levels pane, create a Sales Reasons hierarchy that contains the Sales Reason Type level and the Sales Reason level, in that order.
In the Properties window, define All Sales Reasons as the value for the AllMemberName property of the Sales Reasons hierarchy.
Define All Sales Reasons as the value for AttributeAllMemberName property of the Sales Reason dimension.

Defining the Many to Many Relationship

To define the many-to-many relationship

Switch to Cube Designer for the Analysis Services Tutorial cube, and then click the Dimension Usage tab.

Notice that the Sales Reason dimension has a regular relationship defined with the Internet Sales Reason measure group, but has no relationship defined with the Internet Sales or Reseller Sales measure groups. Notice also that the Internet Sales Order Details dimension has a regular relationship defined with the Internet Sales Reason dimension, which in turn has a Fact Relationship with the Internet Sales measure group. If this dimension was not present (or another dimension with a relationship with both the Internet Sales Reason and the Internet Sales measure group were not present), you would not be able to define the many-to-many relationship.
Click the ellipsis button (...) in the cell at the intersection of the Internet Sales measure group and the Sales Reason dimension.
In the Define Relationship dialog box, select Many-to-Many in the Select relationship type list.

You have to define the intermediate measure group that connects the Sales Reason dimension to the Internet Sales measure group.
In the Intermediate measure group list, select Internet Sales Reason.

The following image shows the changes in the Define Relationship dialog box.
Click OK.

Notice the many-to-many icon that represents the relationship between the Sales Reason dimension and the Internet Sales measure group, as shown in the following image.

Browsing the Cube and the Many-to-Many Dimension

Browsing the cube and the Many-to-Many Dimension

On the Build menu, click Deploy Analysis Services Tutorial.
When deployment has successfully completed, switch to the Browser tab in Cube Designer for the Analysis Services Tutorial cube, and then click Reconnect.
Remove each level of the Internet Sales Orders user hierarchy from the row area of the Data pane and replace it with the Sales Reasons user hierarchy from the Sales Reason dimension.
Verify that the Internet Sales-Sales Amount measure still appears in the data area of the Data pane and that the Adam Powell member of the Customer dimension appears in the Filter pane. If not, add the Internet Sales-Sales Amount measure to the data area and add the Adam Powell member of the Customer Geography user-defined hierarchy to the subcube area (Australia-Queensland-Brisbane-4000).
In the Filter pane, the top pane in the center of the design area, change the Filter Expression to Queensland, by clearing the check box for Adam Powell and selecting the check box for Queensland in the Filter Expression list.
Expand each member of the Sales Reason Type level to review the dollar values that are associated with each reason a customer in Queensland gave for their purchase of an Adventure Works product over the Internet.

Notice that the totals that are associated with each sales reason add up to more than the total sales. This is because some customers cited multiple reasons for their purchase.

The following image shows the Filter pane and Data pane of Cube Designer.

Next Task in Lesson

Defining Dimension Granularity within a Measure Group

Partilhar via