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):
-
TransactionLinesIncremental: grootboekmutaties, inclusief koppen, sterk variërend tussen 2.500 en 10 miljoen, gemiddeld 20.000 per administratie. -
GLAccountsIncremental: grootboekrekeningen, in België zeg 600 per administratie. -
ExactOnlineREST..Journals: dagboeken, in België zeg 10 per administratie. -
ExactOnlineREST..CostCenters: kostenplaatsen (bij analytisch boekhouden), in België sterk variërend, maar zeg gemiddeld 50. -
ExactOnlineREST..CostUnits: kostendragers (bij analytisch boekhouden), in België sterk variërend, maar zeg gemiddeld 50. -
BalanceLinesPerPeriod: saldi (zowel balans als V&W), 6.000 per jaar. -
BalanceLinesPerPeriodCostAnalysis: dezelfde saldi, maar dan bij analytisch boekhouden gesplitst over kostenplaatsen en kostendragers. Zeg gemiddeld 15.000 per jaar. -
AROutstandingItems: ouderdomsbalans klanten, pakweg 250 per administratie. -
APOutstandingItems: ouderdomsbalans leveranciers, pakweg 250 per administratie. -
AccountsIncremental: relaties (klanten, leveranciers, overige), pakweg 5.000 per administratie.
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:
- Betere prestaties op Power BI voor Exact Online accountants
- Filteren op Division in OData voor Power Query op Exact Online XML tabel
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.