MDX is a powerful tool for building custom calculations in SAS OLAP cubes. Here is a good example of computing the percent of total for a given measure and given hierarchy in a dimension. This code is written as part of the PROC OLAP procedure.
DEFINE Member “[SGF].[Measures].[Percent of Total Sales]” AS
‘(
[Product].[Product].CurrentMember, [Measures].[SalesSum])/
if(([Product].[Product].CurrentMember.Parent
, [Measures].[SalesSum]) = 0
OR ([Product].[Product].CurrentMember.Parent
, [Measures].[SalesSum]) = NULL
, ([Product].[Product].CurrentMember
, [Measures].[SalesSum])
, ([Product].[Product].CurrentMember.Parent
, [Measures].[SalesSum])
), FORMAT_STRING=”PERCENT10.2″ ‘ ;
The important thing to realize here is that this MDX code is specific to a hierarchy. I have made the hierarchy references bold in the example above to show this. I will go into how to make this part of the code dynamic in a future post. This MDX calculation does basic division to compute a percentage by using the “CurrentMember” and “Parent” MDX functions to dynamically reference aggregations at the parent or “total” level for a given measure.
Here is the example output:
SAS BI OLAP Cube MDX Calculation Examples
Example code and data can be found here: http://stephenoverton.net/SASCode/SGF2011/.
Bryan Stines and I wrote a paper for SAS Global Forum 2011 called Measures, Members, and Sets, Oh My! Advanced OLAP Techniques, which also describes this technique.
Wait There’s More!
In the upcoming Building Business Intelligence with SAS book, you can learn more about using SAS OLAP Cube Studio, SAS Stored Process, and building dashboard using the SAS BI Dashboard.
Passionate about productivity, quality, strong leadership, and intelligent decisions. Expert in Business Intelligence and Data Warehousing solutions. Primarily work with SAS 9 Enterprise Business Intelligence platform. Loving life and enjoying working for Zencos Consulting (http://www.zencos.com). Enjoy the knowledge!
