Rebranding

Hi, I noticed that I hadn't been on the blog for a while. Just to let you know that we are going through a rebranding exercise at the moment, so look out for the new colours soon!

KPI Industry Specific

Increasingly we are being asked to help out and suggest KPI's for specific industries. We are building up a short list of what we think people should be measuring at the very least in various industries and they will be available on our website shortly http://www.consolidata.co.uk

OLAP KPIs

Is SQL Server 2005's Analysis Services a good tool to use for reporting and KPI reporting in particular? In short - yes, but it does require a fair amount of skills to achieve this. I have found that you really need to understand how the cubes work properly before attempting to add in the more exotic KPI measurements.

Where the cubes really come into their own (as opposed to stored procedures running of relational tables) is in the MDX scripted calculations, in particular I have found the 'periods to date' functions extremely useful. Previously we would have had to precalculate these measures, or in some cases do it on the run, through a SP with mountains of T-SQL code. Now we simply have the short MDX expression on the cube - even better is that it allows any period calculation ( Week to date, Month to date, quatrer to date etc) to be implemented instantaneously, and even better vs the same period last year.

The KPIs added to the cube are just one step further (another tab in the Visual Studio Designer). So now implementing and reporting on these measures is so much quicker and much more accurate than it ever was before. The big challenge is always (and should always be), exactly what does the business need to measure?

Current Technologies

There are a number of database providers out there but the one I will concentrate on in this blog is Microsoft SQL Server 2005. Why? You may ask. No we are not MS Gold partners but in this product we have a full end to end Business Intelligence (BI) solution - specifically aimed at the middle market.

We have the ETL technology - SSIS (SQL Server Integration Services), which is a massive leap forward from the old DTS. We have the OLAP analysis layer - SSAS (SQL Server Analysis Services), which provides the multidimensionality required in modern analysis and we have SSRS (SQL Server Reporting Services) which provides the report delivery mechanism. In my opinion the last is the one that needs updating the most. Of course, on top of this we have the actual SQL Server database engine itself which rivals the likes of Oracle and the whole package together becomes massively cost effective as full BI product.

How does this fit in with KPI discussions? Well, to be able to deliver accurate measurements you need a trustworthy datasource in the form of a datawarehouse. Both SSIS and SSAS provide the mechanism to get us there and in the next article I will discuss SSAS in a bit more detail, specifically referring to how we use it for KPI measurements.

Can't See the Wood for the Trees

Many companies we have dealt with decide to implement a full MIS solution after many years of neglect, however, where on earth do they start? After all there are loads of technologies out there with snazzy front-ends, dashboards, scorecards - you name it.

To me the critical thing is designing the data warehouse correctly, in a well designed star or snowflake schema. So very often these projects are split onto phases whereby the datawarehouse is designed and built first. Some power users may even be given read access to this database to implement their own queries - this needs to be controlled carefully.

The next phase would take into consideration how the data should be presented, what reports should be deliverded and how, and also what KPIs will be displayed and to whom. My next blog will discuss current technologies and best practice for presenting an MIS solution.

What companies want to know

One of the most common things that we get asked when we visit a potential client for the first time is - What KPIs should we be measuring? Initially this surprised me as you would think that they should know better than anyone else how their business operates and what needed to be looked at.

Now I understand that fast growing companies have often ignored their reporting requirements and have been successful almost by instinct. There comes a critical point, however, where accurate performance measurements need to be available and reported on, normally once the business has reached some sort of natural plateau in sales.

Another common reason to report on how well the business is doing is that it can be a requirement from a company that is proposing a take-over or buy-out. They will need a guarantee that reports will be delivered regularly, especially at a high level, showing how the key areas of the business are performing against targets and dare I say it, promises.

Initial Blog

This is an initial test blog to see that everything is working fine. I hope that my readers and contributors will enjoy the discussions on Key Performance Indicators (KPI) and other interesting business measurements.