Kasper de Jonge PowerPivot Blog

I love music, i listen to it a lot on my ipod and on my computer.I also love statistics, I send all the tracks I listen to the online music service last.fm where I have stored all the tracks i have played since 2006.

This is a list of my top artists (yes I am kind of a metal head):

Wouldn’t it be great if I could load all my played tracks into the PivotViewer? This blog posts describes how I got all my played track information into the PivotViewer. In a previous blog post is described how you we can make a PivotViewer application on top of PowerPivot. Today we are going to load data from Last.FM into PowerPivot and base a PivotViewer app on top of PowerPivot.

Read more…

With the release of the Pivotviewer for SSRS application we finally can build our own PivotViewer app on top of our data. In this blog we are going to build an app on top of a PowerPivot sheet.

I have a PowerPivot file that i use for presentations where i loaded data from the contoso database. The scenario is that we use this PowerPivot application to determine which stores have little products on stock.

The PowerPivot sheet looks like this:

To gain even more insight into this scenario, we want to see if there is a relationship between the stores that have little products on stock per employee. We are going to build a PivotViewer app on top of this PowerPivot file. First thing we have to do is of course install the PivotViewer Extension for Reporting Services as I described in a previous blog post.

To create a PivotViewer application we have to do two things:

• Create the images that will be the representation for each store. We create this image by using a SSRS report to be used in the PivotViewer BI Report Crawler to automatically create images for each store that will feed the PivotViewer.
• Next we have to configure the PivotViewer application using the BI Collection Administration SharePoint List. Here we configure what data will be used to navigate through the Pivotviewer data and show data for each store.

We start by creating the SSRS report. As datasource we use the PowerPivot app that is published to SharePoint.

As we can see here i have created a report which gives me the details for a store. This is very important because the report will be used to generate thumbnail for each store. We need a parameter that can be passed through for each store, i just dragged in the Store dimension at filter part of the query designer and selected parameter.

Secondly we  need to remember this report will be used to show in the PivotViewer, we need to be able to identify which store this is and what the state of this store is. In the demo done by Amir Netz at the MS Bi Conference keynote and also at the included PivotViewer demo they have added a background color so we can quickly identify the state of the a store.
So we go to the report Body and set the backgroundcolor dynamically depending the results:

=iif(first(Fields!Sum_of_DaysInStock.Value, “DataSet1″) / first(Fields!EmployeeCount.Value, “DataSet1″) > 70000,”Red”,
iif(first(Fields!Sum_of_DaysInStock.Value, “DataSet1″) / first(Fields!EmployeeCount.Value, “DataSet1″) > 50000,”Orange”,
iif(first(Fields!Sum_of_DaysInStock.Value, “DataSet1″) / first(Fields!EmployeeCount.Value, “DataSet1″) > 30000,”Yellow”,
“Green”)))

When i run this i get the following report for a specific store:

We save the report in the PowerPivot gallery on sharepoint at the same location as the PowerPivot file:

Now we have to go and create the collection of items for the Pivotviewer. We have to configure the BI collection administration to create a image for each item and determine what data we want to show in the PivotViewer for each item.

In Inventory we need to supply a unique name, we call it StoreInventory.

Next we have to configure the report, first we have to point to the location of our report with a URL that will render a PNG file and is able to pass a parameter to it, we can use .net like parameter syntax to give the location of the parameter in the string, in my case this was:
http://powerpivot/ReportServer_SQL?http://powerpivot/storedemo/viewerreport.rdl&rs:Command=Render&rs:Format=image&RC:outputformat=PNG&DimStoreStoreName={0}

And as description i used “store report”.

I’m going to skip the Dataset for a second because i want to finish the report parameters.  These parameters will determine what parameters are going to be passed to the report while generating a thumbnail for all the stores. As Report Parameter Datasource we select the location to the PowerPivot file: “data source=http://powerpivot/StoreDemo/bigdemo.xlsx”.

At the Report Parameter Datasource Type we use “Adomd.net”. As Report Parameter Query I have created a simple MDX that returns all the stores:

select
[DimStore].[StoreName].[StoreName].members
on 0 from [Sandbox]

At the Dataset part of the collection admin tool we are going to configure what data has to be shown for each items of the PivotViewer. So in our case we want to show data for each store, I want a store to have these properties:

• StoreDescription
• ContinentName
• RegionCountryName
• Stateprovincename
• EmployeeCount
• DaysInStock
• AverageDaysinStock

As Dataset Parameter Datasource we  again select the location to the PowerPivot file: “data source=http://powerpivot/StoreDemo/bigdemo.xlsx”.  At the DatasetParameter Datasource Type we use “Adomd.net”. As some of the requested properties are not in the dimension Store I altered my PowerPivot file and added the columns as calculated column to the Store table. Now all we have to do is add a simple MDX Query:

select
[DimStore].[StoreName].[StoreName].members *
[DimStore].[StoreDescription].[StoreDescription].members *
[DimStore].[ContinentName].[ContinentName].members *
[DimStore].[RegionCountryName].[RegionCountryName].members  *
[DimStore].[Stateprovincename].[Stateprovincename].members *
[DimStore].[EmployeeCount].[EmployeeCount].members *
[DimStore].[DaysInStock].[DaysInStock].members *
[DimStore].[AverageDaysinStock].[AverageDaysinStock].members
dimension properties
[DimStore].[DaysInStock].[DaysInStock].key,
[DimStore].[AverageDaysinStock].[AverageDaysinStock].key
on 0 from [Sandbox]

a few things are interesting to notice: first of all the order of items is importent, the order you put in here is the order that you can drill down into the data when using Pivot Viewer:

A second thing that is importent is how you want to show your numerical data. By default a numerical value will be displayed as an string like this:

But a numerical field contains a lot of different values a choice of all of them is not very userfriendly. You can make the PivotViewer aware that this is a numerica value by adding a dimension property to the dataset. In my case i added :

dimension properties
[DimStore].[DaysInStock].[DaysInStock].key,
[DimStore].[AverageDaysinStock].[AverageDaysinStock].key

This will make sure we have a value slider for the DaysInStock and the AverageDaysinStock like this:

Ok now we have filled in all the properties for this PivotViewer app.

We now can start preparing the data by running the crawler for our newly added item:

The database is now filled with information about the stores that will be used in the PivotViewer app:

Since the data is available we now can add our PivotViewer BI Data Browser to a new page in sharepoint. We just add this webpart to your page:

And configure it, all we have to is point the webpart to our newly added item by typing the exact unique name in the Inventory field:

When you press OK the control should be succesfully loaded and you are ready to go:

First of all the PivotViewer Extension for Reporting Services is ready for download here. The installation is described in an excellent installation guide that is included in the package, a must read for anyone who wants to start with the PivotViewer Extension for Reporting Services.

What do you have to do to install it into your SharePoint machine, in my case i have a single box. My sharepoint is at http://powerpivotand have integrated SSRS with the following service url http://powerpivot/ReportServer_SQL  my SQL Server is installed at PowerPivot\powerpivot (machine name\instancename).

The following steps you need to do:

• Open an command prompt with Administrator access and run:
  BICollectionDBProvision.exe -sPowerPivot\powerpivot -op
  This will create a database on a local database in my case “PowerPivot\powerpivot”
• Open the SharePoint 2010 Management Shell command prompt with Admin access:
  .\installBIPivot.ps1 “-whttp://powerpivot” -oi -y
  This will install SharePoint specific features for the Pivotviewer, like the Crawler and viewer control on my SharePoint machine “http://powerpivo”. This syntax is different than from the intallation guide, i had to run it with .\ in front of it because my location wasn’t a certified location.

This is all you have to do. Remember this is not a replacement for the installation guide just gives you what i did.

Next up is installing the Demo app. Again open the command prompt with admin privilages and run

BICollectionSampleBuilder -rhttp://powerpivot/ReportServer_SQL -whttp://PowerPivot -gSampleGallery -dSampleDocuments

this installs the app files on your SharePoint.

Now all you have to do is create a page in sharepoint and add a “PivotViewer BI Report Crawler” webpart to your page. Select the Accountmanagers invetory and start crawling, the report is now rendererd for all options in the pivot viewer.

Finaly create a second page and add the “PivotViewer BI Data Browser” webpart to the new page and in the properties set the Inventory to “Accountmanagers”. Your Pivotviewer app will now be working:

Again make sure you read the manual, i had trouble as well because i didn’t read it as well as i should !

Check out this video about installation as well: http://petcu40.blogspot.com/2010/07/pivotviewer-extension-for-reporting.html

Ok folks it is here, the CTP1 of PivotViewer for Reporting Services is ready for download. Check out the blog post of Cristian Petculescu from the team.

Get your free download from here.  It is a concept project. It’s not supported, and not a feature.

However, it is really cool! It allows you to easily create stunning visualizations on top of your BI data – and these visualizations are fully dynamic: you decide at runtime which subset of data you should see, it shows the actual live numbers, and you decide how much (meta)data you associate with each card. Also, all of the cards are generated by the service, so it is very convenient. Of course it comes with sample data, and a tutorial.

I installed it this morning, it’s not really hard. You need to have Sharepoint 2010, powerpivot server (AS for sharepoint), reporting services in sharepoint mode installed on your machine.

data sources that are supported:

Although, the typical scenario is for data to be in PowerPivot, and the PowerPivot server (Analysis Services 2008R2 in SharePoint mode) is a required prerequisite, the following data sources are supported:

• Analysis Services 2008R2 (Adomd.NET provider)
• SQL Server 2008 or later (SqlClient namespace)

This is what i made this morning using the sample app that is supplied:

Next up will be a short install tips document and then i will be creating my own PivotViewer app on top of PowerPivot.