In 2020 zijn op Invantive Cloud een aantal verbeteringen qua Power BI snelheid beschikbaar gekomen specifiek voor accountants: bij het gebruik van 1.000 administraties tegelijk worden real-time alleen de administraties opgehaald waar specifiek om gevraagd wordt voor de Power BI dashboards. De Power BI gebruiker hoeft hier niks voor te doen; de Power BI connector van Invantive optimaliseert automatisch de queries. Voor meer informatie zie Improved Very Large Company Volume Performance on Exact Online.
Op Invantive Cloud is nu een verdere verbetering beschikbaar gekomen voor gebruik van Power BI Desktop, Power BI service, Azure Data Factory, Power Query en Microsoft Integration Services op Exact Online.
In Power BI kunnen meerdere administraties geselecteerd worden via een filter op Division, DivisionShortName of bijvoorbeeld DivisionLabel:
Tot nu werden dan nog steeds alle administraties uitgelezen en verwerkt, waarna een groot deel van de informatie weggegooid werd door Invantive SQL. De oplossing was om met de app- of gebruikersrechten in Exact Online in te perken zodat alleen het gewenste deel van de administraties gelezen kon worden.
De nieuwe release van Invantive Cloud kan in meer scenario’s de query van Power BI optimaliseren als specifiek gezocht wordt naar een lijst van administraties. Hierdoor worden minder administraties geraakt en verbeteren de prestaties in grote omgevingen verder. De input voor data analyse kan hierdoor vaker ververst worden zonder extra resourcegebruik en bij ongewijzigd gebruik verbetert de response en hoeveel data die verwerkt wordt.
Deze optimalisaties werken voor alle gekoppelde platformen en tabellen, zoals openstaande posten (inkoop en verkoop), financiële gegevens en productie op Exact Online, maar ook op ActiveCampaign, SQL Server of Teamleader. Een lijst van de platformen en tabellen is te vinden op documentation.invantive.com.
Meer trucs om de prestaties te verbeteren met Power BI dashboards zijn te lezen op Overzicht van Power BI-technieken om prestaties en downloadtijd te verbeteren.
Achtergrond
Selectie Administraties
Power BI vraagt op deze manier om de gegevens uit vier administraties, betrekking hebbende op twee grootboekrekeningen:
…$filter=((DivisionShortName eq 1 or DivisionShortName eq 10) or DivisionShortName eq 23) and (Code eq ‘8000’ or Code eq ‘6000’)
De volgende Invantive SQL-code wordt samengesteld voor het OData verzoek van Power BI:
select ...
from exactonlinerest..glaccounts
where (((divisionshortname = 1) or divisionshortname = 10) or divisionshortname = 23)
and ((code = '8000') or code = '6000')
Dit Invantive-SQL statement wordt uitgevoerd door de Invantive engine en vraagt de gegevens op bij Exact Online.
De gebruikte OR-constructie is vervelend in dit SQL-statement; qua optimalisatie hebben zelfs platformen zoals Oracle hier moeite mee. Na een reeks van verbeteringen zijn de prestaties sterk verbeterd.
In een testomgeving met 218 administraties geselecteerd leidde de vorige release tot 218 Exact Online API-calls die samen 12 seconden duurden op de klok. Aan elke administraties werd de vraag gesteld:
…GLAccounts?$select=*&$filter=Code%20eq%20’8000’%20or%20Code%20eq%20’6000’
In de nieuwe release zijn er slechts 4 Exact Online API-calls nodig, die samen 406 ms duren op de klok. Elke van deze vier Exact Online administraties krijgt de vraag:
…GLAccounts?$select=*&$filter=Code%20eq%20’8000’%20or%20Code%20eq%20’6000’
Inhoudelijk zijn de resultaten gelijk, maar de prestaties zijn 50x beter.
Herschrijven OR naar een IN
De nieuwe optimalisatie herschrijft een boom bestaande uit knopen met de OR-operator die allen bestaan uit:
VELD = constante
waarbij de kolom VELD telkens dezelfde is en de constante eventueel varieert. Een dergelijke OR kan de vorm hebben:
where (((VELD = 1) or VELD = 25 ) or VELD = 300)
of bijvoorbeeld:
where (VELD = 1 or VELD = 25 or VELD = 300)
Beide OR-reeksen worden intern verwerkt als een IN-operator.
Bij het verzenden naar Exact Online (nog v3 van OData) wordt de interne IN-operator omgeschreven worden naar een OData v3 OR.
Op een OData bron die de IN-operator ondersteunt kan wel de interne IN-operator 1-op-1 overgenomen worden voor OData.
Andere Platformen
Deze optimalisatie van reeksen OR’s werkt niet alleen op Exact Online, maar op alle platformen. Bijvoorbeeld op Oracle leidt een Invantive SQL-statement zoals:
select *
from tabel
where ((code = '1' or code = '3') or code = '4')
tot de volgende ANSI SQL-query (een Oracle API-call zeg maar):
select * from SCHEMA.TABEL e where ( CODE in (:i1CODE, :i2CODE, :i3CODE) )
Ook op Oracle, SQL Server, PostgreSQL en MySQL kunnen hierdoor performancewinsten verwacht worden, vooral als de kolom CODE de leidende kolom is in een index op de tabel.
De prestatiewinsten zijn niet alleen merkbaar in Microsoft Power BI dashboards op basis van de Power BI connector, maar ook in andere business intelligence toepassingen met Invantive en gegevensuitwisseling in omgevingen met veel administraties.