DAX Studio retourneert 11.000 rijen, VBA retourneert 6.000, en niemand geeft een foutmelding.
Een terugkerend patroon op de Fabric Community-forums: een ontwikkelaar schrijft VBA-code die verbinding maakt met een Power BI werkruimte via de MSOLAP-provider, voert een DAX-query uit met de filters SUMMARIZECOLUMNS en TREATAS, en krijgt minder rijen terug dan dezelfde query in DAX Studio. De verbindingsreeks lijkt correct — Provider=MSOLAP;Data Source=powerbi://api.powerbi.com/v1.0/myorg/[workspace];Initial Catalog=[dataset] — en de query wordt zonder fouten uitgevoerd. De ADODB-recordset wordt gevuld. CopyFromRecordset schrijft gegevens naar het werkblad. Alles lijkt te werken.
Behalve dat het aantal rijen onjuist is. In één gedocumenteerd geval werden 11.000 rijen in DAX Studio ongeveer 6.000 in Excel. Geen ADODB-fout. Geen waarschuwing voor afkapping. De VBA-subroutine wordt normaal voltooid en de ontwikkelaar ontdekt de discrepantie pas achteraf door de totalen te vergelijken.
Dit is geen DAX-probleem. De query zelf is correct: de resultaten worden verpakt in TOPN(500000,...) met datumparameters die zijn samengesteld uit Excel-celwaarden met behulp van de functies FORMAT en DATE. Het probleem zit hem in de lagen tussen het XMLA-endpoint en het Excel-werkblad: het standaardgedrag van de MSOLAP-provider, de ADODB-cursorconfiguratie en de manier waarop CopyFromRecordset grote recordsets verwerkt. Elke laag kan onafhankelijk van elkaar je resultaatset inkorten, en geen van deze lagen zal je daarover informeren.
De standaardinstellingen van de MSOLAP-provider beperken je resultaatset stilzwijgend.
Wanneer VBA een ADODB-verbinding opent met de MSOLAP-provider tegen het Power BI XMLA-endpoint, werken verschillende standaardinstellingen met grote resultaatsets.
Ten eerste is het cursortype van belang. De meeste VBA-voorbeelden gebruiken adOpenForwardOnly (waarde 0) met adLockReadOnly. Dit is correct voor de prestaties, maar cursors die alleen vooruit lezen, zijn afhankelijk van de interne fetch-buffer van de provider. De MSOLAP-provider biedt geen directe MaxRows-eigenschap zoals een SQL Server OLEDB-provider, maar heeft wel intern buffergedrag dat slecht kan samenwerken met de manier waarop CopyFromRecordset gegevens leest.
Ten tweede heeft CopyFromRecordset zelf een stille rijlimiet. De methode accepteert een optionele MaxRows-parameter — ws.Range("A2").CopyFromRecordset rs, MaxRows — maar als deze wordt weggelaten, worden rijen geschreven totdat de recordset uitgeput lijkt. Het probleem is dat CopyFromRecordset kan stoppen met schrijven als het een buffergrens tegenkomt of als de interne cursor van de recordset zijn positie verliest. Dit geeft geen foutmelding; het retourneert simpelweg het aantal daadwerkelijk geschreven rijen, wat de meeste VBA-scripts negeren.
De oplossing is expliciet. Controleer na voltooiing van CopyFromRecordset de retourwaarde:
``vba
Dim rowsCopied As Long
rowsCopied = ws.Range("A2").CopyFromRecordset(rs)
Debug.Print "Aantal geschreven rijen: " & rowsCopied
``
Als rowsCopied minder is dan verwacht, doorloop dan handmatig de resterende rijen:
```vba Do While Not rs.EOF
' Schrijf de resterende rijen veld voor veld
rs.MoveNext Loop ```
Als alternatief kunt je CopyFromRecordset volledig vervangen door een rij-voor-rij-lus wanneer je vermoedt dat er sprake is van afkapping. Het is trager — merkbaar trager bij meer dan 50.000 rijen — maar het elimineert de bufferambiguïteit. Bij datasets van tienduizenden rijen is het verschil seconden, geen minuten.
Het XMLA-endpoint vereist drie permissielagen vóór je eerste regel.
Voordat er gegevens kunnen worden verzonden, moet je VBA-verbinding drie verschillende machtigingspoorten doorlopen. Als je er één overslaat, kan het gedrag variëren van een duidelijke authenticatiefout tot een verbinding die weliswaar slaagt, maar gedeeltelijke of lege resultaten retourneert.
Laag 1: Capaciteit en XMLA-endpointinstelling. De werkruimte moet de capaciteit Premium, Premium Per User (PPU) of Fabric hebben. De tenantinstelling voor het XMLA-endpoint moet minimaal 'Lezen' zijn. Ga naar de beheerdersportal → Capaciteitsinstellingen → Power BI Premium → selecteer je capaciteit → Werkbelastingen → XMLA-endpoint. Als dit is ingesteld op 'Uit', mislukken ADODB-verbindingen met een foutmelding 'Verbinding geweigerd'. Als je tenant beheerder de integratie-instelling 'XMLA-endpoints toestaan en analyseren in Excel met on-premise semantische modellen' heeft uitgeschakeld, worden alle XMLA-verbindingen geblokkeerd, ongeacht de capaciteitsconfiguratie.
Laag 2: Werkruimterol en bouwmachtiging. Het account dat zich via MSOLAP authenticeert, heeft bouwmachtiging nodig voor het doelsemantische model. Werkruimterollen als Bijdrager, Lid of Beheerder verlenen dit impliciet. Een Kijkersrol doet dit niet. Zonder Bouwrechten weigert het XMLA-endpoint querybewerkingen, maar de foutmelding kan verschijnen als een algemene MSOLAP-verbindingsfout in plaats van een duidelijke weigering van machtigingen.
Laag 3: Authenticatieprotocol. De MSOLAP-provider voor Power BI vereist Azure AD-authenticatie (Microsoft Enterprise ID). VBA met ADODB ondersteunt niet standaard de interactieve MFA-flow die tools zoals SSMS afhandelen via "Active Directory - Universeel met MFA". Dit dwingt tot een van de volgende twee benaderingen: gebruik een service principal met een clientgeheim (door User ID=app: toe te voegen aan de verbindingsreeks), of verkrijg extern een OAuth2-bearertoken en geef dit door via het Password-veld in de verbindingsreeks, waarbij User ID leeg wordt gelaten. Geen van beide benaderingen is eenvoudig in VBA zonder extra COM-verwijzingen of externe hulpbibliotheken.
Serviceprincipals voegen een vierde beperking toe: de tenant beheerder moet de instelling 'Serviceprincipals toestaan Power BI API's te gebruiken' inschakelen en de service principal moet worden toegevoegd aan een beveiligingsgroep die toegang heeft tot de werkruimte.
Versieverschillen in de clientbibliotheek veroorzaken de meest bizarre fouten.
De versie van de MSOLAP-clientbibliotheek die is geïnstalleerd op de computer waarop de VBA-code wordt uitgevoerd, is een stille variabele die het gedrag beïnvloedt. Het XMLA-endpoint van Power BI vereist MSOLAP 17.x of hoger. Oudere versies kunnen wel verbinding maken, maar onvolledige metadata of afgekorte resultaten retourneren zonder foutmelding.
Controleer je geïnstalleerde versie door de geregistreerde OLEDB-providers in het register te bekijken onder HKEY_CLASSES_ROOT\CLSID of door een verbindingstest uit te voeren die $SYSTEM.DISCOVER_XML_METADATA opvraagt. In de praktijk is de snelste manier om dit te controleren door een opdrachtprompt te openen en het volgende uit te voeren:
``` reg query "HKLM\SOFTWARE\Classes\MSOLAP" /s | findstr Version
```
Als je MSOLAP 16.x of ouder gebruikt, kunt je de clientbibliotheken bijwerken naar de nieuwste versie via de pagina met clientbibliotheken voor Analysis Services van Microsoft. Door de maandelijkse updatefrequentie kan zelfs een versie van drie maanden geleden mogelijk geen oplossingen bevatten voor specifieke gedragingen van het XMLA-endpoint.
Een gerelateerd probleem: machines met zowel 32-bits als 64-bits Office-installaties, of met meerdere geregistreerde versies van de MSOLAP-provider, kunnen providerverwarring vertonen. VBA in 32-bits Excel laadt de 32-bits MSOLAP-provider. Als alleen de 64-bits provider actueel is, wordt je VBA-code zonder waarschuwing uitgevoerd met een verouderde provider. Controleer of de architectuur (32-bits of 64-bits) overeenkomt door te controleren welke msolap*.dll je Excel-proces daadwerkelijk laadt. Process Monitor van Sysinternals toont je het exacte DLL-pad tijdens de verbinding.
De ADOMD.NET-bibliotheek (versie 19.x+) is een alternatief voor .NET-gebaseerde automatisering, maar deze kan niet rechtstreeks vanuit VBA worden aangeroepen zonder een COM-wrapper. Voor teams die betrouwbare programmatische toegang tot Power BI datasets vanuit Excel nodig hebben, is de meest onderhoudbare oplossing vaak Power Query met geparameteriseerde M-query's, of het ExecuteQueries-endpoint van de Power BI REST API. Beide methoden omzeilen de eigenaardigheden van de MSOLAP-provider volledig.
ExecuteQueries REST API: het nooduitgangspunt dat wél werkt
Wanneer VBA + ADODB + MSOLAP een eindeloze debug-ervaring wordt, biedt het ExecuteQueries-endpoint van de Power BI REST API een schonere oplossing. Dit endpoint, beschikbaar sinds 2021, accepteert DAX-query's via HTTPS en retourneert JSON — geen XMLA, geen OLEDB-provider, geen cursorbeheer.
De URL van het endpoint volgt dit patroon:
``
POST https://api.powerbi.com/v1.0/myorg/datasets/{datasetId}/executeQueries
``
De aanvraagbody bevat je DAX-query:
```json {
"queries": [{"query": "EVALUATE SUMMARIZECOLUMNS(...)"}],
"serializerSettings": {"includeNulls": true} }
```
Vanuit VBA roept je dit aan met MSXML2.XMLHTTP of WinHttp.WinHttpRequest. Authenticatie vereist een bearer-token verkregen van Microsoft Entra ID — dezelfde OAuth2-flow, maar nu maakt je een standaard HTTPS-aanroep in plaats van te vertrouwen op de MSOLAP-provider voor interne authenticatie. Het verkrijgen van het token kan via MSAL (met behulp van een kleine .NET-helper of een PowerShell-script dat het token naar een bestand schrijft dat door VBA wordt gelezen) of via een service principal client credentials flow.
Het belangrijkste voordeel: ExecuteQueries retourneert een volledig JSON-resultaat met een expliciet aantal rijen. Er is geen cursor, geen buffer en geen stille afkapping. Als de query een time-out bereikt of de limiet van 100.000 rijen overschrijdt, krijgt je een expliciete foutmelding – geen gedeeltelijk resultaat dat er compleet uitziet.
De limiet van 100.000 rijen per query is de belangrijkste beperking. Voor grotere extracten kunt je pagineren met TOPN en OFFSET of de query splitsen op datumbereik. Dit is meer werk dan een enkele ADODB-recordset, maar de foutmelding is expliciet in plaats van stilzwijgend – en dat verschil is belangrijk wanneer je financiële team de cijfers aan het einde van de maand afstemt.
MetricSign bewaakt de refresh van Power BI datasets en de gezondheid van query's, en kan detecteren wanneer geplande refreshen mislukken of vertraagd zijn. Het kan echter niet detecteren wanneer een VBA-script stilletjes 5000 rijen verwijdert. Die detectie moet in je code plaatsvinden, bijvoorbeeld door het aantal rijen te valideren ten opzichte van de verwachte totalen of door over te schakelen naar een API die een foutmelding geeft.
Checklist voor betrouwbare data-extractie van VBA naar Power BI
Als je VBA met ADODB moet gebruiken tegen het XMLA-endpoint — en soms zijn dit vanwege organisatorische beperkingen de enige optie — pas dan deze controles toe om ongemerkt gegevensverlies te voorkomen.
Controleer of de MSOLAP-providerversie 17.x of hoger is, overeenkomend met de architectuur (32-bits of 64-bits) van je Excel-bestandssysteem. Voer de hierboven beschreven registercontrole uit voordat je VBA-code schrijft. Werk de clientbibliotheek bij als deze ouder is dan twee maanden.
Stel de ADODB-verbindings- en recordseteigenschappen expliciet in. Vertrouw niet op de standaardwaarden:
``vba
conn.ConnectionTimeout = 120
rs.CursorLocation = adUseClient ' Dwingt cursorgebruik aan de clientzijde af
rs.CursorType = adOpenStatic ' Maakt volledige traversering mogelijk
rs.LockType = adLockReadOnly
``
Het gebruik van adUseClient met adOpenStatic zorgt ervoor dat de volledige resultaatset aan de clientzijde wordt gematerialiseerd voordat CopyFromRecordset wordt uitgevoerd. Dit elimineert het probleem van de bufferlimiet bij alleen voorwaartse verwerking, ten koste van een hoger geheugenverbruik – acceptabel voor datasets met minder dan 100.000 rijen.
Leg altijd de retourwaarde van CopyFromRecordset vast en valideer deze. Vergelijk deze met rs.RecordCount (die beschikbaar is voor statische cursors, in tegenstelling tot cursors die alleen voorwaartse verwerking vereisen, waarbij RecordCount -1 retourneert). Als ze verschillen, registreer dan de afwijking en val terug op rij-voor-rij-iteratie.
Voor authenticatie kunt je in geautomatiseerde scenario's de voorkeur geven aan service principal-referenties boven interactieve gebruikerstokens. Sla het clientgeheim op in Windows Credential Manager en haal het op via de CredRead-API-aanroep van VBA in plaats van het hard te coderen. Vervang geheimen elke 90 dagen.
Wikkel je DAX-query ten slotte in een expliciete TOPN met een limiet die hoger is dan je verwachte resultaatset, en voeg een COUNT-meting toe in een aparte query om de volledigheid te valideren. Als de TOPN-limiet overeenkomt met het werkelijke aantal rijen, weet je dat het resultaat is afgekapt — een onvolmaakte beveiliging, maar beter dan stilletjes afkappen zonder enige waarschuwing.