Thursday, August 30, 2012

Linking and Brushing Visualization with PowerPivot

The other day, I was reading a great post by David Churchward (twitter) on Gantt Chart with Resource Loading. PowerPivot is really powerful with manipulating the data and everyone knows that, but his post made me think from a visualization perspective. Maybe PowerPivot deserves more credit than it usually receives in this department and I decided to test the brushing technique that I described in my last post with PowerPivot. I must say that I was pleasantly surprised with how easily you can achieve it in PowerPivot.


With all the buzz around hurricane Isaac and also considering that I look into the weather a lot nowadays (because I drive 160 miles daily for work), I downloaded a list of the main tropical storms and hurricanes in the US for the last 5 years. You can download the completed PowerPivot v2 workbook from here, if you are interested to go through the data or the technique. To reproduce the visualization, follow the steps below

1) Import the storm data into PowerPivot using the Create Linked Table option. You can download the data from here.

1 Create Linked table for fact

2) Once that is done, we will have to make dummy tables in PowerPivot for each slicer that we need to analyze by. In my case, I am going to analyze by the Year, Month, Storm Category and Storm Type. So we will need to make new sheets for each of these 4 columns, remove the duplicates and then import them into PowerPivot using the Create Linked Table option.

2 Create linked tables for slicers

3) Make inactive relationships from the fact table to the Year, Month, Type and Category tables.

3 Mark inactive relations

4) Next, we will be creating a measure to have the count of all storms in the Fact table.

WindCnt:=CountRows (
Values ( Fact[Name] )

5) Now, create 2 measures – one which will show the count of storms for selected values and one for the rest of the count.

SlicedCnt:=Calculate (
USERELATIONSHIP ( Fact[Year], Year[Year] ),
USERELATIONSHIP ( Fact[Type], 'Type'[Type] ),
USERELATIONSHIP ( Fact[Cat], Category[Cat] ),
USERELATIONSHIP ( Fact[Month], Month[Month Name] )

UnslicedCnt:=[WindCnt] - [SlicedCnt]

Note that the [WindCnt] and [UnslicedCnt] measures will not be impacted by the change in the 4 new tables that we created, since the relationships are inactive. However, the [SlicedCnt] measure will change based on the 4 new tables as we are using the USERELATIONSHIP function to relate.

4 Measures

6) Now we can create a PivotChart from the PowerPivot tab which has the SlicedCnt and UnslicedCnt on the Values, Fact[Month] on the categories and slicers for Month[Month No], Year[Year], Type[Type] and Category[Cat]. Basically, the sources for the slicers are the 4 new tables we created while the chart will have the field from the fact table. Make sure that the chart type is Stacked Column Chart.

5 Pivot chart

You can safely ignore the relationship warning shown in the PowerPivot field list.

7) Similarly, create 2 other charts with Fact[Year] and Fact[Cat] on the category respectively. Also ensure that the slicers are connected to all three pivot tables. This can be done by right clicking on each slicer, selecting the PivotTable Connections property and then selecting the checkbox for all three pivot tables as shown in the image below.

6 Pivot table connections

8) Now you can click on the slicers and see the chart get the brushing effect for the selection. For eg, if I select Hurricanes of Cat 4, I get the following result

7 preview

We can easily see the relationship that there is a hurricane of cat 4 in all the years after 2008, and that it usually strikes in Aug or Sep. We can also get a rough idea of the total numbers with respect to the selected values. If all the values in the slicers are selected, you will get the following result

8 brushig effect initial view

Hopefully, this post will be helpful to you guys for visually unleashing the power of PowerPivot!

Update : 31/8/2012

Javier Guillen (blog | twitter) has come up with an easier idea for doing the calculations. We can keep the relationships as active and instead use these calculations for the measures

WindCnt:=Calculate (
CountRows ( Values ( Fact[Name] ) ),
AllExcept ( Fact, Fact[Cat], Fact[Year], Fact[Month] )

SlicedCnt:=CountRows (
Values ( Fact[Name] )

UnslicedCnt:=[WindCnt] - [SlicedCnt]

I think this is a better idea as we don’t have to make dummy tables just for the sake of slicers and can reuse the dimension tables if it is already there. Thanks Javier!

Sunday, August 26, 2012

Linking and Brushing Visualization with SSRS

In spite of being in the US for more than 8 months now, I still haven’t lost touch with the English Premier League (football). For those who don’t know me, I am a big fan of Manchester United and hence by default, loathe Manchester City. They are like our biggest rivals and won the Premier League last season, and that too in the last minute of the last game! I never thought that the day would come when I would even mention them in my blog, let alone praise them. But then last week, they started a new crowdsourced analytics project called MCFC Analytics and I can’t stop thinking about what a fantastic idea it is. Needless to say, I was among the first set of people to request the data from them and have been playing around with it this week. I was also reading about the brushing and linking visualization techniques, and hence thought of using this data for illustrating this concept within SSRS.

Linking and Brushing in SSRS

Before I go further, let me explain what brushing and linking is. “Brushing lets the user selects data points that get highlighted in one or more views of the same data. When several views are involved, the fact that all of them highlight the same data points is commonly referred to as linking (and the views are called coordinated multiple views)” – Robert Kosara. For demonstrating this concept in SSRS, I am using 5 bar charts – Goals by Venue, Goals by Goals From, Goals by Substitutions, Goals by Team and  Goals by Opposition. Follow the steps below to reproduce the report:-

1) Download the data from and then import it to a table in your SQL Server database called Players..

2) Create a new report called MCFC, and point the datasource to your database containing the Players table.

3) Create 2 hidden report parameters – RP_Field and RP_FValue with no available values and All as the default value. The RP_Field will be used for determining the series field of the chart (can be Venue, GoalType, Starts, Team or Opposition) and the RP_FValue will actually pass the field value which is being clicked.

1 Parameter properties

4) Make a new dataset called DST_Main and then use the query below as your dataset query

SELECT [Player Surname],
       [Player Forename],
-- Field used to dynamically calculate the series field
WHEN @RP_Field = 'All' THEN '0'
WHEN @RP_Field = 'Venue' AND @RP_FValue = [Venue] THEN '1'
WHEN @RP_Field = 'Venue' AND @RP_FValue <> [Venue] THEN '0'
WHEN @RP_Field = 'Starts' AND @RP_FValue = [Starts] THEN '1'
WHEN @RP_Field = 'Starts' AND @RP_FValue <> [Starts] THEN '0'
WHEN @RP_Field = 'GoalType' AND @RP_FValue = [GoalType] THEN '1'
WHEN @RP_Field = 'GoalType' AND @RP_FValue <> [GoalType] THEN '0'
WHEN @RP_Field = 'Team' AND @RP_FValue = [Team] THEN '1'
WHEN @RP_Field = 'Team' AND @RP_FValue <> [Team] THEN '0'
WHEN @RP_Field = 'Opposition' AND @RP_FValue = [Opposition] THEN '1' ELSE '0'
-- Field used to dynamically sort the charts
WHEN @RP_Field = 'All' THEN [Gls]
WHEN @RP_Field = 'Venue' AND @RP_FValue = [Venue] THEN [Gls]
WHEN @RP_Field = 'Venue' AND @RP_FValue <> [Venue] THEN '0'
WHEN @RP_Field = 'Starts' AND @RP_FValue = [Starts] THEN [Gls]
WHEN @RP_Field = 'Starts' AND @RP_FValue <> [Starts] THEN '0'
WHEN @RP_Field = 'GoalType' AND @RP_FValue = [GoalType] THEN [Gls]
WHEN @RP_Field = 'GoalType' AND @RP_FValue <> [GoalType] THEN '0'
WHEN @RP_Field = 'Team' AND @RP_FValue = [Team] THEN [Gls]
WHEN @RP_Field = 'Team' AND @RP_FValue <> [Team] THEN '0'
WHEN @RP_Field = 'Opposition' AND @RP_FValue = [Opposition] THEN [Gls] ELSE '0'
FROM   [MCFC].[dbo].[Players]
UNPIVOT ([Gls] FOR GoalType IN ([Goals from penalties], [Goals Open Play], [Goals from Set Play], [Goals from Corners], [Goals from Direct Free Kick], [Goals from Throws])) AS [Gls]

5) Make 5 stacked bar charts with [sum(Gls)] as Values and [Srs] as the Series Group. The category for the five charts are Venue, GoalType, Starts, Team and Opposition respectively.

2 Chart properties

6) Change the Color expression of all the five charts as below


This is done so that we can see the brushing effect in the bars.

7) Add a textbox on the top of the report with RESET as the value. Set the action properties of the textbox as shown below

3 Reset button

8) Set the action properties of the Venue chart as shown below

4 Chart action properties

Similarly, set the action properties of the other 4 charts.

5 Other chart properties

9) Set the sorting property for the category group as

=iif(Parameters!RP_Field.Value="All" or Parameters!RP_Field.Value="Starts",sum(Fields!Gls.Value),Sum(Fields!SrsGls.Value)) 

You can also add the category group (for eg, the Venue chart will have Venue field) as a secondary sort option, as shown below.

6 SortProperties

This sorting will make it easier for us to understand the data.

10) Now preview the report and you should see it as shown below

7 Report initial view

11) Click on any of the bars, and you shall see the report getting the brushing effect. For eg, you can see it as shown below when Manchester City is clicked in the Team chart.

8 Report brushing effect

Through this chart, we can easily see that Man Utd had more home wins than away, scored more goals through open play and scored most against Arsenal.

If you want to see which team scored most through Corners, click on the corners bar in Goals From chart.

9 Report Final 

Very easily, you can see that Manchester city scored the most and Blackburn Rovers conceded the most goals through corners. You can already see the power of linking the charts instead of keeping it as individual unlinked ones.

12) Click on the Reset button to bring the report to the original state in step 10.

This post has been written keeping an experienced SSRS developer in mind. If you felt that the explanations are not enough, feel free to download the report file and check yourselves.

Download MCFC.rdl

As always, I am interested in hearing your views and opinions about this. Feel free to comment on the MCFC Analytics project as well as this brushing concept.

Note: I am part of the core team that is organizing a SQL Saturday in Charlotte, NC on October 27. If you are around the North Carolina region, go and register yourselves for the event here. If you are far from us and can’t come for the event, spread some love on twitter and other social media sites using the hash tag #sqlsat174.