Benny Austin

Offline OLAP – Working with Disconnected Analysis Service Cube

Microsoft introduced a cool feature in Excel 2007 called Offline OLAP that enables you to work with an Analysis Services Cube in disconnected mode. The Offline OLAP feature basically snapshots a version of the cube into a local cube file which can be used by Pivot Tables.

Some potential scenarios where the offline cube file can be really helpful:

  • Proof of concept demos to client can be done using the local cube file without having to worry about the network bandwidth, access and unfamiliar environments.
  • Accessing your reports while commuting or in places where internet facility is not available.
  • Snapshoting the stable version of cube during development phase especially when certain development activities can potentially bring down the cube.

Creating Offline Cube File

To create the Offline Cube file, click on you Pivot Table and navigate to PivotTableTool>>Options>>OLAP Tools>>Offline OLAP (shown in Figure 1)

Figure 1 - Offline OLAP

Figure 1 – Offline OLAP

A wizard pops up. Click Create Offline Data File button (shown in Figure 2).

Figure 2 - Create offline data file

Figure 2 – Create offline data file

The subsequent wizard steps allow you to select the dimension attributes, members, hierarchies, measures and calculated measures. By default the cube members used in the PivotTable is selected. (Figures 3 and 4) .

Figure 3 - Select Dim

Figure 4 -Select Measures and Dim Members

Figure 4 -Select Measures and Dim Members

The final step is to save the data to a local cube file (figure 5)

Figure 5 - Local Cube File

Figure 5 – Local Cube File

Switching to Online mode

To switch from offline to online mode, navigate as before PivotTableTool>>Options>>OLAP Tools>>Offline OLAP and select the On-line OLAP option which is now enabled (figure 6).

Figure 6 - Switch Modes

Figure 6 – Switch Modes

Editing Offline Cube File

To edit the offline cube file, navigate to PivotTableTool>>Options>>OLAP Tools>>Offline OLAP and select Edit offline data file option.

Figure 7 - Edit offline data file

Figure 7 – Edit offline data file

Offline OLAP is a really cool feature in certain scenarios. One improvement that I would prefer to see is some kind of visual differentiation to indicate that the PivotTable is in offline mode which is currently not available.


4 comments on “Offline OLAP – Working with Disconnected Analysis Service Cube

  1. Masson
    July 13, 2013

    Are there any limitations when using an offline cube versus using an online cube?

    • Benny Austin
      July 14, 2013

      Masson, I am not aware of any difference in cube browsing between online and offline browsing modes.

  2. Arjun
    June 6, 2013

    Its Very good Article,,,

    Helped me a lot,,

  3. Gaurav
    December 4, 2009

    hi Benny
    Your article was a good read.
    I had used “CREATE GLOBAL CUBE” command few days ago and was impressed by its simple syntax. It also produces a .cub file on the server and possibly quite useful for automation of producing multiple .cub files restricted by security requirements of dimensions / measures / etc.. although, I haven’t used it that way but just some random thoughts.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


This entry was posted on September 20, 2009 by in SSAS and tagged , , , , , , .


© Benny Austin.

Get every new post delivered to your Inbox.

Join 312 other followers

%d bloggers like this: