everythingdynamicsaxbi: Reporting in Dynamics 365 for Finance and Operations – The definitive guide

[Blog bot]

Источник: http://www.everythingdynamicsaxbi.co...initive-guide/
==============

I read a lot of different posts about how to connect to Dynamics 365 for Finance and Operations (I’m shortening this down to D365FO throughout this post) for reporting and there seems to be somewhat confusion on what’s available in the latest version of D365FO, so I thought I’d gather everything into one post. As you all know Power BI is the main reporting tool for D365FO, but there are different ways of connecting to the data sources to get the data you need. What options do we currently have available for reporting? Let’s take a look.

I’m not going through Power BI as a reporting tool in this post as I am mentioning it throughout the post and it has already been covered various places/posts.


Entity Store

First things first. The Entity Store is a new database dedicated to Reporting and Analytics, in other words, it is optimized for Reporting purposes. It was first introduced for Dynamics AX 2012 R3 back in May 2016 and as an existing user of AX you could create an Entity Store in Azure or within your own infrastructure using SQL Server 2016 or 2014. It was later introduced for AX7 (now Dynamics 365 for Finance and Operations).



How does it work?

Entity Store enables users to choose “Entites” (E.g. “Customer”) to be moved into the Entity Store. It enables scheduling incremental refresh jobs via the AX batch framework and will pull the data from the transactional database for AX and push the data into the Entity Store database. Since the Entity Store uses in-memory technology build into SQL Server you can connect to it using Power BI direct query to enable near-real time analytical reporting.



This is how it works:







Aggregate Measurements


All the Power BI Report Models published by Microsoft are using the Entity Store as a datasource. If you are a developer, Power user or Business Intelligence consultant you can access the Entity Store by logging into your environment and connect to the AxDw (Entity Store) database through Power BI:



There are already many Aggregate Measurements exposed in the Entity Store (System administration -> Setup -> Entity Store), but what is it? An aggregate measurement is a model that contains a collections of measures together with their corresponding dimension, much like a cube, but these are using in-memory technology and are realtime. Aggregate measurements are deployed to Microsoft SQL Server non-clustered columns store indexes and can then take advantage of in-memory computing that are built into SQL Server 2016 and Azure DB. By default, aggregate measurements are realtime, but as a system administrator you can manage the latency of aggregate data based on available resources, if you need to.

Are you coming from an Dynamics AX 2012 installation you can upgrade the existing cubes into aggregate measurements. Read more about it here.

OData Feed

For ad-hoc reporting and self-service reports you can use OData endpoints to access your data. You can access Odata endpoints for your Dynamics environment via the following URL: https:///data

I’ll show you how it works:

In my example I’m connecting via Power BI. Get data -> OData Feed:



Select Organizational account and Sign in using the same account as you are using for your Dynamics 365 for Finance and Operations environment:



Press “Connect” and the tables will appear:



This is a great and easy way to create reports for the end users and power users. Note that the Odata feeds does not play well with large volumes of data, if you are going to report on a lot of data, use the Entity Store.

Power BI Report Models

Microsoft is releasing a lot of Power BI content onto the Shared Asset Library in LCS (Lifecycle services). The report models are pre-built reports and dashboard within various modules of D365FO can be published to your customers environment and they will work out of the box. This gives the customers a great starting point when it comes to reporting in D365FO. Also, as a Business Intelligence consultant you can of course download these, make modifications to them, and publish them back into your customer solution to meet their specific requirement (I will cover this procedure in another blog post).





This is an example of how the Production Performance report model looks like:



These reports are leveraging the advantages of the Entity Store which gives the users near-realtime refreshed data.

Power BI Embedded

After setting up the Power BI configuration in D365FO you should be able to view Power BI embedded (If you are using an one box environment the setup for using Power BI embedded is a bit different) content in the application.

The workspaces with a red box around is the ones containing Power BI embedded and the ones marked yellow are containing the Power BI integration option (We can integrate Power BI with D365FO and insert dashboards and tiles directly into the D365FO client. Take a look at this guide on how to enable this feature.):



Make sure that you also have refreshed your aggregates measurements before you open up the workspaces containing Power BI embedded functionality or you will get an error message saying”Power BI embedded is not enabled. Contact your system administrator”. This is done by going to System Administration -> Entity Store and selecting all the different Aggregate Measurements, and then clicking “Refresh” on the top ribbon:



After the refresh is done you can select one of the measurements and see when the measurement was last updated:



SSRS (SQL Server reporting Services)

Let’s not forget about SSRS (Or should we ;)). No, SSRS is still a big part of D365FO and is mostly used for Business Documents like invoices, packing slips and picking lists, but also for traditional ERP reports. There hasn’t been a lot of changes to the SSRS platform in D365FO, except for the on-premise version where the reports are opening as a PDF file instead of in the Report viewer. Development of new SSRS reports is still possible and you can also make changes to existing ones as you did in AX2012.

Microsoft Excel

Excel is also deeply integrated in D365FO. You can use Excel for both reporting and to update data back to Dynamics. Remember the Dynamics AX-addin for AX2012? It’s gone. There’s a new Excel Data Connector Addin for D365FO which enables Excel to become a seamless part of the system and enables the users to change and quickly analyze data. This is a great feature that will be heavily used by the end users, especially finance people who just loves to work in Excel. In almost every form in the application you have the office button in the top right corner which enables this feature (Example from the All sales orders form):



This will open up Excel and give you a predefined Sales Order template you can use for importing data or for reporting. Here you can also add other fields to the template and change your filters/parameters.



Financial Reporting

Management Reporter or Financial Reporting as it is called in D365FO is, like SSRS, still a part of the ERP system. There hasn’t been a lot of changes to this Reporting Tool either, but the biggest change from previous versions is that the reports now run inside the client rather than just in a separate window in a browser. Personally I think this is a lot better and it gives a better feel to it all when everything just runs inside the same client.

So when you are working with the reports in Report Designer and hit the “Generate” it is actually just saving your changes back to D365FO and you can now run the report within the client:



If you are doing changes to these reports and wants to see your changes right away I recommend you to go into the Output and Distribution tab on the Report Definition and select this tickbox:



This will open the report directly in the client and run it with the parameters selected in the report, which is a lot faster:



There has also been changes to the database structure. In AX2012 you had two databases for Management Reporter (One database and one Data Mart), but in D365FO you only have the one data mart:



After restoring or copying the Finance and Operations database from another environment you will have to reset the datamart. You can check out this guide to go through the detailed process.

Final words…

D365FO comes with a pretty strong reporting package out of the box, reporting has always been a “problem” in AX2012 and I currently like what I see in the new version of Dynamics, Power BI and the integration with the application being the coolest feature of them all. With Power BI and self service BI incorporated within the ERP-system it gives the users access to the data they need whenever they want it. Also, the Entity store gives a simplified view of the database which makes it easier for super – and end users to develop reports in e.g. Power BI.



Источник: http://www.everythingdynamicsaxbi.co...initive-guide/