Dynamics 365 F&O/AX Dynamics 365 CE / CRM Dynamics GP Business Central / NAV UG Dynamics SL Power Platform Fabric UG MS Copilot UG

[Ian Waring]

We need to write simple Power BI dashboard that lists all transactions in the current fiscal year for a given customer account, cost center, journal number, link to invoice, supplier number and supplier name. However, we can find no reference to the schema of D365 nor any idea of the common fields that implement the joins in order to work out how to write such a nominally simple dashboard with 1-2 filters.

Is there any reference work that would help is effect all the joins to do this sort of query?

We don’t mind deploying this using Power BI embedded on Production via LCS – nor using Power BI Desktop Pro via PowerBI.com (if indeed PowerBI.com is allowed to do DirectQueries on the D365 Production Database). If it helps, we’re on D365 F&O EE V7.3 Update 12.

Any ideas?

——————————
Ian Waring
Finance Special Projects
Jisc
Harwell Didcot
——————————

[Mark Schurmann]

Firstly, unless you have some magic I’m unaware of, I think you are mistaken on your platform version.  Microsoft started auto deploying the platform upgrades back in the spring, so I think you might be on 15.

You may be able to cobble together the customer transaction history with an Odata feed using a combination of the Sales Invoice, Customer Payment Journal, and Customer Invoice Journal (aka Free Text Invoice).  Alternatively, you could push these to an BYOD database to get around the limitation of Odata.  

I’m afraid I have not worked with the embedded PowerBI to speak to how well it will work with the connections for Odata or BYOD, but I think it should work.

FYI, we are on D365FO/PU15, and recently deployed a hotfix for the Sales Invoice data entity.  It was not displaying the credit invoices.  With the hotfix, it looks like it also now includes the Free Text Invoices.  This may simplify you quest, but I’m not sure if the hotfix is available for your version.  I would give you the KB, but our partner did not reference it on the help ticket and I am not able to identify it through the issues search.

——————————
Mark Schurmann
Accounting Systems Manager
Automobile Protection Corp
Norcross GA
——————————
——————————————-

[Ian Waring]

Thankyou Mark,

Our partner stalled the update as we went into production, but we have PU15 loaded on our test system ready to redo UAT. Will ship that onto Production, then do likewise for V8.1 (we’re told we can jump straight over V8.0). Just wanting to get visibility of the new Regression testing tool for D365, but not seen that yet – so we can speed our upgrade checks.

We’ve found instructions to get a directquery connection to AxDW, but are just trying to see if that contains order line detail (alongside it’s aggregations). We can see the SQL that generates each form on D365, so have the steps for going TB, down to journals, down to supplier invoices, and the associated joins. If we can’t, then it’s back to doing queries into AxDB (via localhost) on Power BI embedded, and deploying via LCS – much more around the houses 🙂

If we pull it off, I’ll report back here 🙂

——————————
Ian Waring
Finance Special Projects
Jisc
Harwell Didcot
——————————
——————————————-

[Jeff Bolkovatz]

Ian,

I thought PowerBI embedded only has access to AxDW. Have you been able to connect a PowerBI embedded report to the AxDB database?

——————————
Jeff Bolkovatz
Jenson USA
Riverside CA
——————————
——————————————-

[Rahul Mohta]

Hi Jeff,

As of 8.1 version you can only use AXDW in embedded PowerBI.
If need to build reports on PowerBI.com or PowerBI report server then BYODW is the way forward.

Let me know if this helps.

Regards

——————————
Rahul Mohta
Advisor – #D365FO
Real Dynamics
Irvine CA
——————————
——————————————-

[Rohit Kanwara]

Hi Jeff/Rahul,

BYODW might be the way forward for you, but usually it is very time consuming and costly. Not to mention requires core SQL SSIS/SSAS/SSRS and ETL/ELT skills to build it.
If it is of interest ZAP builds a DW for AX out of the box. We have been working with AX since the 2009 version and connect with AX within hours to bring the tables/customisations onto ZAP for modelling/transformation/building cubes for then reporting onto PowerBI.
Dynamics AX Data Management and Analytics Software | ZAP

Drop me a line if you’d like to discuss further.

Regards,
Rohit

——————————
Rohit Kanwara
UK&I Sales Executive
ZAP
——————————
——————————————-

[Solomon Maricle]

Though this post is older, I feel it is relevant with Version 10 of FO on the way to bring this up.

https://docs.microsoft.com/en-us/business-applications-release-notes/April19/dynamics365-finance-operations/erp-data-entity-store-byod-business-data-lake

——————————
Solomon Maricle
Technical Solutions Architect
PAR Technology
Morris NY
——————————
——————————————-