An OLAP Cube is a data structure that allows fast analysis of data according to the multiple dimensions that define a report. The user interface is typically a multi-dimensional spreadsheet that summarizes data. For example, you may wish to summarize sales data by product, time-period and color to compare and quickly gather insights on sales results. The cube (in theory) may look something like this:

OLAP_cube

ComponentOne OLAP is essentially a cube creator as it can produce a multi-dimensional spreadsheet from any tabular data set, query or collection of business objects. The result is a pivot table report that summarizes your data.

OLAP_Cubes

New in 2014 v3, ComponentOne OLAP for WPF can also work directly with cube data from Microsoft SQL Server Analysis Services (SSAS). In this scenario the controls acts as a front-end for your cube data, which is already summarized across multiple dimensions. The UI controls communicate with SQL Server allowing the user to slice and dice the dimensions and measures present in the cube.

To connect with a cube, you will use the ConnectCube method on the OlapPanel. This method accepts two parameters: the connection string to a SQL Server with Analysis Services installed, and the name of the cube.

OLAP_cube_connect

You can report connection and authentication-related errors to the user by catching an Exception at run-time. So you can choose whether to show a pop-up, ask the user to login or do nothing.

The connection string should set the Data Source and the Initial Catalog. If you have more than one Microsoft OLE DB providers for OLAP installed you may need to specify the version of the provider in the connection string as well. For example, setting the Provider to MSOLAP will use the latest version of OLE DB for OLAP installed on your system. It’s not always necessary, but it doesn’t hurt to include it.

You can use ComponentOne OLAP with SQL Server 2008, 2012 and 2014. You also need to have Analysis Services installed and some data warehouse database. The OLAP cube can be deployed on SQL Server in advance, or you can attach a local cube file at run-time from your application. For more information on setting up a data warehouse and obtaining a sample OLAP cube, I recommend using the sample Adventure Works database with Multidimensional models which is free and widely used.

OLAP_CustomUI_10

At run-time users can build reports from cube data much like they would from regular data sets. The key difference is that cube data sets are represented by a tree in the C1OlapPanel control with each node representing a dimensional entity or a value for measure. All fields that can be added to the report are displayed with a checkbox. Nodes represented by the summation symbol are measures and can be added to the Values collection. Key Performance Indicators, or KPIs, are also supported and they can be summarized like measures.

OLAP_KPI

Fields of entities can be added to the Rows or Columns collections to build your multi-dimensional pivot table.

Download ComponentOne Studio for WPF to get the OLAP controls and all of the C-Sharp and VB samples to help you get up and running building your own cube front end or dashboard. You'll also find the "CustomUI" sample included which provided the screenshots for this blog post.