Understanding Analysis Services Relationships using Dimension Usage – Devin Knight
If you build a dimension directly off a fact table (a fact dimension), I've always used Fact relationships but never known whether there was Many-To-Many Relationships in SSAS MultidimensionalIn "Analysis Services". SQL Server Analysis Services no For more information about fact relationships, see Define a Fact Relationship and Fact Relationship. A fact relationship is a relationship that exists between a dimension and a measure group that are both based on the same relational table. In the Adventure .
When this is a requirement you create the measure group and dimensions separately and then relate them in the Dimension Usage tab with a Fact relationship.
Fact dimension relationships
This type of relationship is also known as a degenerate dimension. An example when you may use this relationship type is with a measure group that stores sales orders.
In this case not only do you want to aggregate sales order data but you also want to slice by Order Numbers in the same table. Referenced relationship types are often used when your data warehouse design utilizes a snowflake schema design. A star schema is a more denormalized view of the data with dimensions all directly relating to the fact table. Imagine you run a data warehouse for a movie store yes I realize movie stores are pretty much extinct.
The fact table measure group holds sales that occur in the store and a dimension will all the movies that could possibly be sold. There is another dimension with the movie genre that is related to the movie dimension instead of the fact table.
If I want to have an independent dimension in my cube for genre then I must use a Referenced relationship type to relate it to the appropriate measure groups.
The dotted line in the diagram on the below images represents this.
- SSAS Interview Questions on Measures, Actions, and Storage
- Fact Relationship Type in SSAS
- Define a Fact Relationship and Fact Relationship Properties
Referenced dimensions are not optimal for performance and if you can optionally avoid them you should do so. Using a Many-to-Many relationship is done when a data warehouse design implements a bridge table between dimensions to appropriately represent all combinations of data.
A relationship between a dimension and a measure group consists of the dimension and fact tables participating in the relationship and a granularity attribute that specifies the granularity of the dimension in the particular measure group.
Regular Dimension Relationships A regular dimension relationship between a cube dimension and a measure group exists when the key column for the dimension is joined directly to the fact table. This direct relationship is based on a primary key-foreign key relationship in the underlying relational database, but might also be based on a logical relationship that is defined in the data source view.
A regular dimension relationship represents the relationship between dimension tables and a fact table in a traditional star schema design.
For more information about regular relationships, see Define a Regular Relationship and Regular Relationship Properties. Reference Dimension Relationships A reference dimension relationship between a cube dimension and a measure group exists when the key column for the dimension is joined indirectly to the fact table through a key in another dimension table, as shown in the following illustration. A reference dimension relationship represents the relationship between dimension tables and a fact table in a snowflake schema design.
When dimension tables are connected in a snowflake schema, you can define a single dimension using columns from multiple tables, or you can define separate dimensions based on the separate dimension tables and then define a link between them using the reference dimension relationship setting. The following figure shows one fact table named InternetSales, and two dimension tables called Customer and Geography, in a snowflake schema.
Defining a Fact Relationship | Microsoft Docs
You can create a dimension with the Customer table as the dimension main table and the Geography table included as a related table.
A regular relationship is then defined between the dimension and the InternetSales measure group. Alternatively, you can create two dimensions related to the InternetSales measure group: You can then relate the Geography dimension to the InternetSales measure group using a reference dimension relationship using the Customer dimension.
The following constraints apply to fact relationships: The granularity attribute must be the key column for the dimension, which creates a one-to-one relationship between the dimension and the facts in the fact table. A dimension can have a fact relationship with only a single measure group.
Note Fact dimensions must be incrementally updated after every update to the measure group that the fact relationship references. You then define the relationship between this new cube dimension and the Internet Sales measure group as a fact relationship. On the Welcome to the Dimension Wizard page, click Next.
On the Select Creation Method page, verify that the Use an existing table option is selected, and then click Next. In the Main table list, select InternetSales. On the Select Related Tables page, clear the check boxes beside all of the tables, and then click Next. On the Select Dimension Attributes page, click the check box in the header twice to clear all of the check boxes. The Sales Order Number attribute will remain selected because it is the key attribute.
On the File menu, click Save All.