Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / How do I find out what...
Finance | Project Operations, Human Resources, ...
Answered

How do I find out what underlying tables a report is using?

(0) ShareShare
ReportReport
Posted on by VickyD

We have a whole host of reports available in our D365 Finance & Operations instance. I am looking to connect to D365 from Power BI using Odata connector and pull the relevant data for say a report and build Power BI reports based on it. 

I'm having a hard time determining what are the source tables/entities that the report is using. Is there an easy and foolproof way to identify the table that I can then import into Power BI? 

My google searches have only provided vague answers but I'm hoping there is a proper way to get the table information. Almost feel like there is a model/cube sitting in between the reports and the underlying tables/entities but I may be wrong. 

Note: The reports I'm referring to have been built using "Financial Reporting Designer". 

  • VickyD Profile Picture
    VickyD on at
    RE: How do I find out what underlying tables a report is using?

    Thanks so much for these links. I guess I was just searching for the wrong this. This option definitely seems way more promising!

  • Verified answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,280 Super User 2024 Season 2 on at
    RE: How do I find out what underlying tables a report is using?

    Hi Vicky,

    You shared a community blog. For sure it is possible to connect via OData, but that would take ages as it is not the best performing tool. You can read the Microsoft documentation for the recommended way of exporting data to Data Lake or BYOD.

    Bring your own database (BYOD) - Finance & Operations | Dynamics 365 | Microsoft Learn

    Export to Azure Data Lake overview - Finance & Operations | Dynamics 365 | Microsoft Learn

  • VickyD Profile Picture
    VickyD on at
    RE: How do I find out what underlying tables a report is using?

    Thanks Andre for the quick response.

    I'm actually tasked with building a centralized data model that will help with reporting across financials, sales, operations from different sources so we do need to bring the financial data into Power BI Service using dataflows in power bi (technically azure data lake) and then build datasets (technically AS models) which will then be used for reporting.

    I thought odata connection as described here is the primary way to make that happen. Is there another/better way?

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,280 Super User 2024 Season 2 on at
    RE: How do I find out what underlying tables a report is using?

    Hi Vicky,

    The financial reports are using a separate datamart database part of Management Reporter. The accounting entries are synced to this database. There is no public access to this datamart database.

    It is not recommended to use OData for querying financial transactions. Instead, look at the options to export transactions to BYOD or Azure Data Lake. These would be performing better as input for Power BI.

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans