When organizations discover the data warehouse, in many cases they think they have found the answer to their prayers when it comes to getting at corporate information. A first reaction of many organizations discovering the data warehouse is the thought that any and all reporting should be done from the data warehouse.
It is normal for there to be some amount of euphoria upon being emancipated by the data warehouse. But lost in this initial blast of glory is a fact that organizations learn the hard way: there are different kinds of reports and as useful as the data warehouse is not all types of reporting should come from the data warehouse.
Operational reporting is reporting about details and reflects up-to-the-second information. Operational reporting is typically used by the front-line operations personnel. Very short-term, detailed decisions are made from operational reports. Informational reporting is much more strategic in nature, looking at summarized data and long time horizons. Informational reporting serves the management and analytical community. Long-term and strategic decisions come from informational reports. Informational reporting should come from the data warehouse, but operational reporting should not come from the data warehouse.
What is the difference between operational reporting and informational reporting? Operational reporting is designed to support the detailed day-to-day activities of the corporation at the transaction level. In operational reporting, detail is much more important than summary. In fact, in operational reporting summary information is often irrelevant. Examples of operational reporting include bank teller end-of-day window balancing reports, daily account audits and adjustments, daily production records, flight-by-flight traveler logs and transaction logs.
These reports are designed to contain details that are essential to the day-to-day, up-to-the-second running of the corporation. The kinds of decisions made from these reports are very detailed, immediate decisions made at the line-manager level.
Now consider informational reporting. Informational reports are used strategically as opposed to operational reports that are used tactically by the corporation. Middle management uses informational reports in order to make longer-term decisions. In an informational report, the details are almost irrelevant, but the summarizations are everything. Examples of informational reporting include monthly sales trends, annual revenue, regional sales by product line for the quarter, industry production figures for the year, number of employees by quarter and weekly shipping costs by carrier.
The decisions that these kinds of reports support are longer term and tend to be strategic. As the data warehouse architect plans the environment, the architect should be aware of the important differences in these reports.
There is an interesting question that follows from the observation that operational reporting is very different from informational reporting. What happens to the data warehouse environment if you try to force all reports out of the data warehouse? Some consequences are:
- The data warehouse is forced to be designed to the lowest level of granularity of the corporation. This may or may not be an acceptable design constraint.
- Update will need to be done in the data warehouse. While this can usually be done by the DBMS that manages the data warehouse, this in many ways runs contrary to the way that data warehouses operate.
- The timing of adjustments and transactions being entered and reflected inside the data warehouse becomes an issue, usually to the detriment of the other operations occurring inside the data warehouse.
In short, the constraints placed on the data warehouse are not positive when operational reporting is done on the data warehouse.
A related issue is whether OLAP and multidimensional technology can be used on top of operational systems. Interestingly, you can force OLAP technology to sit on top of operational systems. However, the reporting done by the OLAP environment is circumspect because the operational data supporting OLAP processing is constantly changing. Building OLAP on top of operational systems is like having a picnic using an anthill for a table. It can be done, but it is not comfortable. If the OLAP analyst runs a report at one moment in time, the analyst can never revisit the report in an iterative manner because the data beneath the report will have changed (or at least might have changed). When the OLAP environment is placed over the data warehouse environment, the data in the warehouse can be frozen; and the OLAP analyst can indeed return to a previous point in time. That is one of the reasons why the data warehouse environment makes such an ideal foundation for OLAP processing.
Along the same lines, exploration processing is not done optimally when its foundation is the operational environment. Some reasons include:
- The lengthy exploration queries are disruptive of the processing in the operational environment.
- There is no accommodation for the "convenience" fields needed for exploration processing.
- Exploration processing often entails much more historical data than is ever found in the operational environment.
Bill Inmon is universally recognized as the father of the data warehouse. He has more than 35 years of database technology management experience and data warehouse design expertise. His books have been translated into nine languages. He is known globally for his seminars on developing data warehouses and has been a keynote speaker for many major computing associations. For more information, visit www.inmongif.com and www.inmoncif.com. Inmon may be reached at (303) 681-6772.