Types of Dimensions in Data warehouse
Make data easy with Helical Insight.
Helical Insight is the world’s best open source business intelligence tool.
What is Dimension?
Dimension table contains the data about the business. The primary keys of the dimension tables are used in Fact tables with Foreign key relationship. And the remaining columns in the dimension is normal data which is the information about the Objects related to the business.
Eg: Product,Customer,Orders,Company,Date etc.
Below are the different types of Dimensions:
1) Slowly Changing Dimensions (SCD) : Dimensions that change very slowly overtime rather than according to regular schedule.
Below are some popular approaches of SCDs:
Type 0: This is Passive method. The records which were inserted will not change any time.
Type 1: This method overwrites the old record with new values without tracking any historical data.
Type 2: This methods tracks the historical data by using version number or by using startdate, enddate columns.The drawback is the table will grow vertically. And it requires more space.
Type 3: This method will also track the historical data by inserting the column with new value. It preserves limited history.
Type 4: This method uses a separate table for storing all the historical data and main table will hold the current_data. And both will pointed with the same surrogate key.
Type 6: This is called hybrid method which uses all type 1+ type 2 + type 3. It uses flag values and effective date to differ between old and new records.
2) Rapidly Changing Dimensions: The Dimension which contains rapidly changing attributes. If we maintain any historical data for these type of tables. We will definitely get an issue related to memory and performance. The solution is to maintain mini dimension tables for historical data like type 4 Dimension in SCD. The main table should contain the current values and mini dimensions can contains historical data.
3) Junk Dimensions: In the data warehouse design we will come across a situation to use flag values. We can use one single table for this so that in Fact table no need to have multiple columns to store the Primary key values of these flag tables.
Eg:
As per above, if we consider Gender_Marital_Status we can use only 1 single column in Fact table.
4) Inferred Dimensions: The Dimension which is important to create a fact table but it is not yet ready, then we can assign some dummy details for one ID and we can use that ID in fact table. After getting the details then we can update the details in the dimension.
5) Conformed Dimensions: Dimensions which are connected with multiple fact tables are know as conformed Dimension.
Eg: Customer Dimension and Product Dimension are required in Shipment Fact,Sales fact and Service Request Fact
6) Degenerate Dimensions: A degenerate table does not have its own dimension table. It is derived from a fact table. The column (dimension) which is a part of fact table but does not map to any dimension.
Eg: Invoice_Number column in Fact table
7) Role Playing Dimensions: The same dimension which can be used for multiple purpose
Eg: Date Dimension can be used as Date of Sale, Date of Hire etc.
8) 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 ProductCategory, which is in the Product table, but much less granular. Creating a smaller dimension table, with ProductCategory as its primary key, is one way of dealing with this situation of heterogeneous grain. If the Product dimension is snowflaked, there is probably already a separate table for ProductCategory, which can serve as the Shrunken Dimension
Make data easy with Helical Insight.
Helical Insight is the world’s best open source business intelligence tool.
9) 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.
Thank You
Lalitha
Best Open Source Business Intelligence Software Helical Insight is Here
