Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Get Data from Dynamics...
Finance | Project Operations, Human Resources, ...
Answered

Get Data from Dynamics 365 Finance and Operations (Online) in Power BI

(0) ShareShare
ReportReport
Posted on by mahmoud_algindy 277

Dear Friends,

I am trying to create some Power BI Reports from Dynamics 365 Finance and Operations (Online),

I used OData Feed, I am able to see all available tables from Dynamics 365 F&O Online, but I am getting blank tables in 90% of the data, however, I have a lot of customers in Dyn365 FO.

I would like to know:

  • What setup I should do in Dynamics to be able to retrieve these data?
  • What's the Table name for Voucher Transactions?
  • I am looking for documentation that explains these tables?
  • I want to access the same data for all available entities (Companies) not only the company assigned to the user?

pastedimage1650049582657v1.png

  • Ian Waring Profile Picture
    Ian Waring 436 on at
    RE: Get Data from Dynamics 365 Finance and Operations (Online) in Power BI

    Everything is headed in the right direction. Courtesy of the System Integrator we use (shout out to Tisski, who've been brilliant), we now have our Management Accounts model (including the GL and budgets) and a Trial Balance model using just tables (not entities) in Power BI. WHat used to take a couple of hours to update is now 3 minutes or so. Hence we can now verify the integrity of data in the lake compared to the data we see on D365FO itself (something our CFO was adamant she wanted to see; not for any Microsoft related reason, just of historical scars on other ERP and Reporting systems where a lot of work to ensure consistency was required before she could rely on the numbers).

    Kudos to the Project COMO team also (you can see then in the Yammer PEAP groups).

  • mahmoud_algindy Profile Picture
    mahmoud_algindy 277 on at
    RE: Get Data from Dynamics 365 Finance and Operations (Online) in Power BI

    Ian Waring

    Me & the whole community should thank you for this response.

    Thanks so much from my heart.

  • Verified answer
    Ian Waring Profile Picture
    Ian Waring 436 on at
    RE: Get Data from Dynamics 365 Finance and Operations (Online) in Power BI

    We've spent the last 4 years getting data extracted out of D365FO via both BYOD extracts and ODATA, supplemented by system integrators having to deploy custom entities to data not exposed in either route in. We've latterly been deploying Microsoft Data Lake which, for the most part, answers most prayers out of the box (though gives rise to a lot of extra setup to show all the table/entity views in Synapse SQL). The sort of issues you'll encounter are:

    1) Some entities are marked "Private", so you cannot see them via ODATA sources at all. Some are also not available for BYOD downloads via Data Management.
    2) Some useful data tables are not exposed by any standard entity (examples - custtrans and vendtrans, which are the only way to suss if an invoice line has been paid or particially paid for).
    3) Some entities leave out useful fields. Eg - the INT field that links Sales Order Line to Sales Invoice line is omitted in both, so no easy way to effect a join connecting them
    4) Some large data structures publish as aggregations - eg: GeneralLedgerActivities (mainly Generaljournalaccountentry/Generaljournalentry - the GL), BudgetrActivities (mainly BudgetTransactionHeader/BudgetTransactionLine - the Budget Registry) - so you end up pulling large data loads over only to filter most of it out on arrival
    5) Microsoft no longer publish ERD's since AX days, so it's an act of discovery to reverse engineer the joins needed

    Alex Meyer kindly provided some code to help work out the joins between tables on D365FO: Alex Meyers ERD Tool: Automated Solution to Find Table Relations in AX 2012 and D365FO - Alex Meyer (alexdmeyer.com)

    GitHub Link:

    https://github.com/ameyer505/MicrosoftDynamicsTableAssociations

    YouTube Link:

    If you want to see this app in action here is a YouTube link: https://youtu.be/8045Vnp8WZY

    However, I can't get that working on my Windows 11 PC (suspect a .net 3.5 issue) but a colleague got a list of tables and joins using this plus by looking in the back of a dev machine in Visual Studio for me.

    You have a journey ahead of you. Happy to help, though I've not yet tackled Voucher Transactions.

    Ian W.

  • mahmoud_algindy Profile Picture
    mahmoud_algindy 277 on at
    RE: Get Data from Dynamics 365 Finance and Operations (Online) in Power BI

    Thank you

  • Suggested answer
    Gunjan Bhattachayya Profile Picture
    Gunjan Bhattachayya 35,421 on at
    RE: Get Data from Dynamics 365 Finance and Operations (Online) in Power BI

    I think it is the better option, considering the limitations on Odata. Please check this link for MS documentation regarding BYOD.

    I am not sure about the certifications where this is explained.

  • mahmoud_algindy Profile Picture
    mahmoud_algindy 277 on at
    RE: Get Data from Dynamics 365 Finance and Operations (Online) in Power BI

    It seems this is the only solution, which I did not like with an Enterprise ERP like F&O.

    BYOD will be the best, please if you could share an easy step by step guide to do that,

    If you know in which Certification this subject is explained, like AZ-100, DP-900 ..... etc.

  • Gunjan Bhattachayya Profile Picture
    Gunjan Bhattachayya 35,421 on at
    RE: Get Data from Dynamics 365 Finance and Operations (Online) in Power BI

    My mistake. Public property in this data entity is se to "No", which is why you can't see it in Power BI. If you want to use Odata feed for Power BI, you will need to develop a custom data entity which can be made public. Else, you could try to use BYOD for your Power BI reports and check if you could export this entity data to your BYOD database.

  • mahmoud_algindy Profile Picture
    mahmoud_algindy 277 on at
    RE: Get Data from Dynamics 365 Finance and Operations (Online) in Power BI

    Hi Gunjan,

    The mentioned Entity not appears in Power BI,

    I am not sure is it require any options to be configured first?

    I used the same entity to export data to Excel, but I need to get it connected to Power BI

    Thank you

  • Gunjan Bhattachayya Profile Picture
    Gunjan Bhattachayya 35,421 on at
    RE: Get Data from Dynamics 365 Finance and Operations (Online) in Power BI

    Hi Mahmoud,

    Did you try the entity I mentioned before - GeneralJournalAccountEntryEntity(General journal account entry)? This should show you all the posted transactions.

  • mahmoud_algindy Profile Picture
    mahmoud_algindy 277 on at
    RE: Get Data from Dynamics 365 Finance and Operations (Online) in Power BI

    Still, I am looking for your support to get the Table Name for the attached.

     

    The required Table is Voucher transactions Table this table showing all transactions posted on G/L Account (Main Accounts), not only the posted through Journals.

    General ledger>Inquiries and reports>Voucher transactions

    pastedimage1650115775336v1.png

    Thanks

    Mahmoud

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,884 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,754 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans