Total Page Preview:   000000001396

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 :





Recent Posts

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

Facebook User: