Home > SSAS > SSAS – Data Granularity

SSAS – Data Granularity

Data granularity is one of the main considerations in data modeling besides integrating different business units’ information. Sometimes the granularity affects the final results at reports so need a detail analysis at data is required. Here discussing a simple scenario of data with Header and Detail records as practical as possible as it is a theoretical topic.

Header record:

Detail record:

Detail FACT record:
While loading the data into FACT table, the header and detail tables will go with LEFT JOIN ON BusinessKey
We will get the result set at target FACT tables as

Possible business scenarios:

Consider for one record BusinessKey = “100001”

1. What is the Total Price for a given date

= SUM(TotalPrice) for the date
Value = 330

Alternate approach is to AGGREGATION using ItemPrice

= SUM(ItemPrice)+SUM(Total GST)
Value = 130

2. What is the Total GST Price for a given date

= SUM(Total GST) for the date
Value = 30

As we could see, in all of the scenarios, we are getting wrong results this is by the processing of aggregations of Analysis Services on the measures.

Solutions:

1. MDX SCOPE()
Using the SCOPE() statement, we could over write the value of Total values with 0 other than the first value so total value will be equal to the expected values
However, SCOPE() statement will have the performance issues in case of large record sets.

2. Calculated column at DSV
We could introduce a calculated column at FACT table at DSV in Cube with same logic as have the value of TotalPrice at first record and remaining with 0 so we will expect the results as expected

3. Calculated field at ETL with physical column at Datamart
This is very similar to the option 2, while having the physical column at FACT table. This is the best option in terms of performance and maintenance.
The solution will have the FACT records as

Total value of 110 in either of calculations, which is expected and required results from the solution.

Points to consider:

1. The solution is useful if we don’t have any other ways to get the cumulative values from detail records
2. If we have detail records at each details item level, we could use of those records to get the header total values but most of the source systems don’t maintain and if you are having detail at details records level, your just lucky with well design of OLTP source systems.
3. Please consider this solution to include the thin client access for ad-hoc reporting.

Advertisements
Categories: SSAS Tags:
  1. Jarret
    May 14, 2013 at 3:32 am

    Would you mind sharing your code of how you got the 0 (on all but one) for the ‘Calculated Total Price’ and ‘Calculated Total GST’ fields?

    • May 14, 2013 at 7:29 pm

      Hi Jarret, Trick is to use row_number function to identify the one header record with business keys then make one row as existing value else go with 0.
      Let me know if requires any details

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: