Configure PowerPivot for SharePoint 2013 – Part 7


As we have seen the other important components of Business Intelligence, it is now time to look at PerformancePoint services and create some charts and graphs. PerformancePoint Service application features not only allow us to create Powerful Business intelligence reports but also provide the ability add dashboards, KPIs and filters. It is more like a consolidation solution. Most of the dashboards I have seen in past are combination of Excel, Reporting Services and PerformancePoint Report builder. Enough Talk let’s set it up.

Open Central Administration à Manage Service Application à New PerformancePoint Service Application

Give it a name and select the default proxy list checkbox

As this is my Development VM I will choose an existing application account.

Now we must complete the following steps

Before we do that we have to do one more thing. We have to start PerformancePoint Service. Go to Manage Services on Server on Central Administrator.

Click Start.

Now go back to PerformancePoint Service Application from Manage Service Application à New PerformancePoint Service Application.

Click on PerformancePoint Service Application Settings

Provide Unattended Service Account and Click OK.

We are ready to create our first BI Item. Let’s go to our BI site.

Click on PerformancePoint Content from Quick Launch and then PerformancePoint Ribbon. You can now ran Dashboard Designer.

The installation will start

Just like previous reports we first will create data connection.

As you can see that you have many choices there but Select Analysis Services and Click OK.

Click on Properties Tab and enter name.

Now I faced an issue. The Editor Tab Database drop down never showed the computer name.

This could be due to permission. My service application is running with contoso\Spfarm account which does not permission to connection to SQL Server Analysis Services. Let’s add him.

Right Click on Server Node and choose Properties.

Click Security à Add

Problem was not solved. After little “Bing” I found it that I have to Install Microsoft SQL Server 2012 ADOMD.NET and Microsoft Analysis Services OLE DB Provider for Microsoft SQL Server 2012 from the Microsoft SQL Server 2012 Feature Pack  and


Install Microsoft SQL Server® 2008 R2 ADOMD.NET and Microsoft Analysis Services OLE DB Provider for Microsoft SQL Server 2008 R2 from the Microsoft SQL Server 2008 R2 Feature Pack


IISRESET Close and Open Dashboard Designer and bingo. Select Cube as Sales Summary

Click Test Data Source

If you right click and view properties of the Data Source you will be sent to data connection library.

Let’s Create a Sales Summary Chart

Select the Data Source and click Finish.

Report Designer will Open. Rename the report to SalesSummary

Drag Sales Amount, Tax, Gross Profile to Series from the Field List and Drag Product Categories to Botton Axis. The report will look like tis. Click Save All from top.

Let’s view the Report on BI Center.

Select the item then Click on Eclipse (…) and select Display Report.

Report will open in New Window in full screen mode.

You can use these controls to change the chart anytime you need.

Let’s take a look at a great tool called “Decomposition Tree”. All you have to do is right click on any of graph item and Decomposition Tree. Be careful of the area you right click. If you right click on Blue area you will look at decomposed Sales Amount. Once the dialog opens up. Click on Bikes and Select Sales Territory.


Select Country. Now Lets Click on United States to further explore. Lets see how much sales amount is came from Promotions.


We now have learned that most of our income came from Volumne Discounts. Lets see what kind of bikes are being sold on discounted.


Last I saw which Products were sold.


Then I added Month to see when Mountain Bike 200 was sold monthly.

I can go all the way. There are other things to test as well. Like KPI, Scorecards but I think that will be too much for one blog post. May be some other day. I will try to deveop a dashboard to combile Excel, Reporting Services, Power View and PerformancePoint Content. The purpose of these blogs is to show how easy it is to setup and develop reports. You may need to learn many other things to become champion of BI features in Microsoft Stack. Being a SharePoint Expert knowing how to configure and use is most important.

End of Part 7.

Configure PowerPivot for SharePoint 2013 – Part 6


In this post I will show you how you can create SQL Reporting Services Report using analysis Services. If you have to setup Reporting Services. Please follow my previous blog Part 1 to 5.

I went to my Business Intelligence site and my reports library where I have attached Reports Content Types. First I will create a report data source.


And I got the error mentioned below.


I Opened ULS Viewer and open the current log and then filtered by the ID mentioned above.


Error being shown: Microsoft.SharePoint.SPEndpointAddressNotFoundException: There are no addresses available for this application. This error means that either my connection to service application is not available or service is not available. I went to Manage Service application and taken properties of SQL Server Reporting Services Service Application.


There was no problem in there. I went to Manage Services and server and found the issue that my Reporting services service was not running. I started it and did an iisreset.


I refresh the page and tried again. Here you go.


I clicked OK and now we will create a report and will update data source to this library in few minutes.


Now we have to install the report builder application.

Lets Click on Blank Report.


First we have to add a data source to the cube.


Type name select use a connection embedded in my report and click Build


Type Server name and select your analysis services database.


Copy connection string text and go back to Report library and Edit the Report Data Source.


Click on the connection name.


Now Click Ok. Go back to Report Builder tool and select use Existing Connection and click Browse. Select Report library and select the connection.


Now lets add a new Dataset


Enter Name and use a dataset embedded in my report and select existing data source. Now click on Query Designer.


As soon as I click on Query Designer I got the following error.


I need to fix the data source to use windows integrated security. Although I could provide the user name and password in this dialog and select use as Windows Credential but it is better to update the data source.


I choose Stored Credential, typed user name and password and selected Use as Windows Credential and click Test Connection.


Went back to SQL Reporting Builder tool. I deleted the data source and created again. I was able to connect to Query Designer.


 


Although I had specified the unattended account but seems like I entered wrong password. So I went to Central Administration à Manage Service Application à SQL Reporting Service Application à Execution Account


I entered the password again and confirm it. And click Ok.


Came back to report and refresh it and the error went away.


I also choose Product and selected filter to Product Line


Review the Result and Click OK.


Let’s Insert a Chart and Table


Product Line as Category and Sales Amount as Values. Click Next


Choose the Style of your choice and Click Next


Change the Title of Report.


Save the Report to Report Library before we try it in Report Builder


Click on Home Ribbon and select Run


Its works fine here. Click on Deign to go back to design view.


Now let’s see the report in browser. Close Report builder and go to reports library.


As we have now see Report creation in Excel and Reporting Services. In the next blog post we will create report using PerformancePoint Services.

End of Part 6.

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

Configure PowerPivot for SharePoint 2013 – Part 3


Part 3 of this Post is about Testing PowerPivot and Power View. PowerPivot is an Excel Add-on that allows us to create reports in Excel where we can use Slicers and later publish the excel sheet in SharePoint to view it in browser.

Let’s create a Business Intelligence Site collection to test Power Pivot.

Go to Central Administration à Create Site Collection

Provide Title, Url, Select Template as Business Intelligence and primary administrator and click OK.

Click on the site collection link to open.

Let’s see PowerPivot in Action

Click on Site Contents à Documents à Click on Excel Services Sample Workbook

Let’s change the Item to Charcoal from left top slicer of the sheet.

Now let’s click on 2011

Now Let’s Enable Power View

PV is a Site Collection feature that is provided by SQL Server Reporting Services to allow Rich and Powerful reports.

Click on Site Link using left top Global Navigation à Click on Site Settings Icon and Site Settings

Under Site Collection Administration Click on à Site Collection Features

Make sure Power View Integration Feature is activated.

Let’s Create a New Library to store our Reporting Services Reports

Click Site Contents à Add an App and Select document library and choose Document library. Select Advance Options. Set Name as “Reports” and Template to None and click Create.

Go to Library Settings à Advanced Settings à Set Yes for Allow Managed of Content Types and Click OK.

Click Add from Existing Content Types à Change Group to SQL Reporting Services à Add all the content types and click OK.

End of Part 3.  In the next post I will show you how to create a basic report using SQL Reporting services and Power View.