How to create time Dimension Table using T-SQL Query between two years

June 16, 2009

How to create time Dimension Table using T-SQL Query from year 2000 to 2029

My Issue

  • I try to create a new olap solution  using  Microsoft sqlserver 2008 and I need to create the time dimension table  by using the time dimension in adventure works  but I find that  it’s from year 2000 to 2007 and we are now 2009

Solution

 

  • I Write a simple query to create the time dimension table  frbetween two years

The Query

 

CREATE TABLE dimDate

(

dateID int IDENTITY(1,1) PRIMARY KEY NOT NULL,

fullDate datetime NOT NULL,

dayNumOfWeek tinyint NOT NULL,

dayNameOfWeek char(9) NOT NULL, — dayName is reserved word

dayNumOfMonth tinyint NOT NULL,

dayNumOfYear smallint NOT NULL,

monthNumOfYear tinyint NOT NULL,

monthNameOfYear char(12) NOT NULL,

monthOfQuarter tinyint NOT NULL,

calendarQuarter tinyint NOT NULL,

calendarSemester tinyint NOT NULL,

calendarYear char(4) NOT NULL

)

–*/

DECLARE @maxDateAlreadyLoaded datetime

DECLARE @dayNumOfWeek tinyint

DECLARE @dayNameOfWeek char(9) — dayName is reserved word

DECLARE @dayNumOfMonth tinyint

DECLARE @dayNumOfYear smallint

DECLARE @monthNumOfYear tinyint

DECLARE @monthNameOfYear char(12)

DECLARE @monthOfQuarter tinyint

DECLARE @calendarQuarter tinyint

declare @calendarSemester tinyint

DECLARE @calendarYear char(4)

DECLARE @endDate datetime

— Preload the date by check the data in the table for highest date already

–loaded

IF EXISTS(SELECT fullDate FROM dimDate)

SET @maxDateAlreadyLoaded = (SELECT TOP 1 DATEADD(day,1,fullDate) FROM

dimDate ORDER BY dateID DESC)

ELSE

— here you Initialize the startdate of the Time Table

SET @maxDateAlreadyLoaded = ‘1/1/2000’

— Initialize the endDate to one year beyond the start value

— here you can Initialize the end year from the startdate in these case it will be 2029

SET @endDate = DATEADD(year, 29, @maxDateAlreadyLoaded)

— Initialize loop to load one year worth of data

WHILE (@maxDateAlreadyLoaded < @endDate)

BEGIN

— Calculate each of the fields (simplifies INSERT statement)

SET @dayNumOfWeek = DATEPART(weekDay, @maxDateAlreadyLoaded)

SET @dayNameOfWeek = (SELECT CASE

WHEN DATEPART(weekDay, @maxDateAlreadyLoaded)= 1 THEN ‘Sunday’

WHEN DATEPART(weekDay, @maxDateAlreadyLoaded)= 2 THEN ‘Monday’

WHEN DATEPART(weekDay, @maxDateAlreadyLoaded)= 3 THEN ‘Tuesday’

WHEN DATEPART(weekDay, @maxDateAlreadyLoaded)= 4 THEN ‘Wednesday’

WHEN DATEPART(weekDay, @maxDateAlreadyLoaded)= 5 THEN ‘Thursday’

WHEN DATEPART(weekDay, @maxDateAlreadyLoaded)= 6 THEN ‘Friday’

WHEN DATEPART(weekDay, @maxDateAlreadyLoaded)= 7 THEN ‘Saturday’

END)

SET @dayNumOfMonth = DAY(@maxDateAlreadyLoaded)

SET @dayNumOfYear = DATEPART(dayOfYear, @maxDateAlreadyLoaded)

SET @monthNumOfYear = MONTH(@maxDateAlreadyLoaded)

SET @monthNameOfYear = (SELECT CASE

WHEN MONTH(@maxDateAlreadyLoaded) = 1 THEN ‘January’

WHEN MONTH(@maxDateAlreadyLoaded) = 2 THEN ‘February’

WHEN MONTH(@maxDateAlreadyLoaded) = 3 THEN ‘March’

WHEN MONTH(@maxDateAlreadyLoaded) = 4 THEN ‘April’

WHEN MONTH(@maxDateAlreadyLoaded) = 5 THEN ‘May’

WHEN MONTH(@maxDateAlreadyLoaded) = 6 THEN ‘June’

WHEN MONTH(@maxDateAlreadyLoaded) = 7 THEN ‘July’

WHEN MONTH(@maxDateAlreadyLoaded) = 8 THEN ‘August’

WHEN MONTH(@maxDateAlreadyLoaded) = 9 THEN ‘September’

WHEN MONTH(@maxDateAlreadyLoaded) = 10 THEN ‘October’

WHEN MONTH(@maxDateAlreadyLoaded) = 11 THEN ‘November’

WHEN MONTH(@maxDateAlreadyLoaded) = 12 THEN ‘December’

END)

SET @monthOfQuarter = (SELECT CASE

WHEN MONTH(@maxDateAlreadyLoaded) IN(1,4,7,10) THEN 1

WHEN MONTH(@maxDateAlreadyLoaded) IN(2,5,8,11) THEN 2

WHEN MONTH(@maxDateAlreadyLoaded) IN(3,6,9,12) THEN 3

END)

SET @calendarQuarter = DATEPART(quarter, @maxDateAlreadyLoaded)

SET @calendarSemester = (SELECT CASE

WHEN MONTH(@maxDateAlreadyLoaded) IN(1,2,3,4,5,6) THEN 1

WHEN MONTH(@maxDateAlreadyLoaded) IN(7,8,9,10,11,12) THEN 2

end)

SET @calendarYear = YEAR(@maxDateAlreadyLoaded)

–/*

— Used the PRINTS to test values as script designed

PRINT ‘———– New Row ———–‘

PRINT ‘@maxDateAlreadyLoaded ‘ + CAST(@maxDateAlreadyLoaded as varchar(20))

PRINT ‘@dayNumOfWeek ‘ + CAST(@dayNumOfWeek as varchar(20))

PRINT ‘@dayNameOfWeek ‘ + CAST(@dayNameOfWeek as varchar(20))

PRINT ‘@dayNumOfMonth ‘ + CAST(@dayNumOfMonth as varchar(20))

PRINT ‘@dayNumOfYear ‘ + CAST(@dayNumOfYear as varchar(20))

PRINT ‘@monthNumOfYear ‘ + CAST(@monthNumOfYear as varchar(20))

PRINT ‘@monthNameOfYear ‘ + CAST(@monthNameOfYear as varchar(20))

PRINT ‘@monthOfQuarter ‘ + CAST(@monthOfQuarter as varchar(20))

PRINT ‘@calendarQuarter ‘ + CAST(@calendarQuarter as varchar(20))

PRINT ‘@calendarYear ‘ + CAST(@calendarYear as varchar(20))

PRINT ‘@endDate ‘ + CAST(@endDate as varchar(20))

PRINT ”

–*/

— Load row into database

INSERT INTO dimDate

(fullDate, dayNumOfWeek, dayNameOfWeek, dayNumOfMonth, dayNumOfYear,

monthNumOfYear, monthNameOfYear, monthOfQuarter, calendarQuarter,

calendarSemester,

calendarYear)

VALUES

(@maxDateAlreadyLoaded, @dayNumOfWeek, @dayNameOfWeek, @dayNumOfMonth,

@dayNumOfYear,

@monthNumOfYear, @monthNameOfYear, @monthOfQuarter, @calendarQuarter,

@calendarSemester,

@calendarYear)

— Increase the date to load by one

SET @maxDateAlreadyLoaded = DATEADD(day, 1, @maxDateAlreadyLoaded)

END

Advertisements

Partition Table in SQL Server 2008

June 13, 2009

1. What is Partition table?

Data is partitioned horizontally by range

Partition Table

2. How to Create Partition Table?

You can create partition table by 2 ways

  1. programmability
  2. wizard in SQL Server 2008 New

But you must understand 2 things before creation Partition Tables

Partition Function: Partition functions define partition boundaries and Boundary values can be assigned to LEFT or RIGHT

clip_image004

Partition Scheme: A partition scheme assigns partitions to filegroups

clip_image006

3. How to Create Partition Table by wizard in SQL Server 2008?

Right click on table then go to storage then select Create Partition as in fig bellow

1

Select a column you want to use it in partition as in fig bellow

2

Create Partition function as in fig bellow

3

Create Partition Scheme as in fig bellow

4

Determine ranges as in fig bellow

5

Create Partition by select run immediately as in fig bellow

6    

Congratulation your table now partitioned

4. How to Create Partition Table programmability?

/***************************************************

1)Create database TestDb

****************************************************/

CREATE DATABASE [TestDb] ON PRIMARY

( NAME = N’TestDb’, FILENAME = N’C:\db\TestDb.mdf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ),

FILEGROUP [fg1]

( NAME = N’f1′, FILENAME = N’C:\db\f1.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ),

FILEGROUP [fg2]

( NAME = N’f2′, FILENAME = N’C:\db\f2.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ),

FILEGROUP [fg3]

( NAME = N’f3′, FILENAME = N’C:\db\f3.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ),

FILEGROUP [fg4]

( NAME = N’f4′, FILENAME = N’C:\db\f4.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N’TestDb_log’, FILENAME = N’C:\db\TestDb_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)

GO

USE [TestDb]

GO

/***************************************************

3)Create Partition Function

****************************************************/

Create Partition Function pf_Date(Date)

as range right

For values(’01/01/2006′,’01/01/2007′,’01/01/2008′)

go

/***************************************************

3)Create Partition scheme

****************************************************/

Create Partition Scheme ps_Date

AS Partition pf_Date

to(fg1,fg2,fg3,fg4)

go

/***************************************************

3)Create Partition Contact Table

****************************************************/

CREATE TABLE [dbo].[Contact](

[ContactId] [int] IDENTITY(1,1) NOT NULL,

[FullName] [nvarchar](50) NULL,

[CreationDate] [date] NULL

)on ps_Date(CreationDate)

GO

/***************************************************

4)Insert rows to table

****************************************************/

insert into dbo.Contact values(‘amosa’,’01/01/2006′)

insert into dbo.Contact values(‘mmosa’,’01/01/2007′)

insert into dbo.Contact values(‘zemo’,’01/01/2008′)

insert into dbo.Contact values(‘test1′,’05/01/2005’)

insert into dbo.Contact values(‘test2′,’05/01/2006’)

insert into dbo.Contact values(‘test3′,’05/01/2007’)

insert into dbo.Contact values(‘test4′,’05/01/2008’)

insert into dbo.Contact values(‘test5′,’05/01/2009’)

go

/***************************************************

5)Query Table to know where did rows save

****************************************************/

Select *,$partition.pf_Date(CreationDate)FileGroupNo From dbo.Contact


Be Ready for Microsoft SQL Server 2008 R2

June 4, 2009

to get more information about Microsoft SQLServer 2008 R2 you can visit SQLServer2008 R2


SQL Server 2008 Compare Edition Features

June 4, 2009

to find full comparison of key capabilities between SQL Server 2008 Enterprise, Standard, Workgroup, Web and Express editions

Just click on Microsoft


SQLHero June Session – Microsoft Egypt

June 4, 2009

SQLHero delivered June Session in Business Intelligence Scenarios (SQL 2008)

Topic Business Intelligence Scenarios (SQL 2008)
Date June 4, 2009
Time 10:00 – 3:00
Location Microsoft, Smart Village
Presenter Ahmed Mosa
Presentation download


Ranking Functions

May 31, 2009

Introduction

Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.

Transact-SQL provides the following ranking functions:

RANK :The rank of a row is one plus the number of ranks that come before the row in question.

NTILE :The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

DENSE_RANK :The rank of a row is one plus the number of distinct ranks that come before the row in question.

ROW_NUMBER : Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Example

 

USE AdventureWorks;

GO

SELECT c.FirstName, c.LastName

      –Each Row Take row number based on postal code

    ,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS ‘Row Number’

    –Each same postalcode take same rank then next postalcode take (Total count of same PostalCode rows)+1

    ,RANK() OVER (ORDER BY a.PostalCode) AS ‘Rank’

    –Each same postalcode take same rank then next postalcode take rank+1

    ,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS ‘Dense Rank’

    –divide Total Result Sets to 4 groups

    ,NTILE(4) OVER (ORDER BY a.PostalCode) AS ‘Quartile’

    ,s.SalesYTD, a.PostalCode

FROM Sales.SalesPerson s

    INNER JOIN Person.Contact c

        ON s.SalesPersonID = c.ContactID

    INNER JOIN Person.Address a

        ON a.AddressID = c.ContactID

WHERE TerritoryID IS NOT NULL

    AND SalesYTD <> 0;

result will be

FirstName

LastName

Row Number

Rank

Dense Rank

Quartile

SalesYTD

PostalCode

Maciej

Dusza

1

1

1

1

4557045

98027

Shelley

Dyck

2

1

1

1

5200475

98027

Linda

Ecoffey

3

1

1

1

3857164

98027

Carla

Eldridge

4

1

1

1

1764939

98027

Carol

Elliott

5

1

1

2

2811013

98027

Jauna

Elson

6

6

2

2

3018725

98055

Michael

Emanuel

7

6

2

2

3189356

98055

Terry

Eminhizer

8

6

2

3

3587378

98055

Gail

Erickson

9

6

2

3

5015682

98055

Mark

Erickson

10

6

2

3

3827950

98055

Martha

Espinoza

11

6

2

4

1931620

98055

Janeth

Esteves

12

6

2

4

2241204

98055

Twanna

Evans

13

6

2

4

1758386

98055


Recursive using Common Table Expressions (CTE)

May 29, 2009

Introduction

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to

  • Create a recursive query (Current Example)
  • Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
  • Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
  • Reference the resulting table multiple times in the same statement.

    Syntax of CTE

    with CTE_Name(Col1,Col2,Col3..)
    as (
    –Anchor Query
    Union All
    –Recursive member is defined referencing cte_name
    )
    Select * From CTE_Name

    Example

    use AdventureWorks

    go

     

    with CTE_EmployeeLevel(EmployeeId,FullName,ManagerId,ManagerName,EmpLevel)

    as

    (

    select

    EmployeeID,

    (Select FirstName +‘ ‘+LastName From Person.Contact where Person.Contact.ContactID= EmployeeID)FullName,

    ManagerID,

    (Select FirstName +‘ ‘+LastName From Person.Contact where Person.Contact.ContactID= ManagerID)ManagerName,

    1

    From HumanResources.Employee

    where ManagerId Is Null

     

    union all

     

    Select

    tbEmp.EmployeeID,

    (Select FirstName +‘ ‘+LastName From Person.Contact where Person.Contact.ContactID= tbEmp.EmployeeID)FullName,

    tbEmp.ManagerID,

    (Select FirstName +‘ ‘+LastName From Person.Contact where Person.Contact.ContactID= tbEmp.ManagerID)ManagerName,

    EmpLevel+1

    From HumanResources.Employee tbEmp inner join CTE_EmployeeLevel CTEEmp

    on tbEmp.ManagerID=CTEEmp.EmployeeId

    )

     

    Select * From CTE_EmployeeLevel

    Example Code Walkthrough

    when Anchor query run the result set will be

    EmployeeId

    EmployeeName

    ManagerId

    ManagerName

    EmpLevel

    Stephanie Bourne

    109

    NULL

    NULL

    1

    then We will take Employee Id from above result set then join it with employee id from Employee table

    Then the result will be

    EmployeeId

    EmployeeName

    ManagerId

    ManagerName

    EmpLevel

    6

    Frances Adams

    109

    Stephanie Bourne

    2

    we will repeat step before again Then We will take Employee Id from above result set then join it with employee id from Employee table

    Then the result will be

    EmployeeId

    EmployeeName

    ManagerId

    ManagerName

    EmpLevel

    2

    Catherine Abel

    6

    Frances Adams

    3

    46

    Stephen Ayers

    6

    Frances Adams

    3

    106

    Corinna Bolender

    6

    Frances Adams

    3

    119

    Christopher Bright

    6

    Frances Adams

    3

    we will repeat step before again Then we will take Employee Id from above result set then join employee id from Employee if no result

    CTE start join all result set to be

    EmployeeId

    EmployeeName

    ManagerId

    ManagerName

    EmpLevel

    Stephanie Bourne

    109

    NULL

    NULL

    1

    6

    Frances Adams

    109

    Stephanie Bourne

    2

    2

    Catherine Abel

    6

    Frances Adams

    3

    46

    Stephen Ayers

    6

    Frances Adams

    3

    106

    Corinna Bolender

    6

    Frances Adams

    3

    119

    Christopher Bright

    6

    Frances Adams

    3