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

In my previous article, we have covered configure SSAS, importing sample DB for SSAS now it is time for us to explore in detail 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 do 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”

Enter the SQL SSAS Server name as per your requirement.

Select AdventureWorksDW2012 and Adventure Works cubes in the below list.

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

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

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

Insert slicer to drill down the report:

Select State-Province

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

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

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

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

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”

d. Enter the mandatory values in General tab

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

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

Hope this helps happy reporting in SharePoint 2013 !!!

You may like following SharePoint tutorials:

• Data Refreshing issue Access Denied to Analysis services Database.
• Microsoft office 365 chatbot
• Add left navigation links using REST API in SharePoint
• Show/Hide/Disable HTML Controls using jQuery
• How to Call SharePoint Search using REST API
• Excel Service to import SSAS Cubes and Generating Pivot tables in SharePoint BI
• Importing SQL Server Analysis Services (SSAS) Sample CUBE and Processing the CUBEs
• Configure Active Directory Domain Services in Windows Server 2012
• Install and configure SQL server reporting services (SSRS) in SharePoint 2013/2016