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

excel-pivot-001

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

excel-pivot-002

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.

excel-pivot-003

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

excel-pivot-004

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

excel-pivot-005

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’.

excel-pivot-006

Step 5. Click ‘Finish’.

excel-pivot-007

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.

excel-pivot-008

Simply drag fields into form axis to create your PivotTable.

excel-pivot-009

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.

 


Facebooktwitterlinkedinmail