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

 

Einführung in PowerQuery Teil 2 von ?

thIn diesem Eintrag möchte ich das Tutorial in der Einführung in PowerQuery weiterführen. Hier geht es um die Gruppierungsfunktion und das Verbinden zweier Tabellen. Hier werden wir eine Excel Datei auslesen mit 2 Tabellenblättern – eins hält die Bestellungen für das Jahr 2014 und das zweite Blatt die Informationen zu den Kunden. Ziel ist die Auswertung der Umsätze pro Kunde und pro Monat. Normalerweise würde ich das in PowerPivot lösen, aber für eine einfache Aufbereitung der Daten und deren Darstellung in diesem Kontext bietet PowerQuery eine etwas leichtere Methode – da hier gleich die Daten zum Schluss gruppiert zur Verfügung stehen und lediglich dargestellt werden müssen. Vielleicht aber auch Geschmackssache …

pq_1Als erstes wechseln wir auf das Ribbon PowerQuery und laden Daten aus einer Datei, bzw. gleich Excel.

pq_2Im nächsten Schritt befindet sich im Navigator die jeweilige Tabellen aus dem Excel Sheet. Einerseits sehen wir hier die Tabellenblätter aber auch die Tabellen die sich auf den Blättern befinden. Da sich hier jeweils eine Tabelle auf jedem Blatt befindet, wähle ich die Tabellenblätter aus. Hier setze ich gleich die Haken bei beiden, wähle aus, dass die Daten in das Datenmodell (PowerPivot) geladen werden.

Weiterhin wähle ich die erste Tabelle Bestellungen aus und klicke auf Bearbeiten.

 

 

 

 

 

pq_3

Nun muss in beiden Tabellen jeweils die erste Zeile noch als Tabellenüberschrift gekennzeichnet werden. Nachdem dies bei den Bestellungen durchgeführt wurde, klicke ich auf Anwenden und schließen und wähle die Tabelle Kunden im Navigator aus und Bearbeite diese genauso:

pq_3_4Nachdem die Kundentabelle ebenfalls die Überschriften aus der ersten Zeile entnimmt, schließe ich ebenfalls das Fenster über Anwenden und schließen und klicke im Navigator auf Laden damit beide Excel Tabellen in PowerQuery verfügbar sind.

Nun müssen beide Tabellen miteinander verbunden werden …

Dazu wähle ich aus dem Navigator die Kunden Tabelle aus und klicke auf Abfragen zusammenführen:

pq_4

 

 

Nun sind die Tabellen verbunden – technisch ist das wie ein Left Join zu sehen. Über den Haken „nur Übereinstimmende Zeilen einbeziehen“ würde das einem Inner Join entsprechen.

pq_5Nun können die Spalten aus der rechten Tabellen hinzugefügt werden.

pq_6

Da ich hier lediglich die Bestellungen aus dem Jahr 2014 vor mir habe und das Ziel eine Auswertung pro Monat ist, transformiere ich die Spalte Bestelldatum über einen Rechtsklick in Monate um. Danach steht das Feld als Monatsnummer zur Verfügung.

Die Transformation kann nachträglich immer noch wieder zurückgestellt werden. An sich handelt es sich nur um eine Formatierung.

 

 

Im nächsten Schritt brauchen wir eine Gesamtsumme. In den Daten steht derzeit nur die Summe der Einzelposition und die Menge zur Verfügung. Daher fügen wir hier nun eine Benutzerdefinierte Spalte ein mit folgender Formel:pq_7

 

pq_8Nun markiere ich noch die nicht mehr benötigten Spalten und entferne diese:

 

pq_9Nun markiere ich die Spalten Name und Monat und klicke auf Gruppieren Nach – nun erfolgt die Gruppierung, bzw. Summierung auf den Gesamtpreis.

pq_10

Nach dem Klick auf Anwenden und Schließen befindet sich im PowerPivot Datenmodell die gruppierte und aufbereitete Tabelle. Diese kann ich nun in Excel darstellen….

 

Einführung in PowerQuery, Teil 1 von ?

thIn diesem Blog Eintrag möchte ich eine kurze Einführung/Tutorial in das Thema PowerQuery geben. Das ganze wird eine Serie, Teil 1 von ? – ganz in der Manier des Report Viewers. Schauen wir mal wieviele es werden.

In diesem Beitrag schauen wir uns den Import von Daten aus dem Web an und wie die Daten verarbeitet, transformiert und in das Modell geladen werden können. Weiterhin werden 2 Tabellen mit einander verbunden und als eine Liste später in Excel dargestellt. Soviel zum Ziel…

2014-06-28_21-19-21Starten wir auf einer Webseite. Dort liegt eine fiktive Preisliste die außerhalb der eigenen Umgebung liegt. D.h. diese Liste steht weder auf internen SQL Servern o.ä. zur Verfügung und liegt auch nicht unter der eigenen Kontrolle. Dennoch soll diese Preisliste in Excel bzw. PowerPivot verarbeitet werden. Um an solche Daten zu kommen, bietet sich das kostenlose Excel 2013 Addin „PowerQuery“ an.

2014-06-28_21-20-19In Excel wechseln wir auf den Ribbon Power Query und laden Daten aus dem Web. Dort wird die URL der Webseite eingegeben.

2014-06-28_21-21-12Nach dem Bestätigen werden uns 2 Tabellen angeboten. Auf der Webseite war schon zu sehen das die erste Tabelle die Preisliste darstellt. Wenn man sich nicht sicher ist, fährt man mit der Maus über die Einträge und bekommt dort schon eine Vorschau.

Mit einem Klick auf Bearbeiten wechselt man in ein eigenes PowerQuery Fenster um die Verarbeitungsoptionen festzulegen.

2014-06-28_21-24-03In dem Bereich „Angewendete Schritte“ wird eine Historie geführt. Das erste nach dem Laden ist immer das setzen, dass die erste Zeile die Überschriften der Zellen beinhaltet. Jegliche Schritte können hier manuell gelöscht werden. Da die Spalte Rabatt leider die Daten mit Punkten anstatt einem Komma in der Fließkomma Zahl trennt, muss dies beim Import geändert werden, da ansonsten dies in Excel als Tausender Trennzeichen interpretiert wird. Weiterhin muss das Prozentzeichen entfernt werden. Beim Preis wurde schon automatisch das Euro Zeichen entfernt. Über den Bereich Werte Ersetzen werden diese Änderungen vorgenommen.

2014-06-28_21-24-572014-06-28_21-25-29Weiterhin wird die Spalte noch als Datentyp Zahl definiert. Der ETL Prozess ist somit eigentlich abgeschlossen. Über den Bereich „Einstellungen laden“ können nun die Daten nach Excel und/oder PowerPivot (via in Datenmodell laden) übergeben werden.

2014-06-28_21-32-59Nun haben wir aber noch eine zweite Preisliste in einem anderen Format die hier dieser Liste angefügt werden soll. Ein Feature was in PowerPivot schonmal nicht möglich wäre, da wir so nur zwei Tabellen bilden könnten. Diese Preisliste unterscheidet sich in den Spalten. Die Rabatt Spalten fehlen völlig und die Beschreibung wie auch die Preisspalte sind auch noch anders benannt. Dennoch importieren wir einfach über PowerQuery die Daten:

2014-06-28_21-34-212014-06-28_21-35-14

 

 

 

 

 

 

 

Hier benennen wir erstmal die Spalten so wie in der vorherigen Tabelle indem man mit einem Doppelklick auf die Überschriften der Spalten klickt und die vorherigen Werte überschreibt.

2014-06-28_21-37-30Nun fügen wir einfach die beiden fehlenden Spalten noch der Tabelle hinzu damit die beiden Tabellen das gleiche Format/Modell haben. Als Standardwert tragen wir hier einfach eine 0 ein.

2014-06-28_21-40-37Nun nutzen wir das Feature „Abfrage anfügen“. Hier wählen wir die vorherige in PowerQuery verarbeitete Tabelle aus und bestätigen das Fenster mit OK.

Nun sind beide Tabellen in einer zusammengeführt. Über den Aktualisieren Knopf werden auch beide Tabellen aktualisiert, d.h. aus zwei Schritten wird einer.

2014-06-28_21-41-41Wenn nun die Daten nach PowerPivot oder Excel übergeben werden, liegen die Daten in einer Tabelle.

Fazit:
Mächtiges Tool was teils komplexe ETL Prozesse in den Self Service BI Bereich bringt und Anwendern die Möglichkeit bietet, Internet Daten schnell und einfach ohne IT zu verarbeiten.

 

 

 

 

 

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

Power BI mit Office 365 – Preview kann genutzt werden

1Power BI mit Office 365 ist nun in der Preview verfügbar. Dafür kann man sich mit einer eigenen Office 365 Farm anmelden und die Funktionalitäten testen. DAzu zählt auch die Power BI App im Windows 8 Store. In Office 365 können dann nach Zuordnung der BI Lizenz die Power BI Biblitotheken eingerichtet werden. Die RSS Schnittstelle in die lokalen Umgebungen können ebenfalls getestet werden. Eine Menge Biespiel PowerViews stehen auch zur Verfügung. Die Preview kann 6 Monate kostenlos genutzt werden.

PowerPivot: Zählen von Datensätzen die dem anderen gleichen

PowerPivot_LogoFolgendes Problem: Ich habe eine flache und breite Tabelle, in der ich zählen möchte, wieviele Datensätze noch existieren, die dem anderen gleichen aufgrund eines Kriteriums. In TSQL eigentlich in einem Query kein Problem, wenn man ein einfaches Subselect in die Feldliste einfügt, in PowerPivot ist das etwas anders. Dort heißt das Zauberwort ALLEXCEPT.

Folgendes Beispiel:
Eine Tabelle mit Umsätzen die Kunden und Verkäufern zugeordnet sind:

1

Mit einem Measure lässt sich relativ simpel zählen, wieviele Umsätze der jeweilige Kunde gemacht hat. Schwieriger wird es aber, wenn ich das einerseits zählen möchte, und den Kunden gleich ein sortieren möchte, ob er mit der Menge an Umsätzen ein guter oder ein „schlechter“ Kunde ist. Für diese Gruppieren nutze ich noch folgende Tabelle:2

Nun müssen in einer berechneten Spalte die Anzahl der Umsätze gezählt werden, dafr folgender DAX Befehl:

=CALCULATE(COUNTROWS(Daten); ALLEXCEPT(Daten; Daten[Kunde]))

Zum Zuordnen der Gruppen noch folgende eigene Spalte (man könnte dies auch zusammenführen in eine Spalte, aber aus Übersichtlichkeits-Gründen mache ich dies selten; insbesondere in einem Blog Eintrag):

=CALCULATE(VALUES(Kundengruppe[Kundengruppe]); FILTER(Kundengruppe; Daten[Anzahl Umsatze pro Kunde] >= Kundengruppe[Anzahl Umsaetze Min] && Daten[Anzahl Umsatze pro Kunde] <= Kundengruppe[Anzahl Umsaetze Max]))

Folgendes Ergebnis:

3

Mit einem DistinctCount auf das Attribut Kunde kann man das nun wunderbar pivotieren:

4