I am coming off of a failed project where a DW was not built and the data mart development group tried to take over the functionality of a DW and the DW (ie they tried to handle both the intake process and the presentation). The data mart team was using the classic dimensional model. They were dealing with large disparate sources of data from a number of very disparate system (OS, hardware... etc).
My question is this: Is a warehouse in a more normalized form needed to supply (guarantee) success for) a data mart effort, or can purely dimensional structure handle everything? Estimated initial raw data was approximate 3/4 of a terabyte a month.
I see many "once a star, always a star" implementations that are crumbling. Categorically, star schemas cannot do it all! They are an important part of the architecture. The key word being "part". Your intake layer is best served via a relational model that is highly normalized and strikes the right balance between your enterprise data model and your inventory of source application data structures. The intake layer also is designed to capture and retain audit trail history. This is called the PSA. (Persistent Storage Area). The data marts are sourced from the PSA. Data marts DO NOT mean star schemas. Data marts are designed for performance and/or specific information access requirements. Star schemas are very good for those business requirements that need a lot of dimensional analysis and trending analysis. Relational schemas are good for business requirements that need a lot of selective reporting and list management.
For More Information
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Data Warehousing and Business Intelligence Web Links: tips, tutorials, scripts, and more.
- Have an DW tip to offer your fellow administrators and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical data warehousing questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle DBA jobs, training and certification
Related Q&A from Mike Lampa
When trying to design a data warehouse, we often try to model the database on the operational data model. Are there any guidelines in trying to ... Continue Reading
What is a surrogate key in a table? Continue Reading