Sunday, February 8, 2009

Why Use Reporting Repositories for Business Intelligence?

I regularly review web statistics with interest to see how Google searches route people to my blog. One person from Indiana came looking for reasons why you should use a data mart. The searcher was probably disappointed since the answer was not really there as advertised. I feel bad, so let me fix that and add some content -- perhaps he or she will return.

Rather than use industry business intelligence terms pregnant with political meaning like Data Warehousing and Data Marts, I am using "Reporting Repository," which I hope is free from bias.

While there are proponents for doing reporting directly against the operational systems' data (see another blog for some information), it is generally speaking just not a good idea. The main argument is that operational data has live, real-time information. But there is a long list of disadvantages.

Instead of using operational data for reporting purposes, you should provide your end users with a centralized, shared copy created especially for BI: a Reporting Repository.

With this term, I mean that the operational and external data feeds have been restructured and stored in a way to make end-user reporting as simple and easy as possible. In addition to changing the data structure, the organization has added an abstract layer (master data management, hierarchies, and so forth) as well as a metadata layer.

A team of technical specialists must bear the burden of understanding the complex data one time while creating the reporting repository and the necessary integration processes. Otherwise, the end users are yoked everyday with the burden of trying to write reports against the complex operational data (which is not meant for reporting).

Here are some reasons you should not use operational data for your end-user reporting:
  • Because of critical nature, operational system must be isolated and protected
  • Data is structured for ease of data maintenance, not reporting
  • Since not designed for end-user reporting, there is rarely documentation for doing that
  • Typically, operational system does not physically store all data (uses business rules inside application code)
  • Typically, operational system does not store historical data (only a snapshot of current situation)
  • Typically, operational system does not have an abstract layer for end-user reporting
  • Typically, operational system does not have a metadata layer for end-user reporting
  • Typically, operational data not accessible by common BI tools

Reporting Repositories provide a corresponding solution to each of those operational data problems:
  • Intentionally designed for access with considerations for security, performance, etc.
  • Data is structured for ease of reporting
  • Intentionally designed and documented for end-user user
  • Either physically stores calculated columns or exposes virtual columns
  • Captures historical information for auditing, comparisons, trending
  • Provides abstract layer to provide context for understanding information
  • Provides metadata layer to provide explanations for accessing and using information
  • Designed to be accessed by BI tools

But what are you going to put in your Reporting Repository? Well, you will figure out the design and content based on the needs of your business decision makers. If you want to build an enterprise repository to serve any and all reporting purposes, you are looking at a Data Warehouse (Bill Inmon approach). If you go with a very specific repository for a unique purpose, you are considering the Data Mart (Ralph Kimball).

Here are the steps for designing your Reporting Repository:
  • Your decision maker needs to take important business action
  • Which requires specific information.
  • Decision maker also needs specific method of interacting with the information (information delivery or interactive, on-demand access)
  • Which determines the proper storage of information in the repository
  • Which determines the requirements for integration with operational systems
  • Which determines the requirements for capturing and storing data within the operational systems

If you came here through Google looking for answers to your BI questions, I hope this helped. If you have any questions, please contact me.

1 comment:

wietse.bakker said...

Hi Doug,
thanks for this 'fresh thinking' in the DWH and BI space! I support your note, and from my experience of implementing BI solutions, I think there are some other important aspects when "designing your Reporting Repository":

In most Operational BI cases, the 'important business action' is related to the business processes. A business action might be to increase the capacity (resources) of a specific step in a business process. The 'decision maker' is generally a owner (complete or partly) of a business process. I would suggest to add one step where you aim to understand the business processes and related SLAs / KPIs. If you understand the business process, you will be able to move from the 'main stream work' report building (and reporting repository) to the more added value Business Process Intelligence /Business Process Improvement type of consultancy.

Next, after design you will need to build and deploy your solution. Recently I started an engagement with Altosoft as a tooling to do exactly this. This tool automatically generates, maintains and populates a reporting repository (Altosoft's term is MetricsMart), which can be used by BI tools or by the web dashboard tool from Altosoft itself. This tool is really unique because it uses concepts such as (Complex) Event Processing, Intelligent Data Aggregation, Process State Awareness, Right-time streaming analytics and incident management. But for my case, the ease of mapping and extracting data from multiple source systems was crucial for success. The conclusion of my engagement is that it can dramatically ease and accelerate the design, build and deploy phases.

Feel free to contact me if you need more information; wbakker [at]

About Me

My photo

I am a project-based software consultant, specializing in automating transitions from legacy reporting applications into modern BI/Analytics to leverage Social, Cloud, Mobile, Big Data, Visualizations, and Predictive Analytics using Information Builders' WebFOCUS. Based on scores of successful engagements, I have assembled proven Best Practice methodologies, software tools, and templates.

I have been blessed to work with innovators from firms such as: Ford, FedEx, Procter & Gamble, Nationwide, The Wendy's Company, The Kroger Co., JPMorgan Chase, MasterCard, Bank of America Merrill Lynch, Siemens, American Express, and others.

I was educated at Valparaiso University and the University of Cincinnati, where I graduated summa cum laude. In 1990, I joined Information Builders and for over a dozen years served in regional pre- and post-sales technical leadership roles. Also, for several years I led the US technical services teams within Cincom Systems' ERP software product group and the Midwest custom software services arm of Xerox.

Since 2007, I have provided enterprise BI services such as: strategic advice; architecture, design, and software application development of intelligence systems (interactive dashboards and mobile); data warehousing; and automated modernization of legacy reporting. My experience with BI products include WebFOCUS (vendor certified expert), R, SAP Business Objects (WebI, Crystal Reports), Tableau, and others.