Best Practices: To Write or Not to Write PROC OLAP… That is the Question
There are 2 ways to develop OLAP cubes in the SAS EBI platform: using SAS OLAP Cube Studio or by writing the PROC OLAP statement by hand and submitting the SAS program. There are pros and cons for both that I will go through in this post. Personally I prefer writing the PROC OLAP statement(s) myself but I will use SAS OLAP Cube Studio from time-to-time to get the basic skeleton code written for me if I need it. Different personas will have their own preferences.
Using SAS OLAP Cube Studio
Using SAS OLAP Cube Studio is fairly straightforward using the wizard driven approach. Going through the wizard allows you to build a cube and have most of the functionality you need then it writes the code for you.
- Easier and faster to create a cube
- Minimal complexity for beginners
- Maintain code in metadata
- Hard to add additional code to enhance the cube build process
- Hard to manage things like macros and pre-processing steps written in Base SAS
You can find documentation on usage from SAS Support.
You can also find more OLAP Cube Studio usage notes in Tricia’s post.
Writing PROC OLAP Manually
Writing PROC OLAP code directly, in something like SAS Enterprise Guide, is much more involved but in the end gives more advanced developers much more flexibility because you have the full power of Base SAS and Macro programming. Like I mentioned above, to start a cube I normally use SAS OLAP Cube Studio to get the skeleton PROC OLAP statement written, then I continue to add dimensions and measures on my own through Enterprise Guide. Or if a project already contains code for a cube, I will use it as a starting point.
- Easy to add Base SAS pre-processing steps in the same program
- Can use macro language to write dynamic code and MDX queries
- Cube is still created and registered in SAS metadata
- Can break up PROC OLAP into multiple steps if needed
- Complexity can grow quickly as more Base SAS programming techniques are used
- Source code is most likely managed separately from metadata
A previous blog post of mine explains how to use multiple PROC OLAP steps in a single SAS program to build OLAP Cube aggregates more efficiently. Writing this code in a SAS program allows full control of the aggregate build process; using OLAP Cube Studio does not. Another post of mine shows how to use Enterprise Guide to write MDX code, which can be used as well.
When faced with building an OLAP Cube in SAS, different personas have different options depending on requirements. If reporting requirements do not require much complexity, using SAS OLAP Cube Studio is probably the best way to develop. If challenges with data or complex measures, members, or sets are needed, developing the OLAP cube manually could be a better alternative.
Check out the SAS code I have provided for more examples of OLAP techniques which can be implemented by writing PROC OLAP yourself.
Also keep an eye out for future posts or papers on SAS OLAP tricks!
Never miss a BI Notes post!
Click here for free subscription. Once you subscribe you'll be asked to confirm your subscription through your email account. You email address is kept private and you can unsubscribe anytime.
Latest posts by Steve Overton (see all)
- BulkLoading with PostgreSQL available in SAS 9.4 - 2013-10-22
- Everything Still Starts and Ends with Data in SAS Visual Analytics - 2013-07-30
- Describe Your Table in SAS to Write the SQL Code - 2013-05-22
- Update Table Metadata Using Base SAS Code - 2012-12-19
- SAS Administration: Fetch Data Faster across ODBC - 2012-11-01
Tags: SAS Enterprise Guide