Data filtering in Invantive voor dossier met enorm veel data

Ik had een vraag omtrent het filteren in Invantive zelf. We hebben een dossier met enorm veel data en we zouden deze willen filteren in Invantive alvorens we deze inlezen in PowerBI. Is dit mogelijk in het SQL statement, of is er een andere manier?

Er zijn meerdere mogelijkheden om te werken met bijzonder grote dossiers.

Om goed verder te helpen is nodig om te weten “veel data” betekent qua aantal rijen en in welke soort (factuurregels, artikelen, documentbijlagen)? Kunt u daarvan van de belangrijkste een indicatie geven zoals: 100.000, 1 miljoen, 10 miljoen, 100 miljoen rijen?

Het gaat om één specifiek Exact Online-dossier voor rapportage in Power BI?

Laat ons zeggen voor 200 individuele dossiers.

De grootte per dossier varieert. Van managementvennootschappen tot de grotere KMO.

Bedoeling is volgende tabellen in te lezen:

  • Volledig grootboek
  • Ouderdomsbalans klanten
  • Ouderdomsbalans leveranciers
  • Relaties (overzicht van klanten en leveranciers)

Als ik het goed begrijp, dan gaat het niet om een individueel enorm dossier, maar al met al een mix van enkele honderden dossiers op Exact Online richting Power BI.

Dit kan direct met Invantive Cloud, maar als gaat zeker vanaf 1.000 dossiers raden we Invantive Data Replicator aan. Die schrijft de data bijvoorbeeld naar een Azure SQL Server of on-premises.

Omvang Exact Online Dossiers in Power BI of SQL Server

Uitgaande van gemiddelde kengetallen zijn paar honderd dossiers goed gelijktijdig te verwerken. De verwachte tabellen zijn (met grove schattingen van de aantallen rijen, zonder garanties):

Zonder filteren is het volume dan (ook hier grove schattingen):

Tabelnaam Rijen Initieel API Dagelijks
TransactionLinesIncremental 8.000.000 10.000 400
GLAccountsIncremental 120.000 200 400
ExactOnlineREST..Journals 2.000 200 200
ExactOnlineREST..CostCenters 10.000 200 200
ExactOnlineREST..CostUnits 10.000 200 200
BalanceLinesPerPeriod 1.200.000 400 400
BalanceLinesPerPeriod 3.000.000 400 400
AROutstandingItems 50.000 600 600
APOutstandingItems 50.000 600 600
AccountsIncremental 1.000.000 1.250 400
Totaal ± 13.4420.000 14.050 4.000

Dit volume zal grofweg circa 13 GB per dag aan netwerkverkeer veroorzaken (gecomprimeerd) bij 1x laden in een dataset.

Optimalisatie

Nu een grove schatting zichtbaar is qua rijen (1 KB per rij zonder optimalisatie) en initieel en dagelijks API-gebruik, kan gekeken worden naar manieren om het volume te beperken en binnen Fair Use te blijven.

De eerste stappen zijn:

  • Beperk verversingsfrequentie: werk met gedeelde datasets en ververs niet onnodig, bijvoorbeeld 5x of 7x per week.
  • Beperk kolommen: haal alleen kolommen op die gebruikt worden.
  • Beperk rijen: haal alleen rijen op die gebruikt worden.

Daarna kunnen de tips & tricks toegepast worden van Overzicht van Power BI-technieken om prestaties en downloadtijd te verbeteren

De beperking van kolommen gebeurt automatisch door alleen de gewenste kolommen op te geven. Hetzelfde geldt voor de verversingsfrequentie.

Voor de rijen is het belangrijk om de filterstap zo dicht mogelijk tegen de query aan te zetten in de dataset zodat Power BI’s query-folding het filter kan doorsturen. Dit zorgt er voor dat de filters doorgestuurd worden naar Invantive Bridge Online. Zie ook bijvoorbeeld Verminder hoeveelheid data en verbeter de prestaties van uw Power BI-rapport door te filteren.

Invantive Bridge Online biedt voor accountancygebruikers meerdere unieke optimalisaties zodat dossiers niet onnodig uitgelezen worden in Exact Online, zie hiervoor bijvoorbeeld:

Een deel van de filters zal alleen betrekking hebben op de stap tussen Power BI Service / Power BI en Invantive Bridge Online (zogenaamde “Server-Side Filters” (Invantive term) of “Query Folding” (Microsoft term)). Deze filters zijn ook zichtbaar in de SQL query die getoond wordt in Invantive Bridge Online Monitoring als een OData-verzoek aangeklikt wordt.

Veel van deze filters zullen ook naar doorgestuurd worden. Dit is vooral handig bij selectieve filters; soms is maar 1% van de data nodig.