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.


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.


About Author

Angela Hall

Senior Technical Architect

Angela offers tips on using the SAS Business Intelligence solutions. She manages a team of SAS Fraud Framework implementers within the SAS Solutions On-Demand organization. Angela also has co-written two books, 'Building BI using SAS, Content Development Examples' & 'The 50 Keys to Learning SAS Stored Processes'.

Related Posts


  1. Donna Lanningham on

    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.

  2. Pingback: Cleaner OLAP physical folders, a digital pack-rat's story

Back to Top