One of the most useful aspects of a Business Intelligence system is the ability to add calculations to create new measures. This centralises the logic of the calculation into a single place, ensuring consistency and standardisation across the user base.
By way of example, a simple calculation for profit (Income – Expenditure) wouldn’t be provided by the source database and historically would be implemented in each and every report. In a data warehouse and/or cube we can create the calculation in a single place for everyone to use.
This post highlights some of methods of doing this, each with their respective pros and cons.
Calculated Members in SSAS Cube
SSAS provides a ‘Calculations’ tab in the cube designer which allows you to create new measures using MDX. You can use any combination of existing measures and dimension attributes, along with the plethora of MDX functions available to create highly complex calculations.
Pros:
- Very complex calculations can be created using all available MDX functions
- No changes are required to the structure of the data warehouse
- Changes to the calculation will apply to every record, historic and new
- The results are not stored in the warehouse or cube, so no extra space is required
- New calculations can be added without having to deploy or reprocess the cube
- Calculations can be scoped to any level of aggregation and granularity. Different calculations can even be used for different scopes
- Calculations can easily combine measures from different measure groups
Cons:
- The calculation will not make use of SSAS cube aggregations, reducing performance
- SSAS drill through actions will not work
- The calculation results are not available in the data warehouse, only the cube
SQL Calculations in the Data Source View
There’s a layer in-between the data warehouse and the cube called the data source view (DSV). This presents the relevant tables in the warehouse to the cube, and can be used to enhance the underlying data with calculations. This can either be the dsv layer within the cube project, or I prefer to create SQL Server views to encapsulate the logic.
Pros:
- No changes are required to the table structure of the data warehouse
- Calculations use SQL not MDX, reducing the complexity
- Changes to the calculation will apply to every record, historic and new
- The calculation will make full use of SSAS cube aggregations
- SSAS drill through actions will work
- The results are not stored in the warehouse, so the size of the database does not increase
Cons:
- The cube must be redeployed and reprocessed before the new measure is available
- The results of the calculation must be valid at the granularity of the fact table
- The calculation results are not available in the data warehouse, only the cube
Calculate in the ETL process
Whilst bringing in data from the source data systems, it sometimes makes sense to perform calculations on the data at that point, and store the results in the warehouse.
Pros:
- The results of the calculation will be available when querying the warehouse as well as the cube
- In the ETL pipeline you can import other data sources (using lookups etc.) to utilise other data in the calculation
- If the calculation uses time based data, or data valid at a specific time (i.e. share price) then by performing the calculation in the ETL, the correct time based data is used, without having to store the full history of the underlying source data
- The calculation will make full use of SSAS cube aggregations
- SSAS drill through actions will work
Cons:
- You have to be able to alter the structure of the data warehouse, which isn’t always an option.
- The results are stored in the warehouse, increasing the size of the database
- The results of the calculation must be valid at the granularity of the fact table
- If the calculation logic changes, all existing records must be updated
In Conclusion
If the calculation is valid for a single record, and it would be of benefit to have access to the results in the warehouse, then perform the calculation in the ETL pipeline and store teh results in the warehouse.
If the calculation is valid for a single record, and it would not be of benefit to have the results in the warehouse, then calculate it in the data source view.
If the calculation is too complex for SQL, requiring MDX functions, then create an MDX calculated measure in the cube.
[…] This post was mentioned on Twitter by Purple Frog IT. Purple Frog IT said: RT @PurpleFrogSys: [New Frog Blog] Creating calculations in SSAS cubes http://bit.ly/dSKgEw […]
Good evening Alex.
I often read your useful blogs’posts and comments.
I write here for a question about calculations in Cubes.
My big trouble i have with GRAND TOTALS not correct with calculated member.
I use SSAS 2008 R2 version.
I figured out that using Excel , Grand Totals not work well with calculated members.
I read a lot of articles on web about it..but none solved my problem.
I have a Sales Cube.
I have a real measure [Importo Venduto] and a calculated member [Punti Maturati]
I need to have both measures about a list of customers that i have in a another dimensions called CLIENTI…
CREATE MEMBER CURRENTCUBE.[Measures].[Punti Maturati]
AS VBA!Int([Measures].[Importo Venduto] / 10),
FORMAT_STRING = “Standard”,
VISIBLE = 1 , DISPLAY_FOLDER = ‘Calcoli Dati Venduto Premi TopClass’ ;
In Excel i have this situation :
CLIENTI Importo Venduto Punti Maturati
A 92.82 9
B 459.07 45
GRAND TOTAL (wrong) 551.89 55 (It should be 54 !!!!)
I understood that also for GRAND TOTAL , Excel/SSAS compute the same formula.
In fact 551.89 / 10 = 55 and not 9+45 = 54.
How can i do to solve problem ???
I read a lot of programmers had the same problem…
I read to use SCOPE Statement i did with in Script Section.
Besides i read to create a new real measure that do calculation i need …
Scope ([Measures].[New Real Measure]);
Scope ([Clienti].[Clienti].children);
This = VBA!Int([Measures].[Importo Venduto] / 10);
End Scope;
End Scope;
I change a lot of times this expression ,,,
I use descendants , existing, but nothing…
I think it is possibile to ha ve a solution,,,i hope in your very good experience.
Regards in advance…
Hi Mauro
To understand the problem we need to look at where SSAS does the rounding. And you’re right, it adds up all the raw numbers and then does the rounding. You want the rounding to happen first and then the aggregation to take place.
The problem with this is that you will get very very poor performance, as the cube will always need to calculate data at the leaf level and then aggregate – which is very bad.
So your best option is to round the values in the DSV, or in the source query, before it even gets into the cube. So you’ll end up with two real measures; Importo Venduto and Punti Maturati, neither of which need any MDX calculation.
Once you do this, SSAS can just aggregate the data correctly without having to worry about the rounding.
Regards
Alex