Power Platform and Dynamics 365

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

using Power BI with Dynamics 365

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

using Power BI with Dynamics 365

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

using Power BI with Dynamics 365

Now that I have the OData endpoints ready, I can bring them into Power BI.

Open Power BI Desktop

Click Get Data -> OData feed

using Power BI with Dynamics 365

using Power BI with Dynamics 365

For Authentication, click on Organizational Account and sign in with your D365 credentials.

using Power BI with Dynamics 365

Click Connect.

using Power BI with Dynamics 365

It shows me a preview of the data and now you can click load.

using Power BI with Dynamics 365

The data is now loaded into the application.

using Power BI with Dynamics 365

using Power BI with Dynamics 365

Right click on Query1 and rename it to “SalesInvoiceHeaders.”

using Power BI with Dynamics 365

Repeat the same steps for Invoice Lines.

using Power BI with Dynamics 365

using Power BI with Dynamics 365

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!

By |2018-12-17T09:29:16+00:00December 17th, 2018|Dynamics 365, Power BI, Technology|0 Comments

About the Author:

Justin Languirand
Senior CRM Technical Consultant with a focus on Microsoft Dynamics CRM, Data Integration (Scribe), Microsoft Dynamics GP, SQL Server, SSRS, SSIS, Jitterbit. Justin works with clients to transform data into information they use to run their businesses.
This material has been prepared for general informational purposes only and is not intended to be relied upon as accounting, tax, or other professional advice. Please refer to your advisors for specific advice.

Privacy Information

We use cookies to personalize the website for you and to analyze the use of our website. Click 'Privacy Options' to configure how we can interact with you and your device or computer.

Privacy policy | Close
Settings
private equity services construction accountants Agriculture Services Construction Services Non-Profit Services Government Services