Skip to main content

Notifications

Announcements

No record found.

Dynamics 365 Community / Forums / Finance forum / What is the best way t...
Finance forum
Answered

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

Posted on by 220

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:
  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: What is the best way to connect data from D365 F&O to Power BI

    I think Sheikh Sohail has applied a valid solution in order to save money. I also think that while this solution is not official supported, it is only so because Microsoft is pushing its partners to pursue its cloud business. If it supported and advertised On-prem solutions it would loose its Cloud services revenue. 

    Think of it like this: 

    If the producers of a hit movie decide to release the movie for free online, but a website would want you to use their website to stream the movie instead of downloading it as well as pricing the streaming cost of the movie at 5$. Would you download the movie or pay streaming fee for something that is already free? 

    This website would be Microsoft and its Cloud Business. And the data on your paid enterprise version of Dynamics 365 F&O is the free movie. 




  • Verified answer
    Starter123 Profile Picture
    Starter123 220 on at
    RE: What is the best way to connect data from D365 F&O to Power BI

    I contacted Microsoft on this subject.

    They have said that it is best to create Power BI reports is on the Development Environment. There you can connect to the AXDW database by inputting "." as the server name.

    Once you have created the Power BI Report it can be added to a deployment package and uploaded to LCS. The database is connected though the entity store in UAT and Production Environments.

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,148 on at
    RE: What is the best way to connect data from D365 F&O to Power BI

    Well, yes, MS can change their product any way they want. But if supported features are deprecated, it happens in a controlled fashion and clear communication in advance. Unsupported things can break overnight without warning. (Yes, supported things sometimes break too but they are fixed). For example using reflection to access private class members works, but it can break any time.

    So even though anything can break, some solutions are more likely to work in a predictable way than some others. And that's why it's important to know if a solution is supported or not.

  • Suggested answer
    Sheikh Sohail Profile Picture
    Sheikh Sohail 6,125 on at
    RE: What is the best way to connect data from D365 F&O to Power BI

    HI,

    Yes agree with you but the same time We all knows that Microsoft have right to unsupport the supported items as well and its happened multiple time on D365FO history for improvement purpose and then we have to go with alternate solution, i hope you are agree with my thoughts?  

    About BYOD If Microsoft changing this so it will not related to D365FO it will relate to SSIS because in background of BYOD SSIS framework is working.

    For just instance if Microsoft announce that they are not supporting COC in future then what we can do with it? Nothing because he is owner of the product and have rights to do things for improvement. So we all will change our developed soultion.

    As we all know Microsoft also not provide support with our customize solution when we logged technical tickets. It doesn't mean they restricting us to don't customize solution.

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,148 on at
    RE: What is the best way to connect data from D365 F&O to Power BI

    Sohail, I think you might be too optimistic here. There are many examples of things that are working but not officially supported. Not supported means for example that MS can make a change without warning and break it, even if it works for you right now. Or in some scenario it might work unexpectedly. And if it breaks, MS might not create a fix, instead they will use their time on creating fixes for bugs in the supported solutions.

    I'm sure they didn't just forget to add this info. On prem SQL has always worked and the article says it can be used for testing.

    I would recommend trusting only the official documentation when designing architecture for business critical solutions.

  • Suggested answer
    Sheikh Sohail Profile Picture
    Sheikh Sohail 6,125 on at
    RE: What is the best way to connect data from D365 F&O to Power BI

    Please try your selft you will find my solution. As know most them time articles were not updated or missed information this is common. You are senior technical, i would request you to try your self and request team MS to update the blog/article.

    As i mentioned multiple time on this blog you require public static ip with heavy bandwidth and powerful machine. We are doing thia on one of our project.

    I hope you will try and inform Microsoft to update the article.

    If you need any help regarding this you can email me or can contact me over Skype.

  • Mea_ Profile Picture
    Mea_ 60,274 on at
    RE: What is the best way to connect data from D365 F&O to Power BI

    There is a big difference between "working" and "supported", my question was "is it supported?"  Because this article says you can use it for testing on one box VM but only for testing.

  • Suggested answer
    Sheikh Sohail Profile Picture
    Sheikh Sohail 6,125 on at
    RE: What is the best way to connect data from D365 F&O to Power BI

    Its working my friend, you can try on your local VM as well.

  • Suggested answer
    Sheikh Sohail Profile Picture
    Sheikh Sohail 6,125 on at
    RE: What is the best way to connect data from D365 F&O to Power BI

    Yes, you can export data on local database as well if you public static ip.

    In background DMF using SSIS which requires on connection string to connect database for export.

    You can try your self hope you will get your answer.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: What is the best way to connect data from D365 F&O to Power BI

    I always thought byod is only for azure db, but it would be nice it can export to the local database.

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,773 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,513 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans