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.

Pros

• Easier and faster to create an OLAP cube
• Minimal complexity for beginners
• Maintain code in metadata

Cons

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 an OLAP Cube, I will use it as a starting point.

Pros

• 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

Cons

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.

Conclusion

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!

Steve Overton  (12 Posts)

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!

Tags: Best Practices, OLAP Cube Studio, SAS Enterprise Guide, SAS olap cubes

This entry was posted on December 28, 2011 at 7:00 am and is filed under Building Business Intelligence, SAS Enterprise Guide, SAS OLAP Cubes. You can follow any responses to this entry through the RSS 2.0 feed.