Related Links

Web Seminars

Q: We are in the early design stage of a data warehouse. The general outline is a set of data marts loaded from an operational data store consisting of tables from a number of databases supporting administrative areas (e.g., HR, finance, contracts). Is there a best practice associated with the number of distinct databases that should be used in the overall warehouse? One group is arguing that everything (marts and ODS) should be in a single database. Another group is arguing that the marts and ODS should be in separate databases to allow tuning to support the different roles. What criteria are most important to consider in reaching a decision?

Sid Adelman's Answer : I wouldn't try to put everything in a single database. The database designs are different, the tuning choices based on the nature of the data, the volumes and on access would be different and, most importantly, the politics of getting everyone's concurrence is almost impossible. Keep them separate but this does not mean you couldn't have common subject areas that span multiple divisions such as customer and supplier.

Chuck Kelley's Answer: I believe that will depend on your architected approach. Personally, I believe that the ODS would be in a separate database (and hardware) than the data marts because the ODS is an operational system that should be designed much like a transaction system. The data is then converted and put into (a data warehouse) your data marts which will be more dimensional in design and analytical in processing. Since the usage of the two systems are different (transactional versus analysis), I would place them in separate databases on separate hardware.

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.

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.