Time Dimension

uhr

In diesem Artikel will ich kurz beschreiben wie man eine Zeit Dimension erstellt und in PowerPivot verwendet. Für eine Datums-Dimension siehe hier.

(English Verison)

Als erstes brauchen wir eine Zeit Tabelle:

CREATE TABLE [dbo].[Dim_Time]
(
[Time] TIME NOT NULL,
[am_pm] CHAR(2) NOT NULL,
CONSTRAINT [PK_Table] PRIMARY KEY ([Time])
)

Mit folgendem Script befallen wir die Tabelle:

DECLARE @time TIME = '00:00:01';
DECLARE @ampm CHAR(2);
DECLARE @Stop BIT = 0;

WHILE @Stop = 0
BEGIN
IF @time < '12:00:00'
BEGIN
SET @ampm = 'am';
END
ELSE
BEGIN
SET @ampm = 'pm';
END

INSERT INTO Dim_Time (Time, am_pm)
VALUES (@time, @ampm);

SET @time = DATEADD(ss, 1, @time);
IF @time = '23:59:59' SET @stop = 1;

END

Nun können wir die Tabelle mit Daten in PowerPivot (oder was auch immer ..) verwenden:

2014-07-27_15-53-28 2014-07-27_15-59-51

 

English Version

In this article I will describe, how to create and use a time dimension in a powerpivot model– how to create and populate a date dimension table take a look here.
First we must create the table with the time data:

CREATE TABLE [dbo].[Dim_Time]
(
[Time] TIME NOT NULL,
[am_pm] CHAR(2) NOT NULL,
CONSTRAINT [PK_Table] PRIMARY KEY ([Time])
)

Now we must populate the table with data:

DECLARE @time TIME = '00:00:01';
DECLARE @ampm CHAR(2);
DECLARE @Stop BIT = 0;

WHILE @Stop = 0
BEGIN
IF @time < '12:00:00'
BEGIN
SET @ampm = 'am';
END
ELSE
BEGIN
SET @ampm = 'pm';
END

INSERT INTO Dim_Time (Time, am_pm)
VALUES (@time, @ampm);

SET @time = DATEADD(ss, 1, @time);
IF @time = '23:59:59' SET @stop = 1;

END

Now we can use the data in PowerPivot (or something else):

2014-07-27_15-53-28 2014-07-27_15-59-51

 

Script zum Erstellen einer Datums-Dimensions Tabelle

PowerPivot_LogoDa ich ständig immer wieder Systeme habe, wo ich keine ausreichende Datums-Dimensions Tabelle vorfinde, bspw. für ein Data Warehouse, habe ich nachfolgendes TSQL Script entwickelt welches mir alle nötigen Felder zurückliefert. Das Script erstellt hier erstmal eine Table Variable und füllt diese, dies sollte man dann durch ein CREATE TABLE austauschen und die Tabelle so erzeugen. Das Script ist teils etwas umfänglich und redundant geschrieben, für aber einen nicht so TSQL Profi ist es so übersichtlich. Gerne dazu auch Feedback!

Frohes Neues Jahr!

Die Tabelle

Das Script

SET NOCOUNT ON;

-- Start Datum ab wann gezählt werden soll
DECLARE @Start DATE = '20000101';
-- Ende Datum bis wann gezählt werden soll
DECLARE @Ende DATE = '20401231';

-- Table Variable; sollte umgestellt werden auf ein CREATE TABLE wenn die Tabelle physisch angelegt werden soll
DECLARE @DateDimension TABLE (
    DateKey INT NOT NULL PRIMARY KEY,
    Tagesdatum DATE NOT NULL,
    TagDerWocheNummer TINYINT NOT NULL,
    Wochentagsname NVARCHAR(10) NOT NULL,
    TagDesMonatsNummer TINYINT NOT NULL,
    MonatsNummer TINYINT NOT NULL,
    Quartal TINYINT NOT NULL,
    QuartalName NVARCHAR(20) NOT NULL,
    KalenderJahr SMALLINT NOT NULL,
    Semester TINYINT NOT NULL,
    QuartalMitJahr NVARCHAR(30) NOT NULL,
    KalenderWoche TINYINT NOT NULL,
    KalenderWocheName NVARCHAR(20) NOT NULL,
    MonatsnummerMitJahr NVARCHAR(20) NOT NULL,
    KalenderWocheMitJahr NVARCHAR(20) NOT NULL,
    Monatsname NVARCHAR(30) NOT NULL,
    SemesterMitJahr NVARCHAR(20) NOT NULL
    );

DECLARE @DateKey INT;
DECLARE @Tagesdatum DATE;
DECLARE @TagDerWocheNummer TINYINT;
DECLARE @Wochentagsname NVARCHAR(10);
DECLARE @TagDesMonatsNummer TINYINT;
DECLARE @MonatsNummer TINYINT;
DECLARE @Quartal TINYINT;
DECLARE @QuartalName NVARCHAR(20);
DECLARE @KalenderJahr SMALLINT;
DECLARE @Semester TINYINT;
DECLARE @QuartalMitJahr NVARCHAR(30);
DECLARE @KalenderWoche TINYINT;
DECLARE @KalenderWocheName NVARCHAR(20);
DECLARE @MonatsnummerMitJahr NVARCHAR(20);
DECLARE @KalenderWocheMitJahr NVARCHAR(20);
DECLARE @Monatsname NVARCHAR(30);
DECLARE @SemesterMitJahr NVARCHAR(20);

DECLARE @inkrement INT = 1

WHILE @Start < @Ende
BEGIN
    
    SET @DateKey = CONVERT(INT, CONVERT(VARCHAR(8), @Start, 112));
    SET @Tagesdatum = @Start;
    SET @TagDerWocheNummer = DATEPART(dw, @Tagesdatum);
    SET @Wochentagsname = DATENAME(dw, @Tagesdatum);
    SET @TagDesMonatsNummer = DATEPART(dd, @Tagesdatum);
    SET @MonatsNummer = MONTH(@Tagesdatum);
    SET @Quartal = DATEPART(qq, @Tagesdatum);
    SET @QuartalName = 'Q ' + CONVERT(VARCHAR, @Quartal);
    SET @KalenderJahr = YEAR(@Tagesdatum);
    SET @Semester = CASE WHEN @MonatsNummer < 6 THEN 1 ELSE 2 END;
    SET @QuartalMitJahr = @QuartalName + ' ' + CONVERT(VARCHAR, @KalenderJahr);
    SET @KalenderWoche = DATEPART(wk, @Tagesdatum);
    SET @KalenderWocheName = 'KW ' + CONVERT(VARCHAR, @KalenderWoche);
    SET @MonatsnummerMitJahr = CONVERT(VARCHAR, @Monatsnummer) + ' ' + CONVERT(VARCHAR, @KalenderJahr);
    SET @KalenderWocheMitJahr = CONVERT(VARCHAR, @KalenderWoche) + ' ' + CONVERT(VARCHAR, @KalenderJahr);
    SET @Monatsname = DATENAME(m, @Tagesdatum);
    SET @SemesterMitJahr = CONVERT(VARCHAR, @Semester) + ' ' + CONVERT(VARCHAR, @KalenderJahr);
    
    -- Auch hier muss eine Anpassung dann auf die physische Tabelle erfolgen
    INSERT INTO @DateDimension
        ( DateKey, Tagesdatum, TagDerWocheNummer, Wochentagsname, TagDesMonatsNummer, MonatsNummer,
          Quartal, QuartalName, KalenderJahr, Semester, QuartalMitJahr, KalenderWoche, KalenderWocheName,
          MonatsnummerMitJahr, KalenderWocheMitJahr, Monatsname, SemesterMitJahr)
        VALUES 
        (@DateKey, @Tagesdatum, @TagDerWocheNummer, @Wochentagsname, @TagDesMonatsNummer, @MonatsNummer,
         @Quartal, @QuartalName, @KalenderJahr, @Semester, @QuartalMitJahr, @KalenderWoche, @KalenderWocheName,
         @MonatsnummerMitJahr, @KalenderWocheMitJahr, @Monatsname, @SemesterMitJahr);
         
    SET @Start = DATEADD(day, @inkrement, @Start);
END

SELECT * FROM @DateDimension