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 » BI Tools

SAS OLAP Cubes: Tips for Building OLAP Cube Aggregations Effectively

Submitted by on 2011-11-30 – 7:00 AM 3 Comments

 

As promised, this is a guest post by Steve Overton, who was one of the external reviewers on the Building Business Intelligence with SAS.  Steve works as a SAS BI consultant in the Raleigh-Durham, NC area and is sharing some of his SAS OLAP Cube wizardry with us today!


Building OLAP Cube Aggregations Effectively

Let me start by saying that I follow a general practice of building cubes by writing the code directly in a SAS program rather than relying solely on SAS OLAP Cube Studio. 

Tip #1: Use SAS OLAP Cube Studio for Skeleton Code

A good technique I’ve learned is to start building the cube in SAS OLAP Cube Studio to get the skeleton code written for you.  Then proceed with additional dimensions, measures, and customizations by enhancing the SAS code directly.   Once you have a few cubes built, it is also just as easy to copy the OLAP code from one program to the next!

Tip #2: Use Separate Steps for OLAP Cube Aggregations

Aggregations in OLAP cubes are used to improve the response time of a cube.  By default, aggregations are defined along with the cube in one giant PROC OLAP step.  One critical technique I’ve used when building cubes is to separate the aggregation steps from the main PROC OLAP step so that syntax errors do not break the entire cube build process.  When building cubes by hand through SAS programs, this can become a frequent problem.  Separating the aggregation steps also organizes the code better and allows the user to grid-enable the SAS program to run faster in SAS environments that have a high performance grid compute tier.

 

sas bi olap cube code

Tip #3: Avoid the NWAY when Coding Cubes

Another technique I use when building cubes is to NOT define the NWAY aggregation.   One thing to note is that even if you do not explicitly define the NWAY aggregation, also known as the Default aggregation, SAS will still build it unless you specify the NO_WAY option in the PROC OLAP statement.  This simplifies the code some and eliminates the need to constantly update the NWAY definition every time you add levels to the cube.  Again, this is only necessary if cubes are built strictly using SAS programs rather than SAS OLAP Cube Studio.

If you are considering removing the NWAY and using prebuilt aggregates, think about performance at run time. If there is no NWAY aggregation built, SAS will refer back to the source data and summarize on-the-fly. In some cases this could seriously impact performance.

Visit Steve’s site for source code that demonstrates these and other OLAP techniques. 


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.

 

The following two tabs change content below.

Tags: , ,

3 Comments »

  • Simon says:

    It makes sense how your separation of the two sections results in efficiency. It is interesting that you only need to re-register when the schema changes…

  • Steve Overton says:

    No you do not have to register cubes in metadata after submitting them in code form. There is an option in the PROC OLAP statement which specifies the location in metadata. Regardless, the tips provided in this article do not change the schema of the cube, just the method for which it is built so information maps and other possible dependencies will not break either.

  • Simon says:

    Nice tip! Thanks! After you run this, do you still need Infomation Map Studio to register the cubes to metadata server? I haven’t done OLAP lately… I actually have more practice with proc mddb… I left the analytics field for academics shortly after proc olap was introduced back in 2003 or so…

1 Pingbacks »