Live Reporting for Microsoft Dynamics vs. Data Warehouse and OLAP

Data integrations can be confusing, and they make a difference when picking the right BI tool.  This article will discuss the difference between live reporting for Microsoft Dynamics versus data warehouse or OLAP cube integrations.

Data, data, data.  These days, companies are swimming in it.  Transactional and operational information is a required aspect of conducting business these days – for many reasons, but perhaps, especially when it comes to making important decisions about the future of the company.  Business Intelligence (BI) involves analysis of enterprise data for understanding of trends and trajectories, successes and failures, as well as related planning or forecasting for the fiscal year.
https://youtu.be/Ybq0FwZ1p7I
Whether a company is using an OLAP cube or some sort of data warehouse, important company facts and figures, such as transactions, personnel information, and inventory, are used in Enterprise Resource Planning (ERP) systems and BI tools like financial report writers, budgeting solutions, and dashboards.  When selecting which BI tool to use, an important question arises pretty early in the selection process: is it more advantageous to integrate live to and from ERP systems like Microsoft Dynamics, Sage, Acumatica, or SAP Business One, or to integrate to a BI data storage option?

The answer to that question will vary from company to company, depending on size and BI needs, but even beyond the goals particular to an organization, the two types of integrations have their strengths and weaknesses.  It will be vital, when investing in BI solutions, to weigh the pros and cons of each, including a consideration of products that offer either mode of data access.  This article will discuss data integration – and how the current methodology options will affect how to run reports, create budgets, and generate dashboards.
First, it might be important to clarify the terminology.  Consolidation refers to aggregating data from multiple sources into one space.  Data migration involves moving the information from one source to another, like when moving data from a legacy hierarchical ERP database to a more contemporary, relational one.  Finally, integration refers to combining data from multiple sources into a unified, meaningful, accessible view.  A data integration generally entails detection, preparing, monitoring, converting, and transport of information from potentially several sources.  Data integration is a must these days – and good for performance management.
Technology development and advancements have allowed for storage of massive amounts of varied data.  Integration of this company data into one accessible database empowers superior analysis and critical reporting and planning.  The more data, the more precise the analytics are for decision-making.  Recently, the business world learned that data analysis can mean life or death for a company.
In terms of real world application, BI tools rely on dashboards, reports, and budgeting to navigate the sometimes tumultuous marketplace.  The recession of 2008 resulted in massive personnel layoffs, bankruptcies, and a related spike in the US unemployment rate.  Hopefully, this economic crisis was a wakeup call for corporate officers, but regardless, consistent analysis and response to company data is a must for maneuvering in the marketplace.  Theoretically, integrating data into BI analysis can keep a corporation afloat by seeing the iceberg with plenty of time to then adapt to the trajectory of the market before ever coming close to a collision.  But when it comes to the options of live integrations and data storage integrations, which is best for company needs?
Live integration is exactly what it sounds like – the integration happens in the moment from the ERP system to the BI tool.  For example, if a Microsoft Dynamics user utilized a BI solution product that allowed for a live integration, anything input into GP up to the minute he runs a report will be included in a budget with actuals versus projected report or dashboard for executives to review.  Live integrations allow for real-time analysis.  Sometimes, officers, budget managers, or any member of the finance team needs up-to-the-minute analysis that has to do with sales, inventory, and/or personnel.  Live integration would have to be an option to perform this kind of real-time analytics.  However, depending on the number of users and the size of the query performed to generate a certain report, live integrations can slow down the ERP server for all users.  In this regard, data storage options offer a more stable, speedy analysis.
Data warehouses and OLAP cubes require the data to be replicated into the storage space before any analysis can take place, but it is generally a quicker integration.  Unlike live integrations, the data is static and therefore, stable.  Any query performed to generate an analysis is grabbing replicated data, almost like a shortcut or copy of an icon on a PC.  It does not slow down an ERP database that others might be using, but instead reaches into the data storage space and pulls established information, resulting in high performance analytics.  Integrating from data storage systems is good for data analysis that does not have to be up-to-the-minute, like monthly reports that are likely routine in nature.  That allows for professionals to replicate the relevant data into the warehouse or cube for analysis.  However, if either route does not seem sufficient exclusively, there are options for both types of integrations.
There are few products on the market that allow companies to run reports live from the ERP and also have the option of integrating from a data warehouse or OLAP cube.  Arguably, this flexibility is very attractive, at least for companies where multiple people are working within the ERP system.  That said, it is somewhat rare to find both integration options in a product.  Solver, Inc. is happy to answer questions and generally review BI360’s easy-to-use Excel, web, and mobile BI tools with both real-time or data warehouse integrated analysis and collaboration as a way to accelerate company performance management beyond the ERP.