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.
I Unchecked the cumulative update check box and click Next to install Setup support files.
Select “Add Features to an existing Installation of SQL 2012” and choose MSSQLServer from dropdown. Click Next
Choose Analysis Services and 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 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