Installation steps of Exact Online driver for Microsoft Power BI

Question

I want to use Microsoft Power BI Desktop edition to run reports including data from Exact Online. The Exact Online driver is not available with the pre-configured sources Microsoft offers.

How do I install an Exact Online driver?

Answer 1

There are a number of steps currently, there is a setup possible through a *.msi file. These steps also hold for other platforms such as Freshdesk, Microsoft Dynamics CRM, Twinfield, Teradata, XML files etc:

• Run the MSI of the Invantive ADO.net provider, such as downloadable test version ADO.net provider:
• When you solely want to connect to one provider use the sample listed below at Code Exact Online.
• When you want to connect to multiple physical databases: install Invantive Query Tool and run discovery, OR copy an existing settings.xml with Exact Online for the country you want to use in it from another PC.
• For use without local installation or with Power BI web or dataflows, use the OData4 connector from Invantive Bridge Online (part of Invantive Cloud).
• Install and start Microsoft Power BI desktop.
• Go to Get Data -> Blank Query:

• Choose Advanced Editor:

• In Query editor paste the code listed below.
• Then click "Edit Permission" on the message "Permission is required to run this native database query."
• Then click "Run".
• When requested enter user name and password as you use with Exact Online:

• Two phase authentication is not yet supported, so you will need to use a separate log on code when you need two phase authentication for your normal account.
• From here on use the results as a normal SQL Server or alike query:

• Or graphically:

Code Exact Online

let
    Source = AdoDotNet.Query
             ( "Invantive"
             , "provider=ExactOnlineAll;apiUrl=https://start.exactonline.nl;"
             , "select * from PayablesListOverdue"
             )
in
    Source

Please note that the text within the AdoDotNet.Query does not support multiline statements, so the easiest way is to put the queries somewhere else and then copy/paste them here while replacing line feeds by space.

The SQL resembles Microsoft SQL Server or Oracle syntax, but is actually Invantive SQL (Invantive SQL Grammar). The table names and connector attributes are available online in the documentation.

Please note that the queries can be created using the Online SQL Editor or one of the query products. Exact Online has something like 500 APIs available. Not everything, for instance item average cost price is NOT available.

Please note that Power BI retrieves a lot of information from Exact Online. When running across multiple companies, it can retrieve them in parallel. However, when you for instance download all financial transactions of a company, that can take a long time. Also, the information is normally stored within the pbix file, so that one can become very large. As always, apply proven Power BI design guide lines for optimal results.

Code XML Audit Files

You can use the same approach for XML audit files. Use the following query for XML Audit File Cash Registers 3.0 (xaa 3.0):

let
    Source = AdoDotNet.Query
             ( "Invantive"
             , "provider=Xaa30;"
             , "select * from LocationCashRegisterCashTransactionLines"
             )
in
    Source

Please note that especially with detailed processing of cash register audit files you will get a LOT of cash register transaction and large Power BI sets and Power BI has some problems handling large volumes. Cash register or financial audit files larger than 1 GB will run slow and require a lot of physical memory to be present.

Please note that with audit files, Power BI insists that you enter a user. Just enter 'x' or something. It is ignored.

Sample result:

Code Twinfield ERP (obsoleted with new versions)

You can use the same approach for Twinfield. But, Twinfield also requires you to specify a company or environment code on log on as third field, so you need to specify that one too.

Option 1 - Twinfield environment code to Power BI

let
    Source = AdoDotNet.Query
             ( "Invantive"
             , "provider=Twinfield;environment=YOUR_ENVIRONMENT_CODE"
             , "select * from Twinfield.Twinfield.Periods"
             )
in
    Source

Option 2 - Twinfield environment code to Power BI through connection string

You can also specify the values of attributes on the connection string in a separate window in Power BI. This keeps you code clean of stuff associated with users: