Dimensional modeling

From Wikipedia, the free encyclopedia

Dimensional modeling (DM) is the name of a logical design technique often used for data warehouses. It is frequently and mistakenly considered to be different from entity-relationship modeling (ER), but when applied to relational databases, it is another form of ER and, if done properly, is in 2nd or even 3rd Normal Form. Dimensional Modeling, however, does not necessarily involve a relational database. The same modeling approach, at the logical level, can be used for any physical form, such as multidimensional database or even flat files. According to Dr. Kimball, DM is a design technique for databases intended to support end-user queries in a data warehouse. It is oriented around understandability, as opposed to database administration. According to him, although transaction-oriented ER is very useful for the transaction capture, it should be avoided for end-user delivery.

Dimensional modeling always uses the concepts of facts (sometimes referred to as measures) and dimensions. Facts are typically (but not always) numeric values that can be aggregated, and dimensions are groups of hierarchies and descriptors that define the facts. For example, sales amount is a fact; timestamp, product, register#, store#, etc. are elements of dimensions. Dimensional models are built by subject area, such as store sales, inventory, claims, etc. Because the different subject areas share some but not all dimensions, efficiency in design and operation, as well as consistency, is achieved by conforming the dimensions, that is, using one copy of the shared dimension across subject areas. The term "conformed dimensions" was originated by Ralph Kimball.