Dimensions
1. Degenerate Dimension
In a data warehouse, a degenerate dimension is a dimension which is derived from the fact table and doesn't have its own dimension table. Degenerate dimensions are often used when a fact table's grain represents transactional level data and one wishes to maintain system specific identifiers such as order numbers, invoice numbers and the like without forcing their inclusion in their own dimension. 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.
Degenerated Dimension is achieved through a gradual modeling approach following Dimensional Modeling standards. Let's take example of a Star Schema representing Sales Invoices. The FACT would have the "Invoiced Amount" as primary measure. Now when we look at the source of the Invoice, it is the body if the Paper Invoice that gives us the following particulars about each Invoice:
Invoice DateCustomer IDProducts within the InvoiceReference to Order Number(s)Invoice NumberInvoice Line Numbers (which are multiple lines in single Invoice)Invoice Line AmountInvoice Total Amount
When we model the above following Dimensional Modeling standards, we get following distinct Dimensions:
Calendar Dimension - representing the Invoice DateCustomer Dimension - representing Customer IDProduct Dimension - representing Products within the InvoiceOrder Dimension - representing OrdersInvoice Dimension รข€“ representing Invoice Number & Invoice Line Numbers
Question comes - what attributes would be left to be part of the INVOICE DIMENSION, if at all we decide to have one! Only candidate attributes are Invoice Number and Invoice Line Numbers. But, this is at the granularity of the FACT, which stores references to all above said Dimensions as well as the measures i.e. Invoice Line Amount, Invoice Total Amount (Derived by aggregation).
It is at this situation, we may decide to degenerate the attributes Invoice Number & Invoice Line Number into the Fact and avoid having a distinct entity to represent Invoice Number / Line Numbers as a Dimension. What we achieve by this:
1. avoiding a huge join as both Fact and this Dimension would have the same granularity,2. still able to query with Invoice Number as the entry point
So, when such a scenario appears, we make the left out attributes (i.e. Invoice Number & Invoice Line Number in our case) part of the Fact and part of the Primary Key in the Fact. This is why and how we model Degenerated Dimensions.
2. Junk Dimension
A junk dimension is a convenient grouping of typically low-cardinality flags and indicators. By creating an abstract dimension, these flags and indicators are removed from the fact table while placing them into a useful dimensional framework.
3. Conformed Dimension
They are dimension tables in a star schema data mart that adhere to a common structure and therefore allow queries to be executed across star schemas. For example the Calendar dimension is commonly needed in most data marts. By making this Calendar dimension adhere to a single structure regardless of what data mart it is used in your organization you can query by date/time from one data mart to another to another.
4. Role Playing Dimension
Role Playing dimension refers to a dimension can play different roles in a fact table depending on the context. For example, the Date dimension can be used for the ordered date, scheduled shipping date, shipment date, and invoice date in an order line fact.
In the data warehouse, you will have a single dimension table for the dates. You will have multiple warehouse foreign key from the fact table to the same dimension.
In Oracle BI Server, the role playing dimension can be created as multiple alias in the physical layer of the repository file (RPD). You can create the foreign key relationship from the fact table to the different alias of the dimension table.
5. Shrunken Dimensions
A shrunken dimension is a subset of another dimension. For example, the Orders fact table may include a foreign key for Product, but the Target fact table may include a foreign key only for Product Category, which is in the Product table, but much less granular. Creating a smaller dimension table, with Product Category as its primary key, is one way of dealing with this situation of heterogeneous grain. If the Product dimension is snow flaked, there is probably already a separate table for Product Category, which can serve as the Shrunken Dimension.
6. Static Dimensions
Static dimensions are not extracted from the original data source, but are created within the context of the data warehouse. A static dimension can be loaded manually — for example with Status codes — or it can be generated by a procedure, such as a Date or Time dimension.
7. Inferred Dimension
While loading fact records, a dimension record may not yet be ready. One solution is to generate an surrogate key with Null for all the other attributes. This should technically be called an inferred member, but is often called an inferred dimension.
Facts
In a data warehouse, a degenerate dimension is a dimension which is derived from the fact table and doesn't have its own dimension table. Degenerate dimensions are often used when a fact table's grain represents transactional level data and one wishes to maintain system specific identifiers such as order numbers, invoice numbers and the like without forcing their inclusion in their own dimension. 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.
Degenerated Dimension is achieved through a gradual modeling approach following Dimensional Modeling standards. Let's take example of a Star Schema representing Sales Invoices. The FACT would have the "Invoiced Amount" as primary measure. Now when we look at the source of the Invoice, it is the body if the Paper Invoice that gives us the following particulars about each Invoice:
Invoice DateCustomer IDProducts within the InvoiceReference to Order Number(s)Invoice NumberInvoice Line Numbers (which are multiple lines in single Invoice)Invoice Line AmountInvoice Total Amount
When we model the above following Dimensional Modeling standards, we get following distinct Dimensions:
Calendar Dimension - representing the Invoice DateCustomer Dimension - representing Customer IDProduct Dimension - representing Products within the InvoiceOrder Dimension - representing OrdersInvoice Dimension รข€“ representing Invoice Number & Invoice Line Numbers
Question comes - what attributes would be left to be part of the INVOICE DIMENSION, if at all we decide to have one! Only candidate attributes are Invoice Number and Invoice Line Numbers. But, this is at the granularity of the FACT, which stores references to all above said Dimensions as well as the measures i.e. Invoice Line Amount, Invoice Total Amount (Derived by aggregation).
It is at this situation, we may decide to degenerate the attributes Invoice Number & Invoice Line Number into the Fact and avoid having a distinct entity to represent Invoice Number / Line Numbers as a Dimension. What we achieve by this:
1. avoiding a huge join as both Fact and this Dimension would have the same granularity,2. still able to query with Invoice Number as the entry point
So, when such a scenario appears, we make the left out attributes (i.e. Invoice Number & Invoice Line Number in our case) part of the Fact and part of the Primary Key in the Fact. This is why and how we model Degenerated Dimensions.
2. Junk Dimension
A junk dimension is a convenient grouping of typically low-cardinality flags and indicators. By creating an abstract dimension, these flags and indicators are removed from the fact table while placing them into a useful dimensional framework.
3. Conformed Dimension
They are dimension tables in a star schema data mart that adhere to a common structure and therefore allow queries to be executed across star schemas. For example the Calendar dimension is commonly needed in most data marts. By making this Calendar dimension adhere to a single structure regardless of what data mart it is used in your organization you can query by date/time from one data mart to another to another.
4. Role Playing Dimension
Role Playing dimension refers to a dimension can play different roles in a fact table depending on the context. For example, the Date dimension can be used for the ordered date, scheduled shipping date, shipment date, and invoice date in an order line fact.
In the data warehouse, you will have a single dimension table for the dates. You will have multiple warehouse foreign key from the fact table to the same dimension.
In Oracle BI Server, the role playing dimension can be created as multiple alias in the physical layer of the repository file (RPD). You can create the foreign key relationship from the fact table to the different alias of the dimension table.
5. Shrunken Dimensions
A shrunken dimension is a subset of another dimension. For example, the Orders fact table may include a foreign key for Product, but the Target fact table may include a foreign key only for Product Category, which is in the Product table, but much less granular. Creating a smaller dimension table, with Product Category as its primary key, is one way of dealing with this situation of heterogeneous grain. If the Product dimension is snow flaked, there is probably already a separate table for Product Category, which can serve as the Shrunken Dimension.
6. Static Dimensions
Static dimensions are not extracted from the original data source, but are created within the context of the data warehouse. A static dimension can be loaded manually — for example with Status codes — or it can be generated by a procedure, such as a Date or Time dimension.
7. Inferred Dimension
While loading fact records, a dimension record may not yet be ready. One solution is to generate an surrogate key with Null for all the other attributes. This should technically be called an inferred member, but is often called an inferred dimension.
Facts
Types of Facts
1. Additive
Additive facts are facts that can be summed up through all of the dimensions in the fact table.
For example assumes that we are a retailer and we have fact table with the following columns.
Date
Store
Product
Sales_Amount
The purpose of this table is to record the sales amount for each product in each store on a daily basis, Sales_Amount is the fact. So if we want to know sales amount for a particular product in particular date , in particular store, we need to sum up all these three columns, so we can say it is additive fact.
2. Semi-Additive
Semi-additive facts are facts that can be summed up for some of the dimensions in the fact table, but not the others.
Say we are a bank with the following fact table:
Date
Account
Current_Balance
Profit_Margin
The purpose of this table is to record the current balance for each account at the end of each day, as well as the profit margin for each account for each day. Current_Balance and Profit_Margin are the facts. Current_Balance is a semi-additive fact, as it makes sense to add them up for all accounts (what's the total current balance for all accounts in the bank?), but it does not make sense to add them up through time (adding up all current balances for a given account for each day of the month does not give us any useful information).
3. Non-Additive
Non-additive facts are facts that cannot be summed up for any of the dimensions present in the fact table.
If we found the Profit_Margin fact in the above table Profit_Margin is a non-additive fact, for it does not make sense to add them up for the account level or the day level.
Types of Fact Tables
Based on the above classifications, there are two types of fact tables:
1. Cumulative
This type of fact table describes what has happened over a period of time. For example, this fact table may describe the total sales by product by store by day. The facts for this type of fact tables are mostly additive facts. The first example presented here is a cumulative fact table.
2. Snapshot
This type of fact table describes the state of things in a particular instance of time, and usually includes more semi-additive and non-additive facts. The second example presented here is a snapshot fact table.
Store
Product
Sales_Amount
The purpose of this table is to record the sales amount for each product in each store on a daily basis, Sales_Amount is the fact. So if we want to know sales amount for a particular product in particular date , in particular store, we need to sum up all these three columns, so we can say it is additive fact.
2. Semi-Additive
Semi-additive facts are facts that can be summed up for some of the dimensions in the fact table, but not the others.
Say we are a bank with the following fact table:
Date
Account
Current_Balance
Profit_Margin
The purpose of this table is to record the current balance for each account at the end of each day, as well as the profit margin for each account for each day. Current_Balance and Profit_Margin are the facts. Current_Balance is a semi-additive fact, as it makes sense to add them up for all accounts (what's the total current balance for all accounts in the bank?), but it does not make sense to add them up through time (adding up all current balances for a given account for each day of the month does not give us any useful information).
3. Non-Additive
Non-additive facts are facts that cannot be summed up for any of the dimensions present in the fact table.
If we found the Profit_Margin fact in the above table Profit_Margin is a non-additive fact, for it does not make sense to add them up for the account level or the day level.
Types of Fact Tables
Based on the above classifications, there are two types of fact tables:
1. Cumulative
This type of fact table describes what has happened over a period of time. For example, this fact table may describe the total sales by product by store by day. The facts for this type of fact tables are mostly additive facts. The first example presented here is a cumulative fact table.
2. Snapshot
This type of fact table describes the state of things in a particular instance of time, and usually includes more semi-additive and non-additive facts. The second example presented here is a snapshot fact table.
No comments:
Post a Comment