In het verleden heeft mijn collega Gideon een blog geschreven over het self service ophalen van data uit Exact Online. In deze blog is ingegaan op de authenticatie en het aanroepen van de API. In dit blog wil ik verder ingaan op het ophalen van deze data door middel van een aangepaste functie in Power BI.
Let op! In deze blogpost ga ik er vanuit dat de authenticatie inmiddels al is uitgevoerd. Nog niet gedaan? Lees dan eerst de blog van Gideon.
Inleiding
Eén van de eigenschappen van de API van Exact Online is dat er maximaal 60 regels per keer ontvangen kunnen worden. Bij het ophalen van bijvoorbeeld alle financiële transacties kunnen we er vanuit gaan dat dit meer dan 60 transactieregels zijn. Om alle transactieregels op te halen zal de API van Exact Online meerdere keren aangeroepen moeten worden. Hiervoor werkt Exact Online met de Cursor-Based pagination techniek. Dit betekent dat bij elke aanroep naar de API er een link mee teruggestuurd wordt waarmee de vervolgpagina opgehaald kan worden.
Cursor-Based pagination in Power BI ophalen
Het ophalen van deze zogenaamde Cursos-Based pagination kan in Power BI opgehaald worden door gebruik te maken van een zelf te maken functie en de functie List.Generate().
Allereerst maken we een functie waar we een willekeurige URL van Exact Online in kunnen plaatsen. Deze functie haalt vervolgens deze gegevens op uit Exact Online en geeft deze terug in een tabel. Dit is uiteraard nog gelimiteerd tot de eerste 60 regels.
Aangepaste functie maken
De Query die via de Geavanceerde Editor geplakt moet worden is:
| [code language=”csharp”] let// Start Authentication // actualUrl = URL & “/api/oauth2/token”, cntnt = Text.ToBinary(Uri.BuildQueryString( [refresh_token= Refresh_token ,grant_type=”refresh_token” ,client_id= Client_id ,client_secret=Client_secret]) ),options = [Headers =[#”Content-type”=”application/x-www-form-urlencoded”],Content=cntnt], result = Web.Contents(actualUrl, options),#”JSON” = Json.Document(result), access_token= #”JSON”[access_token], AccessTokenHeader = “Bearer ” & access_token,// End Authentication //// Set variables //varURL= URL & “/api/v1/” & Number.ToText(DivisionCode) & “/financial/GLAccounts/”, iterations = 60, // End variables // // Start dataload // Source = (Web.Contents( varURL, [Headers=[Authorization=AccessTokenHeader]])), |
Let op! De variabelen zijn reeds ingesteld in het vorige blog.
De uitkomst van deze query laat de eerste 60 Grootboekrekeningen zien en de URL voor de volgende 60 Grootboekrekeningnummers. Voor de uiteindelijke functie hebben we nodig dat de data (60 grootboekrekeningnummers) en de link naar de volgende 60 regels naast elkaar staan.
Hiervoor vervangen we de volgende regels:
| [code language=”csharp”] xml = try Xml.Tables(Source,null,65001) otherwise null in xml [/dm_code_snippet] |
in:
| [code language=”csharp”] xml = try Xml.Tables(Source,null,65001) otherwise null, entry = xml{0}[entry], link = xml{0}[link], nextLink = link{1}[#”Attribute:href”] in [Data=entry, Next=nextLink] [/dm_code_snippet] |
Het resultaat is 2 regels. Regel 1 bevat de data van de eerste 60 regels. Regel 2 bevat de link naar de volgende 60 regels. Om deze Query om te zetten naar een functie zullen de volgende aanpassingen doorgevoerd moeten worden:
Helemaal bovenaan de query moet een nieuwe regel toegevoegd worden:
| [code language=”csharp”]
()=> [/dm_code_snippet] |
Deze regel zorgt ervoor dat Power BI de regels eronder als functie gaat lezen. Deze functie geven we in dit geval één variabele mee, namelijk de URL. Deze dient tussen de haakjes vastgelegd te worden. Meerdere variabelen kunnen opgegeven worden door een komma tussen de variabelen te plaatsen. Op de plaats waar we de tekst uit de variabele terug willen laten komen in de query zetten we de naam van de variabele nogmaals neer.
In onderstaand voorbeeld heet mijn variabele ‘varURI’ en geef ik deze het type ‘text’.
| [code language=”csharp”]
(varURI as text)=> let // Start Authentication // actualUrl = URL & “/api/oauth2/token”, options = [Headers =[#”Content-type”=”application/x-www-form-urlencoded”],Content=cntnt], // End Authentication // // Set variables // varURL= URL & “/api/v1/”& Number.ToText(DivisionCode) & “/” & varURI, // End variables // // Start dataload // Source = (Web.Contents( varURL, [Headers=[Authorization=AccessTokenHeader]])), // End dataload // |
Let op! De overige variabelen zijn reeds ingesteld in het vorige blog.
Na het opslaan van deze query is onderstaande scherm zichtbaar:
Bij het invullen van bijvoorbeeld de URL ‘financialtransaction/TransactionLines/’ krijg ik van de functie 2 regels teruggestuurd. 1 regel met daarin de tabel waarin de eerste 60 transactieregels aanwezig zijn en 1 regel waarin de link voor de volgende 60 regels aanwezig is.
In mijn volgende blog zal ik ingaan op de functie List.Generate() zodat alle regels uit de API van Exact Online uitgelezen kunnen worden.
Meer weten over onze oplossingen?
Onze consultants hebben veel ervaring binnen een grote verscheidenheid aan branches.
Eens verder brainstormen over de mogelijkheden voor jouw organisatie?
Maak kennis met onze specialist Arnoud van der Heiden.
Wellicht herkent u een van deze vraagstukken.
Inmiddels hebben wij ruime ervaring opgedaan met Business Intelligence. Wij staan ook u graag te woord bij vragen of opmerkingen.
Business Intelligence (BI) is het technologie gedreven proces van gegevensanalyse dat bedrijven in staat stelt om waardevolle inzichten te halen uit hun gegevens. BI-oplossingen kunnen helpen bij het verzamelen, analyseren en presenteren van bedrijfsgegevens om betere beslissingen te nemen en concurrentievoordeel te behalen.
BI richt zich op het verzamelen, analyseren en presenteren van historische en huidige gegevens om bedrijfsprestaties te meten en te rapporteren. BA daarentegen richt zich meer op het voorspellen van toekomstige trends en mogelijkheden door geavanceerde analysemethoden en -modellen toe te passen op gegevens.
Het gebruik van BI-oplossingen kan bedrijven helpen om hun prestaties te verbeteren, betere beslissingen te nemen en concurrerend te blijven. Het kan hen helpen om inzicht te krijgen in klantgedrag, trends te identificeren, kosten te verlagen, de efficiëntie te verbeteren en nieuwe zakelijke kansen te ontdekken.
Growteq biedt verschillende BI-oplossingen aan, waaronder Power BI van Microsoft, QlikView en Qlik Sense, Tableau en SAP BusinessObjects. Elk van deze oplossingen heeft zijn eigen sterke punten en functies, en kan worden aangepast aan de behoeften van een bedrijf.
Ja, Growteq kan bedrijven helpen bij het implementeren van BI-oplossingen door het bieden van strategisch advies, consultancy en implementatiediensten. Ze werken samen met klanten om oplossingen op maat te maken die aan hun specifieke behoeften voldoen en hen helpen om waarde te halen uit hun gegevens.
De kosten van BI-oplossingen kunnen variëren afhankelijk van de grootte van het bedrijf, de omvang van de implementatie en de gekozen oplossing. Growteq werkt samen met klanten om oplossingen te vinden die passen bij hun budget en behoeften. De voordelen van het gebruik van BI-oplossingen kunnen echter vaak opwegen tegen de kosten, omdat ze bedrijven kunnen helpen om hun prestaties te verbeteren en hun concurrentievoordeel te behalen.
De implementatie van een BI-oplossing omvat meestal de volgende stappen:
- Definiëren van de doelstellingen en vereisten van het BI-project
- Verzamelen en integreren van relevante gegevens uit verschillende bronnen
- Het ontwikkelen van een datawarehouse om de gegevens op te slaan in een logisch model
- Implementatie van dashboards, KPI’s en visualisatie voor gegevenspresentatie
- Training van medewerkers
- Continue monitoring en optimalisatie van de BI oplossing
BI maakt verschillende soorten analyses mogelijk, waaronder:
- Descriptive analyse: Hierbij worden historische gegevens gebruikt om te begrijpen wat er in het verleden is gebeurd.
- Diagnostische analyse: Hierbij wordt onderzocht waarom bepaalde gebeurtenissen of trends zich hebben voorgedaan
- Voorspellende analyses: Hierbij worden gegevens en modellen gebruikt om toekomstige gebeurtenissen en trends te kunnen voorspellen
- Prescriptive analyse: Hierbij worden aanbevelingen gedaan over mogelijke acties op basis van analyse en voorspelling
Enkele populaire BI-tools zijn o.a.:
- Microsoft Power BI
- QlikView
- Qlik Sense
- Tableau
- MicroStrategy
- IBM Cognos
Business Intelligence (BI) kan gegevens uit verschillende bronnen gebruiken, zoals:
- Interne databases en systemen (bijv. CRM-systemen, ERP-systemen)
- Externe gegevensbronnen (bijv. marktonderzoekgegevens, sociale media)
- Gestructureerde gegevens (bijvoorbeeld databases en spreadsheets)
- Ongestructureerde gegevens (bijv. tekstuele gegevens, e-mails, logboeken etc.)
- Big data-bronnen (bijv. sensorgegevens, weblogs)
Growteq heeft ervaring met al deze bronnen.
Selfservice BI verwijst naar het vermogen van niet-technische gebruikers om zelfstandig gegevens te verkennen, te analyseren en rapporten en dashboards te maken met behulp van gebruiksvriendelijke BI-tools. Enkele voordelen van selfservice BI zijn o.a.:
- Verminderde afhankelijkheid van IT-afdelingen voor rapportage en analyses
- Snellere besluitvorming doordat gebruikers direct toegang hebben tot benodigde gegevens
- Verhoogde flexibiliteit en aanpasbaarheid bij het verkennen van gegevens
- Stimuleert gegeven gestuurde cultuur binnen de organisatie
8 reacties. Leave new
Ik krijg de foutmelding ‘Token Literal verwacht’ bij het &-teken van >
Hoe dit op te lossen
Hi Willem,
Bij het plakken van de code naar de blog is niet alles netjes meegekomen. Dit is nu aangepast zodat je de juiste code kan kopieren.
Succes!
Nu werkt het wel, krijg alleen de volgende melding:
‘Geef op hoe u verbinding wilt maken.’ Met de knop ‘Referenties bewerken’. Ik kan kiezen tussen Anoniem, Windows, Basis, Web-api, Organisatieaccount
Hier moet je kiezen voor Anoniem. De authenticatie vind plaats via een Token.
Mocht het niet lukken met de authenticatie. Probeer dan eerst het blog van mijn collega Gideon: http://blog.refine-it.nl/self-service-data-ophalen-vanuit-exact-online-in-power-bi/
Hierin is de werkzijze rondom authenticatie beschreven.
Via Anoniem werk het, thanks.
Wel blijft de query op een of andere manier de tabel ophalen van ‘Financial/GLaccounts’ uit de blog van Gideon, ondanks dat ik de URI parameter heb aangepast of iets anders invul voor varURI bij het aanroepen van de functie.
Kijk nog even in het laatste script op regel 26.
Denk dat hier in jouw code nog hard de verwijzing naar Financial/GLaccounts in.
Let even op dat je bij het aanroepen van een entrypoint altijd een ‘/’ aan het einde meegeeft (‘Financial/GLAccounts/’).
Ik ben benieuwd naar deel 3!
Ha Willem. Deel 3 staat online!