As described in this Dutch article you can easily use Invantive Cloud to retrieve data from over 70 platforms into Azure Data Factory and further process it in your datawarehouse or data lake.
Similar to Power BI and Power Query, Azure Data Lake is capable of discovering the list of tables (entity sets) from the OData4 metadata. However, sometimes I have been asked how to manually easily discover the list of tables and get data from it.
This is quite simple. Retrieve an Exact Online data set over OData consists of a number of steps:
- Create an account on Invantive Cloud.
- Set up the Invantive Bridge Online credentials for bridging from Cloud to OData4.
- Register an Exact Online database for all your companies.
- Open the Bridge Online OData4 URL in the browser.
- Find the tables.
- Retrieve the data from one table across all your Exact Online companies.
These basics also hold for other platforms such as Simplicate, Twinfield or AFAS Profit.
Create an Account
I will assume you have already created an account on Invantive Cloud as described in this topic.
Setup Credentials Bridge Online
After logging on to Invantive Cloud, you can go to your Profile and then select Bridge Online Configuration. Or you can directly navigate to the Bridge Online setup form:
In the Bridge Online configuration set the password for Bridge Online by entering an identical password in (1) and (2), and then press the Save-button. Store the password securely and safely. Also take note of the Bridge user name at (4).
Register Exact Online Database
Next, we will register a virtual Exact Online database which retrieves data across all your available Exact Online companies.
Click on the Databases option in the left-side menu and then choose the “New” button, which leads you to the following form:
Now select the Exact Online button and choose the country:
Enter your Exact Online credentials and approve access:
The database has been created with a data container on Exact Online, ready for use by Azure Data Factory. To enable access from Azure Data Factory you need to enter the required IP-addresses at (1) or enter ‘*’ to disable the IP-address check:
Note at (2) that all your Exact Online companies have been selected for retrieval for Azure Data Factory.
Finally navigate to the Bridge Online configuration settings using (3).
Open OData4 URL for Exact Online
The OData4 URL for Exact Online is displayed at the red (1):
Open a new browser window and copy and paste the URL, like “https://bridge-online.cloud/invantive-exact-online-3/odata4” into the browser.
Press enter and enter the user name displayed at the red (2), in the case “19…”. Complement it with the Bridge Online password previously set.
When successful, the list of over 1.000 Exact Online tables (APIs) is shown in the browser:
Find the Tables
Each table is mapped to an OData4 EntitySet. So for instance, the list of Exact Online apps is to be found as ExactOnlineREST.CodeValues.ComplaintStatuses@eol.
Retrieve Data from Exact Online companies
To retrieve the actual data across all your selected Exact Online companies, you just need to add the name of the OData4 entity set to the URL already displayed in your browser.
Combining these results in an URL like: https://bridge-online.cloud/invantive-exact-online-3/odata4/ExactOnlineREST.CodeValues.ComplaintStatuses@eol. Opening this URL in the browser returns the values:
