Configure PowerPivot for SharePoint 2013 – Part 5


In this post I will create a basic Power View Report by creating a connecting from PowerPivot Add-On. I will create a Table, chart and map.

I Opened Excel 2013 and clicked on Insert Power View button to enable PowerPivot and Power View Add-on.


You must click Enable


Wait for it to Finish


Here you go. You must be able to see PowerPivot and Power View Ribbons. Click On Manage


Now click on Get External Connection. We will use a connection that we created in last post.


Click existing Connections


Click Browse for More


Browse to your SharePoint site where you have your data connection uploaded.


Select your data connection


It will be visible. Click Open


Click to Test



Click Design to Design Query


You will see Dimension and Measurement section on the left. Expand the required Column and right click to add it to result.


It will not show any result. Yes it will not because we do not have any measurement.

Lets click on … Icon next to Adventure Works to filter the measurements by cubes.


Let’s Select Sales Summary.


Use Right Click or Drag the column to the right.



The results will show up now.


Click Ok when done.


PowerPivot will now load data in Excel. I have tested with 2.5 million rows.


It will be visible in Excel PowerPivot section.


Let’s Add a column with Formula to calculate Tax Percentage.


Now Click on Power View from Insert Ribbon and Change the title.


Use the Fields section to show fields in content section. Use Checkbox or Drag.



Click in an empty area and select the fields again to add a second section.


Select the 2nd Area which will enable Power View Ribbon for you.


Select the option you like.


You can also change the chart if you like.


Add a second Power View and select the fields. Select the area and select Map to see a live map of results.


Save the excel sheet to SharePoint library.

Before you browse the excel sheet in browser you must give access of PowerPivot cubes to users.



 
 


Let’s go to the library.



Check out the Map.


End of Part 5. In the next Post I will use SQL Server Reporting Services to display data.

  

Configure PowerPivot for SharePoint 2013 – Part 4


Although I have configured SQL Reporting, PowerPivot and Power View in last 3 posts but to see the real power of these features I need SQL Server Analysis Services. PowerPivot configuration in SQL Server does add an instance of Analysis Services but it is mostly used by PowerPivot and some of the features of standard Analysis services does not appear to work. I tried to import an Analysis services database using xmla file and got the following error.

Cube Permissions are not supported for Analysis Services instances that are deployed in SharePoint integrated mode.

So first thing I will do is to add a SQL Server 2012 Analysis Services instances to my Existing SQL Server installation. I remember during my initial SQL server installation I did not installed it.

First thing first let’s get SQL Server Setup and click Add Features to Existing Installation.

Click Ok

I Unchecked the cumulative update check box and click Next to install Setup support files.

Click Next

Select “Add Features to an existing Installation of SQL 2012” and choose MSSQLServer from dropdown. Click Next

Choose Analysis Services and Click Next

Click Next

Click Next

Enter Analysis Services service account and password. I am using administrator. It is good to use a separate account. Click Next

Click Add Current User and click Next

Click next

Click next then Install

Click Close when done. Close SQL Server Installation Center.

Open SQL Server Management Studio. Choose Analysis Services from drop down and click Browse for more. Expand Analysis Services and choose NON PowerPivot instance.

I was able to connection Analysis services. Next was the get an Analysis Services Project and create a cube.

I went to http://msftdbprodsamples.codeplex.com/releases/view/55330 and downloaded the Adventure Works database and Cube file. Database was created lot easily because it was just an MDF file. But when it came Cube there was something I have to learn. SQL Server 2008 and earlier had SQL Server Business Intelligence Studio which in 2012 is not there. After little “Bing” I had to run SQL Server setup once again to Install SQL Server Data Tools. Following same procedure I selected SQL Server Data tools.

Then I downloaded AdventureWorksDW2012 Data File and attached the database to my SQL Server.

Then I downloaded AdventureWorks Multidimensional Models SQL Server 2012 and open it in BI Tools. Modify the connection string to my local server and click on solution to Deploy.




Our basic setup is now done. Let’s create a Basic Excel sheet that display a chart and slicers. This report does not use PowerPivot function to connect with data.

Let’s Open Excel and let’s create a data connection and then save it to BI Site.


Enter Server name and click Next


I will select Sales Summary and Click Next.


Click Browse and Browse to your SharePoint Site Data Connection Library.


Select Data Connection Library


Type Name and click Save


Click Finish and setup the properties and click OK.


Now it is up to you to decide how you want to setup the report. I would say let’s create a chart, a Pivot table and couple of slicers to show sales to our CEO J so I choose Only Create Connection


Let’s Pick up Project Categories for Column and some Sales and Tax values for Chart.


Scroll up and choose these fields


Now Select the Chart and choose “Insert Slicers”


Let’s choose Region and Country



Let’s try the slicers


Lets save the report to SharePoint.


Close Excel and let’s view the report.


Click on Sales Summary



As you can see that we are able to use slicers to update report. In the next post I will show you how to do connection using PowerPivot and create a Power View Report.

End of Part 4