Parents of fact tables.Parents of fact tables.
Dimension tables contain groups of related data, such as dates, hours, minutes, and seconds, represented by one key such as time in a fact table.
ER/Studio DA allows you to further define dimension tables as one of the following types:
● Fixed Dimension: The values in the table are not expected to change.
● Degenerate: A degenerate dimension is derived from the fact table. Degenerate dimensions are useful when the grain of a fact table represents transactional level data and you want to maintain system-specific identifiers such as order numbers and invoice numbers without forcing them to be included in their own dimensions. Degenerate dimensions can provide a direct reference to a transactional system without the overhead of maintaining a separate dimension table. For example, in the sample model, Orders.dm1, you could create a degenerate dimension that references the Commission Credit ID, Payment Detail ID, and Sales Order ID from the Commission Credit, Payment Detail, and Sales Order Line ID fact tables respectively,
● Multi-Valued: A multi-valued dimension can help you model a situation where there are multiple values for an attribute or column. For example, a health care bill has a line item of Diagnosis, for which there could be multiple values. Best practice modeling dictates that there should be a single value for each line item. To model this multi-valued situation, you could create a multi-valued table that would capture the diagnosis information and weighs each diagnosis, so that the total adds up to one. This weighting factor allows you to create reports that do not double count the Billed Amount in the fact table.
● Ragged: In a ragged dimension, the logical parent of at least one member is missing from the level immediately above the member. Ragged dimensions allow for hierarchies of indeterminate depth, such as organizational charts and parts explosions. For example, in an organization some employees can be part of a team which is managed by a team leader, while other employees report directly to the department manager. Another example of a ragged dimension is illustrated in the model below which shows that cities such as: Washington, D.C.; Vatican City; and Monte Carlo do not belong to states.
● Shrunken: A shrunken table is a version of the fact table that it is attached to but with fewer attributes in order to draw attention to those specific attributes.
● Slowly Changing Type 0, 1, 2, 3, 6: Slowly changing dimensions capture information such as the customer’s income level or income-to-debt ratio which can change overtime. The most commonly used methods to maintain these changes are:
• Type 0: No effort has been made to deal with the changes.
• Type 1: Overwrites the existing dimension member values. Does not track the change history.
• Type 2: Creates a new row with the current dimension member. Tag the original row as expired.
• Type 3: Used for dimensions that change with a predictable rhythm, such as every year. Every dimension row has a current category attribute that can be overwritten as well as attributes for each annual designation, such as 2008 category and 2009 category.
• Type 6: Combines the approaches of types 1, 2, and 3. For changes to this type of table, the row is overwritten but the table can contain an additional pair of date columns to indicate the date range at which a particular row in the dimension applies, or the table can contain a revision number.