As business intelligence software technologies have matured, organizations have begun to expand the use of business intelligence software, like Oracle Business Intelligence, beyond reporting from data warehouses. Using tools like Oracle business intelligence to report off of operational systems such as Oracle EBS has become commonplace. So the question becomes, what is the difference between analytical and operational reporting and how should an organization architect a solution for each reporting type.
Answering these questions begins with the definition for analytical and operational reporting. Operational reporting is what organizations have been doing since the early days of systems. Operational reports typically provide transactional level or moderately aggregated level of detail. An Open Purchase Order report or # of Open PO's by Buyer are excellent examples of operational reports. From an architectural perspective, operational reports are typically generated off of source operational systems or some real time replication of the operational system data. Thus, a data warehouse is typically not needed for operational reporting.
Conversely, analytical reporting typically requires looking at trends over time or consolidation of data from multiple data sources. For example, determining the Top 10 vendors in terms of PO dollars during the last five months is more of an analytical report. Traditionally, analytical reports have been delivered based on a data warehouse architecture, where timeliness of information (i.e., last night's data is good enough) is less critical. The architecture of the data warehouse is based on a star schema design as opposed to the relational design of operational systems.
Interestingly as business intelligence software, databases and hardware have matured in terms of increased performance at a lower cost, the line has blurred between the architectural approaches for operational and analytical reporting. In the midmarket, we are seeing more and more organizations forgo the cost and complexity of a traditional data warehouse architecture (i.e., creation of a separate database which is populated on a nightly basis using ETL) and are instead developing reporting views within their operational systems to support both operational and analytical reporting.
The views, both relational and star schema, are employed based on the type of reporting that is required. If performance for analytical reporting suffers, materialized views are employed. This model can also be used even if data comes from multiple sources. We have seen some clients that will use PL/SQL or ETL to source data from other systems and populate tables within their operational system.
Does this mean that data warehouses are no longer required. The answer is, it depends. As the number of source systems increases and data volumes increase, the use of a view or separate schema in an existing operational system becomes less applicable. However, for large part of the midmarket that are looking to rapidly and cost-effectively deploy business intelligence, a traditional data warehouse architecture is overkill.
