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

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


Change Data Capture – Part I

May 31, 2009

With the arrival of SQL Server 2008 we gained access to a new feature called Change Data Capture (CDC). CDC enables you to constantly stream change data to a consuming process, most notably Extract, Transform, and Load (ETL) applications. In the SQL Server environment the ETL application used is of course SQL Server Integration Services (SSIS). 

the first obvious constraint in regard to SQL Server 2008’s CDC feature is that it is only available to SSIS applications that source their data from an OLTP system built on SQL Server 2008. In the field of implementing Microsoft BI there are many relational database systems we have to source data from that are not SQL Server based (and if they are SQL Server based there is a good chance it is not using the 2008 version). All major relational database systems use some form of a transaction log to record the history of its actions in case of rollback or hardware failure. I would imagine that the actual contents of each database vendor’s log is different, however they have the same basic requirement to temporarily persist database actions.

So how to enable Change Data Capture on a database, and on a table, and how to keep track of Data Definition Language changes on a table.

Step 1

Let’s create a database named SqlHero as shown below.

USE [master]
GO

/*** Object:  Database [SqlHero]   ***/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'SqlHero')
DROP DATABASE [SqlHero]
GO
USE [master]
GO

/*** Object:  Database [SqlHero]   ***/
CREATE DATABASE [SqlHero]
GO

Step 2

Now let’s create a table named MyTable on the SqlHero database, as shown Below.

USE [SqlHero]
GO

/*** Object:  Table [dbo].[MyTable]   ***/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]')
	AND type in (N'U'))
DROP TABLE [dbo].[MyTable]
GO
USE [MyDataBase]
GO

/*** Object:  Table [dbo].[MyTable]   ***/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[MyTable](
	[ID] [int] NOT NULL,
	[Name] [varchar](100) NULL,
 CONSTRAINT [MyTable_PK] PRIMARY KEY CLUSTERED
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
	IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
	ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Step 3

In order to track changes on the table, we need to enable the Change Data Capture feature on the database. We can enable the Change Data Capture feature using the following Transact SQL command, as shown below.

Until this point, the only schema that exists on the database is dbo. Once we enable Change Data Capture, a new schema with a bunch of objects will be created.

USE [SqlHero]
GO
EXEC sys.sp_cdc_enable_db_change_data_capture
GO

The following CDC tables are created under the CDC schema, as shown below

cdc.captured_columns
cdc.change_tables
cdc.ddl_history
cdc.index_columns
cdc.lsn_time_mapping

When you query these tables, you will see only zero number of rows.

select * from cdc.captured_columns
select * from cdc.change_tables
select * from cdc.ddl_history
select * from cdc.index_columns
select * from cdc.lsn_time_mapping

Result

(0 row(s) affected)
 (0 row(s) affected)
(0 row(s) affected)
 (0 row(s) affected)
 (0 row(s) affected)

 

Step 3

In order to track changes on the table, we need to enable the Change Data Capture feature on the table as well. Let’s enable the Change Data Capture feature using the following Transact SQL command, as shown below.

USE [SqlHero]
GO
EXEC sys.sp_cdc_enable_table_change_data_capture
@source_schema = 'dbo',
@source_name = 'MyTable',
@role_name = 'cdc_MyTable'
GO

Result

Job 'cdc.MyDataBase_capture' started successfully.
Job 'cdc.MyDataBase_cleanup' started successfully.

 

By reading the result, we can easily understand that SQL Server Agent is a must to do the capture and cleanup. We can see these jobs are actually created as SQL Server Scheduled jobs. [Refer Fig 1.6, 1.7]

Now let’s execute the following Transact SQL to see if any data has been inserted on any of the CDC tables.

select * from cdc.captured_columns
select * from cdc.change_tables
select * from cdc.index_columns

Result

object_id, column_name, column_id, column_type, column_ordinal, is_computed
389576426, ID, 1, int, 1, 0
389576426, Name, 2, varchar, 2, 0

(2 row(s) affected)

object_id,  version,  source_object_id, capture_instance, start_lsn, end_lsn, supports_net_changes, has_drop_pending,
	role_name, index_name, filegroup_name, create_date
389576426, 0, 53575229, dbo_MyTable, NULL, NULL, 0, NULL, cdc_MyTable, MyTable_PK, NULL,
(1 row(s) affected)

object_id, column_name, index_ordinal, column_id
389576426, ID, 1, 1

(1 row(s) affected)

You can see that the CDC schema stores metadata information about which tables and columns are being tracked by Change Data Capture. It also stores information about what Index the table that has been tracked has.

Step 4

We can check to see if Change Data Capture is enabled on a database by using the following transact SQL statement.

SELECT is_cdc_enabled FROM sys.databases WHERE name = 'SqlHero'

Result

is_cdc_enabled
--------------
1

(1 row(s) affected)

Step 5

We can check to see if Change Data Capture is enabled on a table, by using the following transact SQL statement.

SELECT is_tracked_by_cdc FROM sys.tables WHERE name = 'MyTable'

Result

is_tracked_by_cdc
-----------------
1

(1 row(s) affected)

Step 6

Now let’s make some changes to table structure and see if Change Data Capture captures the changes. Execute the following query as shown below.

USE [SqlHero]

GO

Alter Table MyTable add Address varchar(500)

GO

Alter Table MyTable add Salary money

GO

Alter Table MyTable add Bonus money

GO

 

Query the cdc table ddl_history as shown below.

select * from cdc.ddl_history

Result

source_object_id, object_id, required_column_update, ddl_command, ddl_lsn, ddl_time
53575229, 389576426, 0, Alter Table MyTable add Address varchar(500)
, 0x0000001C000001350001,
53575229, 389576426, 0, Alter Table MyTable add Salary money
, 0x0000001C000001370018, 53575229, 389576426, 0, Alter Table MyTable add Bonus money
, 0x0000001C0000013D0018,
(3 row(s) affected)


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