jump to navigation

DIY BI Design Best Practice April 23, 2007

Posted by Cyril Brookes in BI Requirements Definition, General, Issues in building BI reporting systems.
add a comment

Backing up my conviction that DIY business intelligence is going mainstream, I’ve put together a set of good practice guidelines that might, with profit, be followed by the responsible BI Rogue. Will these renegades with spreadsheet in hand, data warehouse on tap and a vague specification in mind have regard for guidelines? Only time will tell, but we won’t have to wait long, the Mongolian hordes are at the PerformancePoint gate.

Many of these points are covered already in this blog, but Dear Reader, let’s face it; a man only gets a few good ideas in a lifetime, so one must expect some repetition!

Check #1: Existence

Does another existing report or spreadsheet cover the perceived requirements, fully or partially?

A no-brainer, but has to be asked

Check #2: Compliance

Will reporting these data and information complicate the Corporate regulatory situation in respect of SOX and similar? Are there security issues relating to the data to be purloined, massaged and disseminated?

This is probably best ignored by the average DIY BI Rogue, except in a bank or some such place where spooks abide. Worry about it when a result is to hand?

Check #3: Iterations

Irrespective of your confidence in your spreadsheet skills and all other aspects of this BI project, be assured that it will require several iterations of specification, build and test before the result is deemed adequate, or other issues supersede the whole episode.

Plan on starting simple; and increase complexity and report niceties in subsequent iterations.

Check #4: Specifications

This is where it is all at. Do this right and it will be fine, ignore it and a mess will result. Make sure you have a specification for each iteration. A whole treatise can be written here, but see for example, Dear Reader, if you want detail look here.

It is self-evident to say that you need to know what information is to be provided, the data required to obtain the information and the transformations needed to convert data to information. Don’t start without at least this. See Check #6 for suggestions on presentation, but they can be later iterations – get the data and basic transformation going first.

Check #5: Know your data

Knowing your data implies – metadata, lineage, update schedules, dimensions, planned amendments. My tool to do this is described here.

Just because a cube has the data you want today, doesn’t mean it will be there tomorrow, or that the update schedule is right for your specification. Don’t waste a lot of time on MDX expressions that will only work on Thursdays to Mondays, because that’s when the update cycle is complete.

Check #6: Presenting results to aid assessment

Part of the specification task, but best left to later iterations, is the design of result presentation. I don’t mean graph versus table versus bar charts, this is relatively trivial. What is important is the way the raw information obtained from the data transformations is pre-analyzed to aid the assessment of implications. This is the point where the amateur and professional, or competent, DIY Rogues part company. Chalk and cheese has nothing on this differentiator.

Again there’s a treatise here, but basically the conscientious DIY BI Rogue should be aware that he/she can offer at a minimum:

Goal Variances (exception reporting if you will);

Benchmark Comparisons (actual versus budget, plan or anything reasonable);

Trend Analysis;

Forecasts (based on time series of the data, if it’s available of course)

Drilldown (more detail about a context, provided the narrower dimensions are in the data cube)

Check #7: Validation

Even DIY Rogues should be aware that the non-numeric data associated with supposedly factual data is important. By this I mean the comments, previous assessments, opinions, suggestions, etc. that relate to this sales or gross margin figure. My more complete and earlier exposition is here.

At a minimum, the subject expert who can offer clarification and amplify context for a number should be identified as part of the reporting. Links to team comments, forecasts, etc are probably beyond the scope of your average DIY BI project, but keep them in mind for later iterations.

See, it’s not that hard!