PBi_2_Uitbreiding_D Oefening 1

Bij de eerste oefening gaat het hem vooral om het schoonmaken en opsplitsen van data om tabellen te kunnen aanmaken. Dan gaan we relaties leggen tussen de verschillende tabellen waardoor we dan informatie kunnen verzamelen voor onze visualisaties.

1. Download de file ‘SalesConvertedToExcel‘ die je HIER vindt en bewaar hem op jouw lokale machine.

We werken met een FactTable en Dimension tables.

Achterliggend werken we met primary keys en foreign keys.

De FactTable is de snelstgroeiende tabel wat de data erin betreft. In dit voorbeeld bevat de FactTable alle verkopen die gebeuren.

De DimTables geven informatie aan de FactTable via de relaties die gelegd worden. De DimTables bevatten meer constante data. Er gebeuren minder snel veranderingen in.

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 de 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:

Oefening 1

1. Open PowerBi
2. We gaan eerst het automatisch leggen van relaties uitschakelen om de relaties zelf te kunnen leggen
3. Sla eerst op (Oef 1 Uitbreiding)
4. Trek de file ‘SalesConvertedToExcel‘ binnen in PowerBI en ga naar de Query Editor.
5. Bekijk welke dimensies je in deze tabel kan onderscheiden om jouw datamodel op te bouwen.

OPSPLITEN IN POWERBI

We zullen hierbij verschillende stappen ondernemen:

1. Benoem jouw tabel ‘FactSales‘.

2. Maak 4 duplicaten van jouw FactSales. Deze gaan we gebruiken om onze verschillende Dimension tables aan te maken. We gaan ons concentreren op DimProduct, DimManufacturer, 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.

DIMMANUFACTURER

We doen hetzelfde met onze DimManufacturer-entiteit.

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.

Maak de volgende visualisaties zonder relaties:

Ga naar modelweergave, zet de tabellen in de stervorm en maak de nodige relaties aan.

Bekijk of je gemaakte tabellen nu wel de juiste weergave tonen door de aangemaakte relaties.

Sla de file op en sluit hem.