SAS BI tips, lessons learned, problems and successes

Unlike BASE SAS tables, OLAP cubes must exist in within the metadata in order to access from any of the OLAP Viewers. In addition to having some metadata OLAP cubes have a physical file structure presence (at least for MOLAP/HOLAP because it's a different story for ROLAP). When you refresh these cubes, the physical file structure is scrapped and recreated. If something goes horrible wrong, the metadata remains but the files are simply ... gone.

From SAS OLAP Cube Studio, the icon next to the cube name changes slightly. This represents the fact that the cube exist in name alone.

But what can you do to check whether it was successful during batch processing? Fortunately, SAS users are sharers. A quick Google search on my custom Google search engine resulted in this SAS Community post on checking to see if a directory exists. You can use this outright, or if you don't have the path handy you can search through the metadata using data step functions and run the whole thing with only the cube name as input.

/*Specify the cube name*/
%let cubename = SalesSummary;
/*Use the Data Step Interface to Metadata*/
data _null_;
  length path cubeuri diruri $256;
  path="";
  cubeuri="";
  diruri="";
/*    Find the CubeURI*/
  rc2=metadata_getnasn("omsobj:Cube?@Name='&cubename'","AssociatedFile",1,cubeuri);
/*    Find the Directory URI*/
  rc3=metadata_getnasn(cubeuri,"Directories",1,diruri);
/*    Retrieve the DirectoryName Value*/
  rc4=metadata_getattr(diruri,"DirectoryName",path);
  call symput('path', strip(path)); 
run; 
 
/*Set the SASCommunity.org Macro*/
%macro DirExist(dir) ; 
   %LOCAL rc fileref return; 
   %let rc = %sysfunc(filename(fileref,&dir)) ; 
   %if %sysfunc(fexist(&fileref))  %then %let return=1;    
   %else %let return=0;
   &return
%mend DirExist;
 
/*Determine if the physical path exists*/
%put %DirExist(&path\&cubename\gen0000);

If it returns a 0, you know that the refresh code failed and there is no data there. You could use this to send an alert or to move to a different phase of the process. What would you do with this code? What other uses can you think of?

Unlike prior versions of SAS OLAP technology, 9.2 provides more options for maintaining and refreshing OLAP cubes. With this comes some discussion about what each one does alone, and how pairing these techniques really provides SAS OLAP Server Administrators and cube developers a cornucopia of OLAP options.

PROC OLAPOPERATE

Remember using the option DELETE_PHYSICAL from the OLAP PROC in 9.1.3 and getting errors when users were still connected to the OLAP cube? In prior versions, you could restart the OLAP Server to manually kick the users out of the system. You can now use SAS Management Console to stop specific user sessions. But when refreshing OLAP cubes in batch, use the PROC OLAPOPERATE technique to DISABLE and ENABLE the cube programmatically.

Update In-Place

If you chose to use the OLAPOPERATE procedure described above, you could run into a situation where a report using the cube is run & fails because the cube is getting refreshed and is currently disabled. Using Update In-Place allows you to add more data to the cube anytime you like, users that are currently connected will not get kicked off and you won't receive any errors about locked cubes. However, using this option alone could mean that it takes awhile for the new data to be available to the users. That's because the new data isn't "pushed" into the cube until ALL sessions accessing this cube are disconnected.

In Section 5.7.4 of my book Building Business Intelligence Using SAS: Content Development Examples, I describe implementing both the Update In-Place technique and PROC OLAPOPERATE disable/enable options to quickly refresh the cube data and get it pushed to users programmatically.

Incremental Updates

Remember having to refresh the entire cube to add any new data? Or receiving user requests to validate the cube before updating all the reports? With incremental updates, this is done using multiple PROC OLAP and PROC OLAPOPERATE steps. The SAS OLAP Server documentation provides a sample step by step approach. But don't forget to occasionally Coalesce Incremental Data using PROC OLAP COALESCE_AGGREGATIONS option. This will keep your filesystem to a more reasonable size, as each incremental refresh generates a new folder of aggregation content.

In one minute (and 10 seconds, but are you measuring that?) you can add a custom measure (also known as calculated members) to your OLAP cube. Watch this video to create a simple calculation multiplying units * price.

Remember that this mechanism is not just used for multiplications, you can choose to use MDX functions to generate complex logic.
One of my favorites is to use IIF techniques to test for conditions. An example is available in the SAS Global Forum paper "Measures, Members, and Sets, Oh My!" from my friends Bryan Stines and Ben Zenick. Another friend, Steve Overton, posted a blog post about using MDX to generatepercent of totals. For more information on MDX logic, the Microsoft programming technique for MultiDimensional Expressions is available at http://msdn.microsoft.com/en-us/library/ms145506.aspx.

Two very nice things about creating custom measures:

• Anyone accessing this cube from Enterprise Guide, Information Maps, Web Reports, etc etc can use this custom measure - making it reusable!
• These measures are calculated on the fly. Using this mechanism measures are not pre-calculated but are based on what the user has selected for view.

For those with no youtube access - here are the basic steps:

1. Select the cube name on the left panel of OLAP Cube Studio
2. Click the Calculated Member icon on the top menu bar
3. Select Add to create a Calculated Member
4. Follow the GUI to create simple or time based calculations
5. For custom MDX expressions, choose custom calculations and then click Build Formula

OLAP cube developers might have noticed that the amount of time required to generate any aggregation (including the NWAY) can negatively impact their productivity. Developers remove the NWAY aggregation (following the sample screens in a prior post) to significantly reduce build time and allow quick modifications to the cube definition.

Previously I explained what is an NWAY anyway. In one sentence, an NWAY is a summarization of the crossjoin between all levels defined for use in the OLAP cube. Removing this NWAY is typically not recommended for consumers of the OLAP cube. You might speed up development, but a user could drop off into the abyss of detail data & experience slow performance. Thankfully for this stick figure, the green box of NWAY is there to save the day.

So before these non-NWAY OLAP cubes are promoted into production take a moment to consider how the user could be impacted.