Navigating Data Entities, BYOD, and Data Lakes in Microsoft Dynamics

insightsoftware -
September 4, 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 […]

Propel Microsoft Dynamics

Historically, users of the Microsoft Dynamics AX product and its predecessor Axapta have had free and open access to the underlying relational database. Consultants and developers familiar with the AX data model could query the database using any number of different tools, including a myriad of different report writers.

When Microsoft released the next generation of the product in 2017, Microsoft Dynamics 365 for Finance and Supply Chain Management (D365F&SCM), there were some significant changes behind the scenes.

Data Entities

One very important change had to do with access to the database. The SQL query language used to extract data for reporting could also potentially be used to insert, update, or delete records from the database. To enhance security, Microsoft decided to restrict direct access and replace it with an abstraction layer comprised of “data entities.”

Now, instead of making a direct call to the underlying database to retrieve information, a report must query a so-called “data entity” instead. Each data entity provides an abstract representation of business objects within the database, such as, customers, general ledger accounts, or purchase orders. It functions as a kind of gatekeeper; it will respond to legitimate queries by serving up information to the requester, but it will not allow the kind of wholesale changes that could be made using traditional SQL queries.

Data entities are more secure and arguably easier to master than the relational database model, but one downside is there are lots of them! Currently, over 1,700 data entities are available and counting. For customers, it can be hard to find the specific entity or entities they need—like looking for a needle in a haystack.

Bring Your Own Database

D365F&SCM customers are invariably processing enough data that they can run into substantial issues with reliability and performance when running reports using entities. To mitigate this performance challenge, Microsoft offered a workaround. Its solution was to replicate data from the production database, using data entities, into a traditional relational database. This approach allowed for the use of traditional reporting tools, including any third-party tools, as had always been the case with AX.

Microsoft referred to this approach as “bring your own database” (BYOD).  BYOD essentially functions as a copy of the production data entities, to be used for reporting or staging data for integration to external systems. That’s appropriate and adequate for traditional reporting tasks. For more sophisticated multidimensional reporting functions, however, a more advanced approach to staging data is required.

Charts and graphs displayed on a tablet.

The Data Warehouse Approach

Data warehouses gained momentum back in the early 1990s as companies dealing with growing volumes of data were seeking ways to make analytics faster and more accessible. Online analytical processing (OLAP), which enabled users to quickly and easily view data along different dimensions, was coming of age.

The challenge with OLAP, however, is that it requires intensive processing power to aggregate data according to various categories or dimensions. Data warehouses addressed that problem by serving as a staging area where detailed transactional data could be aggregated and summarized in advance.  Instead of summarizing tens of thousands of rows of data at runtime, reports could be produced very quickly because the heavy lifting had already been done.

Data warehouses have been in widespread use for years. There is an established body of practice around creating, managing, and accessing OLAP data (known as “cubes”). For the vast majority of financial and business reporting, this is a proven, reliable solution.

Data Lakes

There has been a lot of talk over the past year or two in the D365F&SCM world about “data lakes.”  Data lakes serve a fundamentally different purpose than data warehouses, in the sense that they are optimized for extremely high volumes of data that may or may not be structured.

With the proliferation of social media, for example, organizations see a great deal of unstructured data in the form of posts, comments, shares, and likes. A comment extolling the virtues of a recent movie, for instance, could be described or analyzed in a number of different ways, but it lacks the kind of enforced structure that you expect from transactional business data. There are virtually no rules about what such data looks like. It is unstructured.

The problem is that most unstructured data is still highly relevant in some way. You would like to use it, if not now, then perhaps someday in the future. With the cost of data storage continuing to plummet, many companies have decided that it makes sense to retain that kind of information, even if it will not be used right away.

The answer, therefore, is to store that data as-is, and figure out what to do with it later. Traditional databases and data warehouses do not lend themselves to that task.

Data lakes were developed in response to this problem. Where traditional databases and data warehouses begin by defining what your data should look like (that is, they must have a defined data model or “schema”), data lakes move that step to the end of the process.

The traditional approach to populating a warehouse is known as ETL: Extract the data from somewhere, Transform it to fit the desired data model and make it ready for reporting, and Load it into the data warehouse where it will be ready to use. With data lakes, the order of operations changes to “ELT”; extract, load, and wait until later (when you need the data) to do the transforming.  This is called “schema on read.” In other words, there is no attempt to describe the data until you are ready to do something with it.

Not Designed for Financial and Business Reporting

Data lakes were developed in response to a rapidly growing volume of unstructured data, plus an emerging capacity for analyzing that data with artificial intelligence (AI) and machine learning. That will undoubtedly open up some very exciting opportunities for innovation, but it is very different from financial and business reporting.

Microsoft is investing big in its Azure Data Lake Services (ADLS) platform, but customers should exercise caution before they commit to the data lake approach.

After locking down direct access to the D365F&SCM database, Microsoft appears to be scrambling to address a problem of its own creation. BYOD works adequately for some primary reporting functions, but for sophisticated multidimensional reporting on large volumes of data, the company is lacking a reliable data warehouse solution for D365F&SCM.

Microsoft’s current strategy for D365F&SCM appears to be based on the premise that data lakes are the future of business reporting. However, for many organizations that don’t need to support advanced AI use cases today, data lakes can be overkill, not to mention costly and time-consuming.

Today, data lakes are still an evolving technology aimed at solving a very different problem than financial reporting. Mastery of ADLS’s unstructured storage system requires significant technical knowledge and experience setting up multiple systems. Because ADLS is new technology, there simply isn’t a lot of experience on the market.

Microsoft would like to establish itself as a leader in AI and machine learning, and migrating tens of thousands of Microsoft Dynamics customers to ADLS would certainly help get it there.  However, developing a mature, fully functional product for Microsoft Dynamics customers will likely take several product releases over a number of years, so customers should do their homework before making substantial commitments.

The D365F&SCM roadmap calls for an initial release of the Export to Azure Data Lake feature by August of 2020.  As of this writing, that product is still in testing, and no formal release date has been announced.

Another Alternative to BYOD

Jet Analytics is a data warehouse automation platform that enables D365F&SCM customers to accelerate and de-risk their BI projects without requiring specialist skills. Jet Analytics provides for a one-to-one match with source tables and a set of pre-defined data entities out of the box. This makes the process of getting started with Jet Analytics remarkably fast and easy.

In testing, entity changes were processed by Jet Analytics an average of four minutes faster than with BYOD.  When you multiply that by the 1,700 data entities in D365F&SCM, the time adds up. Whether you are using in-house IT resources or outside consultants, that can get expensive. Jet Analytics can save substantial time and money.

With the traditional data warehouse approach used by Jet Analytics, reports are generated based on OLAP cubes or Tabular Models, which means substantially faster reports. Running a relatively simple journal report using Azure with the BYOD database as its source can take 30 seconds or more, compared with near-instantaneous results from Jet Analytics. For many senior executives, slow reports mean that valuable time is wasted.

Jet Analytics, in contrast, provides a platform to meet the needs of your most demanding users.

What’s The Right Approach For You?

Data lakes are a legitimate and important technological innovation. However, companies should carefully consider the real cost of migrating entities to data lakes before taking the plunge. As a new technology, ADLS has a long way to go before it reaches maturity. Leaders should consider which investment strategy will help them avoid the burdensome cost of redeveloping reports in the coming years.

If ADLS is to serve merely as a data repository for finance and business reporting, then it’s overkill and adds expense and complexity (not to mention risk) to your implementation.

Consider which of the following scenarios applies to your business:

  • If your business needs financial and operational reporting but is not currently leveraging machine learning or other sources of mass unstructured or semi-structured data, avoid the ADLS approach until the technology matures—five to seven years from now. In the near term, address your immediate reporting needs with a tool like Jet Analytics.
  • If your organization wants to keep options open for advanced tech such as AI and machine learning, consider some incremental investments that don’t require a full-on commitment with all of your financial and operational reports. Jet Analytics fully leverages the Microsoft stack, solves your immediate reporting challenges, today, and allows you to incrementally adopt elements of ADLS and Microsoft’s modern data warehouse platform over time.
  • If your organization is committed to an aggressive go-forward strategy with AI and machine learning, ADLS may be the right approach to take, even in the short term. ADLS will likely require broader investments, including Azure Synapse and Microsoft’s data warehouse components.

For a detailed look at Data Lakes from the perspective of a business running Microsoft Dynamics 365 for Finance & Supply Chain, download our Azure Data Lakes Whitepaper.

Navigating Data Entities, BYOD, and Data Lakes in Microsoft Dynamics

Download Now

Key Takeaways

Microsoft’s strategy around reporting for D365F&SCM is very much in flux. The company has slammed the door shut on traditional direct database access, and it is now in a period of transition. The BYOD approach is an adequate workaround, but Microsoft appears to be de-emphasizing BYOD in favor of data lakes.

Unfortunately, Microsoft’s data lake solution is far from being mature. Companies that roll out ADLS as their reporting platform for D365F&SCM will likely need to re-create reports and Power BI dashboards from scratch at some point in the future.

Jet Analytics works with D365F&SCM today, offering a proven, flexible, easy-to-manage data warehouse automation platform that makes it quick and easy to deploy your chosen front-end visualization or reporting, so you can ensure decision-makers have access to the information they need in a format they understand.

Jet Analytics provides enterprise-grade capabilities at a predictable cost. It is built on and leverages the Microsoft Azure platform, offering a clear path to transition to data lakes, if and when you decide that it fits your strategy to do so.

If your organization is running Microsoft Dynamics 365 for Finance and Supply Chain Management and you want a world-class reporting platform with a real-world track record, contact us to arrange a demo of Jet Analytics.

Request Demo