Historische data archiveren

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. Met alle issues van dien.

Incrementeel laden?

blankDat 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 dataset op te splitsen in een dataset met de actuele data van dit jaar en een tweede dataset met de historische data. De actuele dataset kun je dan dagelijks vernieuwen, de historische dataset 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 dataset alsnog een keer vernieuwen.

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

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

blank

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.

blank  blank

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.

blank

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:

blank

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.

blank

We kiezen de historische dataset.

blank

We selecteren de feitentabellen die we willen toevoegen.

blank

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.

blank

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.

blank

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

blank

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.

blank

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.

Geef een antwoord

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *