OLAP Vs OLTP
Slowly Changing Dimensions
Type 0 -> No Changes Ever, Ostrich technique
Type 1 -> U go and update the attribute. Change the brand. Completely lost the history. Very Simple technique. But difficult to manage history !. If you are not interested in history
Type 2 -> Surrogate Key should be Unique. Create a NEW surrogate Key and keep this history data. Every time attribute value changes, add a new tuple in the dimension table with NEW surrogate key. Old facts under old surrogate key. New facts under NEW surrogate key. Unlimited number of changes under new territory.
Type 3 -> Add a New Column
Junk Dimension
In a data warehouse, a dimension is a data element that categorizes each item in a data set into non-overlapping regions. A data warehouse dimension provides the means to "slice and dice" data in a data warehouse. Dimensions provide structured labeling information to otherwise unordered numeric measures. For example, "Customer", "Date", and "Product" are all dimensions that could be applied meaningfully to a sales receipt. A dimensional data element is similar to a categorical variable in statistics.
Types of Facts
There are three types of facts:
Additive: Additive facts are facts that can be summed up through all of the dimensions in the fact table.
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.
Non-Additive: Non-additive facts are facts that cannot be summed up for any of the dimensions present in the fact table.
Slowly Changing Dimensions
Type 0 -> No Changes Ever, Ostrich technique
Type 1 -> U go and update the attribute. Change the brand. Completely lost the history. Very Simple technique. But difficult to manage history !. If you are not interested in history
Type 2 -> Surrogate Key should be Unique. Create a NEW surrogate Key and keep this history data. Every time attribute value changes, add a new tuple in the dimension table with NEW surrogate key. Old facts under old surrogate key. New facts under NEW surrogate key. Unlimited number of changes under new territory.
Type 3 -> Add a New Column
Junk Dimension
In a data warehouse, a dimension is a data element that categorizes each item in a data set into non-overlapping regions. A data warehouse dimension provides the means to "slice and dice" data in a data warehouse. Dimensions provide structured labeling information to otherwise unordered numeric measures. For example, "Customer", "Date", and "Product" are all dimensions that could be applied meaningfully to a sales receipt. A dimensional data element is similar to a categorical variable in statistics.
A star schema that has many dimensions is sometimes called a centipede schema[2]. Having dimensions of only a few attributes, while simpler to maintain, results in queries with many table joins and makes the star schema less easy to use.
There are three types of facts:
Additive: Additive facts are facts that can be summed up through all of the dimensions in the fact table.
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.
Non-Additive: Non-additive facts are facts that cannot be summed up for any of the dimensions present in the fact table.
No comments:
Post a Comment