Het liefst wil je dat de data in je dashboard zo actueel mogelijk is. In Power BI kun je met een Power BI Pro licentie je dataset 8x per dag vernieuwen. Bedenk hierbij wel dat je bij elke refresh alle data opnieuw vanuit de bron ophaalt, dus ook alle ongewijzigde historische data wordt opnieuw opgehaald. Dat leidt tot onnodig dataverbruik.

Wanneer je data ophaalt uit andere systemen dan heb je te maken met limieten. Veel API’s hanteren een fair use policy waarbij je dagelijks een bepaalde hoeveelheid data mag ophalen. Als je dagelijks meerdere keren de volledige historie ophaalt, dan kun je tegen deze limieten aanlopen.

Incrementeel laden?

Dat roept de vraag op of je de data incrementeel kunt inlezen, zodat telkens alleen de nieuwe data wordt toegevoegd. In Power BI zijn daar mogelijkheden voor, maar helaas zijn die niet toepasbaar. Incrementeel laden is alleen mogelijk bij bepaalde databronnen, voornamelijk bij databases. De Power BI Connectors koppelen met de API’s van andere systemen, dan is incremental refresh niet mogelijk. Daarnaast geeft incremental refresh weer andere uitdagingen, bijvoorbeeld wanneer oude data wijzigt of wordt verwijderd.

Data opsplitsen

Wat wel kan is het opsplitsen van de data. Bijvoorbeeld door de data op te splitsen in de actuele data van dit jaar en de historische data van voorgaande jaren. De actuele data kun je dan dagelijks vernieuwen, de historische data hoeft niet vernieuwd te worden. En mocht de historische data toch wijzigen, dan kun je op een rustig moment (bijvoorbeeld in een weekend) de historische data alsnog een keer vernieuwen.

Er zijn 2 methodes om de actuele en historische data op te splitsen. Je kunt dit doen door de tabellen te splitsen of door de volledige dataset te splitsen. Hieronder werken we beide methodes uit.

 

Methode 1: tabellen opsplitsen

De eerste methode is om de tabellen op te splitsen in tabellen met de actuele data en tabellen met de historische data. Het nadeel van deze methode is dat je het uitvoert binnen één PBIX-bestand en het daardoor minder gemakkelijk kunt hergebruiken in andere dashboards. Al zijn er wel mogelijkheden om de data te hergebruiken.

Hieronder lopen we door de stappen om je dataset op deze manier in te richten. We gaan in dit voorbeeld uit van een dataset uit Exact Online van 4 jaar, 2020 tot en met 2023. Alleen de data van 2023 willen we dagelijks vernieuwen, de oudere data wijzigt niet meer.

Historische tabellen

In Power BI Desktop lezen we eerst de tabellen in met historische data, daar lezen we alle 4 jaren in:

We importeren alleen de tabellen waarin de historische data staat. Dat zullen alleen de feitentabellen uit je datamodel zijn, dus bijvoorbeeld Grootboekmutaties, Verkoopfacturen, Goederenleveringen, Loonjournaal, etc.

Nadat we de feitentabellen hebben ingelezen filteren we hieruit alleen de data tot en met 31-12-2022. Dus de periode van onze historische tabellen.

 

We hernoemen deze tabellen door ‘Historie’ achter de naam te zetten, zodat we ze straks kunnen onderscheiden van de actuele tabellen.

Vervolgens stellen we in dat we deze tabellen niet willen verversen bij het vernieuwen van de dataset. Dat doe je in de Power Query Editor door met de rechtermuisknop op de tabel te klikken en de optie ‘Include in report refresh’ uit te vinken.

Let op: deze historische tabellen moeten niet gebruikt worden in een merge of append actie. Zou je deze tabellen namelijk mergen of appenden in een andere tabel en in die andere tabel staat ‘Include in report refresh’ wel aan, dan worden deze historische tabellen alsnog volledig ververst bij elke vernieuwactie!

Actuele tabellen

Daarna lezen we de tabellen in met de actuele data van dit jaar, bij deze import lezen we dus 1 jaar data in. Bij deze tabellen laten we ‘Include in report refresh’ aan staan zodat ze elke keer worden meegenomen bij het vernieuwen.

We hebben nu het onderstaande datamodel:

Bij elke refresh worden de tabellen Grootboek en GrootboekMutaties vernieuwd. De tabellen Kalender en GrootboekMutaties Historie worden niet vernieuwd.

Inrichten dashboard

Vervolgens kunnen we op de gebruikelijke manier het dashboard maken. Dus het toevoegen van DAX-berekeningen, het toevoegen van grafieken, slicers, etc. Bij het maken van de DAX-berekeningen moeten we er alleen rekening mee houden dat we de velden uit de actuele en historische feitentabellen bij elkaar optellen.

Dus de DAX-measure:

Saldo per maand = SUM(GrootboekMutaties[Bedrag])

Wordt nu:

Saldo per maand = SUM(GrootboekMutaties[Bedrag]) + SUM('GrootboekMutaties Historie'[Bedrag])

Verder kun je op de gebruikelijke manier je dashboard inrichten.

Power BI Service

De inrichting in de Power BI Service is niet anders dan voorheen. Ook het instellen van de geplande vernieuwing werkt verder hetzelfde als voorheen. (Zie hiervoor de beschrijving in de handleiding.)

Als je de historische tabellen toch een keer wilt vernieuwen dan kun je dat doen in Power BI Desktop. Daar kun je handmatig een specifieke tabel vernieuwen, door met de rechtermuisknop op deze tabel te klikken en te kiezen voor ‘Refresh data’. Dan wordt alleen deze tabel vernieuwd.

Daarna publiceer je het dashboard opnieuw naar de Power BI Service.

Dit is ook tegelijk een nadeel van deze methode, je kunt dit alleen in Power BI Desktop doen. Met de tweede methode hieronder heb je Power BI Desktop niet nodig om de historische data te vernieuwen.

Aanpassen bestaand dashboard

Om je bestaande dashboards te optimaliseren kun je ook de bovenstaande stappen volgen. Je past de bestaande feitentabellen aan, door daarin alleen het huidige jaar in te lezen (zie de supportpagina voor het aanpassen van het aantal jaren).

Daarna voeg je de nieuwe tabellen toe met de historische data en wijzig je je DAX-berekeningen op de manier zoals hierboven beschreven.

 

Methode 2: datasets opsplitsen

Met de tweede methode kunnen we hetzelfde bereiken. Maar hierbij splitsen we het datamodel op in 2 datasets, een historische en actuele dataset. We maken eerst een dashboard (of eigenlijk alleen een dataset) met de historische data. Die publiceren we naar de Power BI Service, vervolgens hergebruiken we die data in de actuele dataset.

Het voordeel van deze methode is dat je later de historische dataset in de Power BI Service kunt vernieuwen, je hebt daar Power BI Desktop niet meer voor nodig. Het tweede voordeel is dat je de historische dataset in meerdere dashboards kunt gebruiken, waardoor dit tijd zal besparen. Dit wordt in de Power BI termen de “golden dataset” methode genoemd.

Historische dataset

In Power BI Desktop maken we eerst de historische dataset, daar lezen we alle 4 jaren in:

We importeren de tabellen waarin de historische data staat. Dat zullen alleen de feitentabellen uit je datamodel zijn, dus bijvoorbeeld Grootboekmutaties, Verkoopfacturen, Goederenleveringen, Loonjournaal, etc. De dimensietabellen willen we wel dagelijks vernieuwen, dus die importeren we in de actuele dataset.

Nadat we de feitentabellen hebben ingelezen filteren we hieruit alleen de data tot en met 31-12-2022. Dus de periode van onze historische dataset.

 

Vervolgens publiceren we deze dataset naar een werkruimte de Power BI Service. (Let op: dit moet een andere werkruimte zijn dan ‘Mijn werkruimte’!) Bij deze dataset stellen we géén automatische vernieuwing in, want hierin staat alleen data die niet meer wijzigt.

In deze historische dataset staan dus alleen tabellen met data. De rapporten en DAX-berekeningen maken we allemaal in de actuele dataset.

Actuele dataset

Daarna maken we een dashboard waarin we de actuele data uit Exact Online inlezen en waar we de historische data uit de andere dataset aan toevoegen. Dit is ook het uiteindelijke dashboard waarin we de rapporten gaan toevoegen en waarin de DAX-berekeningen worden gemaakt.

In Power BI Desktop lezen we de data in vanuit Exact Online. Dat doen we in 2 stappen. Eerst importeren we de dimensietabellen, daarvan willen we 4 jaar data inlezen. Daarna importeren we de feitentabellen, daarvan willen we maar 1 jaar inlezen.

We hebben nu het onderstaande datamodel:

Daarna voegen we de data toe vanuit de historische dataset. Dat doen we door gegevens in te lezen vanuit een “Power BI Dataset”, dus de dataset die we hiervoor hebben gepubliceerd naar de Power BI Service.

We kiezen de historische dataset.

We selecteren de feitentabellen die we willen toevoegen.

Daarna leggen we de relaties in het datamodel. De historische feitentabellen krijgen precies dezelfde relaties als die van de actuele feitentabellen. Ons datamodel ziet er nu als volgt uit.

Inrichten dashboard

Vervolgens kunnen we op de gebruikelijke manier het dashboard maken. Dus het toevoegen van DAX-berekeningen, het toevoegen van grafieken, slicers, etc. Bij het maken van de DAX-berekeningen moeten we er alleen rekening mee houden dat we de velden uit de actuele en historische feitentabellen bij elkaar optellen.

Dus de DAX-measure:

Saldo per maand = SUM(GrootboekMutaties[Bedrag])

Wordt nu:

Saldo per maand = SUM(GrootboekMutaties[Bedrag]) + SUM('GrootboekMutaties Historie'[Bedrag])

Verder kun je op de gebruikelijke manier je dashboard inrichten.

Als het dashboard klaar is publiceren we deze ook naar de Power BI Service. Het beste kun je beide datasets in dezelfde werkruimte publiceren, zodat de gebruikers van je dashboard ook automatisch toegang hebben tot beide datasets.

Power BI Service

In de actuele dataset in de Power BI Service is er nu, naast de databron van de Power BI Connector, een extra databron. Namelijk de databron van de historische dataset uit de Power BI Service. Daarvoor moeten we de data source credentials instellen.

In de instellingen van de dataset vul je dit in onder het kopje ‘Data source credentials’.

Je kiest voor de authenticatie methode OAuth2 en stelt het privacy level in op Organizational. Dan klik je op ‘Sign in’ en log je in met je Office 365 account.

Het instellen van de geplande vernieuwing werkt verder hetzelfde als voorheen. (Zie hiervoor de beschrijving in de handleiding.)

 

Aanpassen bestaand dashboard

Om je bestaande dashboards te optimaliseren kun je ook de bovenstaande stappen volgen. Je maakt een nieuwe historische dataset met de data van de oude jaren. Je huidige dashboard wordt de actuele dataset. Daarvoor pas je de feitentabellen in het huidige dashboard aan, door daarin alleen het huidige jaar in te lezen (zie de supportpagina voor het aanpassen van het aantal jaren).

Daarna voeg je de data uit de historische dataset toe en wijzig je je DAX-berekeningen op de manier zoals hierboven beschreven.