Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

Business Intelligence for Small Business – Analysis

If you have been following this blog over the last few days, then we are at the stage we  know what we want to measure to get the pulse of our business and we have all of that on a scorecard. However just because we are measuring performance doesn’t mean our work is done.

What do we do when we notice that we are not hitting our numbers? I would submit that the first step is to understand what is going on behind those numbers.  This is where analysis comes in.  The key technical tool for analysis of large numbers of transactions events etc. is called OLAP (on Line Analytical Processing), which tells you precisely nothing about what it is. I prefer to cal the thing I am about to tell you about FASMI :

  • Fast – 90% of queries back in under 10 secs and no query takes longer than 30 secs.
  • Analysis – Drill down, multiple aggregation techniques, sophisticated graphics, trends all form part of this
  • Shareable -  good security at the back end and available to a wide community of users.also multi currency, multi lingual to cope with the global economy.
  • Multi-Dimensional – Excel pivot tables but more so. The ability to have any multiple dimensions of information on each axis of a cross-tab with other dimensions being used to further filter the results returned.
  • Information – Real world KPI’s rather than raw numbers.

    For the business users out there think Excel pivot tables on steroids and speed. The key difference between what I am talking about and Excel pivot tables is that the data doesn’t live in the spreadsheet, it’s hidden away in a special type of database known (the OLAP engine). Since I am a Microsoft evangelist I would add that the Microsoft tool is called analysis services which comes with Standard and Enterprise editions of SQL Server, and that there is built in support to connect to Analysis Services in Excel.

    Anyway how does this help and how can you use it?

  • If you have used pivot tables you will have seen how you can choose what appears on each axis so you can have products on rows and then time on columns, and a given metric as the numbers in the grid.  OLAP adds to this by:

    • Giving you lots of things you can quickly choose to put on rows and columns
    • Enable you to drill to details by navigating a hierarchy like the way you group products into categories and sub categories or time (Year-> quarter-> week –> day), which you define.
    • Lots of metrics (the ones you decided you needed) to put on the grid. 
    • Move all of these around to get the analysis you want
    • Use any of them as a a filter for example limit time to just the current financial year, (this is known as slicing and dicing).
    • Format and produce charts from this data using excels built in features.

    The only downside is that to do the back-end technical work to deliver this is a skilled job and you are going to need external help to get started.  I would expect this to take 10-20 days for a small business depending on the complexity of the implementation and the quality of your data.  That could be a significant cost for a small business, but as I have mentioned in this series of articles before needs to be justified in terms of potential savings arising from the use of this tool.