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 forum / Get data from D365 for...
Finance forum

Get data from D365 for Power BI reports

(0) ShareShare
ReportReport
Posted on by agneum 150

Is there any EASY way to extract data from D365FO for a Power BI report ?


I don't understand why I can't have access to a read-only AXDB for making my reports. I would simply select my tables as I did with AX09 and I would be done. The solutions customers have are not optimal. Not everything I need for the reports are in the entities when I connect to D365 through Power BI. 

If I right click on a form in D365, say Inventory Transactions (InventTrans), is there a way to get the entities it uses? 


BYOD : Works okay. In my case, full push is required on a schedule as I couldn't get incremental working. Tables are still processed sequentially even though they are in the same export batch job and are of course dropped during update which limits the frequency for which it is possible to update data. Worst part is that you can only select entities to export, some of which are derivatives that have limited or no access to some of the table data. You have to figure out which entities you need to export. 


Custom entities:
Requires you to go into the dev VM, select the table, make an entitiy out of it. If it has a recid, which is most of the tables, you have to make an index on it to get past the "Natural key was not found" error then publish the entity. This work should be left to developers, not end users. It's too cumbersome.

So, is there any other reliable way of getting access to the AX tables? 

Categories:
Data Integration
  • Satish Panwar Profile Picture
    Satish Panwar 14,651 Moderator on at
    RE: Get data from D365 for Power BI reports

    Hi Agneum,

    InventTrans entity doesn't exists. Given this table hold all inventory transactions, it may not be worthwhile to create an entity out of this table and use it. You have to do aggregation/filtering/sorting a lot on it. Instead try to see what you want to do and then try to tap appropriate tables. E.g. if you are looking for purchase invoices (item related), then vendinvoiceTrans, if you are looking for customer invoices (item related) then custTransJour. Don't know what you will do with inventory transactions that will come from journals, PR, MRP etc. unless you are trying to get an idea on what's going on with total number of transactions etc and have some broad analytics requirements, you should rather focus on specifics if possible.

    Thanks,

    Satish Panwar

    Please help verify answer(s) if they guide you in right direction so other community members can also benefit from your question with verified answer.

  • Ajit Profile Picture
    Ajit 8,755 on at
    RE: Get data from D365 for Power BI reports

    I think we don't have an entity for inventTrans out of the box but you can create one.

  • agneum Profile Picture
    agneum 150 on at
    RE: Get data from D365 for Power BI reports

    Sure, I can search every table for every reference. But, If I right click on the InventTrans table and click find reference I don't see it being related to a dynamics://DataEntityView, unlike CustTable. Correct me if I'm wrong.

  • agneum Profile Picture
    agneum 150 on at
    RE: Get data from D365 for Power BI reports

    I find it a bit odd that I can interface to the table with a Google chrome addon in that case, but I digress.

    The InventTrans form uses the InventTrans table, amongst a few others. Because entities are just de-normalized views, I would need a few of those and it's not a given they contain all the underlying tables I need.

    Ideally I would want to select the fields/tables and just generate a report on those.

  • Ajit Profile Picture
    Ajit 8,755 on at
    RE: Get data from D365 for Power BI reports

    As of now entities\measures are the way to get data for Power BI. You can right click on table and click on find reference, you could get all related objects and can search for data entities for that.

  • Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at
    RE: Get data from D365 for Power BI reports

    Hi agneum,

    Another option for extracting data is electronic reporting where you can create your own reports that you design based on the tables and fields available in D365FO. Please have a look at the respective MS docs sites to see if this might be an alternative for you.

    Best regards,

    Ludwig

  • nmaenpaa Profile Picture
    nmaenpaa 101,156 Moderator on at
    RE: Get data from D365 for Power BI reports

    The data entities are the way of accessing D365FO data from external systems. There is no other way out of the box. I think there are some third party tools that could allow you to export table data directly, but not sure how it would work out with Power BI.

    There is no way to find out in the UI which entity is related to a form data source. But in Visual Studio you can use the cross references (right click - View references) to find which entities are using some table such as InventTrans.

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

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Coming on 11/8!

In our never-ending quest to help the Dynamics 365 Community members get answers faster …

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 293,278 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,998 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans