Pages

Tuesday, October 18, 2011

Tracking Data over Time Visually in Power View aka Project Crescent

All right, I know most of you Microsoft BI fans would be gushing over the new announcements made in PASS Summit 2011 last week. Now onwards, Project Crescent will be officially known as Power View, SQL Server Denali as SQL Server 2012, Juneau as SQL Server Data Tools and finally, we have a mobile BI strategy! (you can read the official press release from here and some more details on Power View from here). I decided to celebrate the occasion by blogging about one of the new features I particularly like in Power View in CTP3 version – tracking data over time visually.

For this demonstration, I have used the computer in SQL Server Virtual Labs. This is a great place to kick-start your SQL Server 2012 journey. Apart from the great tutorials, I also access it when I have to test out some new feature. Follow the steps below to create a Power View report with the ‘track data over time’ functionality:-

1) Go to the SharePoint site and click on Data Sources. Then click on the small dropdown icon on the right of the data source name and select the Create ‘Crescent’ Report option as shown in the image below.

1 Create Crescent Report

2) If this is the first time you are opening the Crescent report, there is a chance that you might get the following error -
An error occurred while loading the model for the item or data source. Verify that the connection information is correct and that you have permissions to access the data source.

2 First time error when opening Project Crescent report

This is a bug within the current CTP and this should go off if you refresh the address bar of the browser.

3 Crescent report layout

3) Select Category under Product and Quantity under Sales. A table will be displayed in the layout with the values of Category and Quantity.

4 Selecting Category and Quantity

4) Click on the Scatter chart option as shown in the image below.

5 Selecting scatter chart

5) Resize the chart to occupy the full space. A scatter chart requires 3 measures – for the X axis, Y axis and the size of the bubble.  Since we have already selected the Quantity measure, we just need to select 2 more measures for the Y axis and size. Select Revenue and # Products.

6 Selecting measures for scatter chart

6) Once that is done, select Year under Date and this will automatically be taken as the Play Axis of the scatter chart. You should be able to get the graph as shown below.

7 Select Year as the Play Axis 

7) Give a title and then click on the save button on top left corner. Enter the destination path as well as the file name and then click ok to publish the report in SharePoint.

 8 Publish reports

8) Go to the destination path specified in the step above and click on the report name to preview it. Click on the play button to see visually how the Category changes over the year.

Track Categories over Years

You can also click on a particular category and track the changes for that particular category over the year.

Track Selected Category

Isn’t that sweet and simple? Smile Coming from a manufacturing domain, I can already think of lots of scenarios where this can be used (e.g.: tracking price changes, profitability, revenue, stock, sales, etc over time).

Of late, I have come to realize that some people face difficulties in following blogs by just reading the steps or by seeing a few images. So, I have taken a conscious decision to video-blog the steps whenever I can. You can find the video for this blog below

Tracking Data over Time in Power View

Let me know your feedback about this initiative and whether it is helping in conveying my message better. Cheers!

2 comments:

  1. it's needed sharepoint to "publish" power pivot datasources to feed reportings or can we use something like SSAS to create "powerpivot databases"?

    ReplyDelete