Analyze Data with Excel PivotTable

Introduction

Kepion supports data analysis using 3rd party reporting tools. In this article, we will show you how to connect to the Analysis Services (SSAS) database using Excel PivotTable.

Before You Start

Please verify the following in order to successfully connect. 

1. You have an account with access to the SSAS server (i.e. CORP\USER or user@corp.com).
2. You know the Name or IP Address of the server.
3. The SSAS server has the appropriate ports opened and available for access.

Connect to Analysis Services

Analysis Services connection is supported on Office Excel 2003 and above.

Step 1. Go to Data tab in Excel. Get Data > From Database > From Analysis Services.

Step 2. In pop-out window, enter the Name or IP Address of the target server.  Then click Next.

Tip: If your machine is not joined with the same domain as the Analysis Services server you are trying to connect, check the "Use the following User Name and Password" option, and enter the domain account and password.

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

Step 4. In the last window 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, refer to Why Is There No Data in my Pivot Table? for a resolution.