Where do I begin with Power BI? This can feel like a daunting question. But you have to start by thinking about the data. More importantly, start thinking about the data model. Which table am I going to be pulling data from? Almost always with complex ERP systems, the data is going to be spread out amongst various tables. And that means you will have to relate the tables to each other and ultimately create a data model.
In this example, I’m interested in reporting on my customer sales. Nothing fancy, just some of the basic information, such as Sales by Year, Sales by Customer Group, Sales by Product Category, Top 5 Customers etc. I know that I will need to use sales invoices and sales invoice lines to drive the majority of these metrics. It just so happens that there are data entities called “salesinvoiceheaders” and “salesinvoicelines.”
You can find the list of public entities by inserting /data right after the core Dynamics 365 URL.
https://presls.cloudax.dynamics.com/data
Now that I have the name of the entities I’m looking for, I can query the endpoints for data and make sure it is a valid endpoint, and I do in fact retrieve data. Just insert the name of the entity after /data in the url string.
Note: the entity names are case sensitive
https://preslsd.cloudax.dynamics.com/data/SalesInvoiceHeaders
Microsoft has put together some great information on working with Dynamics 365 and OData queries:
https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/data-entities/odata
For example, I can filter OData queries on the different fields in the table. Let’s say I have 20 years of history and I really only want the last 3 years. I can filter the query for invoices greater than or equal to 1/1/2015 by including $filter=InvoiceDate ge 2015-01-01 on the end of my URL.
https://preslsd.cloudax.dynamics.com/data/SalesInvoiceHeaders?$filter=InvoiceDate ge 2015-01-01
Now that I have the OData endpoints ready, I can bring them into Power BI.
Open Power BI Desktop
Click Get Data -> OData feed
For Authentication, click on Organizational Account and sign in with your D365 credentials.
Click Connect.
It shows me a preview of the data and now you can click load.
The data is now loaded into the application.
Right click on Query1 and rename it to “SalesInvoiceHeaders.”
Repeat the same steps for Invoice Lines.
Clicking on the relationships tab
Power BI auto-linked the tables together on InvoiceNumber.
At this point, I can bring in any additional entities and create a date table to provide some context of time and finalize the data model.
Have any questions? Contact us at any time!
