web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance forum / What is the best way t...
Finance forum

What is the best way to connect data from D365 F&O to Power BI

(0) ShareShare
ReportReport
Posted on by Starter123 222

Hi All,

I am trying pull data from a production environment of D365 F&O to create reports for our clients in Power BI.

Connecting via SQL is not an option because for security reasons we do not have access to the server or database credentials.

What is the best method to connect Power BI and D365 F&O.

Embedded Power BI - I could embed the Power BI reports but that seems to require Azure servers and Databases which are expensive. Is there a way to embed without needing Azure subscriptions?

OData Feeds - I have had the most success with this option

Power Apps - used by connecting to a Common Data Model and pulling data into Power BI from this

CDS for Apps -  I have not been able to test as we are currently updating our demo environment. Does this process work for F&O.

Are there any other options which I have missed out on have listed above?

Categories:
Data Integration
  • Community Member Profile Picture
    Community Member on at

    Would you say the following will be included OData Feeds?

    docs.microsoft.com/.../export-entities-to-your-own-database

  • Suggested answer
    jac_rod Profile Picture
    jac_rod 39 Moderator on at

    "Embedded Power BI" is not necessarily the same as an embedded Power BI report.  "Embedded Power BI" results in things like the CFO Overview and Credit and Collections analytical workspaces.  They're hosted in D365 and can't be accessed in any other way.  These do not require any additional licensing or infrastructure, but are a little more complex because they require inclusion in the development lifecycle and have to get promoted to Production.

    Embedding a Power BI report with a different dataset does require addtional Power BI licensing for each user that's accessing it.  Additionally, this may require some additional infrastructure, because you'll need to have the data residing somewhere for the Direct query to work.

    Using analytical workspaces are probably the cleanest approach, but they're only going to work if all your data is based on D365.  There are no external datasets available when using analytical workspaces.

    There's also an API for using DIXF to export the aggregate measurements that make up the analytical workspaces to an external Azure DB.  This would have additional cost, but would give you more control over that database.  Here's a link to how the Power BI solution templates use it:

    github.com/.../UpdateAxConfigurationEntity.cs

    -Jake

  • Suggested answer
    Mea_ Profile Picture
    Mea_ 60,286 on at

    Hi Starter123,

    Easiest option is to Bring your own database, you can export data to on-prem SQL or Azure SQL DB and  build reports against it, please refer for details docs.microsoft.com/.../export-entities-to-your-own-database it also allows you to bring data from different sources to same DB and mix it with AX data.

    The only issue here is that you need to build new entities if there is no standard entity for table you want to export, but you have to do this for any approach you will select because Data Entity is one interface for all data integration.

  • Community Member Profile Picture
    Community Member on at

    Agree with ievgen, Byodw is the ideal way to reap the most features of PowerBI

  • Starter123 Profile Picture
    Starter123 222 on at

    BYOD is expensive for a lot of businesses to host. Is there a less expensive way to do this?

    Can CDS be used with Finance and Operations only to pull data into Power Bi?

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,172 Moderator on at

    Can you define expensive?

    The cheapest Azure SQL tier that supports Clustered Columnstore indexes is S3, which costs something like 125 EUR / month here in Europe.

    Of course the price depends on the performance that you need.

  • Starter123 Profile Picture
    Starter123 222 on at

    According to what I have read online because Dynamics F&O is so large you would need to purchase something more around the €5000/month mark to successfully host the data. Are there any examples of successfully running on the 125/month ?

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,172 Moderator on at

    Well, you can see the prices for yourself and you can also test how the performance feels.

    azure.microsoft.com/.../single

    I know that some customers have been happy with the lower tier databases. But everything depends on the data volumes.

    Even the cheapest tiers include 250 GB of storage and that is already a lot. But the limiting factor is not the space, it's the DTU = throughput speed.

    I'm quite sure that most customers don't need the most expensive performance tiers. And if some company has such data volumes, they will have to prepare to invest some money to the data warehouse anyway, no matter what kind of solution they are using.

  • Verified answer
    Sheikh Sohail Profile Picture
    Sheikh Sohail 6,125 on at

    Hu Starter,

    You can go with BYOD, Its not necessary to keep BYOD database on AZURE the only thing is required is Static IP to access your database.

    You can host BYOD database on Local Instance (Make sure IP is public & Static) and can export data using DMF... it will be less expensive then Azure DB.

    reference for BYOD

    d365technext.blogspot.com/.../byod-bring-your-own-database-d365fo.html

  • Mea_ Profile Picture
    Mea_ 60,286 on at

    @Sohail Sheikh, docs page your referring says that it's has to be Azure DB and you saying that it is not necessary, why do you think that SQL Server is supported?  

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 > Finance

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans