Fact dimension relationships for SQL Server Analysis Service

What is a fact (fact) dimension relationship

Developers who have developed SSAS cube should know that there is a type of fact relationship in Cube's dimension usage, as shown in:

The fact dimension relationship is just like the description in the red box above, which refers to a table even if the fact table is a dimension table, which is usually referred to as a "degenerate dimension" in the Data Warehouse. For example, in this case our factinternetsales (factinternetsales table in the data source view FriendlyName is the Internet Sales Facts, So the FactInternetSales table in cube shows the name of the Internet sales facts as shown in the table is both the fact table and the dimension table, as shown in the dimension of the Internet sales Order Details are built using the columns of the FactInternetSales table.

But in the cube we find that the FactInternetSales table is also the fact table:

So the FactInternetSales table is both a fact table and a dimension table, and the dimension Internet sales Order details and measure group Internet Sales Facts are all references to the same database table FactInternetSales, then we can use the dimension Internet Sales Order details and measure group in the Dimension usage Internet Sales The relationship between facts is defined as a fact relationship as shown:

From here we can see that the fact relationship is not like regular (regular) and referenced (reference) relationships that require setting the dimension table and the fact table to be related by what fields, and the fact relationship does not need to be set for any associated fields. This is because the fact relationship defaults to associating the KeyColumns column in the key property of the dimension table with the primary key column of the fact table .

As shown, we see that the key property of the dimension Internet Sales Order Details is the Internet sales order

And we can see from the key property that the KeyColumns in Internet Sales Order contains the two columns of data for the FactInternetSales table SalesOrderNumber and Salesorderlinenumber

The primary key column of the fact table FactInternetSales is also the SalesOrderNumber and Salesorderlinenumber columns

So the dimension Internet sales Order details and measure group Internet Sales Facts set a fact relationship, is actually using the FactInternetSales table of the SalesOrderNumber and salesorderlinenumber these two columns to do a join to perform the related operations of cube.

We are using the MDX statement to view the "Sales Amount" data of the measure group Internet Sales Facts to the dimensions of Internet sales Order details successfully.

The dimension of a fact dimension relationship can contain more than one table

In fact, the dimension Internet Sales Order details can be set to a fact relationship with the measure group Internet Sales Facts If it contains a table other than the FactInternetSales table. As shown, we added the salesterritoryregion column of the DimSalesTerritory (FriendlyName is Sales Territory) table to the dimension Internet Sales Order Details as a property of sales Territory region, so in fact now the dimension Internet Sales Order details use two tables FactInternetSales and DimSalesTerritory.

As you can see, we used the MDX statement to successfully cut the measure group with the Sales Territory region property of the Internet Sales Order details dimension Internet Sales facts [Sales Amount] Data.

Incorrectly configuring a fact dimension relationship

As we said before, the fact relationship is done by using the KeyColumns column of the key property of the dimension and the primary key column of the fact table for the cube operation. Then if the key property of the dimension is set to the wrong KeyColumns column, then the fact relationship will be incorrectly evaluated when the cube is being processed. For example, we randomly changed the key property of the dimension Internet sales Order details Internet Sales The KeyColumns in order is the Shipdatekey and UnitPrice two columns of the FactInternetSales table.

We can see that in the cube's dimension usage, the fact relationship immediately has an error, suggesting that the data type of the primary key column of the fact table and the dimension table key property are inconsistent with the data type of the KeyColumns column.

So setting the KeyColumns column correctly in the key attribute of the dimension table is critical to the fact relationship and is especially prudent.

What is the difference between a fact relationship and a regular (regular) relationship

Before we say that the fact dimension relationship is a cube calculation using the Keycolmuns column and the primary key column of the fact table in the dimension table key property, we can actually change the fact relationship to the regular (regular) dimension relationship. Because the regular relationship is actually the key-value join for the dimension table and the fact table, the cube operation, except that you specify which columns to use as join columns in the regular relationship. For example, we'll change the relationship between the dimension Internet Sales order details and the measure group Internet Sales facts from fact to regular, and specify the use of the dimension Internet Sales order Details in the Key property keycolumns column SalesOrderNumber and salesorderlinenumber of Internet Sales Order, A join operation that maps to the SalesOrderNumber and Salesorderlinenumber columns of the fact table to do regular relationships.

After we have processed the cube, we use the same MDX as the previous query to get the same query results as the fact dimension relationship:

Fact dimension relationships for SQL Server Analysis Service