SAS OLAP Cubes: Using PROC SQL and MDX to Query OLAP Cubes
SAS PROC SQL has a lot of neat ways you can extract and manipulate data. In my experience, SAS users typically write PROC SQL to query SAS datasets or relational tables in databases. You can also write multi-dimensional queries against OLAP cubes using the MDX expression language. This makes you REALLY think outside the box when you have to construct what measures to aggregate and how they will be sliced on rows and columns. A BIG requirement to using this technique is the ability to write and understand MDX expressions. If you do not understand MDX, turn back now! 😎
Benefits of Proc SQL with SAS OLAP
- Managers want to minimize data infrastructure for environments with a lot of OLAP technology already in place. Rather than create more SAS datasets or RDBMS tables, which represent summary data, SAS developers can query OLAP cubes on-the-fly for better performance on high volumes of data. Or a summary dataset can be produced using a more modular approach rather than going through the same aggregation a SAS program would go through to aggregate data.
- Programmers want to use some of the advanced capabilities in the MDX language like time-series, trending, and forecasting functions.
- Query performance is a must have at run time. I have used PROC SQL against an OLAP cube in SAS Stored Process and SAS BI Dashboard applications where run time performance needed to be very fast. These queries can also be parameterized using Macro variables, but I will save that for another discussion…
Basic Skeleton Structure of MDX Query
Thoughts and Considerations
I have described a trick you can use to let Enterprise Guide write the MDX code for you! So rather than loose sleep over how to structure an MDX query, take the easier route and let SAS Enterprise Guide put you to sleep better at night!
Check out SAS Support for more documentation and technical considerations on using PROC SQL to query an OLAP cube.
You could also use PROC SQL to do a number of other interesting things like create macro variables and lists for advanced programming procedures. I will go into more fun PROC SQL tricks in future blog posts…
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