Microsoft Dynamics 365 - Reporting 

Thanks Leanne. Here are some additional resources you may also like www.netwoven.com/category/modern-applications/dynamics-365/

Ahh ... where to begin? First, with empathy. It is true that some reporting choices for Dynamics are a bit daunting and somewhat limited. SSRS is a good example - while it's certainly possible for SOME end-users to take a course to learn how to develop SSRS reports, it's just not realistic that this could be a widespread option for the end-user community. As well, SSRS against the online version of Dynamics does not support SQL queries, so FetchXML must be used, which is also not exactly what we might call intuitive. I will also admit that the performance of some reporting choices are not ideal against big data sets with this platform. In the case of large data sets it would be a good idea to report against a separate source. It's possible to use something like Azure Data Factory to periodically copy sets of data for the use of reporting - and it's not break-the-bank expensive. And while this concept would have been quite expensive in the past, involving a lot of development work, the more graphical tools such as ADF have made the integration process much simpler than it was. Having said that, there are some serious inaccuracies in the polemic above. For instance, is it absolutely possible to connect Power BI to the production version of Microsoft Dynamics Customer Engagement, now known at the Dataverse. Just open either the cloud version of Power BI or the Desktop version and click Get Data - the ways in which you can connect to that data source are well documented - and honestly, by no means rocket science. The idea of the "data warehouse" being the only thing you can connect to related to Production Dynamics - honestly don't k now what you mean by that. As you well know, Power BI can connect to many sources - a given "data warehouse" being just one. Also, as the video explains - what defines reporting? In the 17 years that I have been involved with consulting on this platform, most of the time, the most popular "reports' with many end-users are two-dimensional representations which are essentially excel spreadsheets. And the argument that if you want meaningful reporting you need to export the data to Azure Data Lake and attack with "meaningful" reporting tools - well, now you sound like the developer that you are. So, not sure if it's appropriate advice to end-users or business people who need to make a business decision as to whether or not a system will work for them. Moreover, it is absolutely TRUE that many end-users of Microsoft Dynamics make extensive use of the native charts and graphs against Advanced Find Views to produce perfectly fine interactive dashboards that provide the ability to drill down and answer questions. There are many people who consider those to be reports. What's more, you can now take any view you have created or that the system is presented and simply click "Export to Excel Online" and work on that data in Excel online, click Save and it writes it back to the database. This is an operational advantage that many systems simply do not have. People love Excel. Sorry, but I'm not aware of many other CRM systems where this is possible. So it's not all "lies, ,lies, lies" from Microsoft. I spent 10 years in the open source world, and I still log into Linux on a weekly basis for some clients, but I don't find the need to bash Microsoft and call them "liars." And I have used other CRM systems - some are absolutely great and well-suited to a vertical market. But there is a lot to like with this system - as well as the low-code, and affordably-priced Power Platform, which is essentially Dynamics with specific IP stripped away and presented as a starting point to quickly develop business applications. But I digress ... back to reporting. Power BI is, of course, the choice for more sophisticated reports, and while I agree that there is a learning curve to create Power BI reports - I personally know plenty of end-users who have an analytical bent and have learned to create Power BI reports for themselves against the data in Dynamics. As well, it's pretty trivial embed a Power BI report into Microsoft Dynamics as an embedded dashboard. Every single system has benefits and weaknesses. Microsoft Dynamics 365 has plenty of benefits, and plenty of weaknesses. This is true for all CRM systems. Don't even get me started on Salesforce -- which is madly loved by millions. At the end of the day, none of them should be immediately and completely dismissed. They should be carefully evaluated to determine for your given business whether the benefits outweigh the weaknesses. Take the time to learn, and decide for yourself, for your own business. It's worth the effort to do so.

​@@blackfincloudservices2844 Wow, I very much appreciate this long and detail comment. Let me give you some background of where I'm coming from. 4 years ago I joined a company already using D365 F&O, cloud hosted by MS. During the installation of D365 they (the Finance staff with no real report dev experience) were told that they could connect directly to D365 data from PBI and generate their own reports. They were also told this was "easy". That's not a lie, it's 2 lies. I had determined, and confirmed twice with Microsoft support on 2 separate occasions (2 tickets), that PBI can only connect to the data warehouse (which is largely useless for serious reporting anyway) and the connection is too slow to even work anyway (except for tiny tables with no more than a few hundred records). Furthermore, even if that did work as promised (and which was even documented in Microsoft's sales materials), the end users lack the skills to do anything meaningful with it. Plus the data warehouse is far too limited for the vast majority of the reporting they wanted anyway (and I also confirmed this directly with Microsoft support - I was pretty relentless in my hounding of them about this actually and was put in direct contact with technical guys who know what's up). I also inquired and pressed our Microsoft partner, a company certified by Microsoft to do D365 installations and support, to pls help me in accessing the D365 database for the purposes of reporting. They didn't know how and couldn't provide an answer either. They just sent me random BS Microsoft articles with information I had already tried and confirmed directly with Microsoft doesn't actually work. So I asked them to open a ticket with Microsoft to inquire as well, and Microsoft's answer yet again was simply: that's not possible. They said that if we were hosting the database locally then the PBI connection to the data warehouse would work, but since it's cloud hosted then not a chance. Not that the data warehouse would have been much use to us anyway, but at the time we had nothing so it would've been better than nothing at least. So as you are likely aware, this lack of data access has seriously ticked off numerous organizations all of the world. Where does Microsoft get off thinking they have the right to sequester a company's own data from them? Especially when they are being paid so much for the service and use of the product. It literally boggles my mind and if I were supreme ruler of earth I would make that illegal. A company's data is it's most precious resource, and for companies to restrict semi real-time access to it is nothing short of crippling and unacceptable. But so many software companies do it and they get away with it because most often the people making the decision to adopt a piece of cloud hosted software don't know to ask the right questions and demand proof of the claims being made around data access. And once the software is installed, a year and millions of dollars later, it's too late to back out and now you're at their mercy. That is the game being played here, I'm quite sure of that. The company I currently work for has been burned repeatedly by this same scam. So with so many disgruntled and misled organizations out there, Microsoft was promising they would FINALLY provide a means to kick out the data. They released that early 2023 I believe it was. It's a mechanism that pushes full actual tables (not that mostly useless data warehouse nonsense) to an Azure data lake (where it's held in csv files). I set that up and tested it and it works great, hooray! Finally we have a way forward. Then oops, not so fast... The data lands in csv files with no column names. The columns name are actually held in separate json files, and Microsoft advises using Azure Synapse to mesh them together and create pipelines to push the data to an SQL database or whatever you want to do with it. Before going further, side note, let me just point out what an incredibly insane amount of work that is. My current reporting uses more than 70 tables, and that's a steadily growing list. That's a LOT of pipelines to set up, schedule, and maintain. Lots of opportunity for things to go wrong too, and having to set up all the datatype transformations since the data is all text in the csv files. Meanwhile, we've had database replication technology for well over 25 years now. What Microsoft is suggesting here is absolutely archaic compared to database replication. Basically, the data lake method completely disassembles the tables into their constituent parts and requires you to reconstruct it yourself, just to make a copy of it in a separate database so you can finally access it (and apply your own indices too of course). What a huge step backwards from database replication. But hey, we're desperate for something close to real time data access, and Microsoft has us over a barrel, so we gotta do it I guess.. So I went ahead and did that, and it turns out it doesn't even work. The specific problem is that Azure synapse (or any other pipeline technology in Azure) isn't able to interpret the date format being kicked out of D365 into the csv files. The dates all come out as nulls. I confirmed this with Microsoft support. However, there is a way to still get that csv data into a pipeline BUT then you lose the columns names. Microsoft's recommendation was to do that and then manually assign the columns names in the pipeline. Not only is that adding an insane amount of additional work to what is already a ridiculously labor intensive and unnecessary development effort, but I'm not sure I'd even trust it, because column names would need to be assigned based on their sequence. If the columns are ever re-ordered or if a new column is inserted then that ETL pipeline breaks and it's a bit of nightmare to track down and fix. I've learned the hard way from many years of experience not to rely on the order of columns in a provided dataset. I just revisited this about 6 weeks ago and lo and behold Microsoft still has not fixed the date issue. Meanwhile their long promised data lake "solution" to the long standing data access issue has been in production for ages now and it still doesn't even work. So to recap, Microsoft appeared to be capitulating to public demand to provide data access but they seem to have pulled another ruse with this archaic data lake solution which remains broken to this day. Did they ever actually intend to provide that access? Or was this just another false promise, leaving many companies with no other option but to get Microsoft or their partner$ to build reports for them with the D365 SSRS methodology (which is also itself extremely archaic and labor intensive compared to using the standard SQL server and PBI). At this point I doubt Microsoft ever intends to provide direct or near real time production data access to D365. Why would they? They have a cash cow going by restricting data access. Dismantling the tables and pushing them out as csv's (with column names held in a separate json file) seems to be all they are willing to do, and then it's up to the companies to undertake the substantial development effort to reconstruct the tables from that. What a disaster. Also I looked into the data verse thing and it's an absolute joke. There's far far too little data available through that for us to do anything with. The vast majority of the tables I need aren't even showing. I'm not even sure what that's even supposed to be actually, or what the point of it is, because it's just so limited. So again I opened a ticket with Microsoft to inquire and they also confirmed what I had observed. It's just not usable for serious reporting. I'm not even sure why it exists at all. I've been at this a long time now. I've spoken to numerous people online facing the same problems due to Microsoft's lies and roadblocks. As of yet I still have not encountered a viable solution to this problem. So I'm left kicking out the database and restoring it locally on my PC and then generating the reports from that. Given that the data lake solution only partly works, and the Azure Synapse pipeline methodology (without column names) just seems too cumbersome, potentially unreliable, and labor intensive to be a viable option, I'm thinking about writing my own ETL application in .Net to do what Azure Synapse is, as of yet, incapable of doing: take the data from the data lake csv files, mesh it with the column names in the json files, and reconstruct the database tables in our own Azure SQL instance. Seems to be the only viable option, and that would probably be less work than setting up all those Azure pipelines. Oh, and another option is tapping directly into the D365 API and pulling the data out that way, but I'm not sure how viable that is for extracting tables with over a million records. Since I have the data available in csv's in Azure blob storage, then I'm more inclined to just use that. However, having said all this, if you feel I'm off the mark about anything I've said here, pls alert me to the error of my ways. I would LOVE to be wrong and for there to truly be a way to access D365 data in close to real time without embarking on the major custom development effort mentioned (and not just the relatively useless data warehouse). To any non-techies reading this, like managers or finance staff thinking of adopting D365, here's my advice: don't. Not only does Microsoft lie egregiously and repeatedly about the data access for reporting, the system itself is generally disliked by everyone in my company. They much preferred Navision actually. I can say with absolute certainty that no one I work with likes it. They find it restrictive and cumbersome, and supporting it costs a fortune too. You can do better.