Share via

SharePoint 2013 : How to Configure Data Connection with SSAS Cubes using Excel Services

  • Article

In this article we will explore the process of consuming data present in Data Cube hosted on SQL Server Analysis Server using Excel Services.

Before we move any further we need to verify the following prerequisites to support this demo:

  • Secure Store Service Application should be created and configured properly
  • Required Application ID should be created in Secure Store Service that can allow authentication for Excel Service over SQL Server Databases
  • Excel Service Application should be created and configured properly

There must be atleast one Data Cube hosted on SQL Server Analysis Server which is having data to consume

Before proceeding any further we need to ensure that all the prerequisites are in place-

  • Validate Secure Store Service & Application ID

** **Go to Central Admin => Manage Service Application

https://howtodowithsharepoint.files.wordpress.com/2015/10/110.png?w=300&h=109

Click on “Secure Store Service”

https://howtodowithsharepoint.files.wordpress.com/2015/10/210.png?w=300&h=69

Check for Application ID that we make use of for Excel Services.

https://howtodowithsharepoint.files.wordpress.com/2015/10/35.png?w=300&h=123

In this case we have created Application ID by the name “Excel Services”

  • Validate Excel Service Application

Now Go to Central Admin => Manage Services on Server

https://howtodowithsharepoint.files.wordpress.com/2015/10/41.png?w=300&h=172

Look for “Excel Calculation Services” and make sure it is also in started mode

https://howtodowithsharepoint.files.wordpress.com/2015/10/51.png?w=300&h=106

Again Go to Central Admin = > Manage Service Application

https://howtodowithsharepoint.files.wordpress.com/2015/10/61.png?w=300&h=167

Look for “Excel Services” and make sure that it is in Started mode

Click on Excel Services Application

https://howtodowithsharepoint.files.wordpress.com/2015/10/71.png?w=300&h=107

Then click on Global Settings

https://howtodowithsharepoint.files.wordpress.com/2015/10/81.png?w=300&h=119

Scroll Down to the bottom and look for Target Application ID, it should be set to the same Secure Store Application ID (Excel Services) that we created in earlier steps.

* https://howtodowithsharepoint.files.wordpress.com/2015/10/91.png?w=300&h=123*

  • Validate SQL Server Analysis Server

** **Login to SQL Server Analysis Server instance and make sure that we have atleast one Data Cube hosted on SQL Server Analysis Server that can serve the data to Excel Service.

A valid Data Cube should look like as shown below:

https://howtodowithsharepoint.files.wordpress.com/2015/10/101.png?w=300&h=155

Demo:

Launch Excel Client Application and choose Blank Workbook

https://howtodowithsharepoint.files.wordpress.com/2015/10/111.png?w=300&h=97

https://howtodowithsharepoint.files.wordpress.com/2015/10/121.png?w=300&h=123

Under Data Tab, Select “From Other Sources” then select “From Analysis Service”

https://howtodowithsharepoint.files.wordpress.com/2015/10/131.png?w=300&h=132

On the Data Connection Wizard Dialog, Enter SQL Server Analysis Server Instance Name

Click Next

https://howtodowithsharepoint.files.wordpress.com/2015/10/141.png?w=300&h=144

On the next screen of Data Connection Wizard,

Choose the required Cube

Click Next

https://howtodowithsharepoint.files.wordpress.com/2015/10/151.png?w=300&h=159

On the next screen of Data Connection Wizard,

Click on Authentication Settings

https://howtodowithsharepoint.files.wordpress.com/2015/10/161.png?w=300&h=147

On Excel Services Authentication Settings Dialog,

Choose None and Click OK

https://howtodowithsharepoint.files.wordpress.com/2015/10/171.png?w=300&h=153

On the Data Connection Wizard,

Click Finish to end the Connection Wizard

https://howtodowithsharepoint.files.wordpress.com/2015/10/181.png?w=300&h=144

On the Import Data Dialog,

Choose any option of your liking, here we are choosing “PivotTable Report”

https://howtodowithsharepoint.files.wordpress.com/2015/10/191.png?w=300&h=136

Choose Pivot Table Fields that suits the report you needed

https://howtodowithsharepoint.files.wordpress.com/2015/10/201.png?w=300&h=151

In this report we are presenting Internet Sales that can be filtered for State Wise

So Select “Internet Sales Amount” as aggregator that shows the sum of Values in “Internet Sales Amount” Column

https://howtodowithsharepoint.files.wordpress.com/2015/10/211.png?w=300&h=92

In order to filter the values we can add Slicers that can help to filter out the records based on the Column which is selected as Slicer

From the Top Ribbon, click on Insert Slicer

https://howtodowithsharepoint.files.wordpress.com/2015/10/221.png?w=300&h=135

On the Insert Slicer Dialog, select State Province and click OK

https://howtodowithsharepoint.files.wordpress.com/2015/10/231.png?w=300&h=134

Now we can select any value of State Province from Slicer and can see the filtered value of Internet Sales Amount corresponding to the selected State Province

https://howtodowithsharepoint.files.wordpress.com/2015/10/241.png?w=300&h=98

https://howtodowithsharepoint.files.wordpress.com/2015/10/251.png?w=300&h=82

Now let’s save the Sheet to SharePoint

Go to File Menu => Save As

Choose SharePoint Document Library

Specify the name of the File and click Save

https://howtodowithsharepoint.files.wordpress.com/2015/10/261.png?w=300&h=159

Now Browse SharePoint Document Library

https://howtodowithsharepoint.files.wordpress.com/2015/10/271.png?w=300&h=148

Click on file to render it in Browser using Excel Services

Click Yes on “Query and Refresh Data” Pop Up, this option allows the sheet to re-query the updated data based on the connection we set-up with in the Excel and ensure data freshness all the time

https://howtodowithsharepoint.files.wordpress.com/2015/10/281.png?w=300&h=161

Hope you find this helpful.