Veel gebruikers hebben behoefte aan een centraal vastgelegd datamodel, waarin bijvoorbeeld velden geselecteerd worden, complexe berekeningen gecentraliseerd of het datavolume beperkt wordt.
Zoals beschreven in Eigen database-views gebruiken voor SQL en BI-tools is het mogelijk om op Invantive Cloud views te definiëren bij een database. In dit artikel wordt aan de hand van een use case op uw eigen Teamleader Focus omgeving uitgelegd hoe de prestaties verbeterd kunnen worden en het onderhoud van Power BI rapporten vereenvoudigd kan worden.
De beschreven methodiek werkt ook voor andere (cloud)platformen zoals Twinfield, AFAS, Exact Online en ActiveCampaign. De aanpak kan ook gevolgd worden voor andere BI-tools dan Power BI, zoals Qlik Sense of Tableau. De hier beschreven aanpak werkt ook op vergelijkbare wijze in andere user interfaces zoals het Invantive Query Tool en de Excel add-in Invantive Control for Excel.
Voorbereiding: Teamleader met Power BI koppelen
Voordat de view op Teamleader gemaakt kan worden, dient eerst Teamleader met Power BI gekoppeld te worden zodat de Teamleader-data geïmporteerd kunnen worden in Power BI. Dit kost circa 5 minuten.
De eerste stap is het definiëren van een account op Invantive Cloud (video) (stappenplan).
De tweede stap is het registreren van een database op Invantive Cloud die vanuit Power BI de Teamleader-tabellen kan uitlezen. De stappen zijn terug te kijken in de video:
Alle Teamleader-tabellen zijn dan meteen en real-time beschikbaar:
Beperken Teamleader calls door facturen te filteren op datum
Een “view” is een voorgedefinieerde query. In dit geval is sprake van een erg trage Invantive view Teamleader.V2.InvoiceLinesAll op Teamleader met daarin per factuur de factuurregels. De regels zijn zichtbaar als je in Teamleader doorklikt op een factuur.
Helaas is het op dit moment niet vlot mogelijk om alle factuurregels met enkele Teamleader API-verzoek op te halen uit Teamleader. Per individuele factuur is een apart Teamleader API-verzoek nodig om de regels op te vragen. In combinatie met vrij beperkte limieten qua API-calls op Teamleader veroorzaakt het ophalen van alle factuurregels een fors vertraging bij gebruikers met duizenden facturen.
Echter, vaak kan men voor de Power BI dashboards volstaan met de meest recente factuurregels, bijvoorbeeld omdat eerder in Teamleader gemaakte factuurregels niet meer relevant zijn voor de rapportage of automatisch samengevoegd kunnen worden met eerdere downloads op basis van de kennis van het eigen facturatieproces.
Filterstap
Meestal wordt daarom een filterstap toegevoegd in Power BI op de factuurdatum van de Teamleader facturen. Meer optimalisatietips voor Power BI zijn te lezen in Overzicht van Power BI-technieken om prestaties en downloadtijd te verbeteren. Deze filterstap moet zo dicht mogelijk tegen het downloaden van de data aanzitten zodat query folding werkt.
Het doorgeven van de filterstap vanuit Power BI aan Invantive Cloud is soms wat lastig voor dashboardbouwers, bijvoorbeeld doordat er tussen de tabel ophalen en de filterstap andere filterstappen tussen moeten zitten waardoor Power BI’s “query folding” niet werkt en het filter op de Teamleader data niet doorkomt en doorgegeven kan worden aan Teamleader.
In een dergelijke geval biedt een eigen view een eenvoudige oplossing om de filtering op factuurdatum naar Invantive Cloud te verplaatsen en zo het datavolume en vooral de doorlooptijd sterk te beperken.
Eigen view op Teamleader factuurregels
De view InvoiceLinesAll is een query op twee Teamleader tabellen, waaronder een “tabelfunctie”:
select p.*
, l.* prefix with 'invoice_'
from Teamleader.V2.Invoices l
join Teamleader.V2.InvoiceLinesById(l.id) p
We willen graag dat alleen facturen gefactureerd vanaf het begin van het vorige kalenderjaar verwerkt worden, waardoor het aantal aanroepen van de Teamleader V2 API tabelfunctie InvoiceLinesById sterk omlaag gaat.
De eerste dag van het huidige jaar kan eenvoudig opgevraagd worden door aan trunc te vragen de “komma” twee posities naar links te schuiven en af te ronden en dan de komma weer terug te schuiven met als eindresultaat de eerste dag van het huidige kalenderjaar:
select trunc(sysdateutc, -2)
Uit trunc(sysdateutc, -2) komt voor 10-2-2022 de datum: 1-1-2022.
Vervolgens nemen we een datum 12 maanden eerder met:
select add_months(trunc(sysdateutc, -2), -12)
De nieuwe geoptimaliseerde Teamleader view voor Power BI wordt dan:
create or replace view InvoiceLinesLastTwoYearsAll
as
select p.*
, l.* prefix with 'invoice_'
from ( select l.*
from Teamleader.V2.Invoices l
where invoice_date >= add_months(trunc(sysdateutc, -2), -12)
) l
join Teamleader.V2.InvoiceLinesById(l.id) p
Deze view zal alleen de factuurkop en factuurregels teruggeven van facturen die de sinds de start van het vorig kalenderjaar gemaakt zijn in Teamleader.
View beschikbaar maken in Power BI
Een eigen view is na definitie alleen beschikbaar in de sessie die de view gedefinieerd heeft met create or replace view. Power BI gebruikt een database. Bij het openen van de Invantive database wordt de zogenaamde “Startup SQL” uitgevoerd.
De Startup SQL kan tegenwoordig door alle gebruikers ingesteld worden en is te vinden bij de database:
Het invullen van de viewdefinitie zorgt er voor dat na het verlopen van de metadata (tabellenlijst) uit cache (hier na 57.600 seconden) er ook een tabel beschikbaar in Power BI met de naam:
InvoiceLinesLastTwoYearsAll@DataDictionary
Deze kan dan gebruikt worden waar voorheen InvoiceLinesAll@tlr gebruikt werd.