Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Answered

Get all entity data from Dynamics 365 when connecting with Power BI via OData

(0) ShareShare
ReportReport
Posted on by 94

Hi

Was asked to assist with setting up a user to allow them to build Charts using Power BI using the Dynamics 365 F&O data, so I set them up with an Odata connection between Power BI and Dynamics 365 F&O.

Using the Azure>Dynamics 365 Legacy connection type.

They then queried why they only see a small amount of the data in some tables when they use them, an example is projectposttrans. 


When I looked this I could see if was only pulling data for the entity the user is assigned to and changing them from one entity to another in the System administor> Users section would alter the data they where able to pull : 

pastedimage1679577844451v1.png

The problem is the user wants to make global charts (our setup is broken down into entities per region and the business is spread across the globe)

My question is there a way to retrieve this data so its not entity specific?

Cheers;

Median 

  • Suggested answer
    Mohit Rampal Profile Picture
    Mohit Rampal 12,554 Super User 2024 Season 1 on at
    RE: Get all entity data from Dynamics 365 when connecting with Power BI via OData

    Even BYOD is hosted in Azure as Azure SQL Database. However, there are some limitations with BYOD due to which we will move to Azure Data Lake in near future.

    Check this out

    mohitrampal.com/.../

  • Median Profile Picture
    Median 94 on at
    RE: Get all entity data from Dynamics 365 when connecting with Power BI via OData

    Thanks both I will need to go away and look into this more then as I had never even heard of Data Lake and BYOD to me meant bring your own device which obviously isn't what you mean.

    The Data Lake sounds interesting though as we are using Azure heavily already I will research this and may come back to this forum in the future with more questions around this.

    Cheers;

    Neil

  • Suggested answer
    Mohit Rampal Profile Picture
    Mohit Rampal 12,554 Super User 2024 Season 1 on at
    RE: Get all entity data from Dynamics 365 when connecting with Power BI via OData

    Hi, As mentioned above, for Power BI reports, its best to export F&O data to BYOD or Azure Data Lake and use that as a source in reports. You will have all the required data from all legal entities and can create global reports. (We have implemented BYOD for same, few years ago)

  • Verified answer
    Frank Hamelly | MVP, MCP, CSA Profile Picture
    Frank Hamelly | MVP... 46,259 Super User 2025 Season 1 on at
    RE: Get all entity data from Dynamics 365 when connecting with Power BI via OData

    I think you'll need to setup an export from F&O to BYOD, or preferably Azure Data lake, then connect to either of those storage options with Azure Synapse.  That will allow your users to develop reports in Power BI efficiently and with all data.  Perhaps someone else will chime in with other ideas as well.

  • Charlotte X Profile Picture
    Charlotte X on at
    RE: Get all entity data from Dynamics 365 when connecting with Power BI via OData

    Hi Median,

    Please wait for someone with experience in this area to answer this question.

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

Announcing Our 2025 Season 1 Super Users!

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

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Congratulations to the January Top 10 leaders!

Check out the January community rock stars...

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,996 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,853 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans