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

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”

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 !!!