We’ve talked about scorecards and KPIs, now it’s time to put it all together and build a dashboard. I will be using PerformancePoint as my tool of choice because as of Office 14, it will be included with MOSS and, as I’ve mentioned before, I kinda like it! Our plan of attack will be as follows:

1. Create a datawarehouse
2. Create a data source
3. Create a Time dimension
4. Add Indicators (the pretty bits)
5. Create KPIs
6. Create a scorecard
7. Define how KPIs rollup
8. Add a report
9. Create a dashboard
10. Publish the dashboard

Sounds so simple doesn’t it? Well, I guess, if you have planned your KPIs properly as we discussed in the previous article, it really is.

1. Create a datawarehouse

Why can’t I just get it directly from my transactional system or my existing datawarehouse, you ask? Sure you can. But let’s be realistic for a second. A scorecard is made up of Actuals and Targets and it compares unrelated data. Do you see where I’m going with this? Let me speak plainly. The data is coming from different places so it will have different, keys and structures and so on. Plus somehow, it all must relate – even when it isn’t related. What I mean by that is that you need a single Time dimension and probably a single Business Hierarchy dimension. Oh and don’t forget Scenario. By all means, if all that information already resides in an existing datawarehouse go for it, but chances are it doesn’t. And we will be building a scorecard; which is all about aggregation. So what tool does aggregation the best? A cube baby!

2. Create a data source

In PerformancePoint, you can have datasources that are multidimensional, relational (ODBC) or tabular lists (Excel Services, Excel Spreadsheet, SharePoint List or SQL table).

Obviously, the most used option is Analysis Services – and so it should be. Multidimensional sources have all that handy stuff like aggregation, optimisation and encapsulated business logic. Obviously there are circumstances where other sources are necessary, such as displaying lists or real time data but generally speaking, it’s wise to take advantage of the speed of cubes wherever possible, as I so zealously advocated in the previous step.

3. Time. It’s all relative right?

Every scorecard, pretty much, needs a time dimension. One of the joys of PerformancePoint is that a lot of the hard work is baked in for you. Bless their cotton socks! You’ve got a Time dimension in your cube (and it contains an appropriately structured hierarchy right?) so PerformancePoint just needs to know what it’s called and, as at today, which member represents “Now”. I don’t mean you need a “Current” member, I mean literally, today is 1^st July so select the member for 1^st July. Oh yeah, and it’s a “Day” (as opposed to say, a month).

* ok yeah, this shows 2002 but that’s because my sample database doesn’t contain 2009 J So you’ll just have to imagine. You can do that right?

While you’re at it, tell me which level of the hierarchy refers to a year, which a month and so on.

PerformancePoint will work out “CurrentPeriod” from here on in. Handy!

4. Indicators. The Pretty Bits

Dashboards are ALL about the pretty. If you think otherwise, well, you’re kidding your-table-loving-self. Dashboards aren’t for you. They’re for marketing people, business analysts, general managers and business development managers et al… you know the types… they just want to see things at a glance and they’ll gush and kiss you inappropriately if it looks real purdy. Lucky you. I’m generalising sure – I’ve created dashboards for financey people and other more-data-centric types but the majority of the audience is not. If they were, they’d be using ProClarity or Excel to do analysis not a dashboard. I will say though, the finance people claim to be data-oriented but show them a pretty graph (which links out to some data) and they’re putty. FTW!

Indicators are your stoplights, and smiley faces (why? Why? I ask you!) and gauges. The heart and soul of the scorecard. It’s what people look at before they look at anything else. PerformancePoint comes with a tonne of them.

The screen shot above shows the categories you have to choose from – and each category contains umpteen indicators. Plus you can add your own custom ones. Nice.

5. Create KPIs – both Actuals and Targets

As discussed in the previous article in this series, planning your KPIs is critical. There are a few planning tips I would suggest:

• For your initial deployment, limit the number of KPIs to no more than 10
• Automate as much as is humanly possible; including Targets
• Don’t try to be too clever – measure things that are easily understood by the reader
• Don’t forget Targets under the weight of ETL’ing your Actuals – it ain’t a KPI without a Target and Targets are not as easy to design
• Make sure that the readers have agreed with each other on what the measurement or business rule is to be

So you have a list of KPIs, you’ve loaded the Actuals and Targets into your datasource and now we need to create them in Dashboard Designer. It’s as simple as hitting New KPI. The designer presumes you want an Actual and a Target and it will add your previously select set of indicators to the Target.

See how under Data Mappings, it says Fixed Values. That’s what it defaults to. A fixed value. Unlikely that you want to use a fixed value and luckily it’s as easily as clicking on it to map it to your data source. You then hit change source, select your cube and map it to a measure enter some MDX if you prefer – particularly handy if you were too lazy to get your Target values into your datawarehouse and you just want to calculate them on the fly.

You can also specify how you want the KPI to be displayed on the scorecard using the Number Format column eg with commas, to 2 decimal places etc or you can let PerformancePoint work it out based on the format of your measure in the cube.

Thresholds is where we define what’s good, what’s bad, what’s ok and what’s a disaster!

Calculation is where we define how our KPIs aggregate. Sum is the default.

We probably also want to group our KPIs into objectives such as Financial, People, Environment etc so we will need to create those as well. It’s the same process as creating a KPI.

When creating a KPI or objective, on the first page of the wizard, you’ll also notice a little check box “Grant Read to All Authenticated Users”. KPIs can be used by any number of scorecards or dashboards. That’s part of the beauty of them. Say you’re developing a dashboard for a particular department and the finance team already has a dashboard which is a global view, you can re-use their KPIs and just filter it to your particular team or department on your dashboard. If however, the data in the KPI is sensitive you may never want anyone else to see it so in that case, you would untick the option. Remember that PerformancePoint is intended for development by business analysts or other technically minded super-users so the idea is that everything can be shared and re-used as needed.

6. Create a scorecard

Now we have the core elements for our scorecard so we can build it. Like everything in PerformancePoint, right-click and Add New will do the trick. There are a few templates available to you, which just sets it up with a default layout and data source mapping. The options available are:

• Microsoft Analysis Services
• Standard
  • Blank
  • Fixed Values
    Handy for creating mockups for showing customers, managers etc
• Tabular
  • Excel 2007
  • Excel Services
  • SharePoint List
  • SQL Tabular

The option you pick really comes down to what type of data source you created way back in the beginning. Mostly, I find myself choosing Blank just because it’s easier to fiddle around with exactly what I want. A browser appears on the right hand side, which shows all the objects that are available to the scorecard and you can drag & drop them onto the scorecard. With KPIs, guides appear showing you where to drop the object in order to make it a parent, a child or a sibling. KPIs can be children of other KPIs or of Objectives or they can sit by themselves – it’s up to you.

You can also add dimensions to the scorecard (presuming they exist in your cube of course)!

7. Define how KPIs Rollup

Once our scorecard has been created, we need to decide how our KPIs aggregate. There are 3 types of rollup:

• Average weighted rollup
  Displays an indicator that reflects a calculated score, combined with the weighting that is assigned to each child.
• Indicator count Rollup
  Displays a count of the indicator images that are used in individual KPIs;, or child KPIs of each objective KPI eg
• Worst child rollup
  Displays an indicator that reflects the child KPI that has the worst score.

8. Create a Report

Dashboards are more than just scorecards. We can also add reports of varying types.

In most examples the report will need to already exist somewhere eg on our SSRS portal or in an Excel Services document library in SharePoint however Analytic Chart and Analytic Graph can be created wholly within PerformancePoint. A simple drag & drop interface is used to build these on-the-fly reports.

Note:  I will be discussing reports in much greater detail in the next article in the series.

9. Create a dashboard

We have reports, we have a scorecard so now we can build a dashboard. PerformancePoint gives us a few default layouts to start with.

Most often, I find the Header, 2 Columns is the one I go for so I can stick my Page filters in the top row, a scorecard on the left and a report on the right. Once the empty dashboard is created, the browser pane on the left will show you a list of objects that can be dragged & dropped onto the drop zones.

Drag the scorecard to the left column, the analytic report to the right zone and oh, now we need some Page Filters.

Time is the most obvious one, so start there. Time Intelligence will respect and understand your “CurrentPeriod” selections made earlier. You can add more filters (from other dimensions) using Member Selection or MDX query etc. These Filters will now appear in the object browser pane for you to drop onto the dashboard. In the example given, they’d go into the Header zone.

Of course it doesn’t end there, somehow the Page Filters have to be linked to the scorecard and the report in the other zones. Simply drag the filter onto the “Drop Fields to Create Links” drop zone within each object that you want to link.

Each object has an “endpoint”. A popup will appear that allows you to define how the filter and the object should be mapped to each other. It could be from a unique member or it could be a formula – this would be mapped to the endpoint for the object you dragged it to.

10. Publish the Dashboard

Yep that’s actually it. Dashboard done. From here it gets published to a SharePoint document library. You will be prompted to enter the SharePoint URL and select the appropriate library. What does that mean? Well, you need the library to exist, you need write access to it and your SharePoint installation needs the PerformancePoint web parts installed.

That dear friends, is the whole shebang!

	Part 1 – Getting to Know You – Intro to SharePoint BI
	Part 2 – Sell! Sell! Sell! Why Build a Dashboard Anyway?
	Part 3 – KPIs – Where do I Come From?
	Part 4 – Dissecting Dashboards
	Part 5 – We Really Need to Talk – Scorecards & Reports
	Part 6 – Beware the Bogeyman – Securing Dashboards

Siyonara amigos! Till next time.

Kristen Hodges, MCTS, MCITP
http://www.bi-tch.blogspot.com

http://b-iq.org

References

http://office.microsoft.com/en-us/performancepoint/HA102711891033.aspx