SAS OLAP Cubes: Percent of Total MDX Calculation
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
, [Measures].[SalesSum]) = 0
, [Measures].[SalesSum]) = NULL
), 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:
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.
Learn all about SAS BI
You can learn more about SAS Business Intelligence from the "SAS BI Bible." Take a peek inside the Building Business Intelligience with SAS book.
Attending SESUG 2015?
If you are planning to attend SESUG 2015 in beautiful Savannah, Georgia on Sept 29 then join me for my talk about data visualization "How to be the MacGyver of Data Visualization"! I'll show you some cool ways to visualize data with SAS Visual Analytics as well as some traps to avoid.
Latest posts by Steve Overton (see all)
- Need to Migrate Users, Groups, and Roles between SAS Environments? - November 5, 2013
- BulkLoading with PostgreSQL available in SAS 9.4 - October 22, 2013
- Update Table Metadata Using Base SAS Code - December 19, 2012
- SAS Administration: Fetch Data Faster across ODBC - November 1, 2012
- SAS Code: Simple Macro to Benchmark Data Performance - August 8, 2012