Dedicated to design and performance of databases and audio systems.
A few years ago I attended the Kimball Dimensional Modeling In Depth four-day course.
Now anybody who knows me, knows that I have a philosophical disagreement with Mr. Kimball's approach; however, having an open mind and being able to articulate why you have a difference of opinion is important. While I agree that dimensional modeling is one semantic modeling technique, I do not agree that it is a one-size-fits-all physical design implementation methodology. It may be required for SMP platform architectures, but is not necessarily an optimal choice for the MPP platforms (e.g. Teradata, Informix XPS).
During the class no products were mentioned by name (e.g. Oracle, DB2/UDB, SQL Server), except for one: Teradata. Twice. Once by Margy Ross, who indicated the technique being discussed at the moment need not apply to Teradata; and second, by Ralph Kimball. During a break, I approached Mr. Kimball and asked his opinion of Teradata since I inferred that he was not a fan. Ralph told me that Teradata has a world-class optimizer, no doubt about that. However, he disagreed with their promoted data architectural approach. He elaborated by stating (and I'm paraphrasing here) that Teradata encourages customers to load replications of their OLTP 3NF systems into the DBMS and then place views on top of the data to provide the BI/reporting/analytics perspectives required by the users. And, when the performance is not acceptable, buy a larger machine.
An extreme option to be sure. My reply was that, at the time, the Teradata optimizer had a table join limit of 64 (since raised to 128 in Teradata 13.0); therefore, replicating an OLTP 3NF physical model, without any transformation, would be futile.
Teradata is not pro-3NF, despite common perceptions. It is also not anti-Dimensional Modeling. As Stephen Brobst, Teradata's CTO, once said to me physical data design is an engineering exercise, leveraging the strength of the database platform and architecture with the usability requirements.
The Kimball Design Tip 148 Complementing 3NF EDWs with Dimensional Presentation Areas starts to talk about a popular modification to the Kimball Architecture has evolved. It combines data warehouse atomic data with dimensional tables and presentation. Sounds like detail and summary data, and transformation, to me. It goes on to state that the majority of organizations will implement the dimensional presentation layer physically. However, other organizations will implement it logically via database views. That would solve redundancy, integration, timing, and data integrity concerns that would exist with separate physical instantiations. We are moving in the right direction--store it once and use it in many different perspectives. The power of virtualization.
However, the article then concludes that do so in views is relatively unproven for large-scale production use in most environments. Hmm. Then what would be a viable candidate technology platform that virtualization of data perspectives would work in?