Excel Service to import SSAS Cubes and Generating Pivot tables in SharePoint BI

display pivot table in sharepoint 2013
SharePoint deveopment training course

Objective:
In my previous article we have covered configure SSAS, importing sample DB for SSAS now it is time for us to explore in-detail on how to leverage Excel services to get SQL SSAS data from CUBES. In this article I would like to walk you through the steps to import SQL SSAS data from CUBES and create PIVOT table reports.

Why we need to learn this being a SharePoint techie????
This step / approach is really important if you want to configure BI in SharePoint so my friends I advise you to learn this process.

Open a new excel workbook and create connection to SSAS Cubes by following the steps advised below. Click on Data from ribbon control in excel
a. Click on Form other sources
b. Click on “From Analysis Services”

generate pivot table in excel
generate pivot table in excel

Enter the SQL SSAS Server name as per your requirement.

Excel Service to import SSAS Cubes
Excel Service to import SSAS Cubes

Select AdventureWorksDW2012 and Adventure Works cubes in the below list.


generate pivot table in excel 2010
generate pivot table in excel 2010

Without fail select “None” for excel services authentication settings. By selecting this option we are forcing SharePoint to use dedicated Target Application.

generate pivot table in excel 2013
generate pivot table in excel 2013

Select “PivotTable Report” option to render the cube in excel work book.

generate pivot table in excel sharepoint 2013
generate pivot table in excel sharepoint 2013

Select “Internet Sales Amount” fields from the right side and drop it in left side.

generate pivot table in excel sharepoint
generate pivot table in excel sharepoint

Insert slicer to drill down the report:

generate pivot tables in excel sharepoint 2013 workbook
generate pivot tables in excel sharepoint 2013 workbook

Select State-Province

generate pivot table in excel sharepoint 2013 from multiple worksheets
generate pivot table in excel sharepoint 2013 from multiple worksheets

As soon as you add the slicer you will see the excel as below:


generate pivot table in excel sharepoint 2013 step by step
generate pivot table in excel sharepoint 2013 step by step

Save the excel file and upload it in document library under SharePoint BI site collection.

excel pivot table in sharepoint 2013
excel pivot table in sharepoint 2013

Double click on SSAS excel file and it will render the excel cube as below:

publish excel pivot table to sharepoint 2013
publish excel pivot table to sharepoint 2013
how to create a pivot table in sharepoint 2013
how to create a pivot table in sharepoint 2013

Now let us perform some filter operations with a non-admin account.
Click on Yes in the pop-up window.

how to create a pivot table in sharepoint online
how to create a pivot table in sharepoint online
how to create a pivot table in sharepoint 2010
how to create a pivot table in sharepoint 2010

Note: To achieve this seamless progress ensure that BI service has required access on CUBE
Follow the below steps to add/grant the required access to BI service account.
a. Logon to SQL Server with SQL administrator account
b. Expand the Databases and select Roles.
c. Right click on Roles and select “New Role”

how to create a pivot table in sharepoint 2016
how to create a pivot table in sharepoint 2016

d. Enter the mandatory values in General tab


display pivot table in sharepoint 2013
display pivot table in sharepoint 2013

Select Membership and add all the required users and click on “Add”

display pivot table in sharepoint 2010
display pivot table in sharepoint 2010

Select Cubes and add Read permissions for Adventure works cube and click on OK

display pivot table in sharepoint 2016
display pivot table in sharepoint 2016

Hope this helps happy reporting in SharePoint 2013 !!!

Check out Best Alternative to InfoPath -> Try Now

free sharepoint training

SharePoint Online FREE Training

JOIN a FREE SharePoint Video Course (3 Part Video Series)

envelope
envelope

About Krishna Vandanapu

I am Krishna.Vandanapu a SharePoint architect working in IT from last 13+ years, I worked in SharePoint 2007, 2010, 2013, 2016 and Office 365. I have extensive hands on experience in customizing SharePoint sites from end to end. Expertise in SharePoint migration tools like Sharegate, Doc Ave and Metalogix. Migrated SharePoint sites from SharePoint 2007 to 2010 and 2010 to 2013 several times seamlessly. Implementing CSOM with Microsoft best practices. Spent quality time in configuring SharePoint application services like User Profile, Search, Managed Meta data services etc. Now exploring SharePoint Framework and SharePoint 2019

View all posts by Krishna Vandanapu →