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.