Total Page Preview:   000000000135

How many types of dimension tables available in data warehouse?

Data warehouse have six types of dimension table.

1. Conformed Dimensions
when a particular dimension is connected to one or more fact tables. ex) time dimension
 
2. Parent-child Dimensions
  • A parent-child dimension is distinguished by the fact that it contains a hierarchy based on a recursive relationship.
  • when a particular dimension points to its own surrogate key to show an unary relationship.
 
3. Role Playing Dimensions
  • when a particular dimension plays different roles in the same fact table. ex) dim_time and orderDateKey, shippedDateKey...usually a time dimension table.
  • Role-playing dimensions conserve storage space, save processing time, and improve database manageability .
 
4. Slowly Changing Dimensions:
A dimension table that have data that changes slowly that occur by inserting and updating of records.
  1. Type 0: columns where changes are not allowed - no change ex) DOB, SSNm
  2. Type 1: columns where its values can be replaced without adding its new row - replacement
  3. Type 2: for any change for the value in a column, a new record it will be added - historical data. Previous values are saved in records marked as outdated. For even a single type 2 column, startDate, EndDate, and status are needed.
  4. Type 3: advanced version of type 2 where you can set up the upper limit of history which drops the oldest record when the limit has been reached with the help of outside SQL implementation.
 
Type 0 ~ 2 are implemented on the column level.
 
5. Degenerated Dimensions:
  • a particular dimension that has an one-to-one relationship between itself and the fact table.
  • When a particular Dimension table grows at the same rate as a fact table, the actual dimension can be removed and the dimensions from the dimension table can be inserted into the actual fact table.
  • You can see this mostly when the granularity level of the the facts are per transaction.
  • Example: The dimension salesorderdate (or other dimensions in DimSalesOrder would grow everytime a sale is made therefore the dimension (attributes) would be moved into the fact table.
 
6. Junk Dimensions: holds all miscellaneous attributes that may or may not necessarily belong to any other dimensions. It could be yes/no, flags, or long open-ended text data.
 

Thank You

About Author

Brijesh Kumar

Database Developer

I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall and Database Migration On-Premise to Azure Cloud.
LinkedIn : https://www.linkedin.com



Comments


                           
                           

                           

                           

Recent Posts

How to get the alternate rows or records from table in sql server
07-Jun-2017
Data Warehouse Three Tier Architecture
20-Aug-2017
SQL Server - Fragmentation on azure sql database
07-Oct-2018
Limitations of Azure SQL Database
19-Sep-2019
PIVOT and UNPIVOT in SQL Server with Example
29-Jun-2017
SQL Server - Find all index with key columns on Azure SQL Database
02-Oct-2018
SQL Server - Find unused indexes on azure SQL database scripts
02-Oct-2018
Find Resource usage in Azure SQL Database using script or T-SQL
07-Oct-2018
How to Set client IP Address using the firewall security in Azure SQL Using Portal
29-Jun-2017
What is a Trigger and type of trigger in SQL server?
10-Sep-2021
What is Table Variable? Explain its advantages and disadvantages?
10-Sep-2021
Top 60 SQL Server Interview Questions Answers for freshers and experienced
17-Sep-2021
Cloud Data Engineer SQL Server Interview Questions Answers for freshers and experienced
31-Aug-2021
Top 16 performance query optimization techniques in SQL Database?
16-Sep-2021
Data warehouse Interview Questions Answers for freshers and experienced
16-Sep-2021
What are the types of User Define Function and difference between a nested UDF and recursive UDF in SQL server?
10-Sep-2021
What are the different types of Error Handling in SQL Server?
11-Sep-2021
What are the differences between OLTP and OLAP?
07-Sep-2021
How many types of dimension tables available in data warehouse?
16-Sep-2021
What are characteristics and advantages of stored procedure?
10-Sep-2021

Facebook User: