Continue in 2 seconds

We are trying to create a policy on information reporting that would identify the types of reports that should come from our DW vs. those that should come from our operational systems.

  • Joe Oates, Michael Jennings, Clay Rehm, Les Barbusinski, Nancy Williams
  • January 13 2003, 1:00am EST


We are trying to create a policy on information reporting that would identify the types of reports that should come from our DW vs. those that should come from our operational systems. Can you provide some guidance on how we could create such a policy?


Les Barbusinski’s Answer: Here are some simple guidelines for deciding which system should generate a certain type of report:

  1. Use operational systems to generate reports that:
    • Involve simple lists, lists of exceptions or simple summaries
    • Involve detail data about specific objects, entities, or transactions
    • Reflect current (i.e., day-old) or real-time conditions
    • Are sourced from a single application system
    • Service a homogenous group of users
  2. Use data warehouses or data marts to generate reports that:
    • Involve complex (i.e., compound) analytics
    • Involve drilling down or across hierarchies
    • Involve derived or highly aggregated information
    • Measure activities over time (i.e. trends, YTD totals, etc.)
    • Integrate information from a variety of source systems
    • Service a diverse user community

Mike Jennings’ Answer: Depending on your business requirements, amount of operational systems, amount of data and complexity of data transformation and integration processes, all of your reporting could be done from your data warehousing environment. Data from your operational systems can be extracted and loaded into an operational data store (ODS) into your warehousing environment on an intra-day basis. This operational data should go through minimum transformation processing, except where integration and consolidation of core data between operational systems is required (e.g., customer, product, etc.). By off loading data from your operational systems into the ODS your operational systems can perform their primary function, transactional processing, without being affected by varying reporting processing loads.

On-demand reports can be run by users against your ODS. These reports allow users to select filters, constraints, and columns they wish to see in the report based on their security privileges and needs. Scheduled reports can be run periodically against the ODS and pushed out to users based on their subscription. These reports have a standard format and are constrained by the security of the user receiving the report. Data from the ODS is periodically (weekly, monthly) extracted and transformed into the enterprise data warehouse (EDW). Data from the EDW is then processed into either a data mart or OLAP cubes (or both) for analytical reporting needs.

Joe Oates’ Answer: Here are some general guidelines that I recommend to clients.

Reporting needs best satisfied by a data warehouse (and the appropriate presentation tools):

  • Historical reports and trend analysis reports;
  • Reports requiring drilling;
  • Reports requiring precalculated metrics not carried in operational systems;
  • Reports requiring integration across operational system lines. Examples include sales, channel, etc., reports for all products and channels when each product/channel has a separate operational system;
  • End-business-user access requiring ad hoc and predesigned standard reports, which may choose all of the above.

Reporting needs best satisfied by operational system reports:

  • Intra-day reports or reports at a finer grain than are stored in the data warehouse, e.g., daily, weekly, etc.
  • Information about a particular transaction, particularly when talking with a customer on the telephone.

The key thing to remember is that a data warehouse is designed for analytical needs to help management understand how the whole organization is performing and to provide information to help make better tactical and strategic decisions about the organization as a whole. So anything that is analytical, is usually best handled by a data warehouse.

Clay Rehm’s Answer: You probably have heard or read somewhere that you should not use the data warehouse as a source for operational type reporting. I am not sure that I subscribe to this logic. Here is why: Typically the data in the operational systems is the data your users need because of its current nature. The problem is that the operational database is quite normalized and very difficult to use for a non-technical person. On the other hand, the data warehouse design is very ease to use and if designed correctly is integrated with other data sources. The data warehouse becomes very attractive for reporting then and these users wonder why they can’t use it. After all the money that has been spent on the data warehouse, why can’t they build reports off of it? The data warehouse becomes the logical place to do any query and reporting from since it was designed to do so.

It boils down to data warehouse design and semantics. For example, your data warehouse could have database tables that are updated more frequently than others. Some tables will have lots of detail, and other tables will be summarized. It is a collection of different types of tables, that is more current data verses historical data, more detail data verses summary data and so on.

The reason I mention semantics is that you can call this database anything you want – data warehouse, reporting database, decision support database, etc., but the bottom line is that all of your users query and reporting needs are met.

Regarding your question on how to establish a policy, the first task to be accomplished is to create a query and report inventory of each and every query and report that currently exists. The following table provides an example:

Report or Query Name and Number Brief Description Requested By Create Date Business Rules (ie, what is in the SQL Where clause) Tables and Columns Used
RPT001 Marketing Report This report provides YTD information about etc… Sid Adelman 12/01/1998 Customer number = client number and effective date is less than or equal to current date, etc Table1.column1, table1.column2, table2.column1, etc

Nancy Williams’ Answer: There are a number of guidelines that are commonly used for creating policy in this area. Since the purview of operational systems is supporting the day-to-day functioning of specific business process areas e.g., order tracking, operational system reports are best suited to support the information needed for this purpose. By definition, operational system reporting will be limited to the information that resides in each individual operational system database. While the operational system focus is current and has a specific business process focus, the data warehouse provides an integrated, historical perspective of the business. Its strength is in supporting analysis and reporting for strategic and tactical decision making which requires an integrated historical perspective of the business. The data warehouse also commonly supports operational reporting that requires integration of data across multiple operational systems.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access