Organize Data Warehouse Objects

by Amol 17. March 2011 08:35

Organize Data Warehouse Objects using schema.

Since my last data warehouse project I started using schemas to identify the data warehouse objects in SQL Server database. Earlier I was using suffix or prefix to identify data warehouse objects.  For example DimCustomer, DimProducts for dimension tables and SaleFact or FactSales for fact tables. This is also one of the practices we usually follow in any data warehouse project.

Microsoft introduced schemas in SQL Server, after that I realize this could be another best approach to organize data warehouse objects. So decided to use schemas for different objects in databases according to their role. The objects above would then rename as Dim.Customer, Dim.Products. So all dimesion tables built with schema named "Dim". Similarly schema "Fact" is created to for all fact tables. Then fact tables would rename as Fact.Sales.

Apart from dimension and fact, there are other objects as well so I have started to follow schema based objects and created below schemas to create different kind of objects.

Schema Pupose

Dim schema used for all the table which are dimensions in the nature.

Fact Fact schema used for all the table which are facts in the nature.
ETL ETL schema is used for all the objects which are used in ETL.
Stg Stg schema is used for all the objects which are used for staging purpose.
Report Report schema is used the objects which are used for reporting purpose.

There are some advantages of using schemas for objects.

  • Schema based security can be implemented easily.
  • Under SSMS, objects are getting sorted alphabetically by schema so its become more handy to find the objects.

Tags: ,

Data Warehouse

Add comment

  Country flag
  • Comment
  • Preview

Tag cloud