Connect Power BI with Exact Online more efficiently than through HTML

Question

I am using Microsoft Power BI with Exact Online using Data Access Point on https://data-access-point.com using queries such as select * from exactonlinexml..aroutstandingitems. However, the URL must contain the user name and password to allow Data Access Point to connect to Exact Online on my behalf.

This is not desired behaviour; I would like it to be more easy to change the password and also I would like that the pbix file to not contain the user name and especially password.

I've tried generating the pbix file contents by hand, but the format seems to change as Microsoft Power BI further matures and this causes major versioning issues.

How can I avoid embedding the user name and password for Exact Online the Data Access Point URL for Power BI?

Data Access Point requires with the current possibilities of Power BI that the user name and password are embedded in the URL. Some Power BI releases supported basic authentication with an URL but that seems broken. Remember that Power BI is free for a reason: "you are the product " :-) Did you try using it with the ADO.NET provider for Exact Online and others already? — Guido Leenders, Nov 05 '16 at 09:21
Thanks @GuidoLeenders Where can I find the ADO.Net provider? How does it work? — KPI Solutions, Nov 05 '16 at 09:22
Sorry, missed the question. The ADO.Net provider for Exact Online with Power BI can be found on http://ado.net-provider.cloud/. But note that the on-premise version stays hard to configure, it easier to use Bridge Online instead. — Guido Leenders, Dec 05 '20 at 11:20

score 1 · Accepted Answer · edited Feb 08 '17 at 08:39

When the distribution of the pbix files with embedded Exact Online user logon code and password is out of your control or does not meet your security standards, then you can use the ADO.Net provider for Exact Online. However (I work there) it is not a free product as currently Data Access Point. But it works fine and is compatible on SQL level (both support SQL v2 grammar). Steps to install (might improve in the future):

Register the ADO.NET provider in machine.config. In Power BI:
Get Data -> Other -> Blank Query or Edit Queries -> New Source -> Blank Query.

Advanced Editor and use the following as source.

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

When you need another source as ExactOnline, replace ExactOnlineAll by the corresponding provider such as Teradata or CbsNl. There is yet no support for distributed queries.
When you need another country than NL, use the corresponding URL, such as https://start.exactonline.co.uk. It does not work for France and Spain.
And then update the SQL query (put it all on one line or copy/paste from an editor which allows you to enter multi-line statements.
Click 'Done'.
Grant permission when asked (Power BI can be persistent about that, be persistent too).
When Power BI asks for your credentials, select 'Database' and enter your full Exact Online credentials.
Remember to specify a useful name.
Close and apply.
Enjoy.

Thanks for your help @GuidoLeenders – KPI Solutions Nov 07 '16 at 09:59 — KPI Solutions, Nov 07 '16 at 09:59

score 1 · Answer 2 · edited Dec 05 '20 at 11:23

You can also install the Custom Connectors for Power BI from ado.net-provider.cloud. First enable preview feature on July 2017 or later release.

Note! The custom connector program of Microsoft is not yet fully available as of 2020 for certifications by 3rd party. As an alternative connect to Exact Online using the OData connector Power BI and Power Query ship with and register a virtual database on Invantive Cloud.

And then choose a connection such as Exact Online:

And then choose a table.

Connect Power BI with Exact Online more efficiently than through HTML

2 Answers2