Regular Relationship Type in SSAS

Friends,

When the Dimension table and the Fact Table(also called as Measure Group Table) are directly related(i.e Foreign Key Relation) then that type of relationship is called as Regular Relationship Type. Lets take a small example from a very well known database to most of us i.e AdventureWorks.

I am taking the following tables to demonstrate Regular relationship type.

1) FactInternetSales

2) DimProduct

The above two tables have foreign key relationship on the column “ProductKey” and hence to fetch data from both the table the query looks like –

Select * from FactInternetSales
INNER JOIN DimProduct on FactInternetSales.ProductKey = DimProduct.ProductKey.

We used INNER JOIN to join the above two table and the same relationship can be provided  in SSAS cube which is called as Regular Relationship Type. If we want to relate the above query with SSAS Cube implementation then DimProduct table is the Dimension Table in Cube and FactInternetSales is Fact Table and the Regular relationship Type is the JOIN which we provided in the above query. PFB the screen with our providing relationship between dimension and fact.

No Relationship given

Now lets see the steps to be followed to provide Regular Relationship Type between a dimension and a fact. Use the Define Relationship dialog box to define a relationship between a cube dimension and a measure group in Cube Designer. You can display the Define Relationshipdialog box by clicking … on a cell in the Grid pane on the Dimension Usage tab in Cube Designer.

1) Select the relationship Type as “Regular”.

2) Select the attribute that defines the granularity of the measure group with respect to the dimension. This attribute is usually the key attribute of the dimension.(ProductKey in our case).

3) Then it automatically displays Dimension Table and Fact Table.

4) It also displays the Dimension Table columns in RELATIONSHIP GRID and you have to select appropriate Measure Group Columns.

PFB the screenshot of the same.

5) After selecting Measure Group Columns click OK.

That’s it .. The regular relationship is set between a dimension and fact table.

Note – If you provide relationship between dimension table and fact table in SSAS DSV(Data Source View) and the relation ship is direct then Regular will be automatically set by SSAS Server when we add Dimension and Fact. So the best and easiest way is to give relationship in DSV level.

Regards,

Roopesh Babu V