Continue in 2 seconds

Can you tell me how to distinguish reporting that is done from an OLTP system vs. reporting that is done from an ODS?

  • Chuck Kelley, Clay Rehm, Les Barbusinski
  • May 07 2004, 1:00am EDT


Can you tell me how to distinguish reporting that is done from an OLTP system vs. reporting that is done from an ODS? We have developed an ODS, and now are looking for a good way to explain to our users what kind of reporting is best done against the ODS. Our ODS is refreshed nightly. We currently are doing no cleansing of our ODS data. Also, would you consider "operational reporting" to be a term that would describe reporting done against both the OLTP system and the ODS?


Les Barbusinski's Answer: If you ignore the benefits of data cleansing and integration (which you have), the only remaining difference between generating operational reports from an ODS vs. the OLTP system is performance.

An OLTP system generates its operational reports directly from its transactional tables. This is inefficient because a) the structure of the OLTP database is optimized for transaction processing - not report generation - which leads to more "joins" and "table scans" than would otherwise be necessary for the reports, b) processing transactions and generating reports on the same database can lead to contention problems (e.g., "deadlocks" and "timeouts") and c) generating the I/O-intensive reports on the OLTP system puts a significant load on the OLTP database ... thereby slowing the system's ability to process transactions in a timely manner.

An ODS, on the other hand, generates its operational reports from a separate database whose structure has been specifically optimized for report generation. This means that reports will execute faster on an ODS and will have zero impact on the OLTP system's ability to process transactions. The ODS can, if necessary, be updated in near real time, thereby mimicking the OLTP system's ability of reporting up-to-the-minute results.

Some people will argue that an ODS always has a built-in delay in acquiring its data and, therefore, will never generate reports as up to date as an OLTP system. However, it's been my experience that the poor performance inherent in OLTP-based report generation usually means that its reports are no more current than those generated by a "real-time" ODS. Hope this helps.

Chuck Kelley's Answer: To answer the question fully, I would need to know more about how the ODS was built. Assuming that the ODS was built as defined in the Corporate Information Factory (i.e., ODS is bringing together multiple OLTP systems to provide a single view), here is my view: 1) If you need to the minute operational reporting, do it from the OLTP. 2) If you need to the minute compared to history, then either join the OLTP and ODS (you said you are not cleansing the data) or use the OLTP (but this means that you have to keep more history in the OLTP that you might not need to keep otherwise. 3) If the reporting can be done out of the data in the ODS, I would do it there. Why slow down the OLTP system doing queries?

Clay Rehm's Answer: The answer may lie within how often the data is refreshed. OLTP systems are updated real time, where your ODS is only refreshed nightly. Your client's reports are probably relying on both scenarios. Typically an OLTP system will only contain data for a specific function. An ODS is meant to be more integrated and collect data from multiple sources. If the ODS is not cleansed, is the cleansing process taking place when the data warehouse is updated? If there is no data warehouse and only the ODS, then you must implement data cleansing processes into the ODS ETL. Operational reporting most definitely uses OLTP data as its source, and certainly can use the ODS as well depending on the data refresh frequency and the quality and granularity of the data needed for your clients reports.

A data warehouse is made up of many data elements - detailed tables, summary tables, fact tables, dimension tables, indexes, views, etc. Each element may be refreshed or updated at different times. There are many factors, and one of them is that not all data will be refreshed at the same time. Some data (tables, columns, rows) will be updated real-time, some daily, weekly, monthly, etc. It is important to identify specifically which data element needs to be updated and when. How the data will be used, and how often it will be used can help determine the refresh frequency. This will take interviews and meetings with your clients, so you can understand how they will use the data. Additionally, researching the data sources and how frequently they are updated will provide assistance on how often that same data in the data warehouse should be refreshed.

Larissa Moss' Answer: Good question because that distinction is getting more and more blurred. Let's start with some definitions. An "operational" report is an accounting (or output) of data from one operational file or database that was deliberately designed to capture, store and/or manipulate the data for a specific operational purpose. It is simply a part of carrying out a business process. For example, a bank statement produced from a DDA (demand deposit account) file or table. A "tactical" report uses data from more than one operational file or database and is used to better manage a business process. This is a decision-support function for an operational business process, and it is best supported by an ODS, which has the required data integrated from the various operational (OLTP) files or tables. Since an ODS is used for decision support, the quality of the data should be an issue. After all, you wouldn't want the line of business managers make decisions based on dirty data.

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