BI Tools: Just because you can ...

Peter Thomas, in his article Using multiple BI tools in a BI implementation – Part II, points out that the way to drive consistency across dimensions and measures is to define as much logic as possible in the data warehouse.

There was a challenge to this approach because it misses the opportunity to exploit some of the features of BI tools. This challenge, I think, invites the maxim that just because you can ... doesn't mean you should!

BI Software vendors frequently build functionality into their tools that may not always be appropriate to exploit regardless of what the marketing message implores us to do. As an example, many reporting tools are able to integrate multiple data sources in metadata but is this really the best place to integrate?

Recently, I was engaged by a public sector organisation because their BI vendor had implemented this very feature. They outsourced their Data Warehouse and were sensitive to the costs associated with Data Warehouse and ETL changes so saw this as an opportunity to implement a 'virtual' warehouse and take greater control of their reporting solution whilst reducing data warehouse maintenance costs. Sensibly, before they embarked on a significant project to adapt this approach they sought external guidance on a best practice approach. Unsurprisingly, the conclusion was that it remains best practice to transform data from operational systems into a persistent store (data warehouse) rather than a transient (virtual) equivalent because;

• Application Performance. Queries from the reporting tool will have an unpredictable and likely negative performance on operational systems.
  
• Transformation Efficiency. Transformation in a typical DW architecture is typically designed to run once, usually overnight in a controlled and predictable environment rather than many times as each report is required or run.
  
• Maintainability. Transformation is better done as a series of small, manageable and auditable steps rather than a single complex process.
  
• Best Practice. In our experience, persistent, multi-tier data architecture is the most commonly adopted approach by successful BI installations.
  

Over the last ten years, BI tools have grown up into rich, enterprise technologies. However, some boast capabilities far beyond what we, as BI practitioners, should consider using in a properly architected solution.