OLAP

OLAP and Business Intelligence





Reference:

• The Multidimensional Data Modeling Toolkit: Making Your Business Intelligence Applications Smart with Oracle OLAP.
• Data Warehouse Toolkit.

Data Modeling: Degenerate Dimension

A degenerate dimension (DD) acts, as a dimension key in the fact table, dose not have a corresponding dimension table because all its interesting attributes have already been placed in other analytic dimensions. Even though there is no corresponding dimension table of attributes, degenerate dimensions can be quite useful for grouping together related fact table’s rows.

Degenerate dimensions are useful as they tie the transactions, or events, in the fact table back to real-life items - invoices, orders and so on - and they can be a quick way to group together similar transactions for further analysis.

Degenerate dimensions also reduce the need for lookup tables in the ETL processes since there is not actual dimension table associate with a degenerate dimension.

Some examples for degenerate dimensions are, order id, invoice id of a sales order, a flag, which used to indicate if an order is invoiced or not invoiced.

In general, the decision to use degenerate dimensions is often based on the desire to provide a direct reference back to a transactional system without the overhead of maintaining a separate dimension table.


Data,Information,Knowledge and Life

Data Modeling: Special Relationships

1. One to One Relationship
One-to-one relationships are between two entities where both are related to each other, once and only once for each instance of either

For example, one developer is given one development server, but not all development servers are given to the developers








2. Many to Many Relationship

In a many-to-many relationship, multiple occurrences of one entity are related to one occurrence of another, and vice versa.

For example, one developer can assign to many data warehouse development projects and one project can assign to many developers.






Associative entities (also known as intersection entities) are entities used to associate two or more entities in order to reconcile a many-to-many relationship.






3 Recursive Relationships

A recursive relationship is an entity that is associated with itself.

For example, an engineering manager manages a group of engineers. This engineering manager is reporting to the director of engineering who manages many engineering manager. This director ,in term, is also managed by the Vice President of engineering.
















Data,Information, Knowledge and Life