OLAP Cubes in the SCSM Data Warehouse : Maintenance and Administration

OLAP Cubes in the SCSM Data Warehouse : Maintenance and Administration

  • Comments 8
  • Likes

This is the eighth and last post in a series of posts detailing the capabilities of OLAP cubes in the Data Warehouse, a new feature that enables self-service reporting functionality added in SCSM 2012.

Unregistration and Reregistration of SM to DW or Uninstall/Reinstall of a Cube MP Requires Manually Dropping the Cube from the ASDB

When the administrator unregisters SM from DW or uninstalls a cube MP, they MUST drop the cube from the ASDB before reregistering SM to DW or reinstalling the same cube MP. We explicitly do NOT drop the cube from the ASDB since it is possible that the cube may still be in use for reporting purposes. The onus here is on the administrator to drop the cube manually when they think it is appropriate. If the user does not do this, when the cube MP redeploys it will fail by design and an error message will be written to the event log stating the cube must be dropped before reattempting deployment. The user must then drop the cube and restart the deployment of the MP.

Periodic ProcessFull of Analysis Services Dimensions is Recommended Best Practice

SQL Server Analysis Services best practices recommends that a periodic ProcessFull of Analysis Services Dimensions are performed. A ProcessFull will rebuild indices and optimize the data storage of multidimensional data which will also improve query and cube performance which can degrade over time. This is analogous to periodically defragmenting a hard disk on a computer.

The drawback to performing a ProcessFull on an AS dimension is that all affected cubes will become unprocessed and will need to have a ProcessFull performed on them to bring them back up to a queryable state. SCSM 2012 does not explicitly perform any periodic ProcessFull commands on AS Dimensions. Because of this, it is up to the discretion of the system administrator when to perform this maintenance. To help, the DW team has written a open-sourced utility with a simple GUI interface that allows the administrator to either perform a ProcessUpdate or ProcessFull on all AS Dimensions or a selected subset. We will release this utility on CodePlex or another hosting platform shortly.

Memory Considerations

If administrators are running all DW ETL operations and OLAP Cube functionality on the same machine, it is highly recommended that they carefully consider the memory requirements for the OS, DW, and Analysis Servers and ensure that the system can handle all the data-intensive operations that can run concurrently. This is especially important given the fact that processing OLAP Cubes is a memory-intensive operation. The SM team will publish memory requirements and guidelines with the release of SCSM 2012 but it is also highly recommended that administrators read the Operations Guide from the Analysis Services team at (http://msdn.microsoft.com/en-us/library/hh226085.aspx)

Here is an index list of the entire series:

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • how is this maintenance and administration, no where do you say what the schedules should be, nor do you talk best practices at all

  • Did the GUI utility to process AS dimensions ever get released?