Dimensional Table Types Indepth
Go Up to Customizing the Data Model
In a dimensional model, each table is assigned a Table Model type, which is distinguished by an icon displayed in the upper left of the entity or table box. You can further define a table in the Table Editor by assigning it a Table Type that displays at the bottom of the table. The Table Type does not change how the data is handled but provides information to the reader about the data in the table:
ER/Studio Data Architect supports the following Dimensional table types:
Fact
Tables with one or more foreign keys and no children.
Fact tables contain individual records, the data for which the database is being designed. Fact tables are the central tables in a star schema.
ER/Studio Data Architect allows you to further define fact tables as one of the following types:
- Aggregate: Contains information that is more coarsely granulated than the detail data contained in other tables. For example, in a retail, transactional database, you can have a Sales_Receipts fact table that contains information about individual sales, and a Sales_Monthly aggregate table that aggregates information from the Sales_Receipts table to present information such as monthly sales totals for software, hardware, and services. Aggregates provide quick access to data during reporting; like indexes they improve performance.
- Atomic: Contains detail information, such as the Sales_Receipts table in the discussion above.
- Cumulative: Contains cumulative information such as how long it took to complete the sale by accumulating the time it took between the date of sale and the shipping date for each line item of the sales receipt.
- Snapshot: Contains time-related information that detail specific steps in the life on the entity. For example, snapshot information for a sale could contain information such as when the order was created, committed, shipped, delivered, and paid for.
Dimension
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 Data Architect 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.
Snowflake
Parents of dimension tables.
Snowflake tables present a more-normalized format where elements of dimension tables are listed. The following example shows two snowflake tables which are parents of a dimensional table. There are many other good examples or snowflake tables in the sample model, Orders.dm1.
Bridge
Implements a many-to-many relationship. Bridge tables support multi-valued dimensions or complex hierarchies. The Bridge is also known as a helper table or an associative table. It is the only way to implement two or more one-to-many relationships or many-to many relationships.
Used to support complex hierarchies, such as organizational charts.
Undefined
All other tables. Assign this type to flag tables for which you have not yet determined the appropriate type, such as a table with a many-to-many relationship, or a table that is a parent to both a fact table and a dimension table.