Date Dimension Generator

Following SQL query is to create simple date dimension table and then insert values to the given range.

Create table

CREATE TABLE [dbo].[DimDate](
[Date] [date] NOT NULL,
[Day] [varchar](10) NULL,
[Month] [varchar](10) NULL,
[Year] [int] NULL
) ON [PRIMARY]
GO

Script to generate date


  DECLARE @startDate date;
  DECLARE @endDate date;

  SET @startDate = '2017-01-01';
  SET @endDate = '2018-12-31';

 
  WHILE(@startDate <= @endDate)
            BEGIN
                        INSERT dbo.DimDate
                        SELECT
CONVERT(Datetime, @startDate, 120) [Date],
DATENAME(DW,@startDate) [Day],
DATENAME(MM,@startDate) [Month],
DATEPART(YY,@startDate) [Year]

                        SET @startDate = DATEADD(DD, 1, @startDate )
            END

Dimensional modeling

After working with .NET and SharePoint technologies for long time, I started to look in BI Analytics area. This article is to give an overview of data warehouse, dimensional modeling, and fact constellation. First of all we will see what is data warehouse and data mart.

Data warehouse and data mart

Data mart is a subset of data ware house where it represents a specific business scope. Multiple data marts together create a data warehouse. In a typical organization, there are multiple departments focusing different business objectives such as IT, administration, finance, manufacturing, HR, etc. each department data can be considered as data marts and altogether it will come under a data ware house.

Data marts often holds only one subject are as explained in the above figure. Also it may holds more summarized data. Data mart focused on a dimensional model using star schema. Whereas data warehouse holds multiple subject areas in very detailed manner. It works to integrate all data sources. Data warehouse does not necessarily use a dimensional model but feeds dimensional models.

Start schema and snow flake

Start schema and snowflake are two different ways to organize data marts and data warehouse using relational database. Both methods use dimension tables to describe aggregate data in fact tables.

Star schema has single fact table connected to multiple dimension tables and it visualize as star. In this model only one link establishes the relationship between the fact table and any of the dimension tables. Following figure explains the design of the star schema.

Snowflake schema can be seen as the extension of star schema. Here large dimension tables are normalized into multiple sub dimension tables. Every dimension table in this model is associated with sub dimension table and multiple links. Following figure explains the design of the snowflake schema.