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

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

1

Click on “Secure Store Service”

2

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

3

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

4

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

5

Again Go to Central Admin = > Manage Service Application

6

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

Click on Excel Services Application

7

Then click on Global Settings

8

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.

 9

  • 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:

10

Demo:

Launch Excel Client Application and choose Blank Workbook

11

12

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

13

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

Click Next

14

On the next screen of Data Connection Wizard,

Choose the required Cube

Click Next

15

On the next screen of Data Connection Wizard,

Click on Authentication Settings

16

On Excel Services Authentication Settings Dialog,

Choose None and Click OK

17

On the Data Connection Wizard,

Click Finish to end the Connection Wizard

18

On the Import Data Dialog,

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

19

Choose Pivot Table Fields that suits the report you needed

20

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

21

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

22

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

23

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

24

25

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

26

Now Browse SharePoint Document Library

27

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

28

Hope you find this helpful.

Advertisements

SharePoint 2013 : How to Configure Data Connection with SQL Server using Excel Services

In this article we will explore the steps of consuming data present in SQL Server Database using Excel Services.

To ensure smooth execution of this demo we should ensure the following prerequisites to be in place-

Prerequisites:

  • 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 database present in SQL 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

1

Click on “Secure Store Service”

2

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

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

3

  • Validate Excel Service Application

Now Go to Central Admin => Manage Services on Server

4

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

5

Again Go to Central Admin = > Manage Service Application

6

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

Click on Excel Services Application

7

Then click on Global Settings

8

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.

9

  • Validate SQL Server Database

Login to SQL Server Database instance and make sure that we have atleast one SQL Database that can serve the data to Excel Service. It is better to prefer any database is not in use by SharePoint for Content or Service Applications or Configuration

10

Once we are done with the Validation of Prerequisites we can proceed with the demo on SQL Data Access via Excel Services.

Demo:

Launch Excel Client Application

11

Choose Blank Workbook

12

Under Data Tab, Click on Connections

13

In the Workbook Connection Dialog, Click on Add

14

In the Existing Connections Dialog, Click on Browse for More…

15

In the Select Data Source Dialog, Select +NewSQLServerConnection and click on Open

16

In the Data Connection Wizard, enter SQL Server Instance name and click Next

17

Select Database and the corresponding list from the database and click Next

18

Click on Authentication Settings…

19

In the Excel Services Authentication Settings Dialog, Select None and click Ok

20

In Data Connection Wizard click Finish

21

In Workbook Connections Dialog, Click Close

22

In the Data Tab, click on Existing Connection

23

In the Existing Connections choose connection that we have just created and click Open

24

In the Import Data Dialog, choose any option of your liking to render the data, here we are selecting Table as mode of Data Rendering in Excel

25

And if connection is successful, we can see the data imported into the Excel Workbook from SQL Server

26

Now save this Excel Sheet to SharePoint

Go To File Menu => Save As => Choose SharePoint

27

Select any Document Library of your choice to save the file to and click Save

28

Now Launch the Browser

Browse the Document Library to see the document we just saved

29

Click on the document to get it rendered with in Web Browser using Excel Services

30

Select Yes if as asked for Data Refresh

31

32

And sure enough we will get the updated data from SQL Server directly landing into this Excel Workbook

33

Similarly we can render the Chart View for the same data and get it refreshed from SQL Database using Excel Services as shown below

34

So we can see how simple it is to connect Excel Workbooks with SQL Data Source and get them refreshed from within the Web Browser using Excel Services.

Hope you find it helpful.