Tim Kent's blog

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

An Old New Blog

HowTo: Bullet Charts in Reporting Services 2005

In my last post, I showed a dashboard in SSRS 2005 that included sparklines, inline bar charts and bullet charts.  As I mentioned there is good MSDN article on how to do inline charts but the Bullet chart took a little more thought.

As one of my colleagues has pointed out, these aren't bullet charts in the truest sense that Stephen Few designed them as they don't show the "bullet's path" running through them, however I think they serve the same purpose albeit in a simplistic way

The charts are made using 100% stacked bars with three data values for each "zone" of your chart:

• The section below your marker;
• The marker itself (which of course only shows if it falls in that zone); and
• The section above your marker.

So in the Speciality Bike Shop row in the example above, the three values that add up to the first zone of 25 are 11, 1 and 13 with the colour set as Dark Grey, Black and Dark Grey respectively.  Whereas as Value Added Reseller is made up of three zones with values 0, 0 and 25

Likewise, the 2nd zone for Speciality Bike Shop is made up of 0, 0 and 64 whereas VAR is 42, 1 and 22 this time with light grey, black, light grey

So how do we come with the numbers? It's just a case of calculating the size of the zones as a percentage of the whole and deciding whether the marker should show.  Lets follow the MDX query through to explain

First lets define our key measures: the zone limits, and the actual value for our marker.  I'm using constants for the zone sizes but you could easily have this coming from your data:

WITH 
//replace this constant with your Zone 1 measure    
MEMBER [Measures].[Zone1] AS
     300000

//replace this constant with your Upper Zone 2 measure
MEMBER [Measures].[Zone2] AS
     1067000

//replace this constant with your Upper Zone 3 measure
MEMBER [Measures].[Zone3] AS
     1200000
    
//Actual measure
MEMBER [Measures].[Actual] AS
    [Measures].[Reseller Sales Amount]

Next we need to calculate which zone the marker will fall in - we set a boolean for each which is also used as the marker for the chart.

//Are we in the zone 1
MEMBER [Measures].[ShowZone1Marker] AS
    IIF(
        [Measures].[Actual] < [Measures].[Zone1],
        1,
        0
    )
//Are we in Zone 2
MEMBER [Measures].[ShowZone2Marker] AS
    IIF(
        ([Measures].[Actual] >= [Measures].[Zone1]
        AND
        [Measures].[Actual] < [Measures].[Zone2]),
        1,
        0
    )

//Are we in Zone 3
MEMBER [Measures].[ShowZone3Marker] AS
    IIF(
        [Measures].[Actual] >= [Measures].[Zone2],
        1,
        0
    )

Then we need to calculate the size of the lower and upper parts of each zone, lower first: 

MEMBER [Measures].[Zone1a] AS
    (([Measures].[Actual]) * [Measures].[ShowZone1Marker]
    /
    [Measures].[Zone3]
    * 
    100)
    - 
    ([Measures].[ShowZone1Marker] * [Measures].[ShowZone1Marker])

the logic is:

The actual value subtract the previous zone limit (not required in zone 1),  multiplied by the boolean value of whether or not the marker falls in this zone as a percentage of the whole chart.  The bottom line removes one from the value to allow for the marker.

The upper part of zone 1:

MEMBER [Measures].[Zone1b] AS
    ([Measures].[Zone1] - ([Measures].[Actual] * [Measures].[ShowZone1Marker]))
    /
    [Measures].[Zone3]
    * 
    100

this time the logic is:

the zone size subtract the actual value multiplied by the boolean value of whether or not the marker falls in this zone as a percentage of the whole chart.

Repeat the same for subsequent zones and then bring it all together in a select statement on your cube

Just in case I've explained all this as badly as I think I have, you can download the sample project and have a look yourselves (you'll need to sign in/up).

And the scale - errr OK in case you hadn't already guessed, it's an image on the column header - hey what more do you want?the moon on a stick? ;) You can size the chart to fit the scale or create your own image.  The image is also included in the sample project.

All criticism/suggestion for improvement welcome.

Bullet Charts and Sparklines in Reporting Services 2005

Here at Adatis we're big fans of Reporting Services.  Yep, it has it's quirks and frustrations (don't talk to me about excel export!) but doesn't deserve all the criticism it gets in our opinion.  You can create some great looking reports with not a lot of extra effort over the defaults (though how many times have you seen a "slate" style report called "Report1" go into production!)

We're also fans of what some might call the more "trendy" visualisation techniques such as sparklines and bullet charts and with Microsoft acquiring Dundas technology for use in SQL 2008, SSRS has some much improved functionality in this area.  I'll be taking a look at some of these new features in a future post.

Whilst SSRS 2005 does have it's limitations, with a little effort you can still create some very effective information visualisations.  The dashboard below is built using completely standard functionality

Adding sparklines and in-table bar charts is very simple (and pretty clever IMO).  This MSDN Article covers how to do it in detail but in essence you simply add a line or bar chart into a cell in your data table.

Whilst the bullet chart (in the Sales Channel Performance region of the dashboard) may be simplistic compared to those available in SSRS 2008 or MicroCharts from XLCubed (from whom I nicked the styling for our dashboard) it still provides a very clear visualisation of the data.  This required a little more trickery in the underlying query but is also just a chart in a table cell - this time a 100% stacked bar.  I've explained how this works here

BI conference- Day 3

So after a slight delay, here's a quick note on Day 3 and the conference in general.

We only caught the second half of the Keynote from Kurt DelBene of the Office group - investing in Excel - making it more rich - and what a great reporting tool Access is? errr Ok.  You can imagine the fun and games that goes on between the Office and SQL teams, with the PPS team stuck somewhere in the middle :)  More ridiculous staged customer Q&A style discussion followed about how great Office is - which saw a good chunk of the audience leaving again - seriously guys!

The first session of the day - Avoiding Common Mistakes with Analysis Services - nothing ground breaking here but the session was really well presented by Craig Utley, who in particular explained the concept of attribute relationships really well.  Plus a reminder of the extremely useful ignoreunrelateddimension property which stops users from seeing data where they've  selected a dimension that doesn't link to the measure group (think AdventureWorks, Reseller Sales with the customer dimension).

I ducked out of the last session to take a look round the shop - I nearly got myself a Microsoft embroidered denim shirt but I knew how envious the guys back at the Adatis office would be and they didn't have enough for the whole company.  I settled for two Microsoft golf shoe bags - Martyn and Sacha are gonna be so pleased on their birthdays!

So highlights for me:

• Getting to meet some very cool people, particularly some of the PPS product team who took time out of their schedule to chat to us and were genuinely interested in what we had to say.
• The BI power hour was great - some genius use of PPS as I reported in Day 2 - I hope we can get our hands on the "Perfoply" and "Girlfriend Management" Planning models.  They could be deal clinchers with certain potential customers just to show how varied the possible uses of PPS Planning are.

Lowlights:

• Some very average sessions (mixed in with some very good ones mind).  The chalk talks were particularly bad logistically; over-subscribed, overcrowded and poor AV;
• As reported by Dan English and others there was a serious lack of any PPS v2 (or even SP2) information - you'd think with Gemini being announced they'd want to at least hint about what was coming (particularly from a M&A perspective).  However, the "Upgrade to PerformancePoint" button in Gemini might suggest it's going to play a good part.  I have a feeling M&A V2 will be a huge step up.

Generally pretty good - I'll be back next year for sure.  But for now time to get back to the important business of day 5...

BI Conference Day 4 ;)

Day 3 review coming soon - unfortunately I'm busy right now. He he

BI Conference - Day 2

First of all a quick correction following my post on day one where I had a bit of code name confusion.  Project "Madison" is the evolution of the DatAllegro acquisition, Project "Gemini" is the self service BI and "Kilimanjaro" is the code name for the (interim?) release in the first half of 2010 that will include "Madison" and "Gemini"

So what of day two? The Ben Stein keynote was interesting, though not a patch on the Michael Treacy's  from last year.  This was followed by an extremely cheesy and completely staged "Q&A" session with the platinum sponsors about where BI will be in 2020.  I didn't stay to hear the answers! I know they have to keep these sponsors happy but do MS really think people take any notice of this stuff?

I went for the MDM session next which was very heavy going and disappointing in that we still didn't get to see the product - though we may see it at next year's BI conference (same time next year)!

Got to meet Patrick Husting and a number of other PPS experts over lunch, and in the afternoon we got a chance to quiz some of the Gemini team on a few of the points mentioned yesterday.  I raised the topic of "AS hell" where users are creating random cubes all over the place and they had a good response: People are always going to do self service reporting in some way whether we like it or not, no matter how good the underlying data is, so why not do it in a controlled manner where everything is audited and logged and the IT team has full visibility of what is going.  There was a "Gemini" breakout session which showed the operations dashboard behind the scenes - very cool.  "Gemini" really looks impressive and has obviously already had a lot of effort put in, I have a feeling the usual version 1 worries may not be surfacing too much here - people are going to be desperate to get their hands on it!

Last session of the day for me was the  BI power hour which was very entertaining. A working Monopoly game in PPS with full analytics and Profit and Loss for each player, a girlfriend management Planning model (looking at how the seriousness of your relationship affects your future cashflow - ha ha) and two player battleships in Reporting Services - I want these guy's jobs! Also  a chance too briefly see MDM in action - it looks pretty good! Why they couldn't show it in the MDM session I don't understand...

Finally the conference party which was held at the the impressive QWest stadium, home of the Seattle Seahawks, with casino games, lots of x-box and Football and American Football out on the pitch.

Here's Jereminho scoring an absolute peach - back of the net!

Day 1 at the BI conference

So here we are in Seattle at the BI conference.  Day 1 and it's been great to catch up with some old faces and meet some new ones.  We were promised some big news today and as is being reported by Chris, Mosha, Marco and all, we weren't disappointed.  Both "Kilimanjaro" and "Gemini" look super-exciting for all of us in the MS BI world.  The former is the evolution of the DatAllegro acquisition  - we saw an SSRS report that ran in 10-15 seconds against (hold little finger to corner of mouth) one trillion relational records in a 150 terabyte database - impressive. 

Equally impressive is 20 million rows sorting and filtering in the blink of an eye!  The Gemini project (self-service BI) had two key features for me;  Firstly the in-memory storage (think TM1) that allows that sort of performance and secondly, and something that hasn't been widely commented on so far, the ability to publish your Excel reports to SharePoint/web (as XLCubed Web does now) at the click of a button.  The interface looked really good already and of course it's generating AS cubes behind the scenes.  It did raise the question of how it's all going to fit in with PPS V2 - hopefully the Office and SQL teams have been talking!

I think that a lot of BI professionals were probably initially thinking (like me) that this could be bad news for their careers but having taken it all in, I don't think that's the case.   Although it's going to have data cleansing abilities, this isn't going to be a replacement for data warehouses/marts.  In fact for the whole self-service BI thing to really take off, it's going to need really good data underneath it.   Microsoft's intention with the Gemini release is to "democratise" BI; The more people who get to use this type of concept, the better the data quality will have to be - you can't expect every department to have to clean their own data.

Allowing users to create and publish their own cubes and reports has a few warning signs as well - isn't this just excel hell without excel? Every department can create their own calculations, reports, cubes etc; We've been telling our clients for years that they really need one version of the truth - but now we're going to let each user make his own truth? It will certainly need some thought.

As Chris W mentions, it's likely to also still need technical resource to help users create complex calculations so we won't be out of a job just yet ;)

As for the rest of the day, some interesting sessions around PPS and SSRS were the order of the day for me.  Though disappointment in one respect as I found out that the PPS monitoring SDK would definitely not allow you to build a custom cascading filter - something that I was going to investigate.

More later this week....