SYNOPTIC DESIGNER FOR POWER BI – CUSTOM VISUALS

Mit dem Synoptic Designer für Power BI können individuelle Grafiken erstellt und integriert werden. Hierfür nutzen ich den Power BI Designer (Desktop Version)  für Windows.
[Download: https://powerbi.microsoft.com/de-de/desktop/]

Aufbau

In diesem Artikel möchte ich demonstrieren wie ein Fußballfeld in einen Bericht integriert wird, um Statistiken aus einem Fußball-Spiel visuell besser darstellen zu können.

Das Spielfeld ist schnell selbst gezeichnet oder aus dem Internet geladen und auf der Synoptic Webseite [https://synoptic.design/ ] wird dies eingefügt und die Bereiche werden markiert:1

Daten

Die korrespondierende Excel Tabelle mit den Daten ist folgendermaßen aufgebaut:

2

Die Spalte „Area“ gibt auch den Namen im Designer, dadurch erfolgt die Zuordnung der Daten auf das Spielfeld, bzw. die Grafik.

Integration

Im Designer exportiert man nun die Grafik mit den Markierungen via „Export to Power BI“ als SVG Datei.

Zuerst wird aber die Excel Tabelle in den Power BI Designer importiert:

3

Anschließend erstellen wir noch folgende Measures zum zählen des Spielstandes:

  1. Tore Team B = CALCULATE(COUNTA(Spieldaten[Aktion]); Spieldaten[Aktion]=“Tor“; Spieldaten[Team] =“B“)
  2. Tore Team A = CALCULATE(COUNTA(Spieldaten[Aktion]); Spieldaten[Aktion]=“Tor“; Spieldaten[Team] =“A“)
  3. Tore Gesamt = [Tore Team A]+[Tore Team B]

 

Der nächste Schritt ist, die sogenannten Custom Visuals in den Designer zu verankern, dafür wählt man auf der Webseite „Get the latest Version“ und lädt die Datei herunter. Anschließend wird diese integriert:

4

Nun kann dieses Panel in den Bericht integriert werden.

Design und Aufbereitung

Nachdem das Panel positioniert wurde, werden die Felder aus den Daten eingesetzt:

Area -> Legend

Aktion -> Values

5

 

 

 

 

 

 

 

 

Anschließend wird via „select map“ die heruntergeladene Karte (SVG Datei) ausgewählt.

Nun werden noch weitere Grafiken positioniert und befüllt:

6

 

 

 

 

 

Okay, anschließend noch das Dashboard auf das kostenlose Power BI Konto hochladen und im Web veröffentlichen:
Link

Excel 2013 and the Flash Fill Function (ETL for Beginners ;-) )

Off topic but really good to combine with PowerPivot or PowerQuery: The flash fill function.
With this feature in Excel 2013 you can automatically extract text into columns. It works better than the text in columns feature or Left, Right or Find functions in Excel or PowerPivot for me.

Example:

This column contains surname, middle names and last names into one column. First you must convert this data into a table:

10-04-_2015_15-01-42

Next add a column “surname” – please do not add the other columns. Sometimes Excel is confused by the other columns…
Then type in the new column the first surname “Franz”. In the next row start typing the next surname “Karl”. Excel writes automatically the other names into the cells. In this case you can also use the Flash Fill Function in the Ribbon “Data” – but this function will not work by the middle names.

10-04-_2015_15-04-55

 

 

 

 

 

 

 

Now add a new column middle name and type in the first data cell the first middle name “Dieter”. If your data have no double name in the first rows, sometimes you must type much more names if Excel use the Flash Fill Function.

10-04-_2015_15-05-40

 

 

 

 

 

 

 

 

In the last row you can see the surname in the middle name column. If you have middle names or data with more text parts it is sometimes not perfect! The last step is to add the last name column. The data has in the first 2 rows some different data – a single last name and double name. So you must type 2 rows till the Flash Fill Function takes effect:

10-04-_2015_15-06-08

Now you can add this table into your PowerPivot or PowerView model. Enjoy this feature!

More information: http://blogs.office.com/2012/08/09/flash-fill/

Wasserfall Diagramme mit PowerPivot

PowerPivot_Logo(english version below)
Nachdem ich bisher Wasserfall Diagramme manuell in Excel erstellt habe, möchte ich hier zeigen, wie das auch in Verbindung mit PowerPivot für Excel funktioniert. Die Datengrundlage ist die gute alte Northwind Datenbank. Um ein Wasserfall Diagramm zu erstellen, benötige ich folgende Informationen:

  • Aktueller Umsatz
  • Umsatz des letzten Monats
    • Verringerung und
    • Vergrößerung des Umsatz
  • Zeit Informationen

Mit folgendem Query lese ich die Daten und löse das Pivot auf, damit ich die Daten besser in PowerPivot verarbeiten kann:

SELECT
 YearNumber
 , MonthNumber 
 , Amount
 , AmountType
 , CASE AmountType
  WHEN 'BaseValue' THEN 1
  WHEN 'Increase' THEN 2
  WHEN 'Reduction' THEN 3
 END As CategorySort
FROM ( SELECT 
  CASE 
   WHEN Data.SalesAmount - Data.SalesAmountLastMonth  < 0 
    THEN (Data.SalesAmount - Data.SalesAmountLastMonth) * -1 
    ELSE 0
   END As Reduction
  , Data.SalesAmountLastMonth - 
   CASE 
   WHEN Data.SalesAmount - Data.SalesAmountLastMonth  < 0 
    THEN (Data.SalesAmount - Data.SalesAmountLastMonth) * -1 
    ELSE 0
   END As BaseValue
  , CASE 
   WHEN Data.SalesAmount - Data.SalesAmountLastMonth  > 0 
    THEN (Data.SalesAmount - Data.SalesAmountLastMonth)  
    ELSE 0
   END As Increase
  , Data.MonthNumber
  , Data.YearNumber
 FROM (
  SELECT
   MONTH(Orders.OrderDate) As MonthNumber
   , YEAR(Orders.OrderDate) As YearNumber
   , SUM(Quantity*UnitPrice) As SalesAmount
   , (
    SELECT SUM(od1.Quantity*od1.UnitPrice) FROM [dbo].[Orders] o1
    INNER JOIN [dbo].[Order Details] od1 ON od1.OrderID = o1.OrderID
    WHERE MONTH(o1.OrderDate) = MONTH(DATEADD(mm, -1, MAX(Orders.OrderDate))) 
    AND YEAR(o1.OrderDate) = YEAR(DATEADD(mm, -1, MAX(Orders.OrderDate)))
   ) As SalesAmountLastMonth
    FROM [dbo].[Orders]
    INNER JOIN [dbo].[Order Details] ON [Order Details].OrderID = Orders.OrderID
    WHERE Year(Orders.OrderDate) = 1997
    GROUP BY 
   MONTH(Orders.OrderDate) 
   , YEAR(Orders.OrderDate)
  ) data
 ) t
 UNPIVOT (Amount for AmountType in (Reduction, BaseValue, Increase)) As Amount
ORDER BY YearNumber, MonthNumber

 

Nachdem diese nun eingelesen wurden sind, habe ich zu jedem Monat 3 Zeilen mit den notwendigen Informationen. Nun lasse ich die jeweilige Kategorie noch sortieren, damit der Basis Wert im Diagramm unten liegt. Übergibt man nun die Daten in ein gestapeltes Diagramm, sieht das folgendermaßen aus:

1

 

2

 

 

3

Nun müssen lediglich folgende Anpassungen gemacht werden:

  • Die Kategorie mit dem Basis Wert ohne Farbe einstellen
  • Verlustwerte rot färben
  • Gewinnwerte grün färben
  • Die Beschriftungen ausblenden

4

Voila! Fertig ist das Wasserfall Diagramm mit Hilfe von PowerPivot.

 

Waterfall Chart with PowerPivot

 

After I’ve created waterfall charts manually in Excel, I want to show here how this works in conjunction with PowerPivot for Excel. The data is from the good old Northwind database.

To create a waterfall chart, I need the following information:

  • Current sales
  • Turnover last month
  • reduction and increase in the sales
  • Time information

With the following query I read the data and make a unpivot so I can better handle the data in PowerPivot:

SELECT
 YearNumber
 , MonthNumber 
 , Amount
 , AmountType
 , CASE AmountType
  WHEN 'BaseValue' THEN 1
  WHEN 'Increase' THEN 2
  WHEN 'Reduction' THEN 3
 END As CategorySort
FROM ( SELECT 
  CASE 
   WHEN Data.SalesAmount - Data.SalesAmountLastMonth  < 0 
    THEN (Data.SalesAmount - Data.SalesAmountLastMonth) * -1 
    ELSE 0
   END As Reduction
  , Data.SalesAmountLastMonth - 
   CASE 
   WHEN Data.SalesAmount - Data.SalesAmountLastMonth  < 0 
    THEN (Data.SalesAmount - Data.SalesAmountLastMonth) * -1 
    ELSE 0
   END As BaseValue
  , CASE 
   WHEN Data.SalesAmount - Data.SalesAmountLastMonth  > 0 
    THEN (Data.SalesAmount - Data.SalesAmountLastMonth)  
    ELSE 0
   END As Increase
  , Data.MonthNumber
  , Data.YearNumber
 FROM (
  SELECT
   MONTH(Orders.OrderDate) As MonthNumber
   , YEAR(Orders.OrderDate) As YearNumber
   , SUM(Quantity*UnitPrice) As SalesAmount
   , (
    SELECT SUM(od1.Quantity*od1.UnitPrice) FROM [dbo].[Orders] o1
    INNER JOIN [dbo].[Order Details] od1 ON od1.OrderID = o1.OrderID
    WHERE MONTH(o1.OrderDate) = MONTH(DATEADD(mm, -1, MAX(Orders.OrderDate))) 
    AND YEAR(o1.OrderDate) = YEAR(DATEADD(mm, -1, MAX(Orders.OrderDate)))
   ) As SalesAmountLastMonth
    FROM [dbo].[Orders]
    INNER JOIN [dbo].[Order Details] ON [Order Details].OrderID = Orders.OrderID
    WHERE Year(Orders.OrderDate) = 1997
    GROUP BY 
   MONTH(Orders.OrderDate) 
   , YEAR(Orders.OrderDate)
  ) data
 ) t
 UNPIVOT (Amount for AmountType in (Reduction, BaseValue, Increase)) As Amount
ORDER BY YearNumber, MonthNumber

After I imported the data, I have 3 lines for each month with the necessary information. Now I let the respective category yet sort, so that the base value is in the chart below. Then we can create a stacked bar chart:
1

 

2

 

 

3

 

Now only the following adjustments must be made​​:
– Set the category to the base value without color
– Reduction is set to red
– Increase is set to ​​green
– Hide the Labels

4

Voila! Finish is the waterfall chart using PowerPivot.

 

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….

 

SSIS: Doppelte Zeilen in einem Daten-Ziel ignorieren, neue aber einfügen

filestreamIn manchen Projekten ist es unumgänglich Daten in ein Data Warehouse in eine Fakten Tabelle zu laden, wo bereits Werte aus der Quelle im Ziel enthalten sind. Somit dürfen nur die neuen Werte eingefügt werden, die aber bereits vorhandenen sind. Da das Konzept Slowly Changing Dimensions hier nicht anwendbar ist, kann man hier das OLE DB Ziel für diese Aktion verwenden. Beim Laden darf aber nicht das schnelle Laden der Tabelle verwendet werden, da so eine große Transaktion fehlschlagen würde, sondern es muss das „einfache Laden“ verwendet werden:

 

Anschliessend können in der Fehlerausgabe die Fehler ignoriert werden:

 

Das ganze hat natürlich zum Nachteil, dass alle Transaktionen so immer einwandfrei funktionieren, obwohl vielleicht andere Fehler sich in den Daten befinden. Dafür sollte man ggf. mit Zählwerten validiieren um sicherzustellen, dass neue Daten geladen wurden. Insbesondere in einer Sales Tabelle sollten normalerweise immer neue Zeilen hinzukommen, ansonsten ist entweder der ETL Prozess fehlerhaft oder das Business und das Projekt sollte insgesamt umgehend fakturiert werden ;-)

Guten Rutsch ins neue Jahr!!