Dimensional modeling for operational systems

It seems to me that dimensional modeling is adequate for database modeling even for operational systems.

From my perspective, a dimensional model can register any set of business process and it is already adequate for query.

If the whole organization used only one dimensionally designed metadata , I think it would save a lot of work.

Am I crazy?

Rodrigo

Yes you are. The dimensional model, by Dr. Kimball's own admission*, is only applicable for reporting. The dimensional model is designed for read performance, not update and delete performance.

* See Kimball's A Dimensional Modeling Manifesto Manifesto Link

And if I modeled a normalized database (3NF), using snowflake schema, where there were only transact fact tables (degenerate dimension) and dimension tables?

Of course the performance of this approach is not so good compared to a not so normalized but this metadata seems more adequate to business communication and more prepared for an aggregated and denormalized DW.

There's a great thread on this topic - see "Reasons to use 3NF vs Dimensional Modeling"....I must admit that my intuition pushed me in the 3NF direction as I consider the design for our Operational Data Warehouse...however, after reading that thread, I think that a dimensional model will work for us, and "eliminate the middleman" as one poster phrased it..so, rather than design a 3NF data warehouse and tack on a data mart, I think I'll go with a dimensional model for this project.

I do appreciate the ETL considerations raised in the other thread; however, based on our situation, I think the dimensional approach is doable.

swaynebell wrote:There's a great thread on this topic - see "Reasons to use 3NF vs Dimensional Modeling"....I must admit that my intuition pushed me in the 3NF direction as I consider the design for our Operational Data Warehouse...however, after reading that thread, I think that a dimensional model will work for us, and "eliminate the middleman" as one poster phrased it..so, rather than design a 3NF data warehouse and tack on a data mart, I think I'll go with a dimensional model for this project.

I do appreciate the ETL considerations raised in the other thread; however, based on our situation, I think the dimensional approach is doable.

No argument with your point as it applies to data warehouses, however, I believe celoto's suggestion deals with transactional systems (i.e. order entry, payroll, accounts receivable and the like...) which is a whole other matter.

Such systems are implemented using normalized databases for a host of good reasons. Most important of which is a data value exists in one and only one place with unabiguous keys to identify the row. Updates to the data are direct and well defined, which is critical to maintaining data integrity in an evironment subject to highly concurent and random updating. Updates to a data warehouse, on the other hand, are strictly controlled and structured, so you can maintain denormalized structures without the problems you would otherwise have in an operational environment.