PBi_2_Uitbreiding Oefening 1

1. Download de file ‘Sales.csv‘ die je HIER vindt en bewaar hem op jouw lokale machine.
2. Open een nieuw PowerBi document.

3. Trek de file binnen in PowerBI en ga naar de Query Editor.
4. Bekijk welke dimensies je in deze tabel kan onderscheiden om jouw datamodel op te bouwen.

Als we deze tabel in detail bekijken zien we dat dit een heel vlakke (onoverzichtelijke) tabel is, maar we kunnen hier verschillende Dimension Tables uit halen en een Fact Table. Dimension Tables of Look-Up tabellen bevatten beschrijvende informatie die een antwoord kunnen geven op de ‘WIE’, ‘WAT’, ‘WAAR’, ‘WANNEER’, ‘WAAROM’, ‘HOE’-vragen.

De Dimension Tables bevatten geen duplicaten zodat er altijd een ‘One-to-Many’-relatie gelegd kan worden met de Fact Table.

We plaatsen deze Dimension Tables dan rond onze Fact Table zodat er een ster wordt gevormd.

Dit zorgt ervoor dat ons datamodel schaalbaar en overzichtelijk wordt. Het maakt het gebruik van DAX-formules ook veel simpeler.

Elke rij bevat een transactie die plaatsvond door een bepaald persoon.

CONCEPTUELE MODEL

Het conceptuele model zou hier dus kunnen lijken op dit:

OPSPLITEN IN POWERBI

We zullen hierbij verschillende stappen ondernemen:

1. Benoem jouw tabel ‘FactSales‘.
2. Ga naar de ‘Toegepaste stappen‘ en kijk welke transformaties PBI al ongevraagd voor ons gedaan heeft en of deze correct zijn. De ‘Promoted Headers’ is OK, maar je zal zien dat hij van de ZipCode een numerieke waarde (Int64.Type) heeft gemaakt. Dit zorgt ervoor dat Zipcodes die met een 0 starten foutief weergegeven gaan worden, want deze 0 wordt dan weggehaald.

3. Verander dit datatype van ‘Geheel getal’ naar ‘Text’. Je kan dit doen via het pictogram ‘123’ in de hoofding van de kolom als je op de toegepaste stap ‘Changed Type’ staat. Hij vraagt je dan of hij de stap moet vervangen of een nieuwe moet toevoegen. Klik hier op ‘Vervang huidige‘.

4. Maak 3 duplicaten van jouw FactSales. Deze gaan we gebruiken om onze verschillende Dimension tables aan te maken. We gaan ons concentreren op DimProduct, DimCustomer en DimGeography en geven onze entiteiten de betreffende namen.

DIMPRODUCT

1. Selecteer de juiste querie: DimProduct.
2. Behoud enkel de kolommen die je wilt behouden. Doe dit via het tabblad ‘Home‘ > groep ‘Manage Columns‘ > opdrachtknop ‘Choose Columns‘.

3. Duid de attributen ‘ProductID‘, ‘Product‘, ‘Category‘, ‘Segment‘, ‘Unit Cost‘ en ‘Unit Price‘ aan om te behouden.

We hebben nu enkel de kolommen behouden die gerelateerd zijn aan ‘Producten’.

Als we ons Kolomprofiel bekijken zien we hier nog steeds één rij voor elke verkooptransactie, m.a.w. we zitten hier nog met onze originele 675.368 rijen. Daar waar we eigenlijk in deze entiteit unieke waarden willen hebben voor onze ProductID, is dat momenteel niet het geval.

Via de Kolomdistributie kunnen we ook al afleiden dat er 212 verschillende waarden zitten in onze ProductID. Je kan ervan uit gaan dat de kolom met de meeste ‘distinct values’ de kolom is waarop je wilt filteren naar unieke waarden.

4. Verwijder de duplicaten: Selecteer de kolom ProductID (of alle kolommen als je niet zeker weet welke de unieke identificator is), klik met de RMB op de hoofding van de kolom en kies ‘Remove duplicates‘.

Er blijven nu nog 212 rijen over. Dit zijn de 212 verschillende producten die in de tabel aanwezig zijn.

Elke waarde in de kolom ProductID is nu uniek, m.a.w. elk product is identificeerbaar door zijn ID.

In de kolomdistributie zie je nu 212 verschillende, allemaal unieke waarden. Onze volledige DimProduct-entiteit bevat nu dus nog slechts 212 rijen.

In de FactSales is deze ProductID meerdere keren aanwezig. We gaan hier dus een 1:* relatie kunnen leggen.

Onze entiteit DimProduct is nu klaar.

DIMCUSTOMER

We gaan hetzelfde doen met onze DimCustomer-entiteit.

We kiezen hier enkel ‘CustomerId‘ en ‘Email Name‘ en ‘ZipCode‘. Onze andere adresgegevens gaan we appart houden, zodat we, als het model groeit met bijkomende entiteiten -bijvoorbeeld de manufacterers-, deze naar dezelfde tabel met adressen kan verwijzen (DimGeography).

Haal de duplicaten weg. Er blijven nog 282.596 klanten over.

De kolom ‘Email Name‘ gaan we opsplitsen in ‘Email‘, FirstName‘ en ‘LastName‘.

Klik RMB op de hoofding en kies ‘Split Column‘ > ‘By Delimiter…‘.

… en kies ‘Custom‘. Vul de ‘:‘ in als scheidingsteken.

Herhaal deze stap om voornaam en familienaam van elkaar te scheiden. Gebruik de komma als scheidingsteken.

Benoem ook de 3 kolommen: Email, FirstName en LastName.

Vervang in de kolom Email de haakjes door een leeg veld. Je doet dit door met de RMB op de kolomhoofding te klikken en ‘Replace Values…‘ te kiezen.

Onze DimCustomer is nu ook klaar.

DIMGEOGRAPHY

1. Kies de kolommen die je wilt behouden in deze dimensie.
2. Haal de duplicaten weg.

DIMDATE

In deze sectie gaan we door middel van een script een datumtabel aanmaken. Verder in de cursus staan de individuele stappen uitgelegd. Je kan de formuletaal ‘M’ gebruiken om jouw queries op te bouwen in de Power Query Editor.

1. Ga naar jouw Query Editor en klik met de RMB in de zijbalk met de verschillende queries en kies ‘Nieuwe query‘ > ‘Lege query‘.

Je kan hier ook geraken via ‘New Source‘ > ‘Blank Query‘.

2. Open de ‘Advanced Editor‘ op het lint.
3. Kopieer onderstaande code en plak hem in de Editor. Alle reeds aanwezig code mag weg.

//Create Date Dimension
(StartDate as date, EndDate as date)=>

let
    //Capture the date range from the parameters
    StartDate = #date(Date.Year(StartDate), Date.Month(StartDate), 
    Date.Day(StartDate)),
    EndDate = #date(Date.Year(EndDate), Date.Month(EndDate), 
    Date.Day(EndDate)),

    //Get the number of dates that will be required for the table
    GetDateCount = Duration.Days(EndDate - StartDate),

    //Take the count of dates and turn it into a list of dates
    GetDateList = List.Dates(StartDate, GetDateCount, 
    #duration(1,0,0,0)),

    //Convert the list into a table
    DateListToTable = Table.FromList(GetDateList, 
    Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),

    //Create various date attributes from the date column
    //Add Year Column
    YearNumber = Table.AddColumn(DateListToTable, "Year", 
    each Date.Year([Date])),

    //Add Quarter Column
    QuarterNumber = Table.AddColumn(YearNumber , "Quarter", 
    each "Q" & Number.ToText(Date.QuarterOfYear([Date]))),

    //Add Week Number Column
    WeekNumber= Table.AddColumn(QuarterNumber , "Week Number", 
    each Date.WeekOfYear([Date])),

    //Add Month Number Column
    MonthNumber = Table.AddColumn(WeekNumber, "Month Number", 
    each Date.Month([Date])),

    //Add Month Name Column
    MonthName = Table.AddColumn(MonthNumber , "Month", 
    each Date.ToText([Date],"MMMM")),

    //Add Day of Week Column
    DayOfWeek = Table.AddColumn(MonthName , "Day of Week", 
    each Date.ToText([Date],"dddd"))

in
    DayOfWeek 

De functie wordt aangemaakt en ik kan start- en einddatum van mijn DimDate meegeven. Klik daarna op ‘Invoke‘.

De tabel wordt aangemaakt.

Benoem de tabel ‘DimDate‘ en pas de datatypes aan voor de verschillende kolommen.

Onze 4 Dimension Tables zijn aangemaakt en we kunnen de kolommen die we in onze FactSales niet nodig hebben verwijderen.

Klik ‘Close & Apply‘ om de queryEditor te sluiten.

We kunnen nu ons datamodel – Starschema opbouwen…

Sla de file op en sluit hem. Ga hier later gerust verder mee aan de slag.