Best practices for refreshing production OLAP cubes

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.

tags: OLAP

2 Comments

  1. Shankar Narayana
    Posted October 5, 2012 at 7:29 pm | Permalink

    Very helpful

  2. Donna Lanningham Donna Lanningham
    Posted December 12, 2012 at 3:05 pm | Permalink

    The PROC OLAPOPERATE chapter of the SAS OLAP Server User's Guide has been updated for SAS 9.3 and later releases to include more functionality and controls for administering SAS OLAP Servers. In particular, functions for pausing and quiescing an OLAP Server are added as well as functions for SAS OLAP Server cluster management.

One Trackback

  1. [...] information on how to update cubes in place using SAS code is available in my prior post on OLAP refresh best practices. tags: OLAP Bookmark on Delicious Digg this post Recommend on Facebook Share on FriendFeed Share on [...]

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <p> <pre lang="" line="" escaped=""> <q cite=""> <strike> <strong>