Controles, levensbloed voor je model (1)

Een serie blogs over Excel en Power BI.

BLOG – In het vijflagen model van Business Intelligence,  delen, visualiseren, verbinden, voorbereiden en analyseren, kijk ik vandaan naar de tweede en derde laag: data transformeren en het berekenen van die data. Op een of andere manier blijkt data vaak niet van die kwaliteit, die je verwacht. Vaak gaat veel tijd en energie zitten in de aanpassing van beschikbare data naar de boodschap, die jij wilt overbrengen.

Door Henk Vlootman, adviseur, trainer, spreker en auteur, en topspecialist op het gebied van Microsoft Power BI en Power Query.

Deze blog ontstond toen ik een leesbril kocht. Ik koop de goedkoopste leesbrillen, want ik verslijt er veel. In de winkel had ik mijn oude leesbril niet op – de valkuil van mijn ijdelheid – en pakte letterlijk blindelings een bril uit het schap. Thuisgekomen zette ik mijn nieuwe bril op mijn neus en kwam tot de conclusie dat mijn zicht daardoor niet bepaald verhelderde. Nadere inspectie, nu met mijn oude leesbril op, gaf aan dat ik een bril had gekocht van de juiste sterkte (dat kon ook niet missen, want dat stond met koeienletters op de sticker), maar dan negatief. Dat bracht mij tot het fenomeen controles. Want hoe vaak gebeurt het niet dat een plus en een min teken wordt omgewisseld? Een kleine fout met vaak grote (financiële) consequenties.

Het controleren van datakwaliteit en berekeningen is al zolang aanwezig als ik mij kan heugen. Even voor de goede orde: er is een verschil tussen controle van de datakwaliteit en controle van gemaakte berekeningen: het zijn verschillende onderdelen onder de paraplu controles. Je gebruikt dan ook verschillende technieken. Controles op datakwaliteit berusten meer op statische en logische principes, terwijl controles op berekeningen rusten op jouw kennis van DAX functies en business vereisten. In deze blog bekijk ik de definities, in een volgende blog ga ik verder in op structuren en berekeningen, die je in de praktijk kunt gebruiken.

De ene controle is de andere niet

Eerst neem ik je mee naar om de verschillen tussen de controles te onderzoeken. In de afbeelding rechts zie je zowel een serie getallen (kolom A) als een aggregaat berekening (cel C2) staan. Deze opstelling van getallen en aggregaat berekening, gebroederlijk naast elkaar, zie je vaak. Naar mijn mening is dit een ontwerpfout, maar daar gaat het in deze blog niet over.

Controleren van zowel de data kwaliteit als de berekening is op deze schaal visueel prima mogelijk. In de reeks getallen valt gelijk op dat het getal in cel A4, in vergelijk met de overige getallen, wel heel hoog is. We hebben het hier over de datakwaliteit. En diegene die gevoel voor getallen heeft ontwikkeld, ziet dat ook dat de berekening niet juist is.

Wat vroeger niet meer was dan een paar honderd rijen data kan tegenwoordig veel omvangrijker zijn. Het vergroten van data volumes blijkt een relatief geleidelijk proces. Er zijn nog steeds medewerkers die iedere maand “met het handje” getallen controleren op juistheid. Alleen zijn het nu enkele duizenden rijen geworden. De noodzakelijke tijd voor de controles is dan ook ongemerkt meegegroeid. Werkt je met miljoenen rijen in Power Pivot voor Excel of Power BI, dan is het niet meer mogelijk om te controleren door met je vinger langs de cijfers te gaan. Daar heb je andere, geautomatiseerde, technieken voor nodig.

Controle van invoer

Problemen in de datakwaliteit ontstaan vaak door een onjuiste invoer of door een verkeerde transformatie actie. In de nieuwste versie van Power Query in Excel en Power BI staat boven iedere geïmporteerde kolom statische informatie (afbeelding links) en een samenvatting onder het werkgebied (afbeelding rechts). Je moet deze informatie wel aan vinken. Dat doe je op het tabblad Weergave (afbeelding onder). Visueel, maar ook in cijfers, zie je de datakwaliteit van de geselecteerde kolom. Dat is ontegenzeggelijk een belangrijk inzicht, maar is deze plaats in Power Query wel de juiste? Het antwoord luidt ja, zeker als je aan het ontwikkelen bent, maar ook nee als het een productiemodel is. In dat geval werk je helemaal niet meer in Power Query. Data vernieuwen gebeurt door de knop Verversen of gaat automatisch in de cloud.

Controle van berekening

Het controleren van berekeningen is een ander verhaal. In tegenstelling tot het controleren van data, weet je, als niemand de berekening aanpast, dat het resultaat juist is. Dat is het grote voordeel van een computer, waarbij onmenselijke, rechtlijnige herhalingen van berekeningsstructuren plaats vindt. Hier zijn voorgebakken hulpmiddelen lastiger, omdat iedere berekening gemaakt wordt op basis van business logica, die voor iedere bedrijf net even anders kan zijn. Maar berekeningen zijn altijd gebaseerd op data. Kun je de grote hoeveelheid data reduceren tot handzame en herkenbare proporties, dan ben je in staat de onderliggende logica te beoordelen. Werkt de berekening op de kleine dataset goed, dan kan je er van op aan dat de (rechtlijnige) structuur ook werkt op grote hoeveelheid data. Deze techniek heet het isoleren van data. In de volgende blog ga ik daar verder op in.

Blogs in deze serie:

• Excel: Tabellen for sale. Maar welke moet ik kiezen?
• Excel: De kunst van rij in kolom berekeningen
• Explosieve kracht in een cel: aggregaat berekeningen
• De liefdesverklaring van Power Pivot: relaties
• De echte waarde van Power BI
• Schema first of data first? Maar waar is je data?
• Visualiseren, de kunst van het verleiden (1)
• Visualiseren, de kunst van het verleiden (2)
• Low-code of no-code, de weg naar de toekomst (1)
• Low-code of no-code, de weg naar de toekomst (2)
•  Controles, levensbloed voor je model (1)