You can connect to various data sources such as Microsoft SQL Server Analysis Services (SSAS), or online cubes, or attach to a local cube at runtime. FlexPivot control works with Analysis Services and SQL Server 2008, 2012 and 2014.
Setting Microsoft SQL Server Analysis Services
This guide is intended to provide users with information on setting up SQL Server Analysis Service (SSAS). To analyse cube data, you need to setup SSAS. The following steps explain how to setup the database.
- Install a full version of SQL Server.
- Download the Adventure Works database compatible with the version of SQL Server you installed. You can select the database from http://msftdbprodsamples.codeplex.com/releases.
- Install Adventure Works database in the SQL Server.
Connecting to a Cube
Users can connect to a cube database through ConnectCube method. This method accepts two parameters: the name of the cube and the connection string to the installed SSAS.
The connection string must specify the Data Source, that is the Server name, and the Initial Catalog, that is the database name. The version of the Provider must also be specified if more than one Microsoft OLE DB provider for FlexPivot is installed. For instance, if the Provider is set to MSOLAP, the latest version of OLE DB for FlexPivot installed on your system is used.
The code given below illustrates an example of connecting to a cube.
//prepare to build view
string connectionString = @"Data Source=ServerAddress;Provider=msolap;Initial Catalog=DatabaseName;User Id=ValidUserID; Password=ValidPassword";
string cubeName = "Adventure Works";
// show some data.
var fp = c1FlexPivotPage1.FlexPivotEngine;
catch (Exception ex)
Using the Cube
Now it's time to run the application and see how the data appears on the FlexPivot control.
- Press F5 to run the project.
- You'll notice that the control appears, displaying some random data fetched from the database Adventure Works.
- Now, drag-and-drop Country and and State-Province fields from the pivot panel to the Rows list, and Order Count and Internet Sales Amount to the Values list.
- The control now displays a grid summarizing Order Count and Internet Sales Amount by Country and State Province:
- The cube data bound to the FlexPivot control in the above image consists of Dimensions, Measures, and Key Performance Indicators (KPIs). Dimensions are used to categorize the data cube, while Measures are the values for the dimensions. KPIs evaluate the measures in cube so as to present different perspectives of performance.
- The installed cube Adventure Works consists of Geography as one of the many dimensions; Internet Sales as one of the measures; and Customer Perspective and Financial Perspective as the KPIs.
- Users can use the cube data to build reports much like they would using regular datasets. The major difference between cube data and regular dataset is that data in cubes is represented in a tree-like structure in the C1FlexPivotPanel control.
- Each node in the tree-like structure represents a dimensional entity or an object for measure.
- Moreover, dimensions comprises Hierarchies, Levels, and Attributes
- Hierarchies: Organizes levels in which the dimensions of a cube are structured
- Level: Describes position in a heirarchy
- Attribute: Gives additional information about the corresponding data