Filteren op Division in OData voor Power Query op Exact Online XML tabel

Bij gebruik van de REST tabellen in Power Query lukt het mij om de op te halen data te filteren op bijvoorbeeld Division en alleen de kolommen te selecteren die nodig zijn. Wat ik uit de online documentatie begrijp: filter mogelijkheden voor XML zijn een stuk beperkter maar in alle gevallen is het filteren op Division wel mogelijk. Het lukt mij alleen niet om dit filter toe te passen in Power Query.

Onderstaand artikel heeft het alleen over de filteroptie voor een REST tabel: Verminder hoeveelheid data en verbeter de prestaties van uw Power BI-rapport door te filteren

Is ergens een voorbeeld Power Query script beschikbaar waar gefilterd wordt op een Division?

Het gaat specifiek om onderstaande tabel, hier wordt namelijk eenduidig de openingsbalans per boekjaar opgenomen (als er een beter alternatief is hou ik mij aanbevolen):

= OData.Feed(https://bridge-online.cloud/naam/odata4/ExactOnlineXML.XML.BalanceLines@eol, null, [Implementation="2.0"])

Saldibalanstabellen

De Exact Online XML tabellen BalanceLines, BalanceLinesPerPeriod, en de analytische varianten met kostenplaats/kostendrager BalanceLinesCostAnalysis en BalanceLinesPerPeriodCostAnalysis zijn de aanbevolen manier om saldi informatie op te halen. Ze zijn enorm snel in vrijwel alle situaties en schalen gemakkelijk naar tientallen en honderden administraties.

Om te filteren op Exact Online administratie(s) zijn er verschillende mogelijkheden:

  • per query
  • voor alle queries via het use statement
  • voor alle queries gestuurd vanuit Exact Online (gebruikersrechten en/of app-rechten)

Administratie(s) kiezen per OData4 Query

In de OData4 query kan op administratie gefilterd worden via de kolom DIVISION_CODE_ATTR, bijvoorbeeld als volgt:

https://bridge-online.cloud/NAAM/odata4/ExactOnlineXML.XML.Journals@eol?$filter=DIVISION_CODE eq '102673'

De XML tabellen kenden tot 20.1.500 minder optimalisaties dan de REST-tabellen voor het filteren op afgeleide velden zoals DIVISION_HID en DIVISION_NAME (zie Improved Very Large Company Volume Performance on Exact Online). Tot 20.1.500 was het nodig om altijd op DIVISION_CODE te selecteren en dan het liefst op precies 1 of alle; andere administraties worden dan niet opgehaald of geraakt.

De kolomnamen zijn ook eenvoudig terug te vinden door deze OData4 query als URL te openen in een browser.

In het artikel Gebruik Power BI parameters met Invantive Bridge Online wordt uitgelegd hoe dit gestuurd kan worden met Power BI parameters. Het Rapid Deployment Template laat hier een concreet voorbeeld van zien: Exact Online Power BI Rapid Deployment Template

Performanceverbetering voor XML en Power BI

Op basis van gebruikersbehoeften is het vanaf 20.1.500 ook mogelijk om meerdere administraties te selecteren met een OR, vergelijkbaar met Improved Very Large Company Volume Performance on Exact Online. Ook kan geselecteerd worden op naam of het administratienummer uit de Exact Online website. Alleen de gekozen administraties worden verwerkt. In een praktijktest werd de verwerkingssnelheid gemeten van:

select *
from   balancelinesperperiod
where  division_code = 102673 or division_code=868056

De duur op 20.1.490 was 14 seconden en 218 API-calls. Op 20.1.500 en nieuwer is de duur 2.3 seconden en 2 API-calls.

Aantal jaren saldi opvragen

Merk op dat standaard voor twee jaren de saldi teruggegeven worden. Meer jaren kunnen opgevraagd worden met een filter op prevyears zoals:

where  prevyears = 10

Administratie(s) kiezen via het use statement

Voor een database kan in de definitie ook opgegeven worden welke administraties van de beschikbare administraties in aanmerking komen voor gebruik vanuit Invantive Bridge Online met Power BI of Power Query, en voor gebruik in de SQL editor (indien SQL gelicentieerd is).

Standaard wordt bij de databasedefinitie op Exact Online een “Startup SQL” statement ingesteld dat alle administraties selecteert:

Selecteer alle Exact Online administraties

Dit SQL statement dat bij het openen van de database alle administraties selecteert kan momenteel alleen aangepast worden door Invantive Support. Als uitsluitend specifieke administraties gewenst worden, gelieve dan via een e-mail aan support@invantive.com de lijst van divisiecodes door te geven. De aanpassing gebeurt vaak binnen enkele minuten en duurt normaliter hoogstens enkele uren.

Let op! de divisiecodes zijn gewenst, niet de administratienummers. Lees voor tips over het bepalen van de divisiecodes het artikel Foutmelding itgengpr012 bij het selecteren van Exact Online administraties.

De tabel SystemDivisions toont in het veld code wat de divisiecodes zijn. Deze tabel kan ook via Power BI opgehaald worden.

Administraties(s) kiezen vanuit Exact Online

Tenslotte kan via Exact Online gestuurd worden welke administraties gebruikt worden. Dit kan door een aparte gebruiker te maken voor gebruik met Invantive Cloud en hier de gewenste administraties aan toe te kennen.

Voor Invantive Cloud is het gebruik van een eigen client ID aan in combinatie met Exact Online noodzakelijk voor het einde van de proefperiode.

Zolang de Invantive Cloud app gebruikt wordt kan ook via de zogenaamde “division scoping” gestuurd worden waarbij een gebruiker specifiek aan een bepaalde app rechten geeft op administraties zoals in onderstaand plaatje:

De division scoping rechten kunnen achteraf bijgesteld worden zoals beschreven in Forbidden - User division is not within division scope (Itgeneor228 / itgenoda060) - 2 van forums. Het instellen van de administratierechten via “division scoping” is nog aan verandering onderhevig door Exact (situatie begin 2021).

Let op: voor een eigen app hoef je dus geen division scoping en geen data scoping in te stellen.