8人捕鱼机厂家

書名： Microsoft SQL Server 2014 Business Intelligence Development Beginner’s Guide
作者名： Reza Rad
本章字數： 734字
更新時間： 2021-08-13 17:55:24

Time for action – adding a Fact relationship

As a business requirement, you need to add the order line number as a dimension attribute and show it in the browser. As you know, in the current cube, we have an Internet Sales measure group, which has some measures such as order quantity and sales amount, but we need to fetch the order line number from FactInternetSales as a dimension attribute (this is degenerate dimension, which is explained clearly in Chapter 1, Data Warehouse Design).

Perform the following steps to add a Fact relationship:

Open the Data Source View designer and explore data in the FactInternetSales table. You will see that there is a SalesOrderNumber and SalesOrderLineNumber column there. In this exercise, we will create a dimension based on these columns.
Add a Named Calculation in the FactInternetSales table and name it Order Number. Write the following expression to calculate the concatenation of the order line and order number:
```
'Order Number: '+SalesOrderNumber+', Order Line Number: '+convert(varchar(max),SalesOrderLineNumber)
```
Explore data in the FactInternetSales table. You will see that Order Number creates a concatenation of two order line columns such as Order Number: SO43697 and Order Line Number: 1.
In the Solution Explorer option, right-click on the dimensions folder and select New Dimension.
In the Select Creation Method step, choose use an existing table.
Choose FactInternetSales as the table, leave the SalesOrderNumber and SalesOrderLineNumber columns in the key column area, and choose Order Number as Name column, as shown in the following screenshot:
In the Select Related Tables step, uncheck all the tables.
In the Select Dimension Attributes step, uncheck all the attributes and only check the Sales Order Number option.
In the last step, rename the dimension to Dim Internet Sales Order.
The new dimension will be created under the dimensions folder in Solution Explorer. Go to the cube designer; in the Cube Structure menu, you will see that the new dimension does not exist there. We will add this dimension to the cube in the next steps.
Go to the Dimension Usage tab in the cube designer (the second tab). Click on the third icon on the top left-hand side, which is Add Cube Dimension.
In the Add Cube Dimension dialog box, choose Dim Internet Sales Order.
In the matrix, you will see that this dimension is related to the Internet Sales measures group based on the Sales Order Number. When you double-click on the Sales Order Number measure group, you will see that the relationship type is already set as Fact, as shown in the following screenshot:
After deploying and processing the dimension, check the result in the cube Browser. You can view Sales Order Number as a dimension attribute and see Sales Amount and other measures sliced and diced by this attribute.

What just happened?

One of the most important parts of modeling in SSAS Multidimensional is setting up the correct relationship between measure groups and dimensions. Dimension Usage is a tab where we can create, modify, or remove this kind of a relationship.

In this example, we used a descriptive column in the FactInternetSales table, and named it Sales Order Number (which is a concatenation of the order number and order line number). If you read Chapter 1, Data Warehouse Design, you will know about the degenerate dimension and you know that they are in Fact table's granularity but they don't have their own separate dimension table. This is an example of a degenerate dimension.

In this example, we created a SSAS database dimension based on the FactInternetSales table. In step 6, we set the key columns and the name column for the same. Then, we created a relationship in the cube between the Internet Sales measure group and this new dimension (step 11 and 12). As the source table for both the dimension and measure group is the same, the cube designer considered this relationship as a Fact table and set the granularity of the relationship as the key column of the dimension (step 13).

There are different types of relationships. In the previous example, you saw the Fact relationship, which is useful when the dimension is made up of a Fact table. The next table shows information about other relationships. By choosing each relationship, you will see an image that illustrates the relationship between the measure group and dimension (look at the red surrounded area in the screenshot of step 13).

官术网_书友最值得收藏!

Microsoft SQL Server 2014 Business Intelligence Development Beginner’s Guide

Time for action – adding a Fact relationship

What just happened?