FREE DM Review Site Registration!
Sign-up today and access DM Review on the Web!

Your FREE registration entitles you to:
FREE email newsletters

FREE access to all DM Review content

FREE access to web seminars, resource portals, our white paper library and more!

   

Publisher reserves the right to serve qualified requesters only.

What standard/guidelines should be implemented in the transactional systems to make the data business intelligence ready?

Question:

  • What standard/guidelines should be implemented in the transactional systems to make the data business intelligence ready?

Sid Adelman's Answer:

  • My locksmith distributes a keychain with the initials DIRTFT - Do it right the first time. That should be the standard (not guideline - guidelines are only honored when they are convenient). This means that the data should be captured correctly in the source transactional systems. The idea that bad data should be corrected in the ETL process is foolish. Look in the data quality archive section of this Ask the Experts forum and the books written by Larry English and David Loshin.

Tom Haughey's Answer:

  • I don't know of any books or papers specifically on this but here are some practical ideas: capture time, capture measurement and goals data, improve quality and consolidate disparate sources. If a data warehouse is supposed to be a historical, integrated, read-only database of subject data, then make sure you can detect and keep history, that you have adequate quality data so it's worth reading, that you reduce the number of data sources and that you rationalize reference data in preparation for the warehouse. One way to proactively reduce the number of sources is when building operational systems to ensure that project teams reuse existing databases where possible instead of always creating "their own" new database and loading "their own" data into it.

    The warehouse is based on time so time must be available for event or fact data (which it always is) and for reference or dimension data (which it always isn't). Of course, define your calendars; you may have several, such as a fiscal, periodic and Gregorian calendar.

    Understand business goals and embed measurement of them into the operational data. First, you have to collect and understand the goals. Then you have to capture the data you need to perform the success measurement or metric implied in the goal. Here is an example. You want to measure customer order service levels, such as knowing how many potential orders never become orders. To do this, you need to know an order was commenced but later abandoned. It never actually became an order. Ensure you have the necessary base data to measure this.

    The more integrated the data, the easier it is to use with business intelligence (BI). This is not easy and can be costly to correct, but seek to reduce redundant data stores. Find some way to consolidate data. Rewriting systems is a good way but is costly and is not the only way. Creating a system to integrate data from multiple, disparate operational systems is another, less expensive way. It is also, by the way, one justification for building an operational data store (ODS).

    Improve data quality. What quality of data does the business require? Not every warehouse requires data that reconciles to the penny. Determine what your data quality requirements are and work progressively to achieve them. Find poor quality culprit systems and fix them, preferably at the source.

    Finding change data is one of the real challenges in data warehousing. Change data is that data that has changed since the last warehouse extract and load. See what tools you have that will allow you to detect change. Is your DBMS capable of keeping an adequate log that you could examine for change data? See my earlier point on near-real time for more on this.


Sid Adelman is a principal in Sid Adelman & Associates, an organization specializing in planning and implementing data warehouses, in data warehouse and BI assessments, and in establishing effective data architectures and strategies. He is a regular speaker at DW conferences. Adelman chairs the "Ask the Experts" column on www.dmreview.com. He is a frequent contributor to journals that focus on data warehousing. He co-authored Data Warehouse Project Management and is the principal author on Impossible Data Warehouse Situations with Solutions from the Experts and Data Strategy. He can be reached at (818) 783-9634 or visit his Web site at www.sidadelman.com.

Tom Haughey is the president of InfoModel LLC, a training and consulting company specializing in data warehousing and data management. He has worked on dozens of database and data warehouse projects for more than two decades. Haughey was former CTO for Pepsi Bottling Group and director of enterprise data warehousing for PepsiCo. He may be reached at (201) 337-9094 or via e-mail at tom.haughey@InfoModelUSA.com.

For more information on related topics, visit the following channels:



Industry Vendors