https://www.sikich.com

Power Platform and Dynamics 365

Justin Languirand

|

Dec 17 2018

|

3 min read

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!

This publication contains general information only and Sikich is not, by means of this publication, rendering accounting, business, financial, investment, legal, tax, or any other professional advice or services. This publication is not a substitute for such professional advice or services, nor should you use it as a basis for any decision, action or omission that may affect you or your business. Before making any decision, taking any action or omitting an action that may affect you or your business, you should consult a qualified professional advisor. In addition, this publication may contain certain content generated by an artificial intelligence (AI) language model. You acknowledge that Sikich shall not be responsible for any loss sustained by you or any person who relies on this publication.

About the Author

Justin Languirand

Solution Architect 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.