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!

   

Is it wrong to build a billing application off the ODS?

Q: My company plans to build an ODS and also a star schema oriented data mart (on the same machine). In this ODS they want to load point-of-sale data for customers they fulfill for. They are interested in building a billing application off the ODS. I know this is wrong but I am looking for an explanation of why it is wrong (beyond general tuning comments).
A: Chuck Kelley's Answer:In my opinion, the ODS is yet another operational system. So I have less of a problem with doing this than maybe others will. I have done this at a few sites. All the POS (cash registers, reservation systems, etc.) send a feed into the ODS and then there is some "integrating" of the data. This data is used for operational reporting. Then the data is used to feed our data warehouse; and the data warehouse feeds the data marts. The big concern I would have is if they want to write back into the ODS and payments are received. This would be a big no-no because the ODS holds transient data.

Les Barbusinski's Answer: A billing application is transactional nature, and its database needs to be optimized for insert/update activity (batch and on-line). This usually means logging is turned "on" and log sizes are kept small, tables are highly de-normalized and designed around a set of small, well-defined processes, joins are infrequent, units-of-work are small, referential integrity is enforced via constraints, indexes are scarce, and so on.

By contrast, a data warehouse is retrieval-based in nature, and its database needs to be optimized for complex queries with large result sets. This usually means logging is turned "off," tables are organized in either normalized or dimensional structures to facilitate ad hoc queries and analytical computations, joins are frequently used, units-of-work are large, referential integrity is enforced via ETL scripts, there are numerous indexes to facilitate data retrieval, and so on.

The moral of the story is that transactional applications and data warehouses are fundamentally different in the way they store and process data, and each requires a specialized environment to optimize its performance. And, although applications are often built "on top of" data warehouses (CRM and Budgeting are two good examples), transactional applications and data warehouses need to have their databases "loosely coupled" rather than "joined at the hip." Hope this helps.


Chuck Kelley is an internationally known expert in database and data warehousing technology. He has 30 years of experience in designing and implementing operational/production systems and data warehouses. Kelley has worked in some facet of the design and implementation phase of more than 50 data warehouses and data marts. He also teaches seminars, co-authored four books on data warehousing and has been published in many trade magazines on database technology, data warehousing and enterprise data strategies. He can be contacted at chuckkelley@usa.net.

Les Barbusinski is vice president of technology and co-founder of Digital Symmetry, LLC, a consulting firm that specializes in the design and development of data warehousing and business intelligence solutions. He has more than 20 years of experience in data warehouse and operational systems development and provides hands-on expertise in data warehouse design, development and project management. Les can be reached at dwexpert@dsym.com.

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



Industry Vendors