Omzetten pbit Exact Online Controller naar Invantive Cloud

De Power BI connector bij Exact Online Controller is een van de Power BI connectoren in de markt waarmee u eenvoudig gegevens uit Exact Online kunt importeren als Power BI dataset. Na het importeren kunt u hiermee in een Power BI dashboard alle aanwezig data visualiseren.

In dit artikel leest u hoe u na configuratie van de Exact Online connector op Invantive Cloud (video aanmelden, video database registratie) tips om een bestaand Power BI dashboard om te zetten van de Power BI connector bij de Exact Online Controller naar Invantive Cloud. U kunt natuurlijk ook gebruik maken van de gratis voorbeelden zoals de Power BI template voor een dashboard met Exact Online resultatenrekening.

Werking Power BI connector voor Exact Online Controller

(Werking voor zover bekend)

De Power BI connector bij Exact Online Controller maakt gebruik van een replica van de Exact Online gegevens in een gedeelde SQL Server omgeving op Azure: de zogenaamde “Azure SQL Elastic Pool”. Hier wordt per relatie een database gedefinieerd waarvan middels IP-adres authenticatie en gebruikersnaam/wachtwoord per database rechten verleend worden. De database wordt met tussenvallen of doorlopend bijgewerkt op basis van gegevens uit Exact Online.

De naam van de SQL Server is:

eceolserver.database.windows.net

zoals zichtbaar bij de Data source settings:

Dankzij het gebruik van de standaard met Power BI meegeleverde SQL Server driver is geen aparte connector nodig. Hierdoor werkt een samengesteld rapport ook op PowerBI.com.

Omzetten Excel Controller naar Invantive Cloud

Invantive Cloud ondersteunt standaard alle REST (en XML) API’s van Exact Online. Daardoor zijn alle voor het omzetten benodigde gegevens aanwezig.

Voor Invantive Cloud is er een datamodel met tabellen en een grafische weergave.

De administraties kunnen in Power BI Desktop geconfigureerd worden door een filter in te stellen op een van de administratievelden zoals Division, of door de lijst van administraties te laten instellen op de Invantive Cloud database.

Met de onderstaande tabel kunt u de bij ons bekende Power BI connector voor Exact Online Excel Controller tabellen en views koppelen aan de door Invantive gebruikte namen in Invantive Cloud:

Excel Controller Invantive Cloud
Division SystemDivisions
Qsel_Financieel Onbekend.
Qsel_Financieel_Budget Onbekend.
Qsel_Grootboek Onbekend.
Qsel_GrootboekTot Onbekend.

Query Qs_Fin

De query Qs_Fin werkt op basis van de SQL Server objecten Qsel_Financieel en Qsel_Financieel_Budget (de afwisseling tussen Nederlandstalige en Engelstalige kolomnamen is nog onduidelijk qua herkomst):

select Datum
,      ProjectStartDatum
,      ProjectEindDatum
,      Created
,      VervalDatum
,      Modified
,      Koers
,      ExtraDutyAmountFC
,      ExtraDutyPercentage
,      BTW_Perc
,      Aantal
,      Betalingskorting
,      Omzet
,      Cash
,      BedragVV
,      BTW_Grondslag
,      BTW_Bedrag
,      Bedrag
,      RelatieLongitude
,      RelatieLatitude
,      Budget
,      RegelType
,      Status
,      Boekstuknr
,      LineNumber
,      MutatieType
,      Division
,      Jaar
,      Periode
,      Tijdvak
,      Factuurnr
,      OrderNumber
,      Grootboek_TypeCode
,      DocumentNumber
,      EC_VerdichtingCode
,      EC_HoofdverdichtingCode
,      EC_Verdichting
,      EC_Hoofdverdichting
,      Code_Sub_
,      Administratie_Code
,      Administratienaam
,      Administratie
,      Administratie_Division
,      Regelomschrijving
,      Selectie_Jaar
,      Selectie_JaarCash
,      Selectie_Datum
,      Selectie_Periode
,      ProjectKlant
,      ProjectCode
,      ProjectKlassen
,      Project
,      ProjectManager
,      ProjectType
,      Dagboekcode
,      Dagboek
,      MVA_Code
,      MVA
,      Kostenplaatscode
,      Kostenplaats
,      KostendragerCode
,      Kostendrager
,      RelatieklasseCode1
,      Relatieklasse1
,      RelatieklasseCode2
,      Relatieklasse2
,      RelatieklasseCode3
,      Relatieklasse3
,      RelatieklasseCode4
,      Relatieklasse4
,      RelatieklasseCode5
,      Relatieklasse5
,      RelatieklasseCode6
,      Relatieklasse6
,      RelatieklasseCode7
,      Relatieklasse7
,      RelatieklasseCode8
,      Relatieklasse8
,      Artikelcode
,      ArtikelOmschrijving
,      ArtikelOmschrijvingExtra
,      Artikelgroepcode
,      Artikelgroep
,      ArtikelKlasseCode01
,      ArtikelKlasseCode02
,      ArtikelKlasseCode03
,      ArtikelKlasseCode04
,      ArtikelKlasseCode05
,      ArtikelKlasseCode06
,      ArtikelKlasseCode07
,      ArtikelKlasseCode08
,      ArtikelKlasseCode09
,      ArtikelKlasseCode10
,      BalanceType
,      Grootboekcode
,      GrootboekOmschrijving
,      Grootboek
,      Soort
,      Level0_Code
,      Level0_Desc
,      Level0
,      Level1_Code
,      Level1_Desc
,      Level1
,      Level2_Code
,      Level2_Desc
,      Level2
,      Level3_Code
,      Level3_Desc
,      Level3
,      Level4_Code
,      Level4_Desc
,      Level4
,      DocUrl
,      BudgetScenarioCode
,      BudgetScenarioDescription
,      DocumentSubject
,      ModifierFullName
,      Notes
,      SerialNumber
,      Abonnement
,      TrackingNumber
,      Tracking
,      BTWCode
,      BTW
,      BTW_Type
,      YourRef
,      CreatorFullName
,      Valuta
,      Grootboek_Type
,      Betalingsreferentie
,      RelatieCode
,      Relatie
,      RelatieStatus
,      RelatiePlaats
,      RelatieLand
,      Lokatie
,      RelatieManager
from   Qsel_Financieel
union all
select Datum
,      ProjectStartDatum
,      ProjectEindDatum
,      Created
,      VervalDatum
,      Modified
,      Koers
,      ExtraDutyAmountFC
,      ExtraDutyPercentage
,      BTW_Perc
,      Aantal
,      Betalingskorting
,      Omzet
,      Cash
,      BedragVV
,      BTW_Grondslag
,      BTW_Bedrag
,      Bedrag
,      RelatieLongitude
,      RelatieLatitude
,      Budget
,      RegelType
,      Status
,      Boekstuknr
,      LineNumber
,      MutatieType
,      Division
,      Jaar
,      Periode
,      Tijdvak
,      Factuurnr
,      OrderNumber
,      Grootboek_TypeCode
,      DocumentNumber
,      EC_VerdichtingCode
,      EC_HoofdverdichtingCode
,      EC_Verdichting
,      EC_Hoofdverdichting
,      Code_Sub_
,      Administratie_Code
,      Administratienaam
,      Administratie
,      Administratie_Division
,      Regelomschrijving
,      Selectie_Jaar
,      Selectie_JaarCash
,      Selectie_Datum
,      Selectie_Periode
,      ProjectKlant
,      ProjectCode
,      ProjectKlassen
,      Project
,      ProjectManager
,      ProjectType
,      Dagboekcode
,      Dagboek
,      MVA_Code
,      MVA
,      Kostenplaatscode
,      Kostenplaats
,      KostendragerCode
,      Kostendrager
,      RelatieklasseCode1
,      Relatieklasse1
,      RelatieklasseCode2
,      Relatieklasse2
,      RelatieklasseCode3
,      Relatieklasse3
,      RelatieklasseCode4
,      Relatieklasse4
,      RelatieklasseCode5
,      Relatieklasse5
,      RelatieklasseCode6
,      Relatieklasse6
,      RelatieklasseCode7
,      Relatieklasse7
,      RelatieklasseCode8
,      Relatieklasse8
,      Artikelcode
,      ArtikelOmschrijving
,      ArtikelOmschrijvingExtra
,      Artikelgroepcode
,      Artikelgroep
,      ArtikelKlasseCode01
,      ArtikelKlasseCode02
,      ArtikelKlasseCode03
,      ArtikelKlasseCode04
,      ArtikelKlasseCode05
,      ArtikelKlasseCode06
,      ArtikelKlasseCode07
,      ArtikelKlasseCode08
,      ArtikelKlasseCode09
,      ArtikelKlasseCode10
,      BalanceType
,      Grootboekcode
,      GrootboekOmschrijving
,      Grootboek
,      Soort
,      Level0_Code
,      Level0_Desc
,      Level0
,      Level1_Code
,      Level1_Desc
,      Level1
,      Level2_Code
,      Level2_Desc
,      Level2
,      Level3_Code
,      Level3_Desc
,      Level3
,      Level4_Code
,      Level4_Desc
,      Level4
,      DocUrl
,      BudgetScenarioCode
,      BudgetScenarioDescription
,      DocumentSubject
,      ModifierFullName
,      Notes
,      SerialNumber
,      Abonnement
,      TrackingNumber
,      Tracking
,      BTWCode
,      BTW
,      BTW_Type
,      YourRef
,      CreatorFullName
,      Valuta
,      Grootboek_Type
,      Betalingsreferentie
,      RelatieCode
,      Relatie
,      RelatieStatus
,      RelatiePlaats
,      RelatieLand
,      Lokatie
,      RelatieManager
from   Qsel_Financieel_Budget

Query Qs_Grootboek

De query Qs_Grootboek werkt op basis van het SQL Server object Qsel_Grootboek:

select * 
from   Qsel_Grootboek

Query Qs_Division

De query Qs_Division werkt op basis van het SQL Server object Division:

select D.Hid
       Administratie_Code
,      D.Division
,      D.Description Administratienaam
,      D.Hid + ' - ' + D.Description
       Administratie
,      ltrim(str(D.Division)) + ' - ' + D.Description
       Administratie_Division
from   Division D

De velden van Division zijn in Invantive Cloud te vinden onder SystemDivisions.

Query Qs_Cashflow

De query van Qs_Cashflow is:

select D.Administratie_Code
,      D.Division
,      D.Administratienaam
,      D.Administratie
,      D.Jaar
,      D.Periode
,      D.Tijdvak
,      D.Datum
,      D.Dagboekcode
,      D.Dagboek
,      D.Boekstuknr
,      D.LineNumber
,      D.Bedrag
,      D.Cash
,      D.Regelomschrijving
,      D.MutatieType
,      D.Relatie
,      D.GLAccount
,      gt.Soort
,      gt.Grootboek
,      gt.Level1
,      gt.Level2
,      gt.Level3
,      gt.Level4
from   ( select f.Administratie_Code
         ,      f.Division
         ,      f.Administratienaam
         ,      f.Administratie
         ,      f.Jaar
         ,      f.Periode
         ,      f.Tijdvak
         ,      f.Datum
         ,      f.Dagboekcode
         ,      f.Dagboek
         ,      f.Boekstuknr
         ,      f.LineNumber
         ,      f.Bedrag
         ,      f.Cash
         ,      f.Regelomschrijving
         ,      f.MutatieType
         ,      f.Relatie
         ,      case
                when f.Grootboek_TypeCode = 20 
                then ( select top 1 D.GLAccount
                       from   Qsel_Financieel D
                       where  D.MutatieType In (20, 21) 
                       and    D.Grootboek_TypeCode Not In (20, 22, 24) 
                       and    D.Division = f.Division 
                       and    D.Account = f.Account 
                       order 
                       by     abs(f.Bedrag) desc
                     ) 
                else case
                     when f.Grootboek_TypeCode = 22 
                     then ( select top 1 D.GLAccount
                            from   Qsel_Financieel D
                            where  D.MutatieType In (30, 31) 
                            and    D.Grootboek_TypeCode Not In (20, 22, 24)  
                            and    D.Division = f.Division 
                            and    D.Account = f.Account 
                            order 
                            by     abs(f.Bedrag) desc
                          ) 
                     else f.GLAccount
                     end 
                end 
                GLAccount
         from   Qsel_Financieel f
         where  f.MutatieType in (40) 
         and    f.Grootboek_TypeCode not in (10, 12)
       ) D
join   Qsel_GrootboekTot gt
on     D.GLAccount = gt.GLAccount   
and    D.Division  = gt.Division

Notitie 1. Hou er rekening mee dat voor grote aantallen administraties en/of grote administraties het gebruik van de *Incremental tabellen de voorkeur heeft.

Notitie 2. Standaard worden alle administraties in Exact Online waar de app rechten op heeft gekregen verwerkt. Dit kan ingesteld worden via app machtigingen (zie Nieuwe Exact Online administratie niet zichtbaar in Invantive Cloud voor Power Query/BI - #2 door guido.leenders), maar ook verfijnd via een specifieke lijst of via Power BI Desktop. Voor eenvoudige consolidatie van en benchmarks over administraties heen worden de gegevens ingelezen uit Exact Online administraties onder elkaar gezet, zodat het optellen voor consolidatie geen verdere kennis vereist.