Personalized Community is here!
Quickly customize your community to find the content you seek.
Now Available in Community - New TechTalk Videos for 2020
Read More about New TechTalks for 2020
2020 release wave 1Discover the latest updates and new features to Dynamics 365 planned through September 2020
Release overview guides and videos Release Plan | Preview 2020 Release Wave 1 TimelineWatch the 2020 Release Wave 1 virtual launch event
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Upcoming TechTalks
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?
Would you say the following will be included OData Feeds?
"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:
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.
Agree with ievgen, Byodw is the ideal way to reap the most features of PowerBI
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?
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.
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 ?
Well, you can see the prices for yourself and you can also test how the performance feels.
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.
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
@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?
It was only to refer byod only all steps are same except the azure db. So don't get confuse you can ignore those steps....
So, are you saying that it is supported?
I always thought byod is only for azure db, but it would be nice it can export to the local database.
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.
Its working my friend, you can try on your local VM as well.
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.
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.
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.
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.
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.
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.
Business Applications communities