xamPivotGrid and Local (Offline) SSAS Cubes

Local cubes or also known as offline cubes in the SQL Server Analysis Services are files that contain multidimensional data. They work in the same way as normal cubes in MS Analysis Server, but those cubes can be used when the client does not have network connectivity. Local cubes significantly increase the performance of the client application because requests for data are not sent over network, but are handled locally. Analyzing low levels of big dimension hierarchies becomes much faster too as the offline cubes allow for much more precise design of the input data. Local cubes also add essential features to the analysis data sources such as encryption and password protection.

So does the XamPivotGrid support those cubes? The answer is yes. However you can only use them in WPF with the Adomd.NET data source. Future releases might support them in Silverlight too, but at this point the ADOMD client is your only choice. If you want to know how to create such a cube and use it with the xamPivotGrid control please read on.

There are several ways to create a local cube. The easiest way in my opinion is using an MDX query in the SQL Management Studio. Open it up and select New -> Analysis Services MDX Query from the File menu. You will be asked to connect to your analysis server and then a MDX query editor will be opened.

Note that at this point an error might appear that no cubes are found (or the particular cube you want to export is not found). You can only use a cube that is “Processed” and to process a cube you need to grant the user that is running the action on the cube proper permissions.

The easiest way to fix that permission problem is to connect to the database server within the management studio open the properties of Security/Server Roles/sysadmin server role and add the user in question to this role. In my case that was the NT AUTHORITY\NETWORK SERVICE.

After the process is complete you will be able to see the cube in the MDX query editor.

The query for creating a local cube has the following syntax:

CREATE GLOBAL CUBE [ <Name of the cube to be saved> ]
        Storage ' <Path to the file to be saved. Should be .cub> '
        FROM [ <Existing cube name to export data from> ]
        (
                MEASURE [ <Measure1 to include in cube> ],
                MEASURE [ <Measure2 to include in cube> ],
                …
                DIMENSION [ <Dimension1 to include in cube> ],
                DIMENSION [<Dimension2 to include in cube> ],
                DIMENSION [ <Dimension3 to include in cube> ]
                …
        );

Note that the last line within the FROM brackets should not have a comma in the end. You can list as many measures and dimension as you like. You can list groups too. Here is an example with specific names that works with my Adventure Works database.

CREATE GLOBAL CUBE [LocalCube]
        Storage 'C:\LocalCube.cub'
        FROM [Adventure Works]
        (
                MEASURE [Adventure Works].[Internet Sales Amount],
                DIMENSION [Adventure Works].[Product],
                DIMENSION [Adventure Works].[Date],
                DIMENSION [Adventure Works].[Customer]
        );

An alternative way to create your cube with Excel and SQL server is shown in this steb-by-step guide article. It is very useful if you do not have the management studio installed.

After you have produced the .cub file you can go on with the application that uses it.

The first thing that you have to make sure is that you have added to your solution all the necessary assembly references. For a WPF application with a xamPivotGrid, xamDataSelector and AdomdDataSource you need the following assemblies:

• InfragisticsWPF4.Controls.Grids.XamPivotGrid.v10.2.dll
• InfragisticsWPF4.Olap.Adomd.v10.2.dll
• InfragisticsWPF4.Olap.Xmla.v10.2.dll
• InfragisticsWPF4.Olap.v10.2.dll
• InfragisticsWPF4.DragDrop.v10.2.dll
• InfragisticsWPF4.Controls.Menus.XamTree.v10.2.dll
• InfragisticsWPF4.v10.2.dll

Another prerequisite for your application is the MS Adomd.NET Client. If you do not have it installed you can download it freely from this location.

On some systems you might also need to install the following two components if you do not already have them:

• Microsoft® SQL Server® 2008 R2 ADOMD.NET
• Microsoft® Analysis Services OLE DB Provider for Microsoft® SQL Server® 2008 R2

They are part of the MS SQL Server 2008 R2 Feature Pack

After adding your assembly references to the application you need the following namespace references too:

xmlns:ig="http://schemas.infragistics.com/xaml"
xmlns:olap="http://schemas.infragistics.com/olap"

Now you are ready to write your Adomd Data Source. I would recommend putting it in the local resources so the pivot grid and the data selector would easily share it. Here is an example code for it that access the cube, loads the TestLocalCube cube from the TestLocalCube database (Change the names to whatever you chose in the creation process) and load the Product, Date hierarchies and Sales amount measure:

<olap:AdomdDataSource x:Key="adomdDataSource"
                        Database="TestLocalCube"
                        Cube="TestLocalCube"
                        Rows="[Product].[Product]"
                        Columns="[Date].[Date]"
                        Measures="Internet Sales Amount" >
        <olap:AdomdDataSource.ConnectionSettings>
                <olap:AdomdConnectionSettings
                        ConnectionString="Provider=MSOLAP.4;Data Source=C:\LocalCube.cub" />
        </olap:AdomdDataSource.ConnectionSettings>
</olap:AdomdDataSource>

You see from that snippet that the AdomdDataSource is used in the same way as the normal XMLADataSource that you would use with a normal SSAS cube and the only difference is that instead of specifying a ServerURI we provide a custom connection string in the ConnectionSettings property of the DataSource.

ConnectionString ="Provider=MSOLAP.4;Data Source=C:\LocalCube.cub"

This connection string provides basic settings. It just instructs the data source to read the cube located at C:\LocalCube.cub with the MSOLAP engine. As I mentioned earlier in the article local cubes support much more than that. Additional setting such as encryption and credentials can also be added to this string. Another example is this string:

ConnectionString ="Provider=MSOLAP.4;Data Source=C:\LocalCube.cub; UserName=TestUser; Password=TestPasword; ProtectionLevel=PTK PRIVACY"

Specifying those options allow the client to authenticate with user TestUser, password TestPassword and use encrypted channel for the authentication. You can find a complete list with options to add to the string in this MSDN article.

The complete sample that works with the C:\LocalCube.cub file is attached. Note that you will have to provide the Infragistics assemblies yourself as described in the article. You can get the trial version from the download section of the Infragistics website. Also remember to install the Adomd client as mentioned above.

I hope this has been helpful. Should you have any problems, concerns or questions feel free to leave feedback here as comments or write to me at adyulgerov@infragistics.com

Have a great day!