BI Tools

Tips and tricks for building information maps, OLAP cubes, reports, and dashboards

BI Admin

Learn your way around a SAS BI installation.

Visual Analytics

Learn your way around the SAS Visual Analytics tool

Coding & Data

Extract, transform, and load your data into the SAS BI toolset

Stored Processes

Create and design stored processes like a rock star

Home » Enterprise Guide

Best Practices: To Write or Not to Write PROC OLAP… That is the Question

Submitted by on 2011-12-28 – 7:00 AM 4 Comments

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 a cube
  • Minimal complexity for beginners
  • Maintain code in metadata

Cons

  • 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.

SAS OLAP CODE

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

  • 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.

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!

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.
The following two tabs change content below.

Tags:

4 Comments »

  • Giovanni says:

    Big follower of the site, several your articles have definitely helped me out. Looking towards updates!

  • Steve Overton says:

    Agreed, OLAP Cube Studio is a great tool. It all depends on the person developing the cube and the requirements of the task at hand.

  • I agree! I had the same thought – it was like a light went off. 🙂

  • Anders Sköllermo says:

    Hi! The SAS OLAP Cube Studio is not at all bad. However some of the menues are not at all natural. They are easier to understand, once you have seen the code that is generated.

1 Pingbacks »