Wednesday, January 22, 2014

SSAS Local Cube

1         Creating a local cube

Local cubes and local mining models allow analysis on a client workstation while it is disconnected from the network. For example, a client application might call the OLE DB for OLAP 9.0 Provider (MSOLAP.3), which loads the local cube engine to create and query local cubes, as shown in the following illustration:

1.1        Create the XMLA script

Create the XMLA script using server cube. Then copy script to Clipboard.

1.2        Create the local cube file and connect to the local cube.

Specify the local cube path and the filename (including .cub as the extention) as the server name. Then press "connect" which will create cube file.

1.3        Open a new XMLA query

Open a new XMLA query window by right clicking on local cube connection and paste the script copied above.

1.4        Execute the script file

You can search for following elements in the script file to make sure that you are connecting to right database.

1.5        Process the cube

Right click on the local cube database and click “Process”
Ignore following error messages
Value cannot be null.
Parameter name: key (System)

2         Access local cube file

2.1        Microsoft Excel

.cub file can be simply browsed using Excel (2010/2013) and you will get the same interface that you get when access SSAS server cube using Excel.

2.2        XLCubed

.cub file can be also simple connected to XLCubed by creating XLCubed connection. Connection type should be “Analysis Service Cube File.

3         Benefits

  •  Local cube files can dramatically improve browsing speed performance, especially when analysing low levels of large dimensions.
  • Local cube files can also improve browsing performance because requests for additional data are handled on the local computer rather than across a network on an Analysis Server.
  •  Local cube files can now be encrypted and password-protected.
  • Analysis Services 2005 provides more precise control over the creation of local cube files.
  • When you use local cube files you can give each user the specific data they need, or want, or are allowed to see.

4         Limitations

  • When creating local cubes from server-based cubes, the following considerations apply:
  •  Distinct count measures are not supported.
  • When you add a measure, you must also include at least one dimension that is related to the measure being added.
  • When you add a parent-child hierarchy, levels and filters on a parent-child hierarchy are ignored and the entire parent-child hierarchy is included.
  • Member properties are not created.
  • When you include a semi-additive measure, no slices are permitted on either the Account or the Time dimension.
  • Reference dimensions are always materialized.
  • When you include a many-to-many dimension, the following rules apply: 
    • You cannot slice the many-to-many dimension.
    •  You must add a measure from the intermediary measure group.
    • You cannot slice any of the dimensions common to the two measure groups involved in the many-to-may relationship.
  • Only those calculated members, named sets, and assignments that rely upon measures and dimensions added to the local cube will appear in the local cube. Invalid calculated members, named sets, and assignments will be automatically excluded.