VBA-query's tegen Power BI XMLA-endpoints verwijderen rijen zonder waarschuwing.

Een patroon duikt regelmatig op in Power BI ontwikkelaarsforums: iemand bouwt een DAX-query in DAX Studio, bevestigt dat deze de volledige resultaatset retourneert – bijvoorbeeld 11.000 rijen – en gebruikt vervolgens dezelfde query in VBA met een ADODB-verbinding naar het XMLA-endpoint. Het Excel-blad vult zich met ongeveer 6.000 rijen. Geen foutmelding. Geen waarschuwing. De recordset stopt gewoon.

Dit gebeurt omdat de ADODB-laag van VBA en DAX Studio fundamenteel verschillende clientstacks gebruiken. DAX Studio gebruikt ADOMD.NET, dat het XMLA-protocol native ondersteunt en resultaten als een complete rijenset streamt. De ADODB-laag van VBA gebruikt de MSOLAP OLE DB-provider, die intern vertaalt tussen OLE DB-semantiek en XMLA. Deze vertaling introduceert drie potentiële faalpunten: cursorgedrag, afhandeling van tekenreekslengte en bereik van authenticatietokens.

De verbindingsreeks lijkt bedrieglijk eenvoudig:

`` Provider=MSOLAP;Data Source=powerbi://api.powerbi.com/v1.0/myorg/[Workspace];Initial Catalog=[Dataset] ``

Maar de MSOLAP-provider verricht achter die tekenreeks een hoop werk. Deze verkrijgt een OAuth2-token via de Microsoft Authentication Library, onderhandelt over de XMLA-sessie en zet de geretourneerde rijenset om naar een OLE DB-compatibel formaat. Elke versie-incompatibiliteit of configuratiefout op deze niveaus zorgt ervoor dat gegevens stilzwijgend worden afgekapt in plaats van dat er een foutmelding wordt gegenereerd. De VBA-ontwikkelaar ziet een gedeeltelijk resultaat en neemt aan dat de query onjuist is. Dat is niet het geval.

De meest voorkomende oorzaak van het afkappen van rijen is het standaard cursortype in ADODB. Wanneer je een recordset in VBA opent zonder cursorparameters op te geven, gebruikt ADODB standaard adOpenForwardOnly (CursorType = 0) met adLockReadOnly (LockType = 1). Dit is efficiënt voor kleine resultaten, maar problematisch voor XMLA-verbindingen.

De MSOLAP-provider streamt gegevens vanuit de Power BI engine in stukken. Een forward-only cursor leest elk stuk één keer en verwijdert het vervolgens. Als de interne buffer van de provider vol raakt voordat alle rijen zijn verwerkt — wat gebeurt wanneer individuele rijen lange tekenreeksen of veel kolommen bevatten — schuift de cursor verder en verdwijnen de niet-verwerkte rijen. Er wordt geen uitzondering gegenereerd.

De oplossing is om over te schakelen naar een statische cursor:

``vb Dim rs As New ADODB.Recordset rs.CursorLocation = adUseClient ' Dwingt buffering aan de clientzijde af rs.CursorType = adOpenStatic ' CursorType = 3 rs.LockType = adLockReadOnly ' LockType = 1 rs.Open strDAX, conn ``

Door CursorLocation = adUseClient in te stellen, wordt de volledige resultaatset in het clientgeheugen geladen voordat er rijen worden gelezen. Dit elimineert het probleem van het afkappen van gegevens tijdens streaming, ten koste van een hoger geheugenverbruik. Voor een resultaatset van 11.000 rijen met typische BI-kolommen kunt je rekening houden met 20-50 MB geheugenoverhead — verwaarloosbaar op moderne machines.

Er is een tweede valkuil: CopyFromRecordset zelf kapt tekenreeksvelden af die langer zijn dan 8203 tekens. Als je DAX-query tekstkolommen met lange waarden retourneert (beschrijvingen, samengevoegde lijsten, JSON-blobs), worden die velden stilzwijgend afgekapt. De rij verschijnt wel in Excel, maar de gegevens zijn onvolledig. Door veld voor veld door de recordset te lopen, wordt dit voorkomen, ten koste van tragere schrijfbewerkingen:

```vb Dim row As Long: row = 1 Do While Not rs.EOF

For col = 0 To rs.Fields.Count - 1

Cells(row, col + 1).Value = rs.Fields(col).Value

Next col

rs.MoveNext

row = row + 1 Loop ```

Het XMLA-endpoint vereist MSOLAP versie 17.0.40.18 of hoger en ADOMD 19.104.2.0 of hoger. Dit zijn geen suggesties — oudere versies leggen verbindingen tot stand die gedeeltelijk functioneren. Ze authenticeren, accepteren query's en retourneren resultaten. Maar de OAuth2-tokenonderhandeling kan terugvallen op een beperkte scope, waardoor de grootte van de resultaatset die de engine bereid is te retourneren, wordt beperkt.

Je kunt je geïnstalleerde MSOLAP-versie rechtstreeks vanuit VBA controleren:

``vb Dim conn As New ADODB.Connection conn.Provider = "MSOLAP" conn.Open "Data Source=powerbi://api.powerbi.com/v1.0/myorg/[Workspace];Initial Catalog=[Dataset]" Debug.Print conn.Properties("Provider Version").Value ``

Als de versie lager is dan 17.0.40.18, installeer dan de nieuwste Analysis Services-clientbibliotheken van de downloadpagina van Microsoft. Het MSI-pakket werkt zowel de MSOLAP-provider als de ADOMD.NET-bibliotheken tegelijkertijd bij.

Meervoudige authenticatie (MFA) voegt een extra complicatie toe. Wanneer je Azure AD-tenant MFA afdwingt, moet de MSOLAP-provider een interactieve browserprompt weergeven. In VBA werkt dit alleen als je de referenties volledig uit de verbindingsreeks weglaat — geen User ID, geen Password-eigenschappen. Het opnemen van een van beide eigenschappen zorgt ervoor dat MSOLAP een basisverificatie probeert uit te voeren, die tenants met MFA (Multi-Factor Authentication) weigeren. De verbinding kan nog steeds tot stand komen met behulp van een opgeslagen token van een eerdere interactieve sessie, maar dat token verloopt (meestal na 60-90 minuten) en de volgende VBA-uitvoering mislukt met een algemene 'Automatiseringsfout' in plaats van een duidelijk authenticatiebericht.

Voor scenario's die niet door de gebruiker worden uitgevoerd, zoals geplande Excel-macro's en taken in Taakplanner, is authenticatie met een service principal de enige betrouwbare methode. Hiervoor is het nodig om een Azure AD-app te registreren, deze machtigingen op werkruimteniveau te verlenen en de client-ID en het geheim van de app door te geven via de eigenschappen 'Gebruikers-ID' en 'Wachtwoord' in de verbindingsreeks met 'Beveiligingsinformatie behouden=True'. Weinig VBA-implementaties slagen hierin bij de eerste poging.

De discussie in het forum die aanleiding gaf tot dit bericht, gebruikt VBA om Excel-celwaarden in een DAX-queryreeks te injecteren – waarmee dynamisch een TREATAS-filter wordt opgebouwd op basis van spreadsheetinvoer. Dit patroon werkt, maar introduceert twee risico's die niet bestaan wanneer dezelfde query in DAX Studio wordt uitgevoerd.

Ten eerste heeft DAX geen parametergestuurd querymechanisme. Elke filterwaarde moet rechtstreeks in de queryreeks worden opgenomen. Als een cel een enkele aanhalingsteken bevat, geeft de DAX-parser een syntaxfout. Als de cel een komma bevat in een numerieke landinstelling die komma's als decimaal scheidingsteken gebruikt, ontvangt de DATE- of VALUE-functie een argument dat niet wordt verwacht. VBA valideert deze invoer niet en de resulterende fouten verschijnen als 'De expressie bevat een syntaxfout' zonder aan te geven welk token de fout heeft veroorzaakt.

Ten tweede is de verwerking van datums over deze grens kwetsbaar. Een cel die in Excel als datum is opgemaakt, wordt aan VBA doorgegeven als een Double (het OLE Automation-datumserienummer). Het omzetten naar een DAX DATE()-aanroep vereist expliciete extractie van jaar, maand en dag:

``vb Dim d As Date: d = Range("B2").Value Dim daxDate As String daxDate = "DATE(" & Year(d) & "," & Month(d) & "," & Day(d) & ")" ``

Het doorgeven van de onbewerkte celwaarde — Format(d, "jjjj-mm-dd") — aan een tekenreeksvergelijking met een datumkolom in DAX resulteert vaak in nul rijen in plaats van een foutmelding, omdat DAX de niet-gequoteerde tekenreeks behandelt als een identificator, niet als een letterlijke waarde. De query wordt uitgevoerd, retourneert een lege tabel en de VBA-macro schrijft niets naar het werkblad. De ontwikkelaar gaat ervan uit dat de dataset geen gegevens voor die datum bevat.

Het gebruik van TOPN in de DAX-query (zoals de oorspronkelijke poster deed, met een limiet van 500.000 rijen) voegt een misleidend vangnet toe. TOPN zonder een ORDER BY-clausule retourneert een willekeurige subset. Als de interne sorteervolgorde van de database de gefilterde rijen toevallig aan het einde plaatst, knipt TOPN deze af. De resultaatset lijkt compleet — hij bevat het verwachte aantal rijen — maar bevat de verkeerde rijen. Combineer TOPN altijd met een expliciete ORDER BY op de kolom waarop je filtert.

Het gebruik van VBA om Power BI via XMLA te bevragen werkt. Het is echter ook de meest omslachtige methode. Elk onderdeel in de keten – de VBA-runtime van Excel, ADODB, de MSOLAP-provider, het verkrijgen van Azure AD-tokens, het XMLA-protocol en de Power BI engine – voegt een potentieel faalpunt toe dat geen nuttige foutmelding genereert.

Voor het importeren van gefilterde subsets van een Power BI dataset in Excel zijn er drie alternatieven die dit risico aanzienlijk verkleinen. De eerste is de native Excel-integratie met Power BI semantische modellen, beschikbaar in Excel-versies 16.0.18129.x en hoger. Deze maakt intern gebruik van MSOLAP 160.139.29, maar de authenticatie, cursorbeheer en resultaatbuffering worden afgehandeld via de eigen verbindingsinfrastructuur van Excel in plaats van via ADODB. De gebruikerservaring is Invoegen → Draaitabel → Vanuit Power BI, en de verbinding wordt beheerd, niet via scripts.

De tweede optie is het executeQueries-endpoint van de Power BI REST API, dat DAX-query's via HTTPS accepteert en JSON retourneert. Een VBA-macro kan dit aanroepen via MSXML2.XMLHTTP, waarbij een bearer-token wordt doorgegeven dat is verkregen via MSAL. Het voordeel hiervan is dat de resultaatindeling voorspelbaar is — JSON-arrays worden niet stilletjes afgekapt — en dat de foutmeldingen specifieke codes bevatten zoals PowerBINotAuthorizedException of DatasetRefreshInProgressException.

De derde optie is Power Automate, waarmee een DAX-query kan worden uitgevoerd op een Power BI dataset en de resultaten naar een Excel-bestand in OneDrive of SharePoint kunnen worden geschreven. Dit maakt VBA volledig overbodig en delegeert de authenticatie aan de beheerde identiteit van de Power Automate-connector.

MetricSign bewaakt de Power BI datasets die aan deze query's ten grondslag liggen en geeft een refresh_delayed-signaal af wanneer een geplande refresh de verwachte tijdslimiet overschrijdt. Als je VBA-macro verouderde gegevens ophaalt omdat de onderliggende dataset niet is vernieuwd, wordt de query weliswaar succesvol uitgevoerd, maar met de cijfers van gisteren. Een melding voor een vertraagde refresh dicht dit gat voordat een belanghebbende het merkt.

Als je een VBA-macro in productie hebt die al gedeeltelijke resultaten retourneert, kunt je de volgende stappen voor diagnose uitvoeren.

Isoleer eerst of het probleem zich in de query- of schrijflaag bevindt. Vervang CopyFromRecordset door een controle van het aantal rijen:

``vb rs.MoveLast Debug.Print "Totaal aantal rijen: " & rs.RecordCount rs.MoveFirst ``

Als RecordCount overeenkomt met het verwachte aantal (bijv. 11.000), heeft de XMLA-query de volledige resultatenset geretourneerd en ligt het probleem in de manier waarop VBA deze naar het werkblad schrijft. Als RecordCount lager is, is de afkapping opgetreden tijdens het ophalen van de gegevens.

Controleer bij afkapping aan de ophaalzijde drie dingen in de aangegeven volgorde. Ten eerste: is CursorLocation ingesteld op adUseClient? Zonder deze informatie kan RecordCount -1 (onbekend) retourneren bij een cursor die alleen vooruit kan worden gelezen, en zal MoveLast mislukken. Ten tweede: is de MSOLAP-providerversie actueel? Voer de versiecontrole uit de vorige sectie uit. Ten derde: is het OAuth-token tijdens de query verlopen? Bij query's die langer dan een paar seconden duren, kan een token dat tijdens de verbinding is verkregen, verlopen voordat de resultaten volledig zijn gestreamd. Het symptoom hiervan is een gedeeltelijk resultaat zonder foutmelding.

Controleer bij het afkappen van gegevens aan de schrijfzijde of het doelblad voldoende rijen bevat. De rijlimiet van Excel is 1.048.576 — dit zal waarschijnlijk geen probleem zijn bij 11.000 rijen — maar als de macro naar een bereik schrijft dat halverwege het blad begint, wordt de beschikbare ruimte kleiner. Controleer ook of er geen On Error Resume Next-instructie een runtime fout van CopyFromRecordset negeert. Dit is het meest voorkomende antipatroon in VBA: de macro loopt vast op een fout met de tekenreekslengte of het gegevenstype op regel 6001, de foutafhandeling onderdrukt deze en de uitvoering gaat verder na de schrijflus.

Vergelijk de resultaten ten slotte deterministisch: voeg ORDER BY toe aan je DAX-query en voer deze uit in zowel DAX Studio als VBA. Als het aantal rijen verschilt, zullen de rijen in beide resultaten in ieder geval dezelfde rijen zijn, waardoor het mogelijk is om precies te bepalen waar de afkapping begint.