Ssas cube referenced relationship problems

BI Future Blog: SSAS : Select Facts with Reference and Many2Many relationships

ssas cube referenced relationship problems

SSAS Multidimensional – Part III – Complex Business Problems In tabular modeling, there is no need to create reference relationships. that will be returned by the drillthrough action and the cube space where the action will be enabled. Jan 9, The correctBrowse sales cube can be easily explained. up your reference dimension relationship between Georgaphy and Internet Sales? attributes in the customer ssas dimension (red boxes in screenshot below). Jul 15, As a part of designing an Analysis Services cube you must define how each The Fact relationship type is used when a measure group is also.

Lesson 5-1 - Defining a Referenced Relationship

This leads to slower cube processing but better cube query performance. There is, however, an important thing to realize: The materialized relationship link between the facts and the correct attribute members is created during the processing of the partition, not the dimension!

ssas cube referenced relationship problems

Why is this important? Imagine we need to update the relationships between the facts and the referenced dimension.

We can no longer simply process the dimension.

Attribute Relationship Types in SSAS Cube

The link to the older referenced dimension attribute member still exists in the partition! The partition must be processed! If the relationships changed in the referenced dimension, you could find yourself forced to process more than just the most recent partition in order to ensure the relationships between the facts and the referenced dimension are correct.

We could leave the option to materialize the relationship unchecked. The downside to this is that query performance will suffer since the member will need to be looked up on the fly.

The upside is that processing will be faster and we no longer need to process the partition to update the relationship since its looked up at query time. But there is also a gotcha with using un-materialized referenced dimension relationships. Aggregations can not be created for a referenced dimension when the relationship is not materialized.

This is by design. An aggregation should not be created which is possible to do manually for an un-materialized referenced dimension relationship because the relationship is resolved at query time. But what happens with the TSQL query during processing of the measure group? This referenced relationship between the Reseller Sales measure group is materialized. SQL Server Analysis Services Azure Analysis Services Up to this point in the tutorial, each cube dimension that you defined was based on a table that was directly linked to the fact table for a measure group by a primary key to foreign key relationship.

In the tasks in this topic, you link the Geography dimension to the fact table for reseller sales through the Reseller dimension, which is called a reference dimension. This enables users to dimension reseller sales by geography. Remove all hierarchies from the data pane, and then verify that the Reseller Sales-Sales Amount measure appears in the data area of the data pane.

Add it to the data pane if it is not already there. From the Geography dimension in the metadata pane, drag the Geographies user-defined hierarchy to the Drop Row Fields Here area of the data pane.

Notice that the Reseller Sales-Sales Amount measure is not correctly dimensioned by the Country-Region attribute members in the Regions hierarchy. In the Diagram Organizer pane, view the relationship between the Geography table and the ResellerSales table.

Notice that there is no direct link between these tables.

ssas cube referenced relationship problems

However, there is an indirect link between these tables through either the Reseller table or the SalesTerritory table. Double-click the arrow that represents the relationship between the Geography table and the Reseller table.

In the Edit Relationship dialog box, notice that the GeographyKey column is the primary key in the Geography table and the foreign key in the Reseller table.

Gotchas With Referenced Dimensions | Data and Analytics with Dustin Ryan

Notice that the Geography cube dimension does not currently have a relationship with either the Internet Sales measure group or the Reseller Sales measure group.

Click the ellipsis button In the Define Relationship dialog box, notice that a Regular relationship is defined between the DimCustomer dimension table and the FactInternetSales measure group table based on the CustomerKey column in each of these tables.

ssas cube referenced relationship problems

All the relationships that you have defined within this tutorial up to this point have been regular relationships. The following image shows the Define Relationship dialog box with a regular relationship between the DimCustomer dimension table and the FactInternetSales measure group table. In the Define Relationship dialog box, notice that no relationship is currently defined between the Geography cube dimension and the Reseller Sales measure group. You cannot define a regular relationship because there is no direct relationship between the dimension table for the Geography dimension and the fact table for the Reseller Sales measure group.

In the Select relationship type list, select Referenced. You define a referenced relationship by specifying a dimension that is directly connected to the measure group table, called an intermediate dimension, that Analysis Services can use to link the reference dimension to the fact table.

You then specify the attribute that links the reference dimension to the intermediate dimension. In the Intermediate dimension list, select Reseller.