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

MDX - What is Set?

by Kiran 14. March 2011 11:49

A Set is a collection of tuples which defined using same dimensions, both type and number. Set is enclosed within curly braces { and }. following is the example of set based on Adventure Works sample cube.

Tuples ([Customer].[Country].[India]) and ([Customer].[Country].[France]) are from exact same dimension. So collection of such tuples are valid Set.

{ ([Customer].[Country].[India]), ([Customer].[Country].[France]) }

A set can contain zero, one more tuples. A Zero Tuple Set is called as empty set. An enpty set is represented as

{ }

Tags: , ,

SQL Server Analysis Services

MDX - What is Tuple?

by Kiran 14. March 2011 11:34

A Tuple uniquely identifies a cell or section of cube. Tuple is described by one member from each dimension seperated bu comma (,) and its enclosed with parentheses.

Some examples of tuples based Adventure Works sample cube.

([Date].[Calender].[2003].[H1 CY 2003].[Q1 CY 2004], [Customer].[Country].[India])

Tuple represented by single member is called simple tuple and not necessory to be enclosed within parentheses. ([Customer].[Country].[India]) is simple tuple and can be represented by [Customer].[Country].[India]

Tags: , , ,

SQL Server Analysis Services


Recent Posts

Tag cloud