Dimensional modeling for operational systems

View previous topic View next topic Go down

Dimensional modeling for operational systems

Post  celoto on Thu Jul 30, 2009 7:00 pm

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


Last edited by celoto on Thu Jul 30, 2009 11:39 pm; edited 1 time in total

celoto

Posts : 2
Join date : 2009-07-30

View user profile

Back to top Go down

Re: Dimensional modeling for operational systems

Post  BoxesAndLines on Thu Jul 30, 2009 11:14 pm

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
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

And a normalized one?

Post  celoto on Fri Jul 31, 2009 10:33 am

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.

celoto

Posts : 2
Join date : 2009-07-30

View user profile

Back to top Go down

Operational DW

Post  swaynebell on Fri Aug 07, 2009 9:29 am

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

Posts : 1
Join date : 2009-08-06

View user profile

Back to top Go down

Re: Dimensional modeling for operational systems

Post  ngalemmo on Fri Aug 07, 2009 11:20 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Dimensional modeling for operational systems

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum