An OLAP is a data structure that allows fast analysis. 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:
The user interface is typically a multi-dimensional spreadsheet that summarizes data GrapeCity's OLAP controls are essentially a cube creators as they 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. GrapeCity OLAP controls can also work directly with cube data from Microsoft SQL Server Analysis Services (SSAS). In this scenario, the controls act 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 learn more about OLAP in a particular platform, follow the links below:
Next, I’ll show you how easy it is to connect to an OLAP cube using OLAP for WPF as an example.
Connecting to a Cube Using OLAP for WPF
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.
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 OLAP for WPF 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.
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. Fields of entities can be added to the Rows or Columns collections to build your multi-dimensional pivot table.
This article was updated from a November 13, 2014 post.