Some Random Thoughts

Ehh, What’s up, SqlJason?

2013-05-16T00:27:00.001-04:00

It’s been more than a month since the last post and you might be wondering – what’s up with SqlJason? Well, let me tell you that the last one month has been really busy and I thought of filling you guys in with some of the juicy stuff that’s been happening!

1) Speaking Events
Last year April was when I started my first speaking assignment in the USA and it was great to have so many different opportunities to present last year. This year April also turned out to be the starting month for my presentations, and I can’t believe that I already presented at five different places this month.

The PASS BA Conference was the first stop, and this was my first experiencing presenting in a big stage. Looks like it went good from the session evaluations (got a 4.6, my session ranks in the top 15 out of the 60+ sessions in the conference)

The attendance was also pretty decent, managed to pull in 98 people which is the highest I have ever got in any of my sessions.

I had lots of fun with my #sqlfamily and also got to meet a lot of new people. Overall, a very fun experience and I am definitely going to attend PASS BA Conference next year. Next stop was SQL Saturday Chicago and this was just the next day after PASS BA Con. After all the partying and the awesome speaker dinner at the previous night, speaking was always going to be difficult task but luckily, everything including the demos went fine. I also got to see some great sessions by Mark V & Doug Lane, Julie Koesmarno, Leonard Murphy. In short, another great experience and a very well organized event. Next on the list was SQL Saturday Jacksonville which took place 2 weeks after the Chicago event. The Jacksonville event was one of my favourites from last year and it turned out to be even better this year as it was the first time all four of us CBIG buddies (me, Javier, Melissa and Rafael) got selected to speak at the same event. And I must say Jacksonville didn’t disappoint me, great crowd as usual and highly interactive (and very generous with the session evals too hehe). A couple of days later, I started off for my first user group speaking event of the year – Triad SQL BI User Group at Winston Salem. A big thanks to the user group leaders (Katherine Fraser and Doug Purnell) for giving me the opportunity, really enjoyed interacting with the user group members there. The next speaking event was the Carolina Code Camp 2013 which was in Charlotte itself (finally a home event! ). Unlike the other four events, the majority of the attendees was from a non-SQL Server background. So it was fun speaking to a different profile of attendees and I must say that this was one of those sessions where the attendees laughed at all of my jokes (either they got all of my jokes or were being REALLY nice with me ).

I remember someone saying that event speaking is really addictive, I couldn’t agree more! Just to prove that, I have 2 more events coming up in the next week:-

a) SQL Saturday Atlanta – I will be speaking on the topic “GeoSpatial Analytics using Microsoft BI” at 1 pm, this weekend. This is going to be another really great event with registrations hitting record levels for a SQL Saturday (800+ including waiting list). If you are coming for the event, definitely drop by and introduce yourself, I would love to meet you.

b) Mariner Webinar – If you are not able to attend my session at SQL Saturday Atlanta, don’t worry. I am doing the same session (“GeoSpatial Analytics using Microsoft BI”) as a Webinar for my company - Mariner on May 21, 2013 12:00 PM-01:00 PM Eastern. You can register for this FREE event by clicking on this link - Click to Register

2) CBIG Updates

As some of you recall, me and three others (Javier, Melissa and Rafael) started a new BI user group this year in Charlotte – CBIG. We are having a great run with attendees steadily increasing from 35 in the first meeting to 50+ in the last meeting (65 being the highest as of now). Even though five meetings are too early to call the user group a success, the popularity of CBIG is evident from the 87 registrations (including waiting list) we received in our fifth meeting (where our seating capacity is only 60). We had already moved from the initial meeting room after our second meeting (where our seating capacity was 30) and now, due to the increasing number of registrations, we are moving our venue from New Horizons to the Microsoft Campus (which has a seating capacity of 85) in July. That said, the numbers are secondary. The primary aim for us is to cultivate a culture in CBIG which mainly revolves around the community spirit as well as a passion for technology. If you are in or around Charlotte, don’t miss out on our user group meetings on the first Tuesdays of every month. We have got meetings lined up till September as of now and the RSVP links can be found at http://charbigroup.com/. Also, don’t hesitate to contact me if you want to speak at our user group, we are always looking for speakers!

3) SQL Saturday Charlotte – BI Edition Announced

And yes, we have also announced the date for SQL Saturday Charlotte – BI Edition. The event will take place on Saturday, October 19, 2013 (which happens to be the Saturday after the PASS Summit 2013, which also is in Charlotte). The planning is going on full steam and we are confident that we will have a great event laid out for all of you guys. Ensure that you register today to extend your PASS Summit 2013 experience.

4) And now the BIG one - Personal Updates

For those of you who know me personally, this might be old news. And for the others, you might recall me saying at the start of the year that I might not match up to my previous year’s activities this year due to personal reasons. Well, the personal reason is given below.

Yups, I am going to be a father to a baby girl this August 12 and I have no shortage of adjectives to describe my condition right now – excited, anxious, nervous, hyperactive, charged and so on. I am looking forward to those sleepless nights and all the other joys that come with parenthood. For the moment, I am going to stop writing and enjoy a good night’s sleep when I can.

Download GeoFlow Preview for Excel 2013

2013-04-11T10:17:00.001-04:00

Over the last couple of weeks, many people have been asking me for the download links for GeoFlow. Well, I am pleased to say that GeoGlow has made it’s public preview today and is available for download now.

Find below some useful links (including the download link)

GeoFlow Forum:Post and answer questions for the product team and community
GeoFlow Facebook Page: Share datasets, tours, and blogs
GeoFlow Product Page on Office.com: Download requirements, directs to Web Download page below
GeoFlow Download Page: Download the bits on Microsoft Download Center
Excel Blog : Dallas Utilities: Electricity seasonal use simulation using project codename “GeoFlow” Preview and Power View

And I guess it is time to cut down on some of the earlier limitations that I had mentioned about the beta release of GeoFlow:-
- Related tables are not supported in this release.All the data should be in one table.
- Backend data changes are not reflected in the visualization in this release and this will mean deleting and recreating the visualizations.
- No Undo/Redo is supported in this release.
- No way to slice and dice data within the visualization
- No drill down functionalities from a higher level (say, states) to a lower level (say, counties) which is there in tools like Power View.

Not bad huh? Some new features are also there like the Top / Bottom 100 Chart shown below.

It is interesting to note that GeoFlow is not available to all versions of Excel 2013. The supported Microsoft Office versions are:

Office Professional Plus 2013
Office 365 ProPlus
Office 365 Midsize
Office 365 E3
Office 365 E4

Enough of me telling, it’s time to explore this add-in on your own now. Download GeoFlow today and take your data for a 3-D drive!

GeoSpatial Analytics, Microsoft BI & John Snow’s Cholera Map

2013-03-25T22:14:00.001-04:00

Throughout my childhood, I have always been fascinated by the idea of maps. It could be attributed to the number of pirate and treasure hunter stories that I used to voraciously devour. My dreams were filled with the protagonists in those books and their endless struggles to decipher the coded information in the maps. What used to strike me was that the location of the treasure was always present in the maps but still countless men were misled by them. Most of the times, it took the right person or the right context to get to the bottom of the map. As I grew older, my association with pirate / treasure hunter stories decreased (apart from watching the occasional Pirates of the Caribbean releases) but my bond with maps just went on getting stronger. Though my friends might joke that the reason for this is because I am so spatially disoriented (I still use a navigation system to travel from my home to the office which is just a mile away for fear of getting lost), the real rationale is because I see a lot of value in geospatial analytics as a BI consultant. In today’s world, when more and more product companies are bringing forth tools to easily analyse location based data, the excuses for not trying to analyse and explore geographic patterns in your business data are running dry. The Microsoft BI platform has a very able set of tools for geospatial analysis which includes SQL Server Reporting Services, Power View and Geo Flow (this is without including some of the map apps that are present in Office 2013) and helps me a lot in evangelizing geospatial analytics amongst my clients. While I was reading the news, I came to know that the last week was the 200^th birth anniversary of one of the pioneers of geospatial analysis as well as the father of modern epidemiology – John Snow. I decided to pay my homage to him by following his adventure and reproducing his famous 1854 Broad Street Cholera Outbreak map using Microsoft BI.

Before we start, it might be interesting to set the context to the 19^th century London. London was evolving into a big city with a large influx of people, but without the modern day facilities and sanitary services. Houses used to have cesspools below their basements and human as well as animal wastes were accumulated there. Initially, people seemed to forget about the cesspools below and somehow thought that the wastes would get disposed magically, till the cesspools started overrunning. That is when the London government decided to dispose the waste into River Thames (which was also the source for water supply). You can imagine London at that time – stinking with all the human and animal wastes and it was no wonder that diseases were rampant at that time. The reason for the diseases were all attributed to the pollution and foul air emanating from those wastes. John Snow was a big sceptic of this theory though he had no evidence to prove otherwise. That is around when there was a major outbreak of Cholera in the Soho district of London and he decided to investigate it further. I am not 100% sure of how he actually did his investigation, but I am pretty sure that he must have at least gone through some variations (if not exact) of the four main sections in any treasure-hunting plot :-

1) Collecting the Data (or acquiring the Map!)
The first section of most treasure hunter stories weave through the part where the protagonist goes through shady alleys and dilapidated shops to find the treasure map from some ancient hawker. Likewise, John Snow might have gone through the disease laden streets to pick up the statistics on the cholera deaths and their locations. Luckily for me, I just had to search the internet and got the data (thanks to Robin Wilson). You can download a copy of the data from here. I did some simple changes to the source data to extract the longitude and the latitude columns from the geometry column as shown below (pro tip: use the Flash Fill feature of Excel 2013).

2) Exploring the data patterns (or deciphering the Map!)
The plot continues where the protagonist tries to make sense out of the map. Treasure maps are never straightforward and he takes a lot of time to see whether there are any hidden clues or markers in the map. Similarly, John Snow must have spent a lot of time studying the statistics as well as exploring the data visually for geographic patterns. I decided to visualize the data using Power View initially to check for any patterns.

Just as the protagonist tries different approaches, I decided to also use GeoFlow to visualize the same data.

The heatmap visualization in GeoFlow indicated something that Power View didn’t show that explicitly - unusually large number of deaths around the red area.

3) Investigating the Outliers / Patterns (or Validating the potential Treasure locations)
This is one of the most crucial part of the story, where the protagonist tries to cross check and re-validate the patterns or code that he has found within the map. This is what is going to distinguish him from the countless men that have lost their lives in search of the treasure. He makes sure that the clues that he has got from the map is not a red-herring. This is what John Snow did too, as is his evident from his writings – “On proceeding to the spot, I found that nearly all the deaths had taken place within a short distance of the [Broad Street] pump. There were only ten deaths in houses situated decidedly nearer to another street-pump. In five of these cases the families of the deceased persons informed me that they always sent to the pump in Broad Street, as they preferred the water to that of the pumps which were nearer. In three other cases, the deceased were children who went to school near the pump in Broad Street...

With regard to the deaths occurring in the locality belonging to the pump, there were 61 instances in which I was informed that the deceased persons used to drink the pump water from Broad Street, either constantly or occasionally...”. As for all of us, we already know the cause and hence all I had to do currently was to display the pumps also as another layer in GeoFlow and to see the correlation of deaths and proximity of pump.

Isn’t it amazing to see how accurately GeoFlow has plotted the problem causing Broad Street pump right in the area of the maximum deaths!

4) Visualizing and Sharing the Results (or finding the Treasure!)
This is the final part of the story where the protagonist undertakes the arduous journey to find (and hopefully claims) the treasure. The journey is not easy and there are every chance that he might be misled like the countless many before him. Similarly, in geospatial analysis, it is very easy to get carried away and share wrong results.

However, John Snow’s map (shown below) was very instrumental in convincing the London authorities to shut down the problem causing Broad Street Pump and history says that this helped in containing the outbreak.

If done correctly, geospatial analysis is extremely powerful and with technologies like Power View and GeoFlow, this can be done pretty easily and quickly. If you are interested in geospatial analysis or Business Analytics in general, it’s still not late to register for the PASS BA Conference happening on Apr 10-12, 2013. And if you do register, don’t forget to attend my session on GeoSpatial Analytics using Microsoft BI.

You can download the completed Excel 2013 file with the Power View and GeoFlow visualizations from here.

Unpivoting Data in Data Explorer

2013-03-12T23:02:00.001-04:00

Data Explorer is generating a lot of positive buzz within the community and everyone I speak to seems to be really excited about this. (If you are wondering what Data Explorer is, you might want to quickly catch up on my previous post - Introduction to Data Explorer Preview for Excel). And just today, the Data Explorer team announced A new build of “Data Explorer”, and an Auto Update feature (must say I really like the idea of Update button). This was followed by a post from Jamie Thomson aka SSIS Junkie (blog | twitter) on the query language ( M ) in Data Explorer and you can read more about that here. All this made me really excited and I also decided to contribute something through this post.

It is a pretty common requirement to unpivot data, especially when you are scourging the net for open data. Data Explorer currently does not have any feature to unpivot data but the query language looked pretty solid and I decided to give it a try (actually, this exercise started as a way to test the new query editing functionality in Data Explorer). Well, I did succeed though it might not look that easy (if you do find an easier way, please share it!). Read on for the solution:-

1) The source for my post is given below

I just made a simple table in the excel spreadsheet which gives the Sales by State for the years 2010 to 2012. I then click on the From Table option in Data Explorer tab to get the query window as shown below.

2) I add a new column called JCol with a value of 1 using the expression below

= Table.AddColumn(Excel.CurrentWorkbook(){[Name="Table37"]}[Content], "JCol", each 1)

Now this is my first table, and I store the expression with me.

3) My next objective is to make a transpose of the original table. This can be done with the expression below

= Table.PromoteHeaders(Table.Transpose(Table.DemoteHeaders(Excel.CurrentWorkbook(){[Name="Table37"]}[Content])))

4) Now we need to rename the State Column to Year.

= Table.RenameColumns(Table.PromoteHeaders(Table.Transpose(Table.DemoteHeaders(Excel.CurrentWorkbook(){[Name="Table37"]}[Content]))), {"State","Year"})

5) Now, we need to add a column to this resultant table called JCol (just as we did to the first table in Step 2).

= Table.AddColumn(Table.RenameColumns(Table.PromoteHeaders(Table.Transpose(Table.DemoteHeaders(Excel.CurrentWorkbook(){[Name="Table37"]}[Content]))), {"State","Year"}), "JCol", each 1)

6) You might be wondering at this stage why we created the JCol column in both the tables. The reason is that we need to cross join both of these tables so that we get extra rows, and the join column is going to be JCol. Since the values for the join column are all the same, we get a cross join. To do the join of both the tables, delete the existing expression and enter the expression below

= Table.Join(Table.AddColumn(Excel.CurrentWorkbook(){[Name="Table37"]}[Content], "JCol", each 1), "JCol", Table.AddColumn(Table.RenameColumns(Table.PromoteHeaders(Table.Transpose(Table.DemoteHeaders(Excel.CurrentWorkbook(){[Name="Table37"]}[Content]))), {"State","Year"}), "JCol", each 1), "JCol")

7) Now add a new calculated column which will give you the value in NC column if State is NC, SC column if State is SC and FL column if State is FL.

The expression for the calculated column is given below

if [State] = "NC" then [NC] else if [State]= "SC" then [SC] else [FL]

Now your result should look like shown below

8) Now all you have to do is to rename the Custom column to Sales and hide the unnecessary columns. Then you will be able to see the unpivoted data as shown below

9) Also, have a look at how the actual query looks like in the advanced query editor

I would like to see a word-wrap option for the advanced query editor so that I don’t have to scroll over to the right to see long formulas. Apart from that, looks great! Don’t forget to share your comments as well as your posts / experiences with Data Explorer and it’s query language.

Update

I got quite some requests asking for the actual query. So here it is

let
    Source = Table.Join(Table.AddColumn(Excel.CurrentWorkbook(){[Name="Table37"]}[Content], "JCol", each 1), "JCol", Table.AddColumn(Table.RenameColumns(Table.PromoteHeaders(Table.Transpose(Table.DemoteHeaders(Excel.CurrentWorkbook(){[Name="Table37"]}[Content]))), {"State","Year"}), "JCol", each 1), "JCol"),
   InsertedCustom = Table.AddColumn(Source, "Custom", each if [State] = "NC" then [NC] else if [State]= "SC" then [SC] else [FL]),
    RenamedColumns = Table.RenameColumns(InsertedCustom,{{"Custom", "Sales"}}),
    HiddenColumns = Table.RemoveColumns(RenamedColumns,{"2010", "2011", "2012", "JCol", "NC", "SC", "FL"})
in
    HiddenColumns

Remember to change the name of the table name when you use it.

Introduction to Data Explorer Preview for Excel

2013-03-04T09:00:00.000-05:00

It’s just been a couple of days since this add-in has been released and I can’t seem to stop using it. I guess I haven’t been this excited about a Microsoft offering since SSRS 2008 R2 (well, I am really passionate about Tabular and PowerPivot now but I wasn’t that hooked onto those technologies when they were released). As a BI consultant, blogger and speaker, I traverse through lots and loads of open data - sometimes trying to get that extra piece of open data which will add value to my clients’ existing data or to get some interesting stuff for my blogs and presentations. Data Explorer might not be the perfect solution, but it sure does a splendid job of reducing my efforts in searching, shaping and preparing my data. I thought of sharing my experiences in the form of a quick introduction to Data Explorer Preview for Excel.

1) What is Data Explorer Preview for Excel and where can I get it from?

Microsoft “Data Explorer” Preview for Excel is an add-in for Excel 2013 which provides an intuitive user interface for data discovery, data transformation and enrichment. You can download the add-in from here.

2) Basic Tutorial for Data Explorer

You can import data into excel from a wide variety of sources using Data Explorer and the complete list of sources is given here. For the purpose of this demo, I am going to be connecting to my favourite source which is the ‘web page’ source.

a) After installing the add-in, you should be able to see the Data Explorer tab in Excel (if not, go to File—>Options—>Add-Ins—>Com Add-Ins and enable the Data Explorer add-in). Click on the From Web option and enter the url - http://www.nuforc.org/webreports/ndxloc.html as shown below.

b) Now click on Table 0 in the Navigator pane and you should be able to see the UFO sightings by states. The table has some non-US data and so to filter them, click on the dropdown in the reports column and unselect them. Click on OK when you are done.

c) Rename the columns as States and UFO Sightings respectively. Notice that the Steps pane on the right shows the modifications you are making and you can expand the pane.

d) Click on Done and now you can see the data in Excel. This data can be used as a source for your charts / tables and in the image below, I have used the “Geographic Heat Map” Office app to visualize the same. Looks like the aliens have taken a liking for California!

3) Interesting Features

a) Online Search:- I spend a lot of time trying to search for open datasets and this feature is surely going to reduce that time. You can search for data right from Excel by clicking on the Online Search button. For eg, if I search for ‘richest states’, the results as shown below.

Just scroll your mouse over the results and click on Use to add the data.

b) Filter & Shape Data:- The ability to filter and transform your data is what makes this so useful. You can just click on the Filter & Shape button to start the process as shown below.

For eg, if you just want to show the income for 2011, you can hide the rest of the columns as shown below.

You can also do a lot of other operations like Splitting columns, removing duplicates, replacing values, changing types, group-by, etc.

You can read the complete list from here. Hopefully, there will be more additions to this in the future (I could definitely use a Unpivot / Pivot option).

c) Merging & Appending from Multiple Sources:- This is another killer feature in Data Explorer. You can merge or append from multiple sources. For eg, if we have to merge the two sheets that we just created, click on the Merge button and then select the primary and secondary tables from the dropdown.

Then select the matching columns from both the tables and click on Apply.

Now, to display the additional columns, click on the expand icon on the New Column and select the UFO sightings. Click on OK.

Now you have combined both the sources and can use it for your visualization. I have visualized the same data using GeoFlow below.

4) Further Reading

There’s lot of interesting stuff you can do with Data Explorer and if this post caught your interest, make sure to check the below ones too

1) Data Explorer Team - Announcing Microsoft “Data Explorer” Preview for Excel

2) Chris Webb - Importing Data From Multiple Log Files Using Data Explorer

3) Chris Webb - Calling A Web Service From Data Explorer, Part 1

4) Jamie Thomson - Traversing the Facebook Graph using Data Explorer

5) Matt Masson - Access the Windows Azure Marketplace from Data Explorer

6) Jake Smillie - Best Oscar winning Film? My first Data Explorer adventure…

7) Ian Morrish - SharePoint OData and the Excel Data Explorer

8) Data Explorer Help

Updates

9) Dan English - Installing Data Explorer Preview & Demo with IMDB Data

10) Alan Koo - Introduction to Microsoft Data Explorer Preview for Excel 2013 - Part 1

100% Visualization in SSRS

2013-02-26T09:00:00.000-05:00

Recently, Bill Jelen aka Mr Excel came up with a podcast that showcased a visualization that he termed as the “100% Visualization”. I have never seen this visualization before and I am not sure whether Stephen Few would approve of it. But I found the concept interesting and decided to exercise my grey cells by creating this in SSRS. Read on for the solution.

1) Create a new report with a simple dataset having Category name and percentage value

SELECT     'Bikes' AS Category, 82 AS Pc
UNION ALL
SELECT     'Accessories' AS Category, 49 AS Pc
UNION ALL
SELECT     'Textile' AS Category, 24 AS Pc
UNION ALL
SELECT     'Furniture' AS Category, 99 AS Pc

2) Make a simple tablix with 10 columns and 10 rows. Then fill the cell values from 1 to 100 as shown below

It is good to make all the cells of the same size and squares (for eg, size = 0.6,0.6)

3) Now make the border color as white, border style as solid, border width as 4pt and FontSize as 2pt.

4) Add the custom code below

Public Shared Function ColorDWB(ByVal Value As Decimal, ByVal MaxPositive As Decimal, ByVal Neutral As Decimal, ByVal ColStr As String) As String

Dim ColVar1 As Integer
Dim ColVar2 As Integer
Dim ColVar3 As Integer

'Split the #RGB color to R, G, and B components
ColVar1=Convert.ToInt32(left(right(ColStr, 6),2),16)
ColVar2=Convert.ToInt32(left(right(ColStr, 4),2),16)
ColVar3=Convert.ToInt32(right(ColStr, 2),16)

'Find Largest Range
Dim decPosRange As Decimal = Math.Abs(MaxPositive - Neutral)

'Find appropriate color shade
Dim Shd As Decimal = 255

Dim iColor1 As Integer
Dim iColor2 As Integer
Dim iColor3 As Integer
Dim strColor As String

'Reduce a shade for each of the R,G,B components
iColor1 = ColVar1 + CInt(Math.Round((MaxPositive-Value) * ((Shd - ColVar1) / decPosRange)))
iColor2 = ColVar2 + CInt(Math.Round((MaxPositive-Value) * ((Shd - ColVar2) / decPosRange)))
iColor3 = ColVar3 + CInt(Math.Round((MaxPositive-Value) * ((Shd - ColVar3) / decPosRange)))

'Return the new color
strColor = "#" & iColor1.ToString("X2") & iColor2.ToString("X2") & iColor3.ToString("X2")

Return strColor
End Function

Private colorPalette As String() = { "#C85200", "#FF800E", "#5F9ED1", "#2CA02C", "#A59D93", "#B8341B", "#352F26", "#F1E7D6", "#E16C56", "#CFBA9B"}

Private count As Integer = 0
Private mapping As New System.Collections.Hashtable()

Public Function GetColor(ByVal groupingValue As String) As String
   If mapping.ContainsKey(groupingValue) Then
      Return mapping(groupingValue)
   End If
   Dim c As String = colorPalette(count Mod colorPalette.Length)
   count = count + 1
   mapping.Add(groupingValue, c)
   Return c
End Function

The GetColor() is used to select new colors for each category type and the ColorDWB() is used to get a lighter shade of the selected color (you might recognize the ColorDWB function from my post on Custom Code for Color Gradation in SSRS).

5) Add the expression below for the Color and BackgroundColor property for all the cells.

=iif(Me.Value <= Fields!Pc.Value, Code.GetColor(Fields!Category.Value), Code.ColorDWB(2, 10, 1, Code.GetColor(Fields!Category.Value)) )

6) Add a textbox on the top with the Category value, and a textbox below with the Pc field value and place all three objects inside a rectangle to keep them together. You can also format it by adding a background color of Green and a border of White, if needed.

7) Now add a matrix with Category field on column group. Then, add the rectangle with the three objects inside the value field.

Now you can delete the row and the column for the matrix so that it looks like shown below.

8) Preview it to see the result.

Isn’t that pretty? And if there are more charts than what can be fitted in the horizontal way, you might want to check this post on how to repeat it horizontally as well as vertically. As always, you can download the SQL 2012 rdl file from here.

Bandlines in SSRS

2013-02-19T10:27:00.001-05:00

Sometime in January, visualization expert Stephen Few expanded on Tufte’s idea of Sparklines and came up with this new visualization called Bandlines. Bandlines use horizontal bands of color in the background of the plot area to display information about the distribution of values and you can read a detailed description on bandlines in Stephen’s article. Companies like XLCubed have already started integrating this within their products and it was only a matter of time before yours truly tried to replicate the same within SSRS. Read on for the solution.

One of the most important steps to reproduce this in SSRS is to find the percentiles (25^th and 75^th) in your dataset query. You can do it the hard way by coming up with the logic and then computing it within your dataset. I chose not to reinvent the wheel and used Richard Mintz’s (yes, the same guy who came up with the code behind Squarified Heatmaps in SSRS) SSAS stored procedure for the same. He has written an excellent post on how to compute percentiles in both SQL and MDX, so you can use his code depending on whether your source is a relational database or a multidimensional cube. For this demo, my source would be the AdventureWorks multidimensional cube. Follow the steps below to reproduce bandlines:-

1) Download the dll from here and add it to your SSAS instance as per the instructions at the end of Richard’s post. I have named the assembly as SPPercentile.

2) Make a dataset query which will show the Internet Sales amount for a product category and month with the year as a parameter (optional, dataset query for year parameter is not shown here). Also make 2 calculated measures which will show the maximum and minimum values for internet sales by category across the months.

WITH MEMBER measures.[maxP] AS
Max (
StrToSet ( @DateCalendarYear ) * [Product].[Category].CurrentMember * [Date].[Month Name].[All] * [Date].[Calendar].[Month].AllMembers,
[Measures].[Internet Sales Amount]
)
MEMBER measures.[minP] AS
Min (
StrToSet ( @DateCalendarYear ) * [Product].[Category].CurrentMember * [Date].[Month Name].[All] * [Date].[Calendar].[Month].AllMembers,
[Measures].[Internet Sales Amount]
)
SELECT NON EMPTY
{ [Measures].[Internet Sales Amount], measures.[maxP], measures.[minP] } ON COLUMNS,
{
NonEmpty ( [Date].[Month Name].Children, [Measures].[Internet Sales Amount] ) * [Product].[Category].[Category].AllMembers
} ON ROWS
FROM [Adventure Works]
WHERE StrToSet (
@DateCalendarYear
)

Note that the maximum / minimum will be the same for a category across all months. This is required for doing the scaling of the bandlines, and will be explained in a later step.

3) Make a dataset query for getting the 75th and 25th percentiles.

WITH MEMBER measures.[25th percentile] AS
[SPPercentile].ValueAtPercentile ( NonEmpty (
    ( StrToSet ( @DateCalendarYear ) * [Date].[Calendar].[Month].AllMembers * [Product].[Category].Children,
    [Measures].[Internet Sales Amount] )
),
[Measures].[Internet Sales Amount],
.25,
TRUE,
"INC" )
MEMBER measures.[75th percentile] AS
[SPPercentile].ValueAtPercentile ( NonEmpty (
    ( StrToSet ( @DateCalendarYear ) * [Date].[Calendar].[Month].AllMembers * [Product].[Category].Children,
    [Measures].[Internet Sales Amount] )
),
[Measures].[Internet Sales Amount],
.75,
TRUE,
"INC" )
SELECT NON EMPTY
{ measures.[25th percentile], measures.[75th percentile] } ON COLUMNS
FROM [Adventure Works]

Note that we are using the [SPPercentile].ValueAtPercentile() stored procedure and you can find the syntax in Richard’s post. Now we should be having the following three datasets.

4) Now that we have found out the percentiles, store their values in two hidden report parameters respectively – PC25 and PC75. Make sure that that the available values and default values are obtained from the corresponding field in DST_Percentile dataset as shown below.

5) Now drag and drop a matrix into the layout. Use the Categories on the rows and drag and drop a line chart to the values as shown below. In the chart, use Internet Sales Amount as the Values, Month Name as the category group and Category as the Series Group.

6) Make the max and min of the vertical axis as the maxP and minP fields. This will help in scaling the sparkline (yes, by now we have got the basic structure of a sparkline).

Also, remove the vertical axis, horizontal axis, legend and all other titles.

7) Add a value to the chart with the expression below.

=iif(Fields!maxP.Value <= Parameters!PC75.Value, Nothing, Fields!maxP.Value)

Make the chart type as Area Chart and color as #f5f5f5

This will act as the band for the values between 75 and 100 percentile, so ensure it is at the very top as shown in the picture above. If the max value for the category is lesser than the PC75, we don’t want to show this band.

8) Add another value to the chart with the expression below

=iif(Fields!maxP.Value >=Parameters!PC75.Value, Parameters!PC75.Value, Fields!maxP.Value)

Make the chart type as Area Chart and color as #d7d7d7

This will act as the band between 25 and 75 percentile and should be appearing on top of the 75-100 band, so ensure that it appears just under the previous value as shown in the picture above.

9) Add the last band by adding the expression below

=iif(Fields!maxP.Value <= Parameters!PC25.Value, Fields!maxP.Value, iif(Fields!minP.Value>=Parameters!PC25.Value, Nothing, Parameters!PC25.Value))

Make the chart type as Area Chart and color as #adadad

Again ensure that this value appears just under the previous one, as shown in the picture above.

10) Now, we can preview it and see the results.

From the markers, you can also note that there are 24 values. Out of that, there are 6 values in the 75-100 band, 12 values in the 25-75 band and 6 in the 0-25 band. So the percentiles are indeed working! And very quickly, you can make out that Bikes have more of their values in the 75-100 percentile, an extra bit of information that you wouldn’t have got with sparklines. That is the power f Bandlines.

11) Now with a bit of formatting, this is the final end result and you can download the completed report rdl (SSRS 2012) from here.

Custom Code for Color Gradation in SSRS

2013-02-12T16:18:00.001-05:00

Throughout my career as a Business Intelligence Consultant, I have met and interacted with a lot of DBAs. One thing (among many others) I particularly admire about them is that they have their own list of SQL scripts that they carry with them from job to job (and my observation is that the list usually grows with experience). This way, they don’t have to remember all of them, but when the need arises, they have the scripts at their fingertips. The only thing I have close to that is my list of custom codes for doing some specific operations in SSRS. One of my most frequently used scripts originates from this series of posts on how to conditionally color in SSRS. Based on my needs, I had tweaked the code such that it will display the color gradation to white for any input color. Thought that this might come in handy for some of you guys.

Code

Public Shared Function ColorDWB(ByVal Value As Decimal, ByVal MaxPositive As Decimal, ByVal Neutral As Decimal, ByVal ColStr As String) As String
Dim ColVar1 As Integer
Dim ColVar2 As Integer
Dim ColVar3 As Integer

'Split the #RGB color to R, G, and B components
ColVar1=Convert.ToInt32(left(right(ColStr, 6),2),16)
ColVar2=Convert.ToInt32(left(right(ColStr, 4),2),16)
ColVar3=Convert.ToInt32(right(ColStr, 2),16)

'Find Largest Range
Dim decPosRange As Decimal = Math.Abs(MaxPositive - Neutral)

'Find appropriate color shade
Dim Shd As Decimal = 255

Dim iColor1 As Integer
Dim iColor2 As Integer
Dim iColor3 As Integer
Dim strColor As String

'Reduce a shade for each of the R,G,B components
iColor1 = ColVar1 + CInt(Math.Round((MaxPositive-Value) * ((Shd - ColVar1) / decPosRange)))
iColor2 = ColVar2 + CInt(Math.Round((MaxPositive-Value) * ((Shd - ColVar2) / decPosRange)))
iColor3 = ColVar3 + CInt(Math.Round((MaxPositive-Value) * ((Shd - ColVar3) / decPosRange)))

'Return the new color
strColor = "#" & iColor1.ToString("X2") & iColor2.ToString("X2") & iColor3.ToString("X2")

Return strColor
End Function

How To Use

1) Lets say we have a simple report which shows the sales by vehicle type and month

2) You can either compile this code and use it in your report or just paste it in the code tab of the Report Properties. If we just need to get different shades of a color (say #2322EE), we can just use the expression below as the BackgroundColor property of the cell.

=Code.ColorDWB(sum(Fields!Sales.Value), Max(Fields!Sales.Value, "DataSet1"), Min(Fields!Sales.Value, "DataSet1"), "#2322EE")

3) If you need the color gradation to be based on each row, then make sure that the max and min values are passed for the row (in this case, the Vehicle group) rather than the whole dataset.

=Code.ColorDWB(sum(Fields!Sales.Value), Max(Fields!Sales.Value, "Vehicle"), Min(Fields!Sales.Value, "Vehicle"), "#2322EE")

4) If you need different colors for your vehicle, you can make use of the GetColor() custom code and then pass that in your expression:-

=Code.ColorDWB(sum(Fields!Sales.Value), Max(Fields!Sales.Value, "Vehicle"), Min(Fields!Sales.Value, "Vehicle"), Code.GetColor(Fields!Vehicle.Value))

Just make sure that the colors in the palette are using the hex values and not just strings like “Red”.

5) You can also use this code to go across two colors. For eg, what if we want to go from Blue to White to Orange such that the mean is white, more is blue and less is orange? We can write an expression as shown below

=iif(sum(Fields!Sales.Value) >= (Max(Fields!Sales.Value, "DataSet1") + Min(Fields!Sales.Value, "DataSet1"))/2,
Code.ColorDWB(sum(Fields!Sales.Value), Max(Fields!Sales.Value, "DataSet1"), (Max(Fields!Sales.Value, "DataSet1") + Min(Fields!Sales.Value, "DataSet1"))/2, "#6495ED"),
Code.ColorDWB(-sum(Fields!Sales.Value), -Min(Fields!Sales.Value, "DataSet1"), -(Max(Fields!Sales.Value, "DataSet1") + Min(Fields!Sales.Value, "DataSet1"))/2, "#FFC125"))

Note that we are writing a conditional expression to see if the current field is more than the mean (max+min / 2). If yes, then we use the familiar expression with the Blue color (so more the value, darker the blue). If no, we need to do add a negative for all the fields so that the coloring happens in the reverse order (lesser the number, darker the Orange).

You can keep on tweaking this code and use it for a wide variety of visualizations. I am no VB.NET developer, so it is possible that there is a much better way to do the same. If you do know of any better technique, do post in the comments so that others can benefit from it.

UNION Operation in DAX Queries

2013-02-04T09:00:00.000-05:00

In one of my previous posts - Row Selection Using Slicers in PowerPivot - Part 1, I had demonstrated the use of what Marco Russo defined as Reverse Linked Tables and Linkback tables. A particularly eye-catching thing in my post was the use of Microsoft Query instead of DAX Query to obtain the required reverse-linked table and one of the reasons I gave was that it was difficult to do an UNION operation using DAX queries (yes, you heard it right. I said difficult and not impossible). Well, since difficult is such a subjective word, I decided to jot down the technique, maybe it might seem easy for you guys!

For the purpose of this demonstration, I am using two tables having identical structures. There are just two columns in the tables - country and state and we need to do an UNION operation on these two tables.

The result should be 7 rows as shown below

Follow the steps below for the solution:-

1) Import the two tables to PowerPivot (you can also use this technique on a SSAS tabular model). I will be using DAX Studio for writing my queries and displaying the results (though you might as well as use this in SSMS or in the DMX query editor for SSRS depending on your purpose).

2) The first thing to understand here is that DAX, as a query language, can not add rows usually, and UNION requires that we get more rows (since it is the combined result of both the tables). However, there is an operator in DAX which generally generates more rows than its source tables – CROSSJOIN (except when any one of the participating tables has only one row). So let’s first crossjoin the two tables and see the results.

Now you can see that we get 12 rows, however no single column gives the result that we need. Somehow, we need to get a logic to filter the 5 rows and also a logic to combine the right results in one calculated column.

3) To identify the individual rows, let us add a rank column to each of the two tables and then crossjoin them.

EVALUATE
CROSSJOIN (
ADDCOLUMNS (Table1,"Rnk1",RANKX ( Table1, Table1[State],, 1, DENSE ) ),
ADDCOLUMNS (Table2,"Rnk2",RANKX ( Table2, Table2[State],, 1, DENSE ) )
)

4) When I looked at this resultset initially, I felt that I could just filter all rows having Rank1 = 1 and Rank2 = 2 and then add a calculated column each for Country and State such that if Rank1 = 1, then the value comes from Table2 else it comes from Table1.

EVALUATE
ADDCOLUMNS (
FILTER (
CROSSJOIN (
ADDCOLUMNS (Table1,"Rnk1",RANKX ( Table1, Table1[State],, 1, DENSE )        ),
ADDCOLUMNS (Table2,"Rnk2",RANKX ( Table2, Table2[State],, 1, DENSE )        )
      ),
      [Rnk1] = 1 || [Rnk2] = 2    ),
"UnionCntry", IF ( [Rnk1] = 1, Table2[Country], Table1[Country] ),
"UnionState", IF ( [Rnk1] = 1, Table2[State], Table1[State] )
)

Even though the UnionCntry and UnionState columns might appear right, they are not, as one row is missing (in this case, the row with IN country and KL state). This happens because there will always be one row which has Rnk1 = 1 and Rnk2 = 2, and hence there is an overlap. So we need to think of a different technique to filter the rows.

5) Since we are short of one row, we need to include one more extra row having the complementary values of ranks for the overlapping row (in this case, the row with Rnk1=2 and Rnk2=1). This can be done by using the query given below.

EVALUATE
FILTER(
CROSSJOIN (
ADDCOLUMNS (Table1,"Rnk1",RANKX ( Table1, Table1[State],, 1, DENSE ) ),
ADDCOLUMNS (Table2,"Rnk2",RANKX ( Table2, Table2[State],, 1, DENSE ) )
), ([Rnk1]=1 || [Rnk2]=2) || ([Rnk1]=2 && [Rnk2]=1))

6) Now all we have to do is to get the logic for creating the calculated columns. We can do it with the help of a SWITCH statement by (a) assigning the extra row (Rnk1=2 and Rnk2=1) to Table1 and (b) making sure that the overlapping row (Rnk1=1 and Rnk2=2) is assigned to Table2. This can be done by the following query

EVALUATE
ADDCOLUMNS(
FILTER(
CROSSJOIN (
ADDCOLUMNS (Table1,"Rnk1",RANKX ( Table1, Table1[State],, 1, DENSE )    ),
ADDCOLUMNS (Table2,"Rnk2",RANKX ( Table2, Table2[State],, 1, DENSE )    )
), ([Rnk1]=1 || [Rnk2]=2) || ([Rnk1]=2 && [Rnk2]=1))
, "UnionCountry", SWITCH(TRUE,
                    ([Rnk2]=2 && [Rnk1]<>1) || ([Rnk2]=1 && [Rnk1]=1)
                    ,Table1[Country], Table2[Country])
, "UnionState", SWITCH(TRUE,
                    ([Rnk2]=2 && [Rnk1]<>1) || ([Rnk2]=1 && [Rnk1]=1)
                    ,Table1[State], Table2[State]))

Now you can see that the UnionCountry and UnionState column gives us the required results for a UNION ALL operation. This approach can be easily extended in case we have more than two tables to join. The only scenario where this approach will not work is when any of the participating tables has just one row (as the cross join will not give us more rows. Eg, cross-joining two tables with 3 and 1 rows will give only 3 rows whereas we need 4 rows for the UNION ALL). However, this can be resolved by adding a temporary table with two rows (so the cross join will be 3 * 1 * 2 = 6 rows) and then using this logic.

7) To do the UNION operation instead of UNION ALL, we should remove all duplicate rows. For that, we can use SUMMARIZE function.

EVALUATE
SUMMARIZE(
ADDCOLUMNS(
FILTER(
CROSSJOIN (
ADDCOLUMNS (Table1,"Rnk1",RANKX ( Table1, Table1[State],, 1, DENSE )    ),
ADDCOLUMNS (Table2,"Rnk2",RANKX ( Table2, Table2[State],, 1, DENSE )    )
), ([Rnk1]=1 || [Rnk2]=2) || ([Rnk1]=2 && [Rnk2]=1))
, "UnionCountry", SWITCH(TRUE,
                    ([Rnk2]=2 && [Rnk1]<>1) || ([Rnk2]=1 && [Rnk1]=1),
Table1[Country], Table2[Country])
, "UnionState", SWITCH(TRUE,
                    ([Rnk2]=2 && [Rnk1]<>1) || ([Rnk2]=1 && [Rnk1]=1),
Table1[State], Table2[State]))
, [UnionCountry], [UnionState])

In this scenario, there is no difference in the result as there are no duplicate rows. Let me know what you think of this!

Scatter Line Charts in SSRS

2013-01-28T14:34:00.001-05:00

Sometime during the last month, I saw an image of an Archimedean Spiral and was wondering whether I could recreate the same in SSRS. The Archimedean Spiral is actually a scatter line chart (a combination of scatter chart and line chart). I have over 6+ years of experience creating visualizations using SSRS, but the closest I had come to such a visualization was the Scatter Chart.

A scatter line chart is used either to show the relationship among the items in several distinct series of data or to plot a line using x and y coordinates. The x and y coordinates of the data marker are determined by two data values. A scatter line chart draws attention to uneven intervals in data. This type of chart is often used to plot scientific data, and can highlight the deviation of collected data from predicted results. A scatter line chart can also be used to organize data chronologically (even if the data is not in chronological order). For eg, consider the Scatter Chart below

A scatter chart doesn’t tell the full story as we don’t know the chronology in which the data was collected.

A scatter line chart can give that extra bit of information as you can see from the image above. To create a scatter line chart, you need to have at least two measures (for the X & Y coordinates) and a category group. Follow the steps below to create an Archimedean spiral using the scatter chart:-

1) Create a dataset with the two measures (X & Y coordinates) and a category. Since I am trying to replicate the Archimedean spiral, I used the formula given below to come up with the coordinates and category.

You can see that my category (the Pos field) is just a running number used for determining the order.

2) Drag and drop a Smooth Line with Markers chart into the layout. Use Y as the value and Pos as the Category group.

3) Once you are done with that, click on the values and press F4 to bring up the chart series properties. Under Data, expand DataPoint property and then expand Values. Now, type the following formula in the X property as shown in the image below

=sum(Fields!X.Value)

Note that X is the name of the field in our dataset and should be replaced by whatever is the field name that should be present in the X axis.

4) Preview it, and you should be able to see our Archimedean Spiral.

One thing to note here is that the lines are connected based on the sorting order of the Category group field. So in case you are using a name field like month (January, February, etc), ensure that the sorting order is based on the month number (else you might not get the right results). Feel free to download the report file from here. Though scatter line charts might not be as useful as sparklines, they have their place in data visualization and it is always good to know that you can make one using SSRS.

I’m speaking at the PASS Business Analytics Conference

2013-01-23T18:29:00.001-05:00

The speaker lineup for the PASS Business Analytics Conference has been announced and I’m really excited to see my name in the list. In case you haven’t heard, PASS BA Conference is the premium conference for business analytics professionals. If you're a business analyst, data scientist, architect, or BA/BI practitioner, you can’t afford to miss this conference. Attendees will be exposed to an unparalleled range of information about business analytics—with over 60 sessions to choose from—covering data discovery, data exploration and visualization, predictive analytics, content management and architecture, collaboration, information strategies, and much more. You can register for the conference by clicking on this link.

I am really honoured to be presenting along with such an impressive list of speakers. My session details are given below

GeoSpatial Analytics Using Microsoft BI

Speaker(s): Jason Thomas

Duration: 60 minutes

Track: Data Analytics and Visualization

In this age of big data, location matters more than many people imagine. Almost every set of data has some geographic information tied to it, and it is all the more pertinent to understand and unravel the patterns and trends behind them. This session will start by explaining why GeoSpatial analytics is important in today's world and then continue by exploring how we can share, analyze, and visualize data using the comprehensive Microsoft BI toolset, including Power View, GeoFlow, SQL Server Reporting Services, and more. The session will also feature practical demos for each of these tools, during which we will work on some of the publicly available datasets to uncover some interesting facts.

Don’t forget to drop in and say a hi to me, in case you are attending the conference.

Row Selection Using Slicers in PowerPivot - Part 2

2013-01-17T12:41:00.001-05:00

This post is a continuation of the Row Selection Using Slicers in PowerPivot – Part 1 (so it might be a good idea to read that post before this). In our last post, we discussed the basic solution pattern for row selection using slicers in PowerPivot. In this post, we will discuss two other scenarios which are slightly more complex but still uses the same pattern.

Scenario III - GroupNames in multiple dimensions
Our data model for this scenario will have a Product table, a Customer table and a Fact table which shows the sales by Product and Customer. The requirement is to select either Product or Customer in the slicer and display the appropriate rows in the pivot table.

1) As per the solution steps, we first have to create the Group table

Note that we need two grain columns here, as the GroupNames belong to two dimensions – Product and Customer. The GroupName will be Customer and Product, and the GroupValues will be the actual product or customer values depending on the GroupName. As with the previous posts, I use MS Query to generate this table.

SELECT Expr1000 AS GroupName, Customer1 AS GroupValue, ProductKey1 AS Product, Customer FROM
(
SELECT 'Customer' , C.Customer1, `Sheet1$`.ProductKey1, `Sheet1$`.Customer
FROM `Sheet1$` `Sheet1$`
INNER JOIN `Sheet1$` C
ON `Sheet1$`.Customer=C.CustomerKey
UNION ALL
SELECT 'Product', P.Product, `Sheet1$`.ProductKey1 , `Sheet1$`.Customer
FROM `Sheet1$` `Sheet1$`
INNER JOIN `Sheet1$` P
ON `Sheet1$`.ProductKey1=P.ProductKey
)

2) Add this new table (Group) to the data model and then relate the Product & Customer column in this table to the ProductKey column in the Product table & CustomerKey column in the Customer table.

3) Hide the original measure Sales, and then create a new measure with the formula given below

NewSales:=CALCULATE(SUM(Fact[Sales]), 'Group')

4) Check out the results by creating a pivot table with GroupValue on the rows, NewSales on Values and GroupName in Slicers.

Download the Excel 2013 file with the solution from here.

Scenario IV – Hybrid Scenario
In this scenario, we are going to combine all the different scenarios we discussed till now and see how to handle that. The data model for this scenario will contain a Fact table which shows sales by Product and Customer, a Product table with the Product and SubCategory information, a Customer table and a Promotions table which has a many to many relationship with Product. The requirement is to have SubCategory (Scanario I), Promotions (Scenario II) and Customer (Scenario III) on slicers.

1) As usual, the first step is to generate the Group table.

Note that even though we have only 4 subcategories, we end up with 6 rows because we will have to ensure that the grain is at a Product and Customer level. Similarly, we end up with 9 rows for Promotions when we convert the granularity of the Promotions table from just Product to the Product and Customer level. Find below the MS Query way of generating this table

SELECT Expr1000 AS GroupName, SubCategory AS GroupValue, ProductKey1 AS Product, Customer FROM
(
SELECT 'SubCategory',S.SubCategory, `Sheet1$`.ProductKey1, `Sheet1$`.Customer
FROM `C:\Users\Jason\Desktop\Blog\RowSelection\Scenario4_Hybrid.xlsx`.`Sheet1$` `Sheet1$`
INNER JOIN `C:\Users\Jason\Desktop\Blog\RowSelection\Scenario4_Hybrid.xlsx`.`Sheet1$` `S`
ON `Sheet1$`.ProductKey1=S.ProductKey
UNION ALL
SELECT 'Promotions',P.Promotions, P.Product1, S.Customer
FROM `C:\Users\Jason\Desktop\Blog\RowSelection\Scenario4_Hybrid.xlsx`.`Sheet1$` `P`
INNER JOIN `C:\Users\Jason\Desktop\Blog\RowSelection\Scenario4_Hybrid.xlsx`.`Sheet1$` `S`
ON P.Product1=S.ProductKey1
UNION ALL
SELECT 'Customer', Customer, ProductKey1, Customer
FROM `C:\Users\Jason\Desktop\Blog\RowSelection\Scenario4_Hybrid.xlsx`.`Sheet1$`
WHERE Customer IS NOT NULL
)

2) Add this new table (Group) to the data model and then relate the Product & Customer column in this table to the ProductKey column in the Product table & CustomerKey column in the Customer table.

3) Hide the original measure Sales, and then create a new measure with the formula given below

NewSales:=CALCULATE(sum(Fact[Sales]), 'Group', Promotions)

Also note the use of the Promotions table (which is the many to many table) in the filter part of the calculate statement.

4) Check out the results by creating a pivot table with GroupValue on the rows, NewSales on Values and GroupName in Slicers.

Download the Excel 2013 file with the solution from here.

With that, we come to the end of this 2 part series on how to change the rows using slicers in PowerPivot. As you would have seen, the bulk of the work lies in generating the Group table and the rest is pretty simple. Hope these scenarios will help you in case you ever need to do the same.

Row Selection Using Slicers in PowerPivot - Part 1

2013-01-15T10:00:00.000-05:00

A couple of months back, I had written a post on ‘Measure selection using Slicers in PowerPivot’ and this turned out to be one of my most popular ones in the PowerPivot / DAX category. It was just a matter of time before someone asked how to do the same with rows or dimension attributes using slicers in PowerPivot. And the case supporting it is also becoming strong with more and more dashboards being built in Power View and Excel. Well, as usual, this functionality is not available out of the box but you could simulate it with a little change to your data model. Read on for the solution.

I will be first briefly explain the solution pattern and then apply it to a couple of scenarios.

Solution
1) A new table should be built and added to the existing data model. The new table should have at least 3 columns
- GroupName : the text displayed in the slicers (for e.g., Product, Customer, etc.)
- GroupValue : the actual values that should be displayed in the rows on selecting the slicer values (for e.g., if Product is selected in the slicer for GroupName, then GroupValues will be the Product values)
- Grain of the Tables : If all the GroupNames displayed in the slicer belong to the same dimension (for eg, Product, Category and SubCategory in the Product dimension), then only one column is required which would be the grain of the dimension (to be precise, the column of the table linked to the facts which is usually the grain of the dimension). If multiple dimensions are involved in the GroupNames (for eg, Product and Customer), then the number of columns increases based on the grain of the participating dimensions.For each GroupName, all valid combinations of GroupValue and Grain columns should be generated.
2) The grain columns should then be related to the dimensions.
3) Create new calculated measures for each of the required measure by passing the new table in the filter part of a calculate statement and then use them in the pivot tables or charts.

Scenario I - GroupNames in one dimension and 1-to-Many Relationship
Our data model for this scenario will have a Product table and a Fact table which shows the sales by Product and Customer. The Product table has ProductKey, Product, SubCategory and Category columns. Our requirement is that we should be able to select Product, SubCategory or Category in the slicers and display the selected values on the row of the pivot table.

1) As per the solution steps, we will have to create a new table as shown below

Since the slicers need to display the three levels, the GroupName values will be Category, SubCategory and Product. The granularity is at the Product level and hence that will be the third column. Note that for each value in GroupName, there should be a row mapping the GroupValue to the grain column (which is Product here). This sort of table is very easy to create if the source is a relational database. For this demo, I used linked tables and hence to automate the creation of this table, I had to use Microsoft Query as a datasource to pull data from the linked tables.

SELECT Expr1000 AS GroupName, Category AS GroupValue, ProductKey AS Product FROM (
SELECT 'Category' , 'Sheet1$'.Category , 'Sheet1$'.ProductKey
FROM 'Sheet1$' 'Sheet1$'
WHERE 'Sheet1$'.ProductKey IS NOT NULL
UNION ALL
SELECT 'SubCategory' , 'Sheet1$'.SubCategory , 'Sheet1$'.ProductKey
FROM 'Sheet1$' 'Sheet1$'
WHERE 'Sheet1$'.ProductKey IS NOT NULL
UNION ALL
SELECT 'Product' , 'Sheet1$'.Product , 'Sheet1$'.ProductKey
FROM 'Sheet1$' 'Sheet1$'
WHERE 'Sheet1$'.ProductKey IS NOT NULL
)

(Please note that I am not a MS query datasource expert and I had to workaround getting the column alias names by bringing the outermost select statement. This should have been a simple UNION ALL in SQL Server). If there is any data change in the linked tables, the Refresh button should be clicked twice in the Excel window (so that the Query can populate the changed data in the new table as well as refresh the PowerPivot model with the changed data).

2) Add this new table (Group) to the data model and then relate the Product column in this table to the ProductKey column in the Product table.

3) Hide the original measure Sales, and then create a new measure with the formula given below

NewSales:=CALCULATE(SUM(Fact[Sales]), 'Group')

4) Check out the results by creating a pivot table with GroupValue on the rows, NewSales on Values and GroupName in Slicers.

Download the Excel 2013 file with the solution from here.

Scenario II - GroupNames in one dimension and Many-to-Many Relationship
Our data model for this scenario will have a Product table, Fact table which shows the sales which shows the sales by Product and Customer, and two tables – Sales Rep and Promotions which will have a many to many relationship with Product.

1) The Group table should have the below format

To create the Group table, we again use Microsoft Query as a datasource to pull data from the linked tables.

SELECT Expr1000 AS GroupName, SalesRep AS GroupValue, Product1 AS Product FROM (
SELECT 'SalesRep', `Sheet1$`.SalesRep, `Sheet1$`.Product1
FROM `Sheet1$` `Sheet1$`
WHERE `Sheet1$`.Product1 IS NOT NULL
UNION ALL
SELECT 'Promotions' , `Sheet1$`.Promotions , `Sheet1$`.Product2
FROM `Sheet1$` `Sheet1$`
WHERE `Sheet1$`.Product2 IS NOT NULL
)

2) Add this new table (Group) to the data model and then relate the Product column in this table to the ProductKey column in the Product table.

3) Hide the original measure Sales, and then create a new measure with the formula given below

NewSales:=CALCULATE(sum(Fact[Sales]), 'Group', 'Promotions', 'Rep')

4) Check out the results by creating a pivot table with GroupValue on the rows, NewSales on Values and GroupName in Slicers.

Download the Excel 2013 file with the solution from here.

I have got two more scenarios to show but guess that will have to wait till the second part of this series – Row Selection Using Slicers in PowerPivot – Part 2.

Note
There are some unique concepts here which might raise questions like
- why didn't I use DAX queries to seed my Group table? Well, I couldn't find a easy way to do union in DAX and that is the reason why I used MS query. It might not be the best way but it sure is an option.
- Is there any other way to avoid the double refresh of the file? I couldn't find any other option to do so (actually, you will need to do that even if you are using DAX queries to seed the Group table). That is, if you are avoiding VBA. Javier Guillen has written a post on how to do it with a single refresh using VBA (check out the final part of his blog for the VBA code).

Group Multiple Rows to Single Delimited Row in PowerPivot

2013-01-07T10:00:00.000-05:00

When PowerPivot was announced a couple of years back, I was one among the many who started feeling insecure on having to learn a new trade. I could have sat there and kept on cribbing, or picked myself up and started my baby steps towards the new technology. As Hillary Clinton said, the challenges of change are always hard. It is important that we begin to unpack those challenges and realize that we each have a role that requires us to change and become more responsible for shaping our own future. I am glad that I gave PowerPivot a chance and kept on learning (it sure helps sitting next to a passionate PowerPivot supporter like Javier Guillen!), else I wouldn’t have realized how much useful it is to me. Agreed that it has it’s fair sets of limitations, but as the community grows and the technology matures, we will see that many of it gets resolved or have workarounds. For eg, if you had asked me before on whether there was any way in PowerPivot to group all the row values into a single delimited row, I would have said no. But seeing this question in the PowerPivot forum, I decided to spend a little more time in the weekend and found a workaround, which I am sharing through this post.

Scenario
Let us say that we have a table which shows the sales by Country and State. The result should be a table which shows the sum of sales by country along with a delimited row of states as shown below

Solution
There is only one function (atleast as far as I know) in PowerPivot that returns a delimited list from multiple rows – PATH(). But the issue is that PATH() works only with a parent child hierarchy. So the trick for getting this piece done would be to build an artificial parent child hierarchy. Follow the steps below to get the solution:-

1) Make a calculated column called Rnk which will generate a running number of the states within a country.

=RANKX (
FILTER ( All ( Table1 ), Table1[Country] = EARLIER ( Table1[Country] ) ),
Table1[State], , 1, DENSE
)

This will allow us to uniquely identify each state value within a country.

2) Make a calculated column called ParRnk with the formula below

=IF (
Table1[Rnk] <> 1,
Table1[Rnk] - 1
)

This will allow us to get the parent node id of the parent child hierarchy.

3) Now, make a calculated column called ParState which will get the Parent State for the current row (based on the ParRnk column).

=CALCULATE (
FIRSTNONBLANK ( Table1[State], 1 ),
FILTER (
ALLEXCEPT ( Table1, Table1[Country] ),
Table1[Rnk] = EARLIER ( Table1[ParRnk] )
)
)

4) The final part of the solution is to make the delimited list column by using the path function where State is the child node and ParState is the parent node. But if we directly apply the PATH function in each row, we will not get the intended results as you can see in the image below.

What we need is the delimited list value for the row with the maximum rank in each country applied across all other rows in that country. For that, make a calculated column called ConcatStates with the formula below

=CALCULATE (
PATH ( Table1[State], Table1[ParState] ),
CALCULATETABLE (
FILTER ( Table1, Table1[Rnk] = Max ( Table1[Rnk] ) ),
ALLEXCEPT ( Table1, Table1[Country] )
)
)

Now the results should look like below

Now make a new pivot table and drag and drop the Country, ConcatStates and Sales fields to see the intended results

Wasn’t that a fascinating workaround? If you want to play around, you can download the excel file from here.

2012 in a Nutshell

2013-01-03T11:30:00.001-05:00

No, the world didn’t end in 2012 as many expected (looks like the Mayan developer was just damn lazy and decided to take a nap after entering till 2012 in the calendar. Guess who got fired after that!). It took a few days for me to get over the disappointment that I really have to pay off my car loan now, lucky I didn’t get a Ferrari thinking the world would end. Jokes apart, 2012 has been a good year for me and I thought of noting down a few things that happened over the last year and setting new goals.

1) New blog domain - www.SqlJason.com and Twitter handle – @SqlJason
I finally took the plunge and bought my blog a new domain. I had been thinking about this for a long time and finally did it in November. This also meant that my blog lost it’s existing page rank and my stats have fallen down, but I guess it is going to pay off in the long run. On the positive side, it’s much easier to communicate my blog url in presentations. So for all my readers, make sure that you update the links from www.road-blogs.blogspot.com to www.SqlJason.com. I have set up a redirect from the old url to the new one, so the existing links will also work.

I also changed my twitter handle from @de_unparagoned to @SqlJason. This means that I don’t get questions anymore on what de_unparagoned means. Also, makes communicating it easier.

2) Presentations and Talks
This year was crazy in terms of the presentations that I did. When I came to the US this year, I had a goal to do more presentations than I did in the previous year (which was 2 – one for the London UG and one for SQLBits). Well, I am happy to say that I presented in 8 SQLSaturdays, 1 Code Camp, 1 BI Virtual Chapter and 1 UG.

1.    Dec 08, 2012 - SQLSaturday #173 (Washington DC 2012)
2.    Sep 29, 2012 - SQLSaturday #149 (Minnesota 2012)
3.    Aug 04, 2012 - SQLSaturday #158 (New York City 2012)
4.    Jul 21, 2012 - SQLSaturday #122 (Louisville 2012)
5.    Jun 26, 2012 – PASS BI Virtual Chapter (online)
6.    Jun 09, 2012 - SQLSaturday #121 (Philadelphia 2012)
7.    May 12, 2012- SQLSaturday #129 (Rochester 2012)
8.    May 05, 2012- Carolina Code Camp (Charlotte 2012)
9.    Apr 28, 2012 - SQLSaturday #130 (Jacksonville 2012)
10.   Apr 21, 2012 - SQLSaturday #118 (Madison 2012)
11.   Apr 19, 2012 – Charlotte User Group (Charlotte 2012)

Thanks to all the organizers and attendees, I got a lot of love from all of you and it was a wonderful experience (in fact I got selected at all of the SQL Saturdays that I submitted to). I intend to keep on doing more of SQL Saturdays in 2013 though I am not sure whether I can match up to the previous year because of personal reasons. One of my other goals would be to prepare better and start trying for the big conferences like PASS Summit, PASS BA Conference, etc though I am not sure how that will turn up due to the immense quality of presentations and speakers.

3) Blogging
Blogging has also been pretty consistent and I wrote 33 posts - one more than I did last year. From a numbers perspective, I had 112,614 page views this year (which is a big jump as I just have ~178,000 page views totally) and this is excluding the views that I get from my syndicated posts at other sites like www.BeyondRelational.com, www.ssas-info.com, etc. Also, my feedburner reader count has almost doubled to 350+ which is good. My goal for this year would be to write at least one post more than the previous year.

4) Organized a SQLSaturday and started a new User Group
This year, I was able to get more involved with the community and I ended up organizing a SQL Saturday in Charlotte along with Javier Guillen, Rafael Salas and Melissa Coates. This was a great experience and all of us had a lot of fun doing that. We also started a new BI group in Charlotte - CBIG (www.CharBIGroup.com) and our first meeting will take place next week. If you live in or around Charlotte, make sure that you register and drop in for our fun meetings.

5) MSDN Forums
I started my community involvement through the MSDN forums 3 years back and that is where I discovered my passion for MSBI and community in general. This is one part where I would like to keep going and helping as far as I can. I had set a goal for myself to break the 10,000 points barrierthis year and I am glad to say that I did it by participating in the SSRS, SSAS and PowerPivot forums. I got 2300+ points this year (total of 10,100+ points) from 140+ answers and though it is nowhere near to the points I racked in my first year, I think this is a reasonable target for next year due to the additional community involvement.

6) PASS Summit
Attending the PASS Summit was one of my goals ever since I started knowing about this community, and again, I am happy to say that I did it this year for the very first time, thanks to my employer – Mariner, who sponsored it for me. It was such a wonderful experience meeting #sqlfamily and attending sessions (in fact I registered for the coming PASS Summit also, which is going to be held in the city that I am based out of - Charlotte).

7) Certifications – MCSE in SQL Server BI 2012
Thanks to the beta invitations, I was able to complete my MCSE certification in BI 2012. There were 5 exams which included some basic DBA topics (which I am not at all familiar with) but guess what, I ended up passing all of those exams in my first try itself. You can read more about my certification experience here.

All in all, 2012 was a great year for me and I hope 2013 is going to be kind on me… And also for all of you…

Risk Matrix Chart in SSRS

2012-12-30T11:18:00.001-05:00

Of recent, I have been getting mails from my readers whether I have forsaken Reporting Services for Analysis Services in my blog. I want to reassure all of you that this is not the case and that I will be equally focussing on both the technologies. Just that there is a lot of development happening in the AS side and it is very important for all of us to keep on expanding our skillsets and building our expertise. That said, I am writing this post on how to create a Risk Matrix chart in SSRS for all of you guys as a new year gift from my side.

A risk matrix chart, as the name suggests, is used for performing risk analysis. Typically, we combine the Likelihood and Impact ratings of an event to arrive upon a risk score, which aids in deciding on what action to take in view of the overall risk. For the purpose of this post, I have just got 3 levels for the Impact and Likelihood (and this can be increased/decreased as per your requirement). For both Likelihood and Impact, a rating of 1 means Low, 2 means Medium and 3 means High. Now I have categorized the risk score into Low, Medium, High & Critical and would like to plot my events as a scatter chart against this. For that, follow the steps below:-

1) Create an image with the required risk scores in excel / PowerPoint / Paint. I came up with the following image.

This will server as the background image of our scatter chart. Add this image to the report.

2) Create a report with the required data sources and dataset. In this case, I have just made a sample dataset

SELECT     'Project A' AS Project, 0.5 AS Likelihood, 0.8 AS Impact
UNION ALL
SELECT     'Project B' AS Project, 0.8 AS Likelihood, 1.3 AS Impact
UNION ALL
SELECT     'Project C' AS Project, 0.9 AS Likelihood, 2.5 AS Impact
UNION ALL
SELECT     'Project D' AS Project, 1.4 AS Likelihood, 0.9 AS Impact
UNION ALL
SELECT     'Project E' AS Project, 1.5 AS Likelihood, 1.5 AS Impact
UNION ALL
SELECT     'Project F' AS Project, 1.2 AS Likelihood, 2.9 AS Impact
UNION ALL
SELECT     'Project G' AS Project, 2.1 AS Likelihood, 0.2 AS Impact
UNION ALL
SELECT     'Project H' AS Project, 2.4 AS Likelihood, 1.4 AS Impact
UNION ALL
SELECT     'Project I' AS Project, 2.7 AS Likelihood, 2.9 AS Impact
UNION ALL
SELECT     'Project J' AS Project, 1.6 AS Likelihood, 1.2 AS Impact
UNION ALL
SELECT     'Project K' AS Project, 2.2 AS Likelihood, 2.2 AS Impact
UNION ALL
SELECT     'Project L' AS Project, 1.1 AS Likelihood, 0.7 AS Impact

3) Go to the toolbox and drag and drop a chart item into the report body. Select the chart type as Scatter chart and click on OK.

4) Drag and drop Likelihood from the dataset fields list into the Values and then select Impact as the X Value in the chart. Also drop Project field into the Category Group of the chart.

5) Change the Axis titles to Likelihood and Impact for the Y and X axis respectively. Then go to both of the axis properties, and set the minimum as 0, maximum as 3 and Interval as 1.

6) Now click on the chart area and add the RiskMatrix image as the background image.

You might also want to hide the major gridlines for the horizontal and vertical axis.

7) Now with a bit of cosmetic changes to your marker colours, we arrive upon the end result. It is a good practice to enable the tooltips on the points, so that we can just hover our mouse to find out the project names.

The beauty of this approach is that it can be extended to a lot of other scenarios. For eg, I remember using this approach to answer a scatter chart with four quadrants question in the MSDN forum.

Ok, this is all from me for this year. As for my new year wish, I would like to quote Ann Landers-

“Let this coming year be better than all the others. Vow to do some of the things you've always wanted to do but couldn't find the time. Call up a forgotten friend. Drop an old grudge, and replace it with some pleasant memories. Vow not to make a promise you don't think you can keep. Walk tall, and smile more. You'll look ten years younger. Don't be afraid to say, 'I love you'. Say it again. They are the sweetest words in the world.”

Wishing all of you a very prosperous new year!

Introduction to Excel GeoFlow (Beta 1)

2012-12-24T18:03:00.001-05:00

I have been playing around for a couple of weeks with the beta release of Excel GeoFlow and I have got very mixed feelings about the product. No, I am not trying to be the Grinch in this Christmas season and I must say that I sure do like the direction in which Microsoft and this product is going – just that it is not even half as ready as I would have expected (but to be fair to them, it is just the beta 1 release). That said, there are a couple of features that I am really excited about and I thought of jotting down a quick introduction to GeoFlow for those who are interested.

1) How to get the beta release of GeoFlow
If you would like to try GeoFlow, send a mail to geoflowbetarequest@microsoft.com and wait for the team to respond. You must have Excel 2013 for GeoFlow to work.

2) Current Limitations
- Related tables are not supported in this release.All the data should be in one table.
- Backend data changes are not reflected in the visualization in this release and this will mean deleting and recreating the visualizations.
- No Undo/Redo is supported in this release.
- No way to slice and dice data within the visualization
- No drill down functionalities from a higher level (say, states) to a lower level (say, counties) which is there in tools like Power View.

3) Create a basic GeoFlow report
GeoFlow requires your data to have some geographical fields in it. It can be in a variety of formats including latitude and longitude, street address, city, zipcode, state, country, etc. Be aware that you will need internet connection for the geocoding to take place. For the purpose of this demo, I have got the population and personal income data by year of all the counties in the United States. Follow the steps below to create a basic GeoFlow report:-

a) After installing GeoFlow, you should now be able to see the 3D Map button in the Insert tab. Click on any of the cell within the data table and then select the 3D Map button within the Insert tab. Click on the Explore in 3D option as shown in the image below.

b) Choose the geographical field based on which you need the map. In my case, it is the StateName field. Click on Map It button to proceed.

c) Click on the Population field to see the states visualized by this measure.

By default, you have got the Column chart visualization (which can be clustered or stacked if a category is present). This can be changed to a bubble map or a heat map visualization also.

4) Navigating the 3D Map
Navigating the 3D map can be tricky so it is useful to know all the navigation features. To use a mouse to navigate in the GeoFlow 3D environment, do any of the following:
·         Double click rapidly on any portion in the globe to zoom closer to it.
·         Use the scroll wheel on your mouse to zoom in and zoom out.
·         Click and drag the globe in any direction to pan without changing the pitch.
·         Hold the Alt key, and then click and drag to change the pitch.
·         If you lose your point of focus, zoom all the way out with the scroll wheel to reset the globe and view.

To use the Keyboard to navigate in the GeoFlow 3-D environment, do any of the following:
·         Tap the arrow keys up, down, left and right to pan and spin the globe.
·         Hold the Alt key and then tap the left and right arrow keys to orbit the current target, or the up and down arrow keys to increase and decrease the camera pitch.
·         Tap the plus (+) and minus (-) keys to zoom in and out.

5) Interesting Features
There are quite some interesting features in GeoFlow and these are some of the main ones in my opinion:-

a) Handles multiple geographical levels:- We can add multiple geographical levels to GeoFlow. To add the counties (AreaName field) to our basic report, click on the Edit button in the Layer Manager as shown below.

Then select the AreaName field and map it to the County in Geography section. Then click on Map It button.

Now you should see the map visualized by counties. It might take some time for all the data to be processed on the map and it depends on the number of rows that you have. When the processing is done, you should see the message as Finished in the green bar below the map.

You can easily toggle back to the states by selecting the StateName radio button in the Map by section. However, as I mentioned before, drill downs are not possible (as far as I know in this release). Vote to up this request in Connect by clicking here.

b) Visualizing Data over time:- We can visualize the data over time if we have a date or time field. In my dataset, I have the year but this is not enough as GeoFlow requires a date or time field. So I had to make a new filed called DateYr which converts the year to a date field by adding 1/1/ before every year. Vote here to up this request to have the time play axis use any level of the time dimension. Now in GeoFlow, drag and drop the DateYr field to the Time section. Now you will get an additional section for time settings which has values like Time Accumulation, Instant and Persist the last.

You can also see the time play axis on the bottom. Click on play and you can see the data changing over time.

c) Adding Annotations and Textboxes:- This is definitely one of my favourite features. We can now add an annotation to a particular data-point by right clicking on it and then selecting the Add annotation option.

Similarly, you can add a textbox also. The only difference between them is that an annotation is bound to a data point while the textbox is more like a sticky note on the screen. The textbox stays at the same position even when the map is panned or zoomed.

d) Find a Location:- The find a location feature is pretty handy in case you have a lot of data points and want to directly zoom to a particular location.

e) Creating and Saving Tours:- I have obviously saved the most exciting feature for the last. Now you can create tours by clicking on the New Tour button and then including the scenes in the tour by selecting the Capture Scene button.

Scenes will transition automatically and effects are automatically applied. A scene is auto-saved when you capture a new scene. If you want to change a scene, for example if you want the scene to show a different angle or a different time subset, select the scene, make the changes and then click on Save Scene, Once you have created a tour, you can play it by selecting the Play Tour button. I have posted a video of a sample tour that I created.

Excel GeoFlow

After creating a tour in GeoFlow, the tour is automatically saved back to your workbook. You can create multiple tours by clicking on New Tour. Saving your Excel workbook will save each of the GeoFlow tours you created with the worksheet it was created from. Now you will be able to see a new option to Manage Tours also.

To summarize, there are some exciting features but clearly, a lot of work needs to be done before it can be used by data professionals for analysis. With this, we come to the end of today’s post. Wishing all of you a very Merry Christmas from my side and be good!

Querying Attributes and Measures in DAX Multidimensional

2012-12-02T11:08:00.001-05:00

The past week has been pretty exciting for Analysis Services geeks - Microsoft SQL Server 2012 With Power View For Multidimensional Models Customer Technology Preview (CTP) has been made available for download (and if you haven’t tried it out yet, download it from this link). Now the obvious part is that Power View can consume data from SSAS Multidimensional objects now and that was a long time request from the community. This will enable the business to use their existing investments in SSAS Multidimensional to utilize the latest end user tools like Power View. The best part is that all this is achieved through native support for DAX in Multidimensional and there is no translation of DAX into MDX. Now this opens up a whole new door of possibilities. For eg, we should be able to design SSRS reports using DAX queries on top of SSAS Multidimensional objects (and who knows, we might be able to replace some of the slower MDX calculations with faster DAX equivalents). Welcome to the world of DAX Multidimensional (or DAXMD)!

Now the purpose of this post is to introduce you on how to query Multidimensional objects in DAX, and you should be able to get a good overview on how the multidimensional objects are mapped in tabular from here. But there is something extra in this blog that the official documentation has not mentioned when it comes to querying attributes which don't have the same name and key values (for SSAS beginners, an attribute in SSAS MD can have different values for it’s key and name, while in SSAS Tabular, you have to specify the key and name as different columns). For the purpose of this post, I am using the Adventure Works DW 2008R2 database and querying using SQL Server Data Tools (SSDT).

Before we start, lets have a look at the summary

Armed with this knowledge, let us start querying in DAXMD

1) Querying Attributes with same Key and Name

Let us take the example of Calendar Quarter of Year in the Date dimension

We can write a simple DAX query as shown below

evaluate
values ('Date'[Calendar Quarter of Year])

Note that Date is a role playing dimension in the cube and you will need to use the name of the cube dimension as the table name (and not the database dimension name).

2) Querying Attributes with different Key and Name

Let us take the example of Calendar Year.

Now let us see what the query below will result in.

evaluate
values ('Date'[Calendar Year])

We will get an error - Column [Calendar Year] is part of composite key, but not all columns of the composite key are included in the expression or its dependent expression.

I spent hours trying to figure out what this error means and finally managed to figured out that it happens only when the key and names are different. It made sense also as DAXMD will only recognize the single attribute as two different columns and not as one. But I had no idea of what syntax too use. Enter Jeffrey Wang (blog) from the Anaysis Services Dev team -

“ This is a design decision for good performance. A single MDX attribute can produce several DAX columns, some visible, some not, but you can query them in SSMS regardless whether they are visible or not. In general, a MDX attribute, named A, can produce several columns like A.Key0, A.Key1, A. The design requires you to group by at least A.Key0 and A.Key1 if you want to group by any of the three columns. So to query attribute A in DAX, you can do either Summarize('Table', [A.Key0], [A.Key1]) or Summarize('Table', [A.Key0], [A.Key1], [A]). In simple cases where an attribute, named B, only generates a single DAX column, which will also be named B, you can simply query Values([B]) like regular tabular columns.
This constraint helps us achieve good performance since we can send all queries to the MDX engine which only groups by the entire attribute regardless how many DSV columns were used to create this attribute. Otherwise we would have to eliminate duplicate values on the DAX side after MDX query returns the resultset. Hope this makes sense. “

What this means is that your query will have to follow the below format

evaluate
summarize('Date', 'Date'[Calendar Year.Key0], 'Date'[Calendar Year])

Adding the .Key format to the column name is a new addition to the DAX language as far as I know. I am reasonably sure that this would be how Power View also issues the DAX when such attributes are used in the model, though I haven’t tested it so far. If anyone of you have seen the profiler traces of Power View, feel free to comment below and add.

3) Querying Attributes with Multiple Keys

Let us take the example of Calendar Quarter.

As mentioned previously, we will have to group by all the keys at least and then the name, if needed.

evaluate
summarize('Date',
          'Date'[Calendar Quarter.Key0],
          'Date'[Calendar Quarter.Key1],
          'Date'[Calendar Quarter])

4) Querying Measures in a Measure Group

Let us take the example of Internet Sales Amount in the Internet Sales measure group.

We can just write a simple DAX query to display the Internet Sales measure group by Calendar Quarter of Year as shown below

evaluate
summarize('Date',
'Date'[Calendar Quarter of Year],
"test", 'Internet Sales'[Internet Sales Amount])

We can also refer to the measure without the table name in the query. Also note that we don’t need to provide any aggregation to the measure, else we might get the following error - Column 'Internet Sales Amount' in table 'Internet Sales' cannot be found or may not be used in this expression. This makes sense also as the engine should use the aggregation that is defined in the multidimensional cube.

5) Querying Measures without a Measure Group

This section refers to those calculated measures that are made in the multidimensional cube which are not associated with a measure group. Since all the calculated measures in Adventure Works cube are associated with some or the other measure group, I made a simple calculated measure called test in the calculated member script of the cube as shown below

Create Member CurrentCube.[Measures].[Test] As 2;

Now I can refer to this Test measure in a DAX query as shown below

evaluate
summarize('Date',
'Date'[Calendar Quarter of Year],
"test", 'Measures'[Test])

Hopefully, this should get you started thinking in DAXMD now!

Update 3/1/2013

On the same vein, Gerhard Brueckl has blogged on DAXMD and Default Members. Interesting read, check it out!

Classifying and Solving the Events in Progress Problem in PowerPivot

2012-11-27T09:10:00.000-05:00

Last week, I was going through the PowerPivot forums as usual and was answering a question on How to handle count of data with “valid to/from” dates in DAX. This was a classic case of the “Events in Progress” or finding the number of active events between a start date and an end date (In case you haven’t read, Chris Webb (blog | twitter) has got some great posts on the Events in Progress solutions using both DAX and MDX). I replied back with a possible solution and soon, Marco Russo (blog | twitter) came up with a much more elegant solution. But more than the solution, what caught my eye was that our results were not matching and after a few email exchanges with Marco (and a big thanks to Marco for taking time off to answer my questions), we came up to the conclusion that it was because of the difference in our interpretations of the Event in Progress. That is when I thought of classifying the different interpretations of the “Events in Progress” as well as extending Marco’s solution for the same.

In a typical Events in Progress problem, there are fact rows with a start and end date and each row denotes an event. The requirement would be to find the number of events that were in progress on any given date. At a date level, there is no ambiguity as the number of events in progress for a particular day, say D would be the number of those events whose start date is less than or equal to D and the end date is greater than or equal to D (Actually, you can further sub-categorize this into whether the start dates and end dates are inclusive or exclusive i.e. whether the start date is less than or less than or equal to D and whether end date is greater than or greater than or equal to D. For the sake of simplicity, we are assuming that both start and end dates are inclusive). However, the moment you start analysing on a level above the dates, say a month or a year, the question arises on which date of the month to use for comparing the start and end date. Should we be using the first date of the month? Or the last date of the month? Or should we be using a combination of both? There is no wrong answer here and the right definition completely depends on the business requirement. So let us look at the four possible combinations that are possible:-

To give an example, say we are analysing the events in progress for January 2012. For Scenario S2, this would mean the number of events which have a From Date (or start date) less than or equal to January 1 2012 (which is the minimum date for January 2012) and To Date (or end date) greater than or equal to 31 January 2012 (which is the maximum date for the January 2012). Here also, we can further sub-categorize whether the start and end dates are inclusive or exclusive but this is not done for the sake of simplicity and also because our solution can easily be modified by removing the equal to symbol.

It might be easier to understand the relationship between the four scenarios with the help of a Venn Diagram.

As you can see, Scenario S3 is the superset and includes any event that was active on any date in the selected time period. Scenario S1 requires that the event started before the time period but can end during any day on or after the time period. Conversely, scenario S4 requires that the event started on or before the time period and ends on any day after the time period. Scenario S2 is the intersection of the scenarios S1 and S4 and requires that the event starts before the time period and ends after the time period. This means that those events starting or ending in the selected time period will not be counted.

Lets have a look at the four scenarios from a different visualization perspective.

Now that you have got a good idea of the different scenarios, let us look at some sample data that we can use for testing the solution. Given below is a simple fact table which contains Order Numbers with a From Date and To Date and also the Order quantity.

The PowerPivot data model that we are going to use is given below

I just have a simple calendar table (where I have just populated days from 2011 to 2013, again for simplicity) and the above Orders fact table. It is interesting to note that we will not be creating any relationships between the two tables as our solution does not require them. Now follow the steps below to get the solution for the four scenarios:-

1) Before we start, it might be a good exercise to review the original formula that I wrote and why we are moving to a different solution. The original formula I wrote was

OldMsr:=Calculate (
                  CountRows ( Fct ),
                  Filter (
                           Fct,
                          CountRows (
                                          Filter (
                                                  Values ( Calendar[DateKey] ),
                                                  ( Calendar[DateKey] >= VALUE ( Fct[FromDate] ) ) && ( Calendar[DateKey] <= VALUE ( Fct[ToDate] ) )
                                                  )
                                            ) > 0
                              )
                      )

To explain this formula, I am dividing it into 3 parts. The red highlighted area gives us the count of the fact rows which is the count of events in progress, provided the correct set of rows in the Fact table is passed as a table filter. Now to pass the correct table filter, we need to filter the fact table which is the part highlighted in orange and the condition to filter it is highlighted in yellow. Since we don't have any relations between the tables, what I am doing is to filter all the fact rows which has at least one row where there is a date greater than the From Date but less than the To Date. When we are analysing by month, what this will mean is that even if there is a single day in that month which satisfies the condition, include that fact row. This is exactly similar to our scenario S3.

Now some of the problems associated with this:-
- Formula is difficult to understand and write
- Formula can’t be easily changed to that of other scenarios
- Formula doesn’t perform well

2) Now let us look at the formula which was given by Marco in the forums

S3:= Calculate (
CountRows ( Fct ),
Filter (Values ( Fct[FromDate] ), Fct[FromDate] <= Max ( Calendar[DateKey] ) ),
Filter (Values ( Fct[ToDate] ), Fct[ToDate] >= Min ( Calendar[DateKey] ) )
)

You can already see how simple it is to understand. We are just passing the From dates which are less than the max of the time period and those To Dates which are greater than min of the time period as a table filter to the calculate statement and there is no need to filter the fact table explicitly. Not only that, it performs much better as it doesn’t have to parse through the entire fact table and just has to parse through two tables having the distinct values of From Date and To Date respectively (Always remember, it is faster to parse through two smaller tables than one big table). Now the best part is that this can be easily extended to the other scenarios as shown below

S1:= Calculate (
CountRows ( Fct ),
Filter (Values ( Fct[FromDate] ), Fct[FromDate] <= Min ( Calendar[DateKey] ) ),
Filter (Values ( Fct[ToDate] ), Fct[ToDate] >= Min ( Calendar[DateKey] ) )
)

S2:= Calculate (
CountRows ( Fct ),
Filter (Values ( Fct[FromDate] ), Fct[FromDate] <= Min ( Calendar[DateKey] ) ),
Filter (Values ( Fct[ToDate] ), Fct[ToDate] >= Max ( Calendar[DateKey] ) )
)

S4:= Calculate (
CountRows ( Fct ),
Filter (Values ( Fct[FromDate] ), Fct[FromDate] <= Max ( Calendar[DateKey] ) ),
Filter (Values ( Fct[ToDate] ), Fct[ToDate] >= Max ( Calendar[DateKey] ) )
)

Wasn’t that simple?

3) Now let us have a look at the results to make sure everything is coming correctly.

I have filtered for January 2012 and you can see that the dates in that table are showing the same values for all the five measures. Let us pick 10 January 2012 as an example. Only 11001, 11003 and 11004 suffice the conditions and hence the measures are all 3. The results come up correctly even if we look at a month or year level.

Let us take January 2012 as an example, so the max will be 31 Jan 2012 and minimum will be 1 Jan 2012. The order numbers which match the given conditions are given below and matches with the measure counts

S1 = 11001, 11002, 11003
S2 = 11001, 11003
S3 = 11001, 11002, 11003, 11004, 11005, 11006
S4 = 11001, 11003. 11006

4) Another benefit of this pattern is that it can easily be applied to other problems where you might need to find the sum or distinct count or any other aggregations between a from date and to date. Let us say, if I want to find the sum of Order Quantity between the from date and to date according to scenario S1, all I need to do is as follows

S1Sum:= Calculate (
Sum ( Fct[OrderQty] ),
Filter (Values ( Fct[FromDate] ), Fct[FromDate] <= Min ( Calendar[DateKey] ) ),
Filter (Values ( Fct[ToDate] ), Fct[ToDate] >= Min ( Calendar[DateKey] ) )
)

If you want to try this on your own, feel free to download the completed excel file from here.

Changing PivotTable Names in Excel 2013 + Bug Alert

2012-11-23T09:50:00.001-05:00

Today is Black Friday here in the US and I don’t have time to write a regular post (as I am scourging for deals online and planning to get out for shopping as soon as the insane lines outside the stores dwindle in numbers). So this is just going to be a quick tip for those who have tried upgrading their PowerPivot models with linked tables from Excel 2010 to Excel 2013 and found out that their PivotTable names don’t match anymore.

For those who are not familiar, let me give an account of the issue. If you upgrade a PowerPivot model which has linked tables from Excel 2010 to 2013, you will notice that the table names in the field list don’t match the table names in your model. As per my testing, this doesn’t break the measures that you might have created using the old table names (as the measures still use the table names in the data model and this name change looks to be more of a front-end display name) but might be a real inconvenience. Follow the steps below to reproduce and solve the issue:-

1) For the purpose of this demo, let us use the PowerPivot file that I created in Excel 2010 for my last post - Measure Selection using Slicers in PowerPivot. You can download it from this link. If you open it in Excel 2010, you can see the original table names in the field list as well as the linked tables used to create the model above.

2) Now close the file and then re-open in Excel 2013. Julie Koesmarno (blog | twitter) has put a step by step post to upgrade this workbook from 2010 to 2013 here. Also have a look at this post – Upgrade PowerPivot Data Models to 2013 for any potential issues while upgrading your workbook. Now once you have done that, you will see that all the table names have changed in the field list.

However, there is no change to the table names in the underlying PowerPivot data model.

3) To change the name in the PivotTable field list, all you have to do is to select a cell in the respective linked table, click on Design and then enter the original / required name in the Table Name cell as shown below

4) You can see that the changes are reflected in the PivotTable field list now.

You could avoid doing these steps in Excel 2013 if you had renamed your linked tables in Excel 2010 using the same way before upgrading. If not, it will just take the name of the linked table when you upgrade.

Apparently, this is a known issue and will be fixed in the next release as per this thread.

BUG ALERT!!!

If you paid close attention to the file that you just upgraded, you can notice that the slicer for measure name is not working. Now this seems to work for people who have installed the Office Professional Plus (v 15.0.4420.1017) but for people like me who have installed the Excel Preview (v 15.0.4128.1025), the slicers don’t work. Turns out the reason was that FORMAT function doesn’t play well within a measure expression. All you have to do is to change the measure expression for MsrValue from

MsrValue:=switch (TRUE,
Min ( Msr[MsrId] ) = 1,
FORMAT ( Sum ( Fct[Sales] ), "$#,#0" ),
Min ( Msr[MsrId] ) = 2,
format ( Sum ( Fct[Quantity] ), "#,#0" ),
Min ( Msr[MsrId] ) = 3,
format ( Sum ( Fct[Profit] ), "$#,#0" )
)

MsrValue:=switch (TRUE,
Min ( Msr[MsrId] ) = 1,
Sum ( Fct[Sales] ),
Min ( Msr[MsrId] ) = 2,
Sum ( Fct[Quantity] ),
Min ( Msr[MsrId] ) = 3,
Sum ( Fct[Profit] )
)

Of course this does mean that you lose the formatting. Now you can see that the slicers are working. This clearly is a bug as it works in Excel 2010 (as well as in the Professional Plus edition of Excel 2013). I have raised a Connect issue here, please vote for it so that it can be rectified in the next release. Ok, time to run for the Black Friday shopping now!

Measure Selection using Slicers in PowerPivot

2012-11-21T13:41:00.001-05:00

As a Business Intelligence professional, I find it really fascin(/frustr)ating working with Excel users. You get exposed to a completely different way of thinking when you work with them, not to say that they are extremely smart and demanding. From the moment I started working with PowerPivot and interacting with Excel users, I knew it was not going to be easy tackling their questions and requirements. You simply cant escape by saying that the required feature is not available because they will push you till you find a workaround. This was the case when one of my users came back to me asking for a slicer which will dynamically help them to select the measures. I replied saying that the slicers are not intended for that purpose and should be used for filtering the data (and not for just selecting or deselecting values like Measures, which can be easily done from the Field List pane). However, the user pushed back saying that he would like to create a dashboard for the top management and didn’t want them to be using the Field List pane. That is when I had to put on my thinking cap.

For the purpose of this post, I am going to use some simple mocked up data which involves three measures (Sales, Quantity and Profit) for country and year.

Follow the steps below to recreate a slicer which can be used to select or deselect the measures in a pivot table:-

1) Create a table which will have the list of measure names needed in the slicer in the MeasureName column and their representative integer ids in the MsrId column. Import this table into PowerPivot using Linked Tables and name it as Msr.

There is no need to make any relations with the existing tables, and Msr table should be a standalone table.

2) Now make a new measure called MsrValue with the formula given below

MsrValue:=switch (TRUE,
Min ( Msr[MsrId] ) = 1,
Sum ( Fct[Sales] ),
Min ( Msr[MsrId] ) = 2,
Sum ( Fct[Quantity] ),
Min ( Msr[MsrId] ) = 3,
Sum ( Fct[Profit] )
)

This measure will check the value of the MsrId column, and then use the appropriate measure. However, it should be noted that when there are more than one value of MsrId (or if more than one measures are selected), only the measure having minimum value of MsrId would be displayed. So how will we display more than one measures on the pivot table? Read on.

3) With this very simple setup, we are ready to view the results in the Pivot table. Drag and drop MeasureName column into the Slicer, Year into the Column Labels and Country in the Row Labels. Then drag and drop MsrValue in the Values and we should have something similar to the image below. I have also included the fact rows above the pivot so that we can compare.

Now there are a couple of problems with this pivot table. You would have noticed that the pivot table works correctly if only one measure is selected, but if there are more than one selections for the measure name, it will only show the measure with the minimum of the MsrId (eg, Sales in the image above). Also, we would like to see the name of the measure in the column above for clarity. To solve both of these problems, follow the next step.

4) Just drag and drop the MeasureName column in the Column Labels above the Year.

Now you can see that multiple measures are displayed and also that the measure names are displayed in the column above.

You can also change the formatting of the measures if needed

Luckily, that wasn’t as hard as I thought (which means that I could still use my evening free time to focus on more important problems like whether the chicken or egg came first into the world!). If you don’t have such important problems to focus on like me, you can download the completed file from this link and test it out on your own!

PASS Summit 2012 Hangover

2012-11-18T18:14:00.001-05:00

November has always been my favourite month of the year though I don’t really have a clear cut reason for that. Maybe it is because that my birthday falls in this month and I look forward to opening all those lovely gifts that I usually get. Or maybe because it is the time of the year when you get to see nature in her splendid fall colours and her transition to winter. Or could it be the simple reason that you get 50% off Halloween candy? (in case you didn’t know, I have a BIG sweet tooth) Anyways, I always look forward to November eagerly. Moreover, this year’s November was extra special - I was going to attend my first PASS Summit.

I started my baby steps in Microsoft BI six years back although I was blissfully ignorant of whatever community activities was happening then. I have always been an inquisitive mind (or a heckler, depends on whom you ask) and used to ask a lot of questions. Usually, my technical doubts were answered by the senior consultants in my company, and it was by chance that I got redirected to the MSDN forums (the truth is, they were tired of my constant questions). I think that was when I started getting cognizant of the SQL world and the community. I asked more questions, started recognizing the big names in the BI world, read more blogs by them and before I knew it, I was hooked onto this technology. I started answering questions in the forums apart from just asking them and I really enjoyed the kick that you get out of helping people. It also fuelled my personal growth and soon, I decided to share my learning by starting a blog. Fortunately for me, that was the time I moved to London, UK and I got a first real taste of user groups. I started attending the bi-annual SQLBits conference and it sort of inspired me to start speaking. However, I had never given a talk before (not even in my company) and my bouts of stage fright were legendary in my friend circles. But when you wish for something really hard, the entire universe conspires to make it happen. I decided to mail Chris Webb (Blog | Twitter) asking him if I could speak on one of the user groups and he set me up for my first talk (I don’t think I would have ever had the guts to ask again if I was rejected then). He also encouraged me to submit my abstract for SQLBits (which eventually got picked) and I slowly started getting a little more confident about public speaking. This was one of the many pushes forward that I got from my #sqlfamily (oh yes, that is what they are to me now, not just a community). When I moved to the US, I started submitting to the SQL Saturdays with the encouragement of many people in the community, and I am proud to say that the Washington DC one is going to be my 10th SQL Saturday this year (and this is apart from the user group, code camp and virtual chapter talks that I gave). For a person who would have his knees shake if he was standing and speaking in front of 4 people, this definitely was a big achievement and I have my #sqlfamily to thank for. For me, this cooperation and mutual encouragement was best epitomised in SQL Saturday New York City. I was the next speaker after Kevin Kline (Blog | Twitter) and just when I was setting up my laptop, I realized that I didn’t have my HDMI-to-VGA connector for projecting (and I could have sworn that I had checked it before leaving the speaker room, must have dropped it somewhere). I was totally freaked out - none in the speaker room had a converter and I had the demos only on my laptop. I was almost on the verge of cancelling my session. As a last try, I asked Kevin, who was on his way out, whether he could lend me his laptop so that I could try to use his laptop (which had a VGA port) for setting up a live meeting between mine and his laptops, and then projecting the demos from his laptop. Not only did he agree, but he came to my room to calm down my nerves, helped me in setting up the laptops and was throughout my side for the entire session. The session went great and I had lots of my attendees saying that it was really good. If it wasn’t for Kevin, I would have had an embarrassing situation and he saved my day (and to put things to perspective, he barely knew me!!!). I couldn’t even say thanks properly to him because he was already late for some other appointment and had too rush off. That is how selfless people in the #sqlfamily are and I really wanted to be a part of the biggest family reunion of the #sqlfamily – the PASS Summit.

I reached Seattle on Tuesday afternoon and straightaway went to the Chapter Leaders meeting where we discussed on how we can make our user groups more effective. It was very useful to hear from other chapter leaders on how they are organizing their user groups in terms of finance, attendees, marketing, speakers, etc. I also made sure to attend the #sqlFirstTimers meeting where all of the first time attendees were given advice on how to network and make the most of the PASS Summit.

Over the next three days, all the attendees were presented with a rich selection of sessions across all tracks and difficulty levels. I chose the Intermediate to Advanced topics for Business Intelligence and was truly happy with the results. It was great to attend sessions by Chris Webb, Marco Russo, Alberto Ferrari, Stacia Misner, Allan Folting & Akshai Mirchandani, Paul Turley, Melissa Coates, etc and I learned a lot from these great speakers. It was very difficult making a choice between all the great sessions and I hope to catch up with the rest of the sessions through the recordings.

As I mentioned before, the PASS Summit was also a place to meet my #sqlfamily. There were a lot of people I had met over twitter and it was a fabulous opportunity to associate faces to those twitter handles. I was also able to reconnect with my friends from UK and India and it was so much like meeting your family again after a long interval.

The various parties after the sessions kept me busy in the night too. It was awesome getting my first dose of #sqlkaraoke and I was up all the 4 nights – thanks to the Quizball event by SQL Sentry, Hard Rock Cafe event by Pragmatic Works, the Experience Music Project event by Microsoft and of course, the traditional #sqlkaraoke event at Bush Gardens.

I also made sure to stay a couple of days after the event to explore the city as it was my first time in Seattle. I must say the weather gods took mercy on us and we had some nice sunshine, so unlike Seattle .

All in all, it was a great trip and it was hard to bid goodbye to the city when it all ended. People usually say that when you go somewhere with a lot of expectations, you usually end up with disappointment. However, the PASS Summit 2012 was above my expectations and I really savoured every moment of it. Guess it is time to end my long post. Hope to see all of you guys for the next PASS Summit, which will be held in the beautiful city of Charlotte. It will be much easier for me as I am based out of Charlotte and I will be there to welcome all of you guys to our annual family reunion!

SQL Saturday Charlotte Journal

2012-11-05T21:44:00.001-05:00

Was it fun? Oh yeah, it was. Was it perfect? Hmm, maybe not. But was it awesome? Definitely yes! Let me tell you, it was an amazing experience being a part of the whole process. SQL Saturday Charlotte was my 9th SQL Saturday of this year, but there was a big difference from the other ones – this time, I was wearing the Organizer hat and not the Speaker hat.

It all started around March of this year when me and Javier Guillen (blog | twitter) were seriously contemplating on starting a BI user group in Charlotte. We pulled in our common friend, Melissa Coates (blog | twitter) to this plan and she in turn informed us that Rafael Salas (blog | twitter) was also trying to do the same. We joined forces and this marked the start of the evening #BeerAndBI rituals, where we started to come up with ideas and plans to start our new user group.

In one of those sober (?) rituals, the SQL Saturday idea was introduced and then there was no turning back from there. We informed Karla Landrum (twitter) of our intentions and she was extremely supportive. We started with checking venues around June, and I must say we were really lucky to have Alberto Botero of Central Piedmont Community College as our friend. He acted as a bridge between the college and us, and ensured we had a venue to start off with. We confirmed the dates for SQL Saturday with Karla by June end and started our preparations. Having a big team has its own advantages, it was easier to split tasks without over-burdening anyone. Rafael was in charge of the sponsors and food section, Javier was in charge of finance and venue related section, Melissa was in charge of social media, website, emails, general coordination, etc and me in charge of recruiting speakers and making the schedule. Even though we were assigned specific tasks, it was great to see everyone pooling in their efforts wherever they could. We also got Shannon Lowder (blog | twitter) to help us coordinate all the volunteers. And when the day finally came for setting up the venue on the day before, there were lot of unforgettable moments, for example stuffing the bags on the eve of the event with Javier, Rafael setting off the fire alarm when we were already running late for the speaker dinner, setting up the tables and the cute little raffle boxes made by Melissa, last minute printing with the help of Shannon’s iPhone, etc. The speaker dinner was well attended and was fabulous, we even managed to have a few shots before we left.

On the D-day, we reached the venue around 6:45 am in the morning and from that time on till the afternoon, we were so busy that we couldn’t even speak much with each other. I was always of the mentality that we had planned more than necessary and things will flow pretty easily during the event. I couldn’t have been more wrong and it really took a lot of effort from our as well as our volunteers’ side to make sure everything went well. This made me appreciate the great work done by all our volunteers and organizers of past SQL Saturdays all the more. At the end of the day, it felt great to hear all the positive responses about the event from our attendees. A big thanks to all our sponsors, speakers and volunteers who made this possible.

It was fun and although it took me around a week to recover completely, I sort of miss all the madness involved in planning a SQL Saturday right now. Anyways, I am sure there will be much more to it with the start of the new BI group in Charlotte.

Time to pack up my bags for the PASS Summit and reward myself with a relaxing week at Seattle!

SSRS Charts with Data Tables (Excel Style)

2012-10-29T21:55:00.001-04:00

Ok, this post was supposed to come out a lot earlier but I was just too lazy. Complacency is a struggle we all have to fight and I would be lying if I say that I am immune to all these. This post is a direct result of a few forum posts where people said that charts with data tables are not possible in SSRS (unless you add a tablix beneath the chart and spend umpteen hours trying to align both of them!). Working with SSRS is like making love to a woman - you need to know the woman and what makes her click well for a fabulous time. Well, you can still manage without knowing her well enough, just that it wont be that awesome!

For people who are familiar with excel charts, adding a data table to a chart is a one-click affair. It is nice to see the data table just beneath the chart and it also serves as a legend. A simple example is shown below:-

In SSRS, having a tablix beneath the chart serves as an ok workaround as long as the number of fields in the category is fixed. When the number of fields change change (lets say, when the value of a report parameter changes), the alignment goes for a toss. Here, I will be demonstrating a much more elegant way to add the data tables than trying to align the tablix and chart.

1) Create a simple dataset from the AdventureWorks cube, displaying the Month, Category and Internet Sales Amount. Add a parameter for Calendar year with a default value of CY 2007.

2) Drag and drop a matrix to the designer pane and then add the Category field on the row, Month on the column and Internet_Sales_Amount on the data as shown below

3) Add two rows outside group above the category row as shown below.

4) Now you can delete the top row (but not the associated group) containing the Category and the Month Header as shown below

5) Once that is done, drag and drop the month field to the textbox above the data value. Here, I just want to display the first three letters of the month (instead of having the month followed by year), so I am using the expression below directly:-

=left(Fields!Month.Value,3)

You might also want to do some quick formatting like hiding the textbox borders for the top row.

6) Once this is done, the only part remaining is to make a chart, format it and then place it on the top right textbox. For that, drag and drop a stacked column chart outside of the matrix, and then drag Internet_Sales_amount on the Values, Month on the Category Groups and Category on the Series Groups as shown below.

7) Right click on the vertical axis, select the Vertical Axis Properties and then modify the expression for Maximum from Auto to the expression written below

=1.2 * max(Fields!Internet_Sales_Amount.Value, "DST_Main")

8) Delete the axis titles, chart titles and the horizontal axis from the chart

9) Hide the labels for the vertical axis.

10) We need to place this chart within the tablix and make it repeat for each of the month. But before that, we need to make sure that there are no white spaces within the chart area, and hence change the following properties for the Chart Area.

Also set the BorderStyle property for the Chart as None and disable the MajorTickMarks for the Vertical Axis. This will also help in making the charts look continuous.

11) Now as a last step, we need to ensure that the vertical axis is only displayed for the first chart, Hence, set the LineStyle property for the vertical axis as shown below

=iif(Fields!Month.Value=First(Fields!Month.Value, "DST_Main"),"Solid","None")

12) Drag and drop the chart into the tablix cell

13) To add a color as a column next to the Category, we can use the Custom Code technique for consistent colors described here. Then ensure that the BackgroundColor property for the column as well as the series color for the chart is the expression below

=Code.GetColor(Fields!Category.Value)

14) Now preview the report and you will get the intended result. Change the parameter value from 2007 to 2008, and you will still see that the alignment is proper (even though the number of bars have changed)

You can download the completed rdl file from here. Now you know which post to point to incase someone asks on how to create charts with data tables in SSRS

Difference from Maximum of Running Totals in PowerPivot

2012-10-18T19:52:00.000-04:00

I don’t know how many of you are Formula1 fans here, but this season is pretty good. Sebastian Vettel and Fernando Alonso are going neck and neck for the title and there are just 4 races remaining. I try to follow F1 as much as I can, but had missed watching the last couple of races and there were some pivotal moments as Vettel took the lead from Alonso. I really missed knowing how they were doing at the end of each race and how far the other drivers were from the race leader. That is when I decided to relive the experience by mocking up the data in PowerPivot.

So what I want to do is to get a running total of the points for each driver across the races., so that I can see how each driver was doing throughout the season. Also, it would give me a better idea of their performance if I could get the difference of the points for each driver from the race leader at the end of that race. Interesting huh?

Source

The source for our exercise can be downloaded from here and contains the following data:-

1. RaceFact

This table contains the positions for each driver across all the races in this season. The columns are Driver, Race, Position and a flag whether they crashed or not.

2. RaceOrder

This table contains all the completed races in this season and the order in which they happened. The columns are Race and Order.

3. Points

This table contains the points for each position. The columns are Position and Points.

4. Driver

This table contains the list of all drivers and just has one column - Driver.

Requirement

1. The running total of the points for each driver should be displayed race by race as shown below.

2. The difference of each driver from the leader (as of that race) should be displayed.

Implementation

1. Build the PowerPivot model for the RaceData as shown in the diagram above. For simplicity, I have already created the model in the source file available for download here.

2. For creating the running total across the races, we will be following the technique detailed in my previous blog. For that, create 2 calculated columns in the RaceFact table called Points and Order respectively.

=calculate(sum(Points[Points]))

=calculate(sum(Races[Order]))

Notice that the foreign keys in the fact table (Driver, Race, Position) are hidden as it is a best practice to hide the foreign keys. This way, the users will not get confused whether to use the columns in the master tables or the fact tables.

3. For finding the running total, we can create a new measure with the below formula

RT_Points:=calculate(sum(RaceFact[Points]), filter(Allexcept(RaceFact,Driver[Driver]), RaceFact[Order]<=Max(RaceFact[Order])))

When this measure is used against the drivers on rows and races on columns, it will display the running total of the points for each driver across the races.

4. Now for getting the difference of each driver from the leader, we need a measure which will give us the points of the race leader at the end of every race. This measure would be equal to the max of the running total that we just created and that should be the same for all drivers for a particular race. Create the measure with the DAX formula given below

MaxRT_Points:=calculate(maxx(values(Driver[Driver]), [RT_Points]), All(Driver[Driver]))

The result is shown below

5. Now all we have to do to get the final requirement is to subtract the MaxRT_Points measure from the RT_Points measure as shown below

DiffFromLeader:=[RT_Points] - [MaxRT_Points]

The result is shown below

6. With a bit of formatting and adding a chart for the top 6 drivers, I can now clearly see how the battle for the top place has been going

The final version of the PowerPivot file can be downloaded from here. It is interesting to see that Fernando Alonso has been the most consistent driver, as can be seen from the above chart, even though he is in second place. With this information, I am going to bet my money on Alonso winning the Drivers championship (and not because I am a biased Ferrari fan :P). What do you reckon?