For organizations considering a move to Microsoft Dynamics 365 Finance & Supply Chain Management (D365 F&SCM), or for those in the early stages of an implementation project, defining a clear strategy for curating data is a key to developing a comprehensive approach to reporting and analytics.
Microsoft offers several native reporting tools for D365 F&SCM:
- SQL Server Reporting Services (SSRS) is useful for writing simple high-volume reports based on Microsoft Dynamics data, but it’s not suitable for complex reporting needs and can result in bottlenecks and expensive fees for custom development.
- Microsoft’s Financial Reporting tool (formerly Management Reporter) is primarily for financial statements and general ledger. It uses its own data mart, which cannot be customized in any way. It is commonly regarded as an inflexible and limited tool.
- Power BI is an analytical tool for data visualization and discovery. When working with D365 F&SCM data, it typically requires specialized programming skills to develop reports or to make changes to existing reports.
Given the limitations of each of these approaches, reporting in D365 F&SC can be time-consuming and complex, so it’s best to develop a clear understanding of your options early on. For customers considering an upgrade from Microsoft Dynamics AX to D365 F&SCM, or for anyone implementing D365 F&SCM for the first time, it’s important to formulate a strategy in advance. For anyone that needs to develop custom reports and dashboards, it all begins with understanding data entities.
What Are Data Entities?
When Microsoft released D365 F&SCM, the company chose to restrict direct access to the database for security reasons. To enable read-only access for reports, Microsoft developed an abstraction layer on top of the D365 F&SCM database that consists of “data entities.”
Let’s say, for example, that you want to write a report listing the total quantity of each inventory item sold in the past week. You would need to pull those data from the line item detail of your customer invoices. To include detailed information about each inventory item on the report, you might also need to link the item number from the invoice detail to the item master table in which additional information on each SKU is stored.
If you have direct access to the database, that’s a fairly simple proposition. Under the new paradigm, though, you would programmatically access the data entities associated with the information in the report, and Microsoft Dynamics would handle the relationships with underlying tables automatically. By accessing a data entity that provides invoice information, you will automatically be able to see the line item detail for each invoice.
Many data entities are aimed at specific areas for reporting. For instance, the “Customer” concept appears in multiple entities. Each of these Customer entities contains rows of data from the customer, its related tables, and transaction tables. In this way, D365 F&SCM users end up with data entities specific to reporting needs such as customer listings, sales (invoice) reports, or open orders reports.
The Data Entity Store
Confusing matters further, Microsoft has also created something called the Data Entity Store, which serves a different purpose and functions independently of data entities.
The Data Entity Store is an internal data warehouse that is only available to embedded Power BI reports (not the full version of Power BI).
Customers considering a move from Microsoft Dynamics AX to D365 F&SCM might assume that the Entity Store will cover their reporting needs. In reality, they will need to build out the Entity Store further to get a complete picture of their business. That means creating new entities from scratch or paying to have it done by an outside consultant. In practice, the Data Entity Store offers less flexibility than working with the aforementioned data entities.
Working with Data Entities
Data entities are extracted using Microsoft’s Data Extract Apps. They can sit inside your D365 F&SCM instance or in a separate Azure space, referred to as Bring Your Own Database (BYOD), which stores the data entities in Azure but in an SQL format that is accessible to reporting. There are five categories of data entities based on their functions and the type of data that they serve:
- Parameter (e.g., General Ledger)
- Reference (e.g., Tax Codes)
- Master (e.g., Customers)
- Document (e.g., Opening Balances)
- Transaction (e.g., Pending Invoices)
When it comes to using data entities, you have two choices: out-of-the-box or custom data entities. Microsoft has spent a significant amount of time developing out-of-the-box data entities for you to use. There are over 1,700 entities available within D365 F&SCM, all of which are narrowly focused on reporting needs.
At face value, this all sounds like a good thing. It’s simpler, right? There is a significant downside, though. Customers of the D365 F&SCM product are virtually always processing enough data that they can run into substantial issues with reliability and performance when running reports built upon data entities.
Common Challenges with Data Entities
Although data entities can help in several respects, they can also be slow and cumbersome. Writing fresh reports requires deploying data entities, customizing them, and sometimes even creating new data entities from scratch with custom programming. Data entities are accessed using the OData protocol. That requires specialized skills and can be costly and time-consuming.
For an organization running D365 F&SCM, this adds up to a few key challenges:
- A steep learning curve: With over 1,700 entities currently available for D365 F&SCM, how do you figure out which one to use? The new paradigm means getting familiar with those data entities, customizing them, and rewriting existing reports. To make matters even more confusing, Microsoft has added so-called “aggregate data entities” that provide summary-level data only.
- Performance issues: Accessing report information using OData is slow. Customers have reported very poor performance when running reports against data entities using the OData protocol. These performance issues can be mitigated by copying your data entities into a new Azure database outside of D365 F&SCM, which is known as bring your own database (BYOD). Unless using BYOD, OData is the only way to connect to D365 F&SCM data from external report authoring tools (including Power BI). The OData protocol limits transfer to 10,000 records at a time. So, if you plan to use an external report authoring tool (Excel or Power BI, connecting directly to the Data Entities), and will be running reports with data volumes exceeding 10,000 records, your report will not perform well. In the future, customers will be able to deploy Data Entities and replicate transactional tables in an Azure Data Lake. Microsoft is currently developing this capability. While this makes D365 F&SCM data available for cleansing and preparation, the Azure Data Lake stack is complex as it comprises multiple tools and requires new skills, meaning for many customers it may be overkill, as we explained in a recent white paper.
- Technical skills required: Creating and managing data entities requires fairly deep technical skills that can be scarce and costly. If new reports are required that fall outside the current scope of D365 F&SCM, it can take on the complexity of a software development project fairly quickly.
Diving into Data Lakes: Is Microsoft's Modern Data Warehouse Architecture Right for Your Business?Download Now
A Better Way: Self-Service Reporting
Not everyone has a team of in-house experts or the budget to hire consultants every time they need a custom report. Without deep technical expertise in D365 F&SCM’s data entities, frontline users will have a hard time getting the information they need.
Third-party reporting tools exist to make analytics more flexible and accessible to everyone in an organization. Reporting and analytics solutions from insightsoftware eliminate complexity, reduce cost, and decrease the risk of lengthy implementations. They remove the dependency on technical experts, allowing finance and accounting, operations, and other departments to create and modify reports without relying on the IT department.
What insightsoftware Brings to the Table
Providing complete data access is what insightsoftware does. That’s why we’ve tailored our Jet Analytics solution to D365 F&SCM and made significant improvements to how customers are reporting. Data entities have added a layer of complexity to self-service reporting. To lower the impact on your IT staff and finance teams, we offer a different approach aimed at significantly reducing costs and time.
Financial Reporting Made Simple
Atlas is a real-time financial reporting solution specifically designed for Microsoft Dynamics. It supports the needs of finance teams for flexible access to the latest data by enabling them to easily report over their D365 F&SCM data in their tool of choice—Excel.
Atlas comes with 50 data entities, which have been purpose-built to support the reporting needs of the finance team. These entities can be used to create real-time reports or they can be deployed in the BYOD to facilitate reporting over large data sets. Finance users can choose from a rich library of reporting and upload templates or create a custom template in minutes, easily append and update data to any table, and automate Excel reports without needing any help from IT.
Atlas includes multiple features designed to increase your finance team’s productivity and provide them with quick access to information. This includes the ability to drill down through live D365 F&SCM data through balances, journal entries, and into subledger transactions to find and fix data integrity and reconciliation issues fast. Atlas also provides the ability to report offline, mitigating the performance issues of the data entities when handling large data volumes.
With a short time to value, you can be up and running in an hour and seeing tangible benefits before the end of your next reporting cycle.
Enterprise Business Intelligence
Jet Analytics provides a pre-built data warehouse, OLAP cubes, and tabular models with a platform for non-technical users to easily create their own reports in Excel or Power BI. It helps simplify and speed up data management and analytics efforts in D365 F&SCM.
Jet Analytics uses the Microsoft Data Extract Apps to create data entities on a one-to-one basis with core D365 F&SCM tables. This lowers cost by reducing the time and resources required to access new data. It also supports incremental updates to keep this information current. With Jet Analytics, we provide an easy-to-setup pre-packaged set of data entities with our solution.
You also get a pre-built data warehouse and cubes (tabular or OLAP) that use these data entities to de-normalize the tables and keep all your governed data in one place. This is fully supported by incremental loading, so your data is always accurate and up to date.
In Jet Analytics, all your data entities will be stored in a separate database that is optimized for reporting and analytics, which eliminates the OData performance issues completely. Without having to sort through over 1,700 data entities and search through a dozen places, the data is in a much simpler form making it easier for your people to access the information they need.
Jet Analytics enables you to create a robust back-end data warehouse to speed up delivery of reports and dashboards with front-end tools like Power BI, so you can quickly respond to changing business requirements. Point-and-click data warehouse automation makes it easy to add new data sources, calculate new metrics or KPIs, and extend or create new cubes, all without coding and without the need to work with the Data Entity Store. Business users create reports with reporting layers that use familiar business terms, not complex technical database or entity terms.
Jet Analytics makes upgrading vastly easier and less expensive. Reports developed for Microsoft Dynamics AX will work in D365 F&SCM without extensive modification. To avoid time-consuming and risky data migration, Jet Analytics can provide a comprehensive view of your business (including all historic data) through its data warehouse automation capabilities. Combine data from Microsoft Dynamics AX with your new D365 F&SCM data and get a holistic view of your business without the expensive data migration.
If your company is planning a move to D365 F&SCM, it’s important to understand how Microsoft’s new approach to reporting will affect you. Microsoft has closed the door to direct database access, and the new approach is proving to be challenging for customers.
By understanding data entities, the Data Entity Store, and the challenges inherent in Microsoft’s current approach, business leaders can foresee the need to develop a clear strategy around curating data and providing a solid foundation for reporting within their organizations.
To learn more about how insightsoftware can enable agile, streamlined reporting against D365 F&SCM in your organization, contact us for more information or a free demo.