Tim Kent's blog

New e-Learning MS Business Intelligence Course

I’m sure a number of you know of UK SQL Server MVP Chris Testa-O’Neill from his many presentations at user groups and conferences.  Chris has just authored a new e-learning course for Microsoft which covers the BI stack.  From Chris:

"I am pleased to announce the release of the Author Model eCourseCollection 6233 AE: Implementing and Maintaining Business Intelligence in Microsoft® SQL Server® 2008: Integration Services, Reporting Services and Analysis Services

This 24-hour collection provides you with the skills and knowledge required for implementing and maintaining business intelligence solutions on SQL Server 2008. You will learn about the SQL Server technologies, such as Integration Services, Analysis Services, and Reporting Services

This collection also helps to prepare for Exam 70-448 and can be accessed from: http://www.microsoft.com/learning/elearning/course/6233.mspx “

Well worth a look I reckon!

SQLBits VI comes to London

Just in case you hadn’t already heard from Chris or Simon the next SQLBits has been confirmed for 16th April at Church House Conference Centre in Westminster.  Simon also explains why the tight timescales and only single day event this time.

If you need to find a reason to convince your boss why he should give you a day off to attend just look at the session list from the last SQLBits!

And if you fancy presenting yourself, session submission is open now.  You'll need to Login/Join first then complete your Speaker Profile before submitting a Session 

See you on the 16th!

Shapefiles for SSRS 2008 R2 Maps

Great free resource for pretty much every shapefile map you’ll need :)

Shapefiles for Epi Info

Plug them into your SSRS Map and off you go.

SSRS 2008 – Icon Charts

In my last post I had a bit of a moan about the lack of new data visualisation features in PerformancePoint Services but to be fair MSFT have really turned up the dial on the data visualisation functionality with the release of Excel 2007 and SSRS 2008.  SQL 2008 R2 sees further advances with features such as Sparklines, Data Bars and indicators being far easier to create in SSRS.  There’s still a few things missing from the stack though when compared against some of the pureplay visualisation vendors such as Tableau and Spotfire.

Icon Charts (I’m not actually sure that’s their real name!) are another form of Tufte’s “small multiple” that combines the concepts of lattice/trellis charts and heatmaps.  This type of visualisation utilises the user’s perception of colour (hue) and form (size) to allow analysis of multiple categories with multiple quantitative measures.

I was hoping that it might be possible to do something with the new indicators in R2 and though you can use custom images there’s no simple way to hook them up to the data in terms of size and colour.

In this solution (workaround/hack?) I’m using the matrix object again but rather than using an inline chart I’m simply using a square symbol (letter n) from the wingdings font and then setting the colour (with a sequential palette) and size according to the data.  My example uses hard-coded values in a switch statement in the expression but you could be cleverer than this and make the values percentages of the maximum in your dataset for example.

Add a couple of legends so that the viewer can clearly see what they are looking at.  It’s easy to pick out that SouthWest in Q3 had good revenue but low profit.  Is there a Problem? Add a drillthrough to detail report to allow the user to find out.

As always the sample report can be downloaded from here.  Alternatively, e-mail devteam(at)adatis(dot)co(dot)uk for a copy.

PerformancePoint Services 2010 – First impressions

We’ve been lucky enough (or we rather hassled enough people in MSFT for long enough!) to have been participating in the Office 2010 technical preview for the last couple of months but as it’s all been under NDA haven’t been able to blog about it.  This also means that we’ve had a chance to look round SharePoint 2010 and, in particular for me, PerformancePoint Services.  Nick Barclay has just done a series of posts about what’s new/improved/different in the new version so go there for the full list.  Here’s a quick round up of our first impressions:

What looks good so far:

• SharePoint integration – Whilst Dashboard designer is still pretty much the same product for doing your, errr, dashboard design it’s no longer the admin and security tool as well.  This is all carried in SharePoint and in fact you have to set up a specific PPS site to do this.  A great deal of effort has obviously gone into this and which looks to have paid off.

• Security is all through SharePoint – no need to set up permissions twice!
• AS Conditional formatting now works.
• Decomp tree is back!!

• Measures can now be formatted independently.
• Workspace browser is now much more intelligently organised.
• Filter by value – you can now restrict rows\columns by value

• Dynamic dimension measures on scorecards- this was a bit of a workaround in 2007 as I’ve posted about previously. This now works properly
• Re-usable filters – Filters can now be shared and re-used across dashboards

Disappointments:

• Lack of improvements for data visualisation – Very disappointing - other than the decomp tree, the visualisation side of PPS has changed little.  Still no real control over how your graphs look. The only other new item to be introduced is the Pie Chart!!!! oh dear.  Still no bar charts (and I mean Bars not Columns), no chart formatting options or (controllable) second y axis options that I can see :(

• Decomp tree is not a chart type but a right click option from a deployed report.  i Like the option to do this from any point in a report but would be nice to have both options.
• It’s still called PerformancePoint! – I have to admit I when I read another blog of Nick’s following the demise of Planning I didn’t entirely agree with him that it should be renamed.  Having spent the last ten months trying to explain to various IT departments that PerformancePoint is not the devil and that the Monitoring side has not been affected (usually to no avail) has changed my opinion completely. 
• As per Chris’s blog – ProClarity just seems to have disappeared – I know that was never what Monitoring was supposed to be but the lack of an ad-hoc cube browser is a huge oversight.
• Did I mention the lack of data visualisation improvements????

There’s lots more to discuss and there will be more to come over the next few weeks time allowing.  SharePoint 2010 looks pretty impressive…

Boyan Penev – Data Mystification

This is a great post from Boyan Penev which I’m sure will be familiar to many of you who design reports for customers.  Sometimes you just have to give them what they want even when it goes against what you believe :(

SSRS 2008 – Lattice Charts

Update: Nick Barclay has made some nice tweaks to the sample here

As I’ve mentioned before, we always try and design reports, dashboards and charts for our customers taking into consideration data visualisation best practices as promoted by Stephen Few, Edward Tufte et al. 

Whenever I’m reading Visualisation books and in particular the best practice examples, I always find myself wondering whether:

a) it’s possible with Reporting Services? and

b) it can look as good with Reporting Services?

and this was the case whilst I finally got round to starting to read Few’s new book Now You See It (which I  hope to do a review of soon).  One of the key principles of data visualisation is “Compared to What?”.  It’s all very well presenting the information in a clear fashion but without context it’s hard to argue it’s worth.

The use of a matrix of charts (lattice charts, trellis charts) allows the eye to very easily compare data both horizontally and vertically.  This method of display is also known as “Small Multiples” – a term coined by Tufte in his book - Envisioning Information.  These charts also allow you to easily add additional dimensions to a two-dimensional display.

I’m sure many of you will have used SSRS inline-charts before in a table (my bullet chart examples use this method) but it’s also possible to do this with the matrix object as well.  I’m not going to explain in detail the method to produce in-line charts in SSRS as it’s well documented already in this msdn article but in summary you simply set up your matrix with the appropriate row and column groupings, design your chart outside of the matrix then when it’s ready simply drop it in the data cell. 

One thing to make sure you do is to change the maximum setting for your value axis to be the maximum value from your entire dataset using a scoped field expression (=MAX(Fields!Sales_Amount.Value, "DataSet1")).  Without this change you will be visually comparing apples with oranges!

With a bit of work on the formatting, the result looks pretty reasonable (though the spread of AdventureWorks data isn’t that great).  You can easily see which region is performing best and which category has the best sales.

As usual you can download the sample

Please support a good cause

Dynamic Dimension members on a PerformancePoint KPI

One of our customers had read Nick Barclay's post on dynamic sets in SSAS 2008 and was hoping that this would mean that you could create KPI's with dynamic dimension members.  Well the answer is yes and no.  It's already possible to do this in Monitoring (or should I say PerformancePoint Services) using custom sets in the scorecard designer (more on this below).  However in PPSM these sets are resolved at the point the Scorecard is rendered in the browser.

This is fine as long as the members of your sets are not affected by the filters applied to your scorecard (member.children for example) - unfortunately the set does not get re-queried when you change a filter.   For instance if you were to create a set of your top 10 customers and drag that onto the rows of your scorecard, changing a time filter will not cause the KPI dimension members to change even if you've used time.currentmember in your set definition.  So you may end up displaying the Top 10 customers for the current month which may be different to  the Top 10 for the selected time period.

Update: Please see Nick Barclay's comment below for a very neat solution to this issue using filter link formulas.  (Wish I'd thought of that!)

Custom sets in the scorecard designer aren't the most obvious thing to use nor are they very user-friendly.  Your best bet is to use a tool like SQL Management Studio/Mosha MDX Studio to design a query that you know works then paste out the MDX for the set into the custom set formula editor.  You access this by dragging the Custom item in the Details pane onto the relevant position on your scorecard:

Paste your set query in to the pop-up dialog.  For Example:

TOPCOUNT(
    [Product].[Product Model Categories].[Subcategory].members,
    10,
    [Measures].[Internet Sales Amount]
    )

You can then use the update button on the edit tab of the ribbon to see the results.  Unfortunately there's no way to edit the custom set once you've added it.  You have to delete the dimension members and then add a new custom set.

Win\Loss Graphs in Reporting Services 2008

As I mentioned previously, SSRS 2008 allows us to use some nice visualisation techniques in our reports.  Win\Loss graphs are used to show (wait for it......) the trend of wins and losses over time.  The usual example is the performance of a sports team during a season, but they are also useful for showing trends over time such as the performance of a KPI.  Here's another quick how-to on building this type of chart in SSRS 2008.

So first we need a dataset with three fields: Series, Time and Value.  The first two are fairly self-explanatory, for the third you need to transform your value so it appears as either a 1, -1 or 0 (win, lose, no result/tie).  The AdventureWorks built-in cube KPI's work well here so I'm going to shortcut slightly and use these.  If you're going to use a relational source you may need to do something a little cleverer.  For example:

SELECT
    [Month]
    ,[Country]
    ,CASE 
        WHEN [SalesAmount] > 10000
        THEN 1
        WHEN [SalesAmount] < 10000
        THEN -1
        WHEN [SalesAmount] IS NULL
        THEN 0
    END AS Winlose
FROM 
    [AdventureWorksDW].[dbo].[FactInternetSales]

We'll be using AdventureWorks (SSAS) "Internet Revenue" KPI.  Don't forget that when using an AS data source in SSRS you basically have to trick it into thinking it's relational and make sure you have your measures on Columns (as Chris Webb discusses here).  Here's our very simple MDX statement:

SELECT 
    KPITrend("Internet Revenue")
ON COLUMNS, 
    [Date].[Calendar].[Month].members 
    * 
    [Customer].[Customer Geography].[Country].members
ON ROWS FROM 
    [Adventure Works] 

Now lets add a chart - pick the standard column chart and format as follows:

1. Right click and delete the chart title and legend;
2. Right click on both the axis titles uncheck Show Axis Title; 
3. Right click on the Y (vertical) axis and select Axis Properties.  Under Axis Options set Minimum to -1, Maximum to 1 and Cross At property to 0 (this makes sure our baseline runs between the "wins" and "losses").  Click OK;
4. Right click again on the Y axis and uncheck Show Axis;
5. Right click on the X axis and select Axis Properties.  Under Labels  check Hide Axis Labels and do the same for Major and Minor Tick Marks. Under Line set the width to 0.5 and the colour to light grey or similar.  Click OK.

You should now have something like this:

Now lets add some data:

1. Drag your Time field from the Report Data pane into the category area;
2. Drag your Value  field into the Data Field area;
3. Right click on your series and select Series Properties.  Under Fill set the colour suitably.  In our example we're going to show wins as black and losses as red using a switch statement in an expression:

=Switch
(
    Fields!Internet_Revenue_Trend.Value = 1, "Black",
    Fields!Internet_Revenue_Trend.Value = -1, "Red"
)

If you preview the chart now it should look something like this:

To finish off we need to add the chart inline to a table:

1. Add a table and delete the third column;
2. Set the DataSetName property to the same as your chart;
3. Right click on the second row and add a group (using the adjacent above option).  Select your Series value (in our case: Country) from the drop down;
4. Delete the bottom row;
5. Drag your Series field into the First column, second cell;
6. Drag your chart into the second column, second cell.

Finally add a bit of formatting to your table and you're done!

As usual the sample report can be downloaded from the downloads section (don't forget to login).

This also works in SSRS 2005 - though doesn't render quite a cleanly.

PerformancePoint Monitoring - Hide empty rows in a grid or chart by design

So as the dust settles and everyone calms down, it's time to move on and get back to what we're good at! Just in case anyone was worried, we'll continue to be at the cutting edge of PerformancePoint Services and the MS BI stack as well as looking at SharePoint in more detail.

I thought I'd post this one as it's come up with a couple of our customers recently and isn't entirely clear at first glance.  When you design an Analytic Chart or Grid in Dashboard Designer by default empty rows/columns (or series on a chart) are shown.  If you want them to be hidden when the user first opens the chart/grid then click the browse button at the bottom right of the design pane:

The preview window will appear.  Now click either of the two end icons on the toolbar (marked by the red rectangle below) to hide the empty rows/columns (or both)

When you click OK the setting will be saved into your design - you can verify this by going into the MDX view of the query and checking that "Non Empty" now appears in your MDX. 

You can use the same method to apply sorting by right-clicking on a column in the browse window and selecting the relevant sort function.

Of course if you are using an MDX query then use the NonEmpty/Non Empty functions directly.

As I've pointed out before, don't forget the built-in Dashboard Designer help which is pretty comprehensive

How to: Bullet Charts in Reporting Services 2008

Unlike SSRS 2005 where creating bullet graph takes a bit of jiggery-pokery, Reporting Services 2008 almost has them built-in (thanks the the acquisition of Dundas technology).  I say almost as it's actually using a linear gauge object which in the wrong hands allows you to put the likes of a thermometer on your reports to show just how "hot" your business is!  Luckily, there is a built-in bullet graph template for the gauge but it still requires a bit of customisation to make it look like a bullet graph as per Stephen Few's specifications.  However it's almost all formatting/setup.  Here follows a quick(-ish) how-to.

In most cases you'll be using this type of chart inline in a table, however it's easier to develop the graph object outside of the table then drop it in once you've got the formatting right.  To start with create yourself a datasource and build a dataset that has the following fields and one row for each bullet graph you want to display:

• Category Name
• Actual Value
• Target Value
• Bad Zone Max
• Satisfactory Zone Max
• Good Zone max
• Optionally you may need a bad zone min if you have negative values

Next drop a gauge object onto your report and select the bullet graph template. 

You'll notice that the default is lots of jazzy shading and spangly borders.  We particularly need to get rid of these if we are using the graph inline as they will make the graph very hard to read when scaled down (and of course add no value in terms of visualising the key information anyway).  We'll also want to remove the scale if we are using inline - we can add it back into our column header later.

Right click on the graph and select gauge properties.  Select the Back fill section and set Gradient to solid and the colour to no colour.  Then select the Frame section and set the style to none.  Close the properties pane. Looking better already:

Next lets remove the scale - right click on the graph again, this time selecting Gauge and then Scale Properties.  Select the Labels section then check the Hide Scale Labels checkbox.  Then select the Major Checkmarks section and check the Hide Major checkmarks checkbox.  At this point also set the start and end margins to 1 in the Layout section and set the Maximum value of the scale to your good zone max field.  Close the properties panel again.  

Now lets deal with each component of the graph.  You'll notice that the orange bar is actually made up of two objects known as linear pointers.   Lets tidy them up and assign our Actual and Target values to those pointers.  First make sure you've set the relevant datasetname property for the graph.  Then right click on your graph again, gauge sub-menu and Pointer(LinearPointer1) properties.   Set the value to be your actual field from your dataset.  The go to the Pointer Fill section, set the fill style to solid and pick a suitable colour for your "bullet".  Set the Pointer width to 30 and finally select the shadow section and set the shadow offset to 0.  Do the same for your target pointer (LinearPointer2) but this time setting a width of 8 and an length of 80.

Now set the relevant properties for each of the zones.  This time select Range(LinearRange1) properties from your Gauge sub-menu.  Set the Start Range to 0 and the End range to your Bad Zone Max field.  Whilst you have the properties pane open, get rid of the range borders by setting the border style to None and the start and end widths to 60.  Repeat for range two but this time setting the Start Range to Bad Zone Max and the End Range to your Satisfactory Zone Max. The same for zone three but this time with Satisfactory Zone Max and Good Zone Max.

Now add a table to your report, set the datasetname to the same dataset as your graph.  Delete the third column and drop your Category field into the second row, first column and then add a grouping on the Category Field (right click on second row, group properties, add).  Then drop your chart into the second column, second cell.  You should have something that looks like this in design view:

Almost there.  One of the quirks of any inline chart is that it overrides the border of the cell it's placed in so you need to set the border for the GaugePanel to the same as your cell borders.  Finally lets add a scale to the header of column two.  Copy your chart from the cell and then paste it the header.  Remove all the components except the scale from the gauge panel by carefully highlighting and deleting.  Right click the Gauge in your header and select scale properties from the sub-menu.  Uncheck the Hide Scale Labels checkbox and the Hide Major checkmarks checkbox.  For the max value of the scale you need to make sure you use the max grouping for you good zone max rather than the default sum (=Max(Fields!Zone3.Value)).  In the layout section, set the position in gauge to 90, in the labels section set the placement to inside and distance from scale to 30.  Set the major tick mark placement to inside.  Phew!

Tidy up your fonts and general layout and you're good to go:

That's about it - if all that seems like a lot of work download the sample report and use that instead :)

Double MVP award for Adatis

I was struggling to write this post without too much trumpet blowing, but luckily Matt has done that for us!

I'm very pleased to announce that both Sacha and myself have both been awarded Microsoft MVP for 2009.  Whilst this is somewhat surprising it's also nice to know that someone is taking notice of the extra effort that goes into blogging, presenting, answering forum questions, developing freeware like our PPS planning web part etc.

Thanks as ever to the outstanding Adatis team who've earned this just as much as we have.

A bit of fun...

...if you're a SQL geek (like we are)

http://www.microsoft.com/sql/experience/Fun.aspx?loc=en

Happy New Year to you all :)

What's New in PerformancePoint SP2?

Correction: My badly titled post suggested this was the list of fixes rather than new features.

Unless the read-mes (Planning and Monitoring) are still being updated, there's no real surprises in terms of new features in Service Pack 2 for PerformancePoint Server.  More about the fixes in the Knowledge Base (KB958291 KB960815)

Planning

Monitoring