An offline cube allows one to save a local copy of an existing cube. This can be useful when one needs to access the cubes without connecting to OLAP database directly. This article will walk you through an example of creating an offline cube using the built in Excel functionality and pragmatically using the Analysis Services Query Editor.
Before an end user can create an offline cube they must be either:
1. An admin on the Analysis Services Server.
2. Granted local cube permissions.
Granting Local Cube Permissions to a User
1. Open the Jet Data Manager, open the project, right click the OLAP database, and navigate to OLAP Server User Rights. The OLAP Access Control window will appear.
2. Click on the Cubes node and set the permission Read with Drillthrough and Local Cube. In this example we are giving users of our Sales Team role permission to create an offline cube for our Sales cube only.
The users in the selected role now have permissions to create offline cubes.
1. Open Excel and connect to the cube that you wish to create an offline copy of.
2. With the pivot table selected navigate to the ANALYZE tab, OLAP Tools and Offline OLAP...
Make sure you only have a single pivot table open during this insistence of Excel. If you have multiple pivot tables opened you will receive the message below.
3. Click the Create offline data file... button
4. The Create Cube File wizard will appear. Click next.
5. Select the dimension levels you want to see in the offline cube.
In order for the date dimension to work with Excels offline cube functionality we suggest only selecting the Date YQMD user defined hierarchy (as opposed to the individual Month, Quarter, and Year levels).
If you do select the individual Month, Quarter and Year levels you may be presented with the following error:
7. Select the location to which you wish to save the .cub file. Click Finish.
8. You can now open the file using Excel.
Analysis Services Query Editor:
Sometimes the built in Excel functionality may not work properly, or you may need to expand the offline cube functionality. To do this we will need to build a query to create our offline cube.
1. Open SQL Server Management Studio and connect to Analysis Services.
2. Right click your OLAP database, New Query, and MDX.
3. A syntax example is provided below that can be used as a basic template. This example creates an offline cube called Sales Cube Offline, saves the file to the C drive, and uses all the dimensions and measures listed between the parenthesis.
For a full description of how to create an offline cube using the CREATE GLOBAL cube syntax see the link below:
4. Once your query is complete click the Execute button. If your syntax is correct you will get a conformation message in the results pane.
5. You will now be able to open the file from Excel.