Using Power Platform Dataflows to extract and process data from
How do you get data out of Business Central so that you can use it in other systems? For example, how can you visualize Business Central data in Power BI or analyze it in Azure Databricks?
The main way to get data out of Business Central (and in again for that matter) is through the OData web services at https://api.businesscentral.dynamics.com/v1.0/ODataV4. This works well in many cases, but it also has some shortcomings; especially around performance: Whenever you call an OData Web service, the Business Central service will create a short-lived session for you, run some AL code, get some data from a database, run some more AL code, serialize the results to json, and return it to you.
There are many things going on, and invariably it will never be as efficient as if you accessed the data directly in the database. This also impacts how you can use the data it becomes less appealing to do exploratory analysis on the data where you dont know up front exactly which data you will need. Note, that there are good reasons why we dont allow customers to access the database directly. A main reason is security; Business Central must enforce access control to the data, i.e., respect the permissions of the user who is trying to access the data, and this requires running code.
This blog post is the first in a short series of blog posts, which explore using Power Platform Dataflows to get data out of Business Central through OData to subsequently allow customers and partners to do arbitrary and efficient analysis on that data.
You manage Power Platform Dataflows inside the Power BI website, and this also illustrates that a main consumer of Dataflows is Power BI. In the first two blog posts of this short series, we will explore how to use Dataflows with just one purpose; to analyze and visualize the data in Power BI. In the third blog post, we will then set the data truly free, which allows innumerable scenarios on top of the data, including analyzing it in Azure Databricks, copying it to Azure Data Warehouse, etc.
In this first blog post, we take the first steps by simply looking at how you can visualize Business Central data in Power BI using the OData endpoint. The result can be illustrated like this:
This serves as a stepping stone for when we introduce Dataflows in the second blog post.
OData Web Services in Business Central
If you open the Web Services page in Business Central, you can see the list of exposed web services. Below is a screenshot of the Web Services page in one of my environments:
If you copy the OData V4 URL of the first line, you get something like this: https://api.businesscentral.dynamics.com/v1.0/81f327b7-6780-453d-8325-40e1a8e68ec9/ODataV4/Company(‘CRONUS%20USA%2C%20Inc.’)/purchaseDocumentLines
We will only need the first portion of this URL, namely this: https://api.businesscentral.dynamics.com/v1.0/81f327b7-6780-453d-8325-40e1a8e68ec9/ODataV4/Company(‘CRONUS%20USA%2C%20Inc.’) (of course, your AAD tenant Id and company name will be different than mine).
Querying OData Web Services in Power BI Desktop
Power BI is an online service that you can access at https://powerbi.microsoft.com. You can do much authoring and exploration of data here, without ever leaving the browser.
There is also a very powerful desktop application Power BI Desktop that enables more advanced authoring of datasets and reports. Once you have created a nice report with Power BI Desktop, you can upload it to Power BI online where you can then view, explore, and share the results.
In this blog post, we will use Power BI Desktop, which can be downloaded at https://powerbi.microsoft.com/desktop.
Inside Power BI Desktop, we can access the OData URL that we copied earlier: Click Get Data, then locate the OData Feed, and then click Connect:
Now, paste in the URL that you copied earlier:
The easiest and most secure way to authenticate to Business Central is by using Organizational account. Select that and click Sign in to sign in with your Azure Active Directory account. Once logged in, the screen changes to this:
After clicking Connect, Power BI Desktop will call Business Central to get a list of exposed Web services back:
Click the Table link for, for example, purchaseDocumentLines.
You can choose to make additional transformations (filtering, calculations, splitting, grouping, etc.) of the data right here. We will remove the lineNumber column, and we will rename the query from Query1 to PurchaseDocumentLines. We will not go into details on the capabilities of Power Query you can read more about the capabilities at https://docs.microsoft.com/en-us/power-query/power-query-quickstart-using-power-bi.
Close the window when you are done. You will notice that a new PurchaseDocumentLines data source has appeared in Power BI Desktop:
Lets make a quick visualization of some data. If you click the documentNumber, description, and amount fields, you will get a table that looks like this:
Since this blog post is not about Power BI as such, we will keep it at this simple visualization. We will save the report to a file PurchaseDocumentLinesReport.pbix. Next step is to move it to Power BI online.
Uploading the report to Power BI online
Power BI online is organized in so-called workspaces. Each user has a personal workspace called My workspace. This is where we will upload the report that we just created.
There are different ways to upload the report, both from Power BI online and from Power BI Desktop. We will do it from Power BI Desktop.
First, you need to log into Power BI Desktop with your Azure Active Directory account. Click Sign in in the upper right corner, enter your email address and your password.
Now you can click the “Publish” button:
Select My workspace, and soon you should see this message:
If you log into Power BI online, you should see that My workspace now has some new elements; a report and a dataset. Your report looks the same as it did in Power BI Desktop:
If you have changed the data in Business Central and want it refreshed in Power BI, you can click Refresh now on the dataset:
However, since Power BI online doesnt have your credentials, you will be prompted to log in before the data can be refreshed. You can obviously also configure scheduled refresh.
This first blog post showed how we can connect Power BI to the OData Web services of Business Central. The setup we ended with can be illustrated like this:
The dataset has a private data store, and we can refresh the data in that data store either manually or on a schedule. Reports can visualize the data in datasets.
In the next blog post, we will introduce Power Platform Dataflows and expand on the setup that we created in this blog post.
The post Using Power Platform Dataflows to extract and process data from Business Central – Post 1 appeared first on Dynamics 365 Blog.