| This post was kindly contributed by Business Intelligence Notes for SAS BI Users - go there to comment and to read the full post. |
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.
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.
Wait there’s more ….
In the upcoming Building Business Intelligence with SAS book, you can learn more about using SAS OLAP Cube Studio and some tips for dealing with aggregations. Sign up to have SAS Press send you an email when it’s available!
| This post was kindly contributed by Business Intelligence Notes for SAS BI Users - go there to comment and to read the full post. |