Complexity Drives Cost: A Look Inside Data Entities and BYOD for Accountants

insightsoftware -
February 24, 2020

insightsoftware is the global provider of enterprise software solutions for the Office of the CFO to connect to & make sense of data in real time, driving financial intelligence across […]

Complexity Drives Costs Feature

If your company is currently using Microsoft Dynamics AX, you may be planning for a migration to Microsoft Dynamics 365 Finance and Supply Chain Management (D365 F&SCM) at some point in the near future. As you do, you should be aware of some technical changes that will impact your ability to get information out of the system efficiently and cost-effectively.

This article aims to help finance and accounting professionals better understand those changes. While this will touch upon some fairly technical areas, we believe that it’s important for decision-makers in the finance department to understand how Microsoft D365 F&SCM is different, why it’s different, and what it means for your organization going forward. That will enable you to make informed decisions about the right tools and technology to empower your team, deliver accurate reports, accelerate the flow of information through your organization, and preserve scarce resources along the way.

As a finance and accounting professional, you may find it frustrating to have conversations with IT experts about your reporting needs. For IT, it is equally frustrating to try to articulate complex technical issues and explain all the available options. To arrive at the best decisions, it helps if both sides can step outside of their own frame of reference to better understand all of the available options and the implications of each potential path.

Here, we examine some of the key changes of which you need to be aware to find some common ground with the technical experts in the IT department or the consultants who are helping you with your migration to Microsoft D365 F&SCM. We’ll start with some fairly technical topics, but bear with us; it will help you better understand the challenges that lie ahead.

Reporting: A Few Technical Basics

Financial and operational reports retrieve master data and transactional information from your ERP database using something called “SQL.” That stands for “structured query language.” (Don’t confuse “SQL” the language with “SQL Server,” which is Microsoft’s database engine.) In SQL, the common method for retrieving information for any report is something called the “SELECT” statement. Here’s an example:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate

FROM Orders

INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

In plain English, here’s what that statement is saying: “Show me a list of orders. I want to see the Order number, the Customer Name, and the Order Date.” Because the master customer table stores the customer name and a separate “orders” table stores the order information, the statement needs to include information about the “link” between those two tables. That’s what the last line is for.

SQL is a near-universal language in the world of software applications. Every programmer and report developer knows it well. But for companies using Microsoft D365 F&SCM, SQL will no longer work with their live ERP database. Why? In part, it’s because of this:

DELETE FROM Customers WHERE CustomerName LIKE ‘A%’

With a few keystrokes, someone just deleted every customer in your database whose name begins with the letter “A.” Actually, it’s even easier to delete all of your customers. If that sounds dangerous, that’s because it is. And that’s one of the reasons Microsoft has made a decision to close the door on allowing users to access the ERP database using SQL statements.

Here’s another reason for moving away from direct SQL access to your database: in a traditional on-premise software deployment, you own the entire system. You install the software on servers that you control, either within your own data center or at a remote location where you rent computing capacity. Either way segregates your data from that of other customers.

Businessman Works With Stock Infographics In The Virtual Panel O

You typically deploy modern cloud-based software in a multi-tenant environment, though. That means multiple companies use the same ERP system and share the same database servers within a remote data center. ERP vendors must take care to segregate your data from that of other customers, and vice versa. By preventing all customers from directly accessing their databases using SQL, cloud software vendors like Microsoft add another layer of separation between your data and the data that belong to other customers within their data center.

What does all this mean? Most traditional reporting tools rely on SQL to access ERP data, but with the move to the Cloud, you no longer have that option. The reporting and analytics tools that customers previously used to access Microsoft Dynamics AX data will no longer work with Microsoft D365 F&SCM. Instead, Microsoft provides you with a few alternatives:

1. Data Entities

Microsoft suggests two potential alternatives to the traditional SQL approach. The first is an intermediate data access layer known as “data entities.” When a reporting tool asks a data entity for information, it is not making a request to the database directly. It’s addressing the data entity as a kind of intermediary, which, in turn, queries the database for the required information.

The main problem with this approach is that it’s substantially slower than the traditional SQL method. With large data sets, the difference can be material, so much so that for many customers, the performance could be a deal-breaker, rendering reports prohibitively slow.

Data entities also require up-front effort by someone with advanced technical skills. Microsoft provides over 1,700 data entities out of the box, which means that finding the right off-the-shelf data entity for your report can be tricky. Customers will need to spend time customizing the existing entities or creating new ones from scratch to meet their reporting needs. That requires specialized skills, so it costs money. If you’re lucky enough to have the right expertise in-house, it will most likely consume a lot of time. Otherwise, you’ll need to pay an expensive outside consultant to do the job for you.

You will need to put in a lot of up-front effort and perform ongoing maintenance when Microsoft adds new functionality to Microsoft D365, or when you create custom extensions, or when you install third-party extensions that change the data model. That adds up to a high cost of ownership.

2. Bring Your Own Database

Because data entities will be problematic for many Microsoft D365 F&SCM customers, Microsoft has come up with an alternative approach called “bring your own database,” or BYOD for short. With this approach, customers install a second database that contains an exact copy of selected data entities.

That second database (known as the “BYOD”) is for reporting only. You keep it up to date through a constant replication process in which you copy new or updated data from the live database to the reporting database.

The good news is that the BYOD is not subject to the same restrictions as the live production ERP database. That means that customers can use traditional reporting and analytics tools to access it. The BYOD recognizes SQL statements and executes them upon request. That’s great for reporting, and it’s fast. Unfortunately, though, it has some drawbacks as well.

For more powerful, multidimensional OLAP-style reporting, BYOD misses the mark. OLAP reporting has traditionally relied on a data warehouse, which (like BYOD) involves creating a copy of the transactional data from your ERP system. Data warehouses do something more than that, however; by preprocessing data into “OLAP cubes,” a data warehouse makes it possible to aggregate totals and present them much faster. OLAP reporting using a data warehouse is a well-proven solution for companies with robust reporting requirements. BYOD doesn’t do that; there’s no optimization built into it that can support advanced OLAP analytics.

Unfortunately, BYOD complicates things somewhat as well. In the short term, Microsoft recommends this approach as a solution for reporting with third-party tools. It is not clear, though, whether the company plans to support this approach in the long term. In fact, it seems unlikely, given the fact that BYOD is essentially just a workaround to current reporting limitations with data entities. Microsoft has another vision, though, and that option gets quite a lot more complicated.

3. Data Lakes

Microsoft’s next option is called Azure Data Lake Services (ADLS), and for the time being at least, it seems to be the company’s favored long-term solution to the Microsoft D365 F&SCM reporting challenge.

“Data lake” is a generic term that refers to a fairly new development in the world of big data analytics. Data lakes orient toward unstructured data and artificial intelligence.

What are unstructured data? It’s probably easiest to answer that by giving a few examples. First, let’s consider what “structured” data look like:

CustomerID CustomerName Balance Due
12508 ABC Corporation 5,283.56
12509 XYZ Company 10.206.59


Structured data are, by their very nature, orderly and predictable. If you wanted to add another row of information to that table, chances are you could provide information that fits well alongside what’s already there. ERP data are highly structured.

Unstructured data, in contrast, are easy for humans to understand but harder for machines to comprehend. For example, a lot of companies today collect information from social media to learn more about what consumers think about their products. Facebook posts or Twitter messages are examples of unstructured data. Unstructured data have no predictable format. Someone has to decipher the meaning behind conversational statements made online. Machines must be capable of eliciting important words and phrases, distinguishing sarcasm, and discerning positive vs. negative sentiments. Artificial intelligence is the solution to that problem, and that’s what data lakes handle by design.

Woman With Her Arms Crossed

What Else Is There?

Here’s the problem: Although data lakes can also handle structured data, it’s simply not their forte. Microsoft wants to drive the adoption of Azure Data Lakes, and it has a large customer base in need of financial and operational reporting. It only seems natural that the company would try to fit those two needs together. Unfortunately, the two are just not a good match. Data lakes are not straightforward tools for orderly storage and retrieval. They are data science tools used to analyze and decipher highly complex data sets consisting of structured, unstructured, and semi-structured information, all mixed together.

Data lakes are a new technology, and like any new technology, early adoption can be painful and expensive. Microsoft clearly has an interest in promoting its ADLS product, largely because it fits so well with the company’s strategy of promoting “the Microsoft stack.” But what’s good for Microsoft isn’t always necessarily good for its customers, and that’s true in this case.

Customers should be very cautious about moving to a highly complicated, unproven product that was never intended to provide the kind of standard, structured, and reliable reporting on which finance and accounting professionals rely. Being an early adopter of this strategy can be a painful and expensive path.

The Better Alternative: Best-Of-Breed Reporting and Analytics

There is another way to tackle your reporting needs in Microsoft D365 F&SCM, using a proven solution. Jet Analytics from insightsoftware provides you with a turnkey solution for operational and financial reporting. We built Jet Analytics on a traditional data warehouse and designed it to work with Microsoft D365 F&SCM out of the box. It comes pre-configured for Microsoft Dynamics with a complete set of standard ready-to-use business entities. This makes the process of getting started with Jet Analytics remarkably fast and easy.

Jet Analytics works with Microsoft D365 F&SCM out of the box, using proven data warehouse methodologies. In other words, it doesn’t need to “work around” the problem. It handles data automatically, delivering fast, powerful reports without the complicated infrastructure.

We built Jet Analytics from the ground up with finance and operational reporting in mind. We designed our toolset to enable end-users to create ad hoc and custom reports with minimal training, to remove the dependency on expensive technical resources.

Jet Analytics provides enterprise-grade capabilities at a lower total cost of ownership. Because we built our product on Microsoft technology and because it leverages the Azure platform, it offers a clear path to transition to data lakes, if and when you decide that it fits your strategy to do so. As the ADLS technology matures, it may eventually serve a broader range of customers with a wider variety of needs.

Jet Analytics’ benefits include:

  • Powerful and flexible reporting: Most business leaders want a mix of options, including dashboards, standard tabular reports, drill-down capabilities, secure report distribution, and integration with Microsoft Excel. Jet Analytics delivers power and flexibility.
  • Reliability and speed: Management needs to see information on a timely basis. Waiting for an hour or more (perhaps even a full day, sometimes) is unacceptable. Reporting systems need to be operational and available so that leaders have clear visibility of what is happening at all times. Everyone throughout the organization needs to be able to trust the information. Jet analytics provides that single source of truth, with speed, reliability, and accuracy.
  • Self-service: Today’s organizations need to be more efficient than ever before. Unfortunately, many of the reporting and analytics tools on the market are too dependent on IT experts. That dramatically increases the total cost of ownership and creates bottlenecks that slow the business down. Jet Analytics makes it easy for virtually anyone in the organization to create and modify reports without advanced technical skills.
  • Cost-effectiveness: Finally, a good reporting system needs to be cost-effective. Any meaningful cost analysis must necessarily account for the long-term implications of owning and using a product. When you introduce technically complex products into your IT environment, the total cost of ownership goes up. That is especially true when the product is in an early stage of maturity. Jet Analytics delivers high value at an affordable price. Even more importantly, it results in lower long-term costs because it isn’t dependent on heavy-duty IT infrastructure and the technical experts to operate it.

Jet Analytics is a proven reporting product that has been on the market for years, serving thousands of customers in the Microsoft Dynamics market. insightsoftware has helped thousands of companies achieve financial intelligence and operational excellence by making reporting easy. Our no-nonsense approach delivers fast, efficient, and effective reports that deliver high value and flexibility for end-users. If your business is striving to achieve greater speed and agility, insightsoftware can help you reach your goals.

If your organization is running Microsoft D365 F&SCM and you want a world-class reporting platform with a real-world track record, download our Jet Solutions brochure, built for Microsoft Dynamics 365 to learn more.