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.