Using Excel PivotTable for Data Analysis

By - Apr 25, 2013

When OLAP cube models are created and deployed in the Kepion modeler, they are accessible through the Excel PivotTable for analysis.  In this article, we will walk-through the steps of accessing existing cubes created in the CPG Sample Application (available through Kepion support downloads).

Below is a view of the existing cubes in the CPG application


And within each model contains a model data partition with a corresponding table in database.


Once data is loaded into these tables, we can then perform data analysis through PivotTable in Excel.

Connect to Database in Excel

In this article we are using Microsoft Office Excel 2010.  Analysis Services connection is supported on Office Excel 2003 and above.

Step 1. Go to Data tab in Excel. Click ‘From Analysis Service’ in ‘From Other Source’ dropdown list.


Step 2. In pop-out window, enter your server name and click ‘Next’.


Step 3. Select the database you want to connect in drop-down list.


Step 4.  We can either connect to a database application or a specific cube. In this example, we will connect to the Profit cube. Click ‘Next’.


Step 5. Click ‘Finish’.


Create PivotTables for Analysis

Now we can see all of the model dimensions, together with the measure group(s), in the PivotTable Field List on the right side.


Simply drag fields into form axis to create your PivotTable.


If you see an empty table even if the axis are setup correctly, please refer to Why There is no Data in my Pivot Table? for resolutions.