Op een eenvoudige manier één tabel verversen in een Power BI dataset, het kan gewoon!

Context

Het komt je vast wel bekend voor, vele Excel tabellen die de basis vormen voor je rapportages en waarvan er dan regelmatig eentje wordt aangepast.  Voordat dan die ene wijziging is doorgevoerd in je rapport ben je weer minimaal een half uur verder. Dat moet sneller kunnen dacht ik enkele weken terug toen ik betrokken was bij het budgetteringsproces van een klant. En dat klopte gelukkig ook!

Ik werkte aan een dataset die gevoed wordt door een import van vele Excel-bestanden. Met een filter op een voorvoegsel was ik in staat om zo’n 30 bestanden met dezelfde structuur naar een tabel in mijn Power BI dataset te laden. En je raadt het al, de Excel bestanden bevatte budgetteringsgegevens die gedurende het proces nog meerdere keren bijgewerkt zouden gaan worden.

Nadat de Excel-bestanden door de business users worden aangepast waren er eigenlijk maar 2 standaard manieren om de dataset bij te werken:

1. 1. Open het pbix-bestand en vernieuw de specifieke tabel die de bestanden laadt, vervolgens publiceer je deze naar de service. Dit activeert echter een vernieuwing in de service na publicatie. Afhankelijk van de verbinding die je hebt in de pbix (subset van rijen van dev/test/prod) kan het zijn dat je hoe dan ook moet wachten op de vernieuwing in de service.
   2. Vernieuw de dataset in de service en wacht tot die is voltooid.

In beide bovenstaande gevallen wordt de dataset in zijn geheel ververst, en het is niet zo dat de dataset erg groot is, maar wachten (20 tot 30 minuten) op een refresh is natuurlijk zonde van de tijd. En het maakt eigenlijk niet uit hoe lang het precies duurt, als je ergens op zit te wachten, duurt het altijd te lang, toch?

Maar zoals je misschien al uit de titel van deze blog hebt gezien, is er ook een andere oplossing waarmee je één tabel kunt verversen 😀. Mooier nog, er bestaat zelfs een ‘no-code’ oplossing! Spoiler alert: je hebt wel een Premium licentie nodig.

Oplossing: één tabel verversen

Samenvattend ziet de oplossing er als volgt uit: Via het XMLA-endpoint kun je een verbinding maken tussen de Premium-workspace (Per Capacity of Per User) en SQL Server Management Studio (SSMS) om een (enkele) tabelvernieuwing uit te voeren. Daarbij zijn er wel een paar dingen waar je rekening mee moet houden voordat je dit kunt doen:

• XMLA lezen/schrijven inschakelen
• Haal de connectie van het XMLA-endpoint op
• Maak verbinding via SSMS
• Ververs je tabel(len)
• Controleer het resultaat van de vernieuwing in SSMS of controleer de vernieuwingsgeschiedenis in de service

Hieronder beschrijf ik de afzonderlijke stappen in meer detail.

XMLA lezen/schrijven inschakelen

Het eerste dat je moet doen, is de optie Read Write inschakelen op het XMLA-endpoint van je Premium-capaciteit.
Deze instelling is beschikbaar in de Admin Portal, onder Capaciteitsinstellingen of Premium Per Gebruiker, afhankelijk van je licentie.

XMLA Endpoint-verbinding

Je kunt de verbindings-URL ophalen in de workspace instellingen, op het tabblad Premium. Meer info op de documentatiepagina.

N.B. Verbinding maken met een My Workspace met behulp van het XMLA-endpoint wordt momenteel niet ondersteund.

Maak verbinding via SSMS

Gebruik SQL Server Management Studio (SSMS) om verbinding te maken met de URL die in de vorige stap is verkregen. Houd er rekening mee dat je hiervoor versie 18.9 of hoger nodig hebt. Zorg ervoor dat je de volgende optie voor authenticatie gebruikt:

• Azure Active Directory – Universal with MFA: log in met je Microsoft/Power BI account

Naast het verbinden met en bekijken van eigenschappen van je Power BI-modellen, ondersteunt deze methode ook het uitvoeren van DAX-, MDX- en XMLA-query’s. Je kunt dus o.a. ook rechtstreeks DAX queries uitvoeren op je model vanuit SSMS.

SSMS is niet de enige tool die wordt ondersteund door het XMLA-endpoint, je kunt één van de hier genoemde tools kiezen, b.v. Excel, SQL Server Profiler, DAX Studio of Tabular Editor om er maar een paar te noemen. Veel van deze tools staan ook op onze best practices lijst. Bij sommige tools kan het nodig zijn om ook de initiële catalogus op te geven (de dataset om verbinding mee te maken in je workspace). De datasets in je workspace zullen uiteindelijk worden weergegeven als databases onder je AS-server:

N.B. Wanneer je een connectie maakt met SSMS is het beter om deze Connect to database niet in te vullen, omdat je anders tegen een foutmelding met het verversen aan kunt lopen. Deze fout is in SSMS 19 (Preview 4) wel opgelost, maar om geen nieuwe versie te hoeven installeren is het handiger om hier <default> te laten staan met SSMS.

Ververs je tabel(len)

Nu verder met het daadwerkelijk verversen van de tabel.

• Klik met de rechtermuisknop op de tabel die je wilt vernieuwen en selecteer Process Table
  

• Selecteer de juiste processing option, afhankelijk van je behoeften, ik heb een volledige verversing gebruikt
  
• Selecteer eventueel andere tabellen om te vernieuwen
• Klik op OK en wacht tot het vernieuwen is voltooid

Naast het verversen van je tabel(len) in SSMS, kun je dit gedeelte ook scripten en later gebruiken om het proces te automatiseren. Omdat ik het alleen ad-hoc hoef te verversen vind ik mijn manuele oplossing voor nu prima.

Refresh History

Dit type vernieuwing wordt weergegeven als een Via XMLA-endpoint in de refresh history van je dataseteigenschappen in de service.

Als je ooit een lopende verversing wilt annuleren, ook met Premium en het XMLA-endpoint ingesteld op Read-write, bekijk dan dit bericht hoe je een verversing annuleert met een SessionID (in plaats van SPID) en DAX Studio.

Sinds kort is er ook de mogelijkheid om in de service een (Premium) dataset refresh te annuleren, dus gewoon in de workspace.

Nog een ander alternatief

Voor de ‘echte techneuten’ onder ons beschreef Marc Lelijveld nog een andere mooie oplossing in zijn blog over het activeren van een enkele tabel om te vernieuwen in de Power BI-service. Maar als je niet (zo) bekend bent met PowerShell en TMSL of wil je gewoon ad-hoc de tabel verversen (omdat het geen deel uit maakt van een geautomatiseerd schema), dan is mijn oplossing een stuk behapbaarder.

Conclusie

In deze post heb ik een oplossing laten zien hoe je no-code een enkele tabel in je Power BI dataset kunt verversen. Hopelijk heeft deze post je wat inzichten gegeven.

• Kende jij deze methode al?
• Heb je deze manier al eerder gebruikt? Gebruik je dit vaak?
• Gebruik je andere oplossingen om één tabel in een dataset te verversen?

Laat wat van je horen met meer details in de comments!