Stacked Charts: A Little Swiss Army Knife of SSRS

April 5th, 2011
Comments Off

I am a fan of Jon Peltier for his work around Excel visualisation. It is incredible how many ways we can tweak Excel charts to get great results – out of the box, with a little bit of development effort. In terms of flexibility and power, SSRS is very similar to Excel and two of its charting components – the Stacked Column chart and the Stacked Bar chart allow us to create some interesting non-default charts.

Let’s see an example of a typical stacked graph based on a simple T-SQL dataset:

SELECT ‘A’ AS description, 0 AS fPoint, 100 AS sPoint
UNION
SELECT ‘B’ AS description, 50 AS fPoint, 200 AS sPoint

In the designer:

And when rendered:

This is a typical bar chart and there is nothing new here. The interesting part starts when we change the colour of the fPoint to No Color. The same graph, rendered starts looking like something else:

Hmm, it looks like…a Gantt Chart! If we were to replace A and B with task names and the X axis with date/time values we have the opportunity to create a chart somewhat resembling a real Gantt chart. In the data set we must observe the rule that fPoint shows time when the task has begun and sPoint – its duration. For a detailed walk-through you can check out this article on SQL Server Central.

But the post does not finish here. With the same technique (different data set) we can create the following, as well:

A pyramid chart! The data set has its own set of rules, which are different to the Gantt chart. The data set used for this example was:

SELECT ‘A’ AS description, 5 AS fPoint, 1 AS sPoint
UNION
SELECT ‘B’ AS description, 3 AS fPoint, 5 AS sPoint
UNION
SELECT ‘C’ AS description, 2 AS fPoint, 7 AS sPoint
UNION
SELECT ‘D’ AS description, 0 AS fPoint, 11 AS sPoint

The easiest way to show how to build the data set is to imagine we have a third series – tPoint:

SELECT ‘A’ AS description, 5 AS fPoint, 1 AS sPoint, 5 AS tPoint
UNION
SELECT ‘B’ AS description, 3 AS fPoint, 5 AS sPoint, 3 AS tPoint
UNION
SELECT ‘C’ AS description, 2 AS fPoint, 7 AS sPoint, 2 AS tPoint
UNION
SELECT ‘D’ AS description, 0 AS fPoint, 11 AS sPoint, 0 AS tPoint

The sum of fPoint, sPoint and tPoint should always be the same and equal to the base of the pyramid and to maintain the symmetry, we want fPoint and tPoint to be the same. In general, the formula for this is:

fPoint = (Max(sPoint) – sPoint) / 2

We have to guard against 0/2 as we will always have one case (for the Max(sPoint)) in the data set where this equals 0.

And one last example of the power of the stacked charts in SSRS:

Here the Stacked Bar is changed to Stacked Column chart type in SSRS and the data set used is:

SELECT ‘A’ AS description, 5 AS fPoint, 3 AS sPoint, 0.1 AS tPoint
UNION
SELECT ‘B’ AS description, 3 AS fPoint, 8 AS sPoint, 0.1 AS tPoint
UNION
SELECT ‘C’ AS description, 2 AS fPoint, 1 AS sPoint, 0.1 AS tPoint
UNION
SELECT ‘D’ AS description, 1 AS fPoint, 6 AS sPoint, 0.1 AS tPoint

The tPoint is the marker for our targets and we can adjust the thickness by altering our data set. One limitation is that we cannot have targets lower than the top of the actual (i.e. the tPoint will always appear above, and not in the middle of, the fPoint bar), but this could be useful if we know that tPoint must appear above or just on top of fPoint (e.g. sPoint = 0 will give us a marker capping the bar). We can change this by adding one more series – topPoint, which will be 0 when we are below, or on target and will show the amount above target when we have some:

SELECT ‘A’ AS description, 5 AS fPoint, 3 AS sPoint, 0.1 AS tPoint, 0 AS topPoint
UNION
SELECT ‘B’ AS description, 3 AS fPoint, 8 AS sPoint, 0.1 AS tPoint, 0 AS topPoint
UNION
SELECT ‘C’ AS description, 1 AS fPoint, 0 AS sPoint, 0.1 AS tPoint, 1 AS topPoint
UNION
SELECT ‘D’ AS description, 1 AS fPoint, 6 AS sPoint, 0.1 AS tPoint, 0 AS topPoint

Then we can add it to the chart and colour the series in the same colour as the fPoint series:

When we render this we get:

There we go, the limitation has been lifted.

Note that for each different variation of the charts we are using customised data sets. The data transformations needed may not be trivial in some cases, but for most purposes they will be very simple and easy to work with in T-SQL; and because we keep this logic in T-SQL and just feed it ready for plotting to SSRS these charts render fast – a good practice worth re-iterating here.

SSRS, Visualisation , ,

 

Feeds in SSRS Reports

April 3rd, 2011

The XML data source in SSRS can be very powerful. Not only for querying web services but also for showing feed data on our reports. In this post I will show you how we can show the contents of my favourite blog site SQLBlog.com in an SSRS report.

First we need to get the feed URL. In this case it is: http://sqlblog.com/blogs/MainFeed.aspx. Once we have this, we can use it in a new XML data source in SSRS just as it is:

Then, we can use it in a data set in our report. The little tricky part here is that SSRS expects us to send a query to the data source. We do not need to do this for an RSS feed and instead we can leave the query blank.

However, this results in an empty fields list and hitting Refresh Fields does not help. To overcome this problem we need to know what the fields are at the first place. We can open Query Designer and click on the Execute button (!). The result is a number of rows corresponding to the feed items and columns containing various types of information about the items.

In the SQLBlog case, we get one line for each category the author has placed the feed in, while in other cases we may get one line per feed item or another variation. It is always necessary to inspect the contents and ensure we understand what we get from the data source in order to deal with the feed effectively later on because the feed contents vary. Many columns are irrelevant and we need to select only the ones we need. From the sample feed I wanted to show just the title, link, pubDate, creator and the description on my report. Since the field list was blank I added these fields manually as Query Fields.

Then, I placed these fields in a table to check the result.

I could see all items as expected, but the formatting was off. Additionally, there were multiple items repeating because of the multiple categories for each article. With a bit of work I managed to group/format/lay out the items appropriately, add report actions for the links and generally transform the table in a nice report, which I could use as my default feed reader.

And the rendered report:

By the way, the second item is very interesting – Alberto Ferrari has been just awarded the Microsoft MVP award for 2011!

One problem that you may find is the default authentication SSRS uses for an XML data source – Do not use credentials. This will cause a problem once the report is deployed, so I would recommend this should be changed to something else (in example, Windows Authentication).

There we go – we have a fully functional blog reader. We can enhance this by providing a list of authors (like a table of contents with linked reports), report parameters filtering the table, and other nice to have things. Of course, there are many feed readers out there any you may wonder why you would build your own in SSRS. Well, think about not only RSS but also other content which comes through XML – in example web service content which we can query through SOAP, twitter feeds, etc. Additionally, incorporating such content within a SSRS dashboard or report could come in handy if we get our hands on a feed showing financial information. One other source for content could be Microsoft’s Azure Data Marketplace – after all the data there comes in feeds and it could be handy to show it directly on our reports with a little bit of formatting.

Download the sample report here:

SSRS ,

 

Load Test Your SSAS Instance

April 1st, 2011
Comments Off

If you are wondering how performant your SSAS box will be with multiple users executing various queries in an extremely cool visual way, you now have the option to go to codeplex and download the new AS Performance Workbench by Rob Kerr from BlueGranite. It is awesome! And to prove it is – have a look at the following YouTube demo (6m 24s): http://www.youtube.com/watch?v=Kn4-3JTyDXM.

For any comments, requests and lod cheering you can also visit the project discussion page: http://asperfwb.codeplex.com/discussions.

Enjoy!

SSAS , ,

 

MDX Gotchas Presentation Materials

March 15th, 2011

I had the pleasure to present in front of one of the biggest and best SQL Server User Groups in Australia and as Greg Linwood proudly (justified so) said – in the world. As I promised I have uploaded the presentation materials to this blog and you can find links to the slides, MDX scripts I run and some useful links below. As a side announcement, Darren Gosbell is now officially in charge of the Melbourne SQL Server User Group. I am sure that his passion for SQL Server will lead to great results and we will be able to enjoy a strong stream of speakers in the coming years.

To everyone who attended tonight – Thank You for coming and for giving me great reviews!

Useful Resources

The Many-To-Many Revolution by Marco Russo

SQL Server 2008 Performance Guide by SQL CAT and the SSAS Product Team

VBA Functions in SSAS by Irina Gorbach

Performance Improvements in Analysis Services on MSDN

Measure expressions: how performant are they? By Christian Wade

MSDN SQL Server Analysis Services Forum

TechNet Tech Center: Analysis Services – Multidimensional Data

SSAS-Info

Books

Microsoft SQL Server 2008 MDX Step by Step – Beginner/Intermediate

MDX Solutions: With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase – Beginner/Intermediate

The Microsoft Data Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft Business Intelligence Toolset – Beginner/Intermediate

Expert Cube Development with Microsoft SQL Server 2008 Analysis Services – Intermediate/Advanced

Microsoft SQL Server 2008 Analysis Services Unleashed – Expert

Slides and Scripts

Please let me know if you need more reference materials or if you think that some that I have missed should go in this post.

SSAS

 

Many-To-Many Relationship Modelling in PowerPivot: Workaround

February 12th, 2011
Comments Off

So far PowerPivot does not support many to many relationships. It is still early days and I am sure that things will change soon. Moreover, there are a few ways to implement many-to-many relationships for measures in DAX. Two notable posts on the subject can be found on Marco Russo’s and Alberto Ferrari’s blogs:

http://sqlblog.com/blogs/marco_russo/archive/2009/12/07/many-to-many-relationships-in-powerpivot.aspx

http://sqlblog.com/blogs/alberto_ferrari/archive/2010/10/19/powerpivot-and-many-to-many-relationships.aspx

Unfortunately, if we have multiple measures, the approach is somewhat cumbersome as we need to build numerous DAX measures. I have been playing around with modelling options and I just came across an idea which, while not perfect, can help us to mitigate to some extent the lack of this crucial bit of functionality. In fact, “not perfect” is quite an understatement – the more appropriate expression would be “a hack”, but it could still be useful in some scenarios – hence this post!

To put some background, let’s assume we have this simplistic model:

An account table

A customer table

And a fact table

Here we have two accounts, A with an Amount of 100 and B with 50.

Now, let’s assume we have the following mapping table between Accounts and Customers:

Account A maps to customers X and Y; Account B to X only.

Now, instead of trying to directly map the relationships here, we add the Amount to the mapping (bridge) table:

And we then create our relationships in PowerPivot. The problem here is that when we slice by Account A (with a key of 1), we would get the 100 Amount doubled. If we had more than two customers mapped to the account, it could have been tripled, or in fact multiplied by N, where N is the number of mapped customers.

The trick I am offering is adding a negative amount to this impromptu fact table against the same account (A) and against a Z customer with a key of -1, which acts as an unknown/adjustment bucket. Therefore, the Customer table would become:

And our fact/bridge table:

Now, if we create relationships between this table, the account and customer tables we would see the following:

And

Well, we do get one extra row with negative amounts, but all else seems just fine. The total is correct and the amounts per account and customer are also correct. If we have more than one measure, we will have to negate each of the additional measures as well if we want to achieve the same behaviour.

I also created a slightly more complicated model, which included years:

Here we have to compensate for two rows with negative amounts but the experience of using this model is not much different than the previous:

Some other common scenarios could also work quite well. I have tried with two M2M relationships and while the number of rows gets progressively larger, the model still seems to work. Another common scenario would be to have more than 2 attributes in either of the tables (whoa!), and that seems to work very well, too. The key is to remember to negate/adjust for each mapping after the first one. If we had a third node in our X,Y table mapped to A, we would have two additional rows in the fact table – one with a positive and one with a negative amount.

Unfortunately I have not come up with a way to simplify the data modelling through DAX, so at this stage I would do it in the source. However, it should not be too difficult to achieve this in SQL, for example.

I would be interested to see if anyone comes up with scenarios where this could not work and cases in DAX where the additional rows are a problem (beyond the “too much data”, of course).

PowerPivot , , ,