Hello and welcome to our latest Blog Post.
Thank you very much for dropping by, we really appreciate you!
Today we are going to cover in some detail the typical issues of ERP Business Intelligence.
For the purposes of this blog post we will not talk about SAP and Oracle Applications because they have their own BI solutions and we at Meta5 are unlikely to ever be a part of their offerings.
However, there are many other ERPs out there like Microsofts Navision and AX ERPs as well as smaller lesser known ERPs.
Just as a “teaser” we will be able to offer a Microsoft Navision BI solution in a few months time.
This product is in development by one of our customers. Meta5 plays an integral role in this coming solution and we will be able to sell this solution in the USA and Canada.
One of the reasons we are writing this blog post now is that we just completed an ERP BI project and we are in the middle of the development cycle for a version 1.0 Navision BI project.
The ERP BI Project we just completed was for Infor FACTS.
Infor FACTS is an ERP which is primarily used by distributors of various physical products.
Infor claim to have more than 3,000 installations of FACTS in production.
We would like to hear from any and all Info FACTS users who are interested in better, cheaper faster BI from your Infor FACTS ERP.
We have a blog post about this success story that you can read by clicking on the button below.
So, though we have long known about the issues of doing BI on ERPs, we are really only just gaining solid experience of solving those issues very inexpensively.
In this blog post we will talk about the issues of ERP BI and then we will talk about how we have solved those issues.
We believe we have solved them less expensively than anyone else.
Issues With ERP BI Solutions
#01 – The “Bent” ERP Implementation
We all remember the 90s promise of the “ERP Solutions”.
“Just install our ERP, set up simple rules about how your business operates, migrate your data, and WHOLA! You will be running your business quickly, easily and cheaply on our new magical all encompassing ERP Solution!”
Or something like that. Right?
Well? It didn’t quite turn out like that. One of the worlds biggest culprits was SAP but it would be unfair to say they were alone. They were certainly not alone.
Companies soon found that ERP system implementation were not all they were talked up to be and that significant customization was required to “bend” the ERP to the companies business processes.
In fact the “bending” went in both directions to a greater or lesser degree per installation. In some companies the companies business processes themselves were significantly “bent” to comply with the ERP.
In almost all cases there was significant business process re-engineering to the business to get it to fit the ERP and significant “bending” of the ERP to fit the newly defined business processes.
To whatever degree per implementation, ERPs were “bent” to fit the business it was being installed in.
So what did this do to the shiny new “BI Solution” you bought with this shiny new ERP?
Well? When the operational tables in the ERP were changed? Guess what? That had a knock on effect to the shiny new “BI Solution”.
- The ETL had to change.
- The DWH data models had to change.
- The cubes had to change.
- The reports had to change.
- The dashboards had to change.
The promise of your shiny new BI system turned out to be as hollow as the center of the average New York donut and much more expensive.
But now you had the ERP system.
You just paid a fortune to get it installed.
For better or for worse you had to make the changes to all of the above and just spend the money.
Right?
There was no alternative product except that from the supplier, Right?
Well not any more. We solve this problem and we address each issue separately below.
#02 – The Sheer Number Of Tables and Fields
I don’t know if you have ever looked in to the underlying data models of ERPs like SAP, Oracle, Navision, AX or Infor FACTS.
But if you do?
What you will find is a LOT of tables with a LOT of fields.
- What do they all mean?
- How do they relate to each other?
- How do you derive the business reports and dashboards needed from this base underlying data?
- How do you integrate other data, including external data, with this ERP data?
The sheer number of tables and field creates a number of problems for building a BI solution that is expensive to solve.
- The data models for the staging area, data warehouse, and cubes have a cost curve in ETL development, model development, and report development, that is pretty much linear to the number of tables and fields that will be placed in each layer.The more data you want in your reports and dashboards? The more it is going to cost.And the cost of adjusting to the “bending” of the ERP system to use the vendors BI solution gets added to the cost of the project.
- In order to build the data models, reports and dashboards you need expert knowledge of the data itself.And because there is so much of it that expert knowledge generally only resides with the vendor.You have to learn what they know in order to be able to support changes to the BI solutions.
- Processing times. With so many tables and fields to extract data from and so many layers to send data to, you will have real problems with your processing times unless you are able to perform this processing in a some how very effective way.
Meta5 Solutions to Issues With ERP BI Solutions
#01 – The “Bent” ERP Implementation
As you will know, the predecessor to Meta5 was the company Ralph Kimball co-founded in 1982 called Metaphor.
Metaphor was the global leader in BI in the 80s which is why IBM bought the company.
IBM is, to this day, one of the worlds biggest users of Meta5.
Some of the worlds best and brightest BI people came out of the Metaphor experience.
These people have a unique view of BI because they were there in the early days and they understand BI better than those who came later.
Back in the early 2000s there was a great deal of “hype” around “packaged analytical applications”.
The idea was, like ERP BI, that vendors could sell a suite of “packaged analytical applications” for considerable profit.
These failed for the same reasons that ERP BI Solutions are so expensive. But Ralph Kimball and some former Metaphor users were talking with each other about these problems and they came up with a vision.
“If an ETL system can be built that is 10X easier to make mapping changes to than todays ETL software then that would enable the implementation of packaged analytical applications.”
That software was duly built and we call it the “Meta5 Workbench” now.
The “Meta5 Workbench” enables us to change ETL rules created for the “standard ERP” to accommodate the “bent ERP” at a rate of about 10X faster than the changes to those rules if they are implemented with other name brand ETL tools like Informatica and DataStage.
So the first element of being able to more cheaply respond to the issue of the “bent ERP” is a way to alter ETL rules 10X more cheaply than the vendors themselves can alter those rules.
The second element of more easily responding to the “bent” ERP is the Meta5 software itself.
Meta5 has the “Workstation Tools Data Dictionary” which is a dictionary interface between all Meta5 applications and the underlying data warehouse.
This dictionary gives a very high level of insulation of the underlying database to the applications that have their data populated through the Meta5 data access tools.
Tables and columns can be added to this dictionary and exposed to the applications much more easily than can be done with any other tools while retaining the insulation properties of the dictionary.
So when new columns need to be added to a report or a dashboard it is 10X faster, cheaper and easier to do that in Meta5 than it is to do it with most other reporting and dashboarding tools.
The third element of more easily responding to the “bent” ERP is that Meta5 can replace the “OLAP CUBES” that most query tools require to provide reasonable response times.
This element is in conjunction with the newer versions of Excel which contain the PowerQuery Models.
One of the greatest issues with trying to build a single consistent BI solution for an ERP is “what front end tool do we use?”
SAP decided that issue by buying Business Objects.
So if you want BI from SAP on SAP then you get Business Objects.
Oracle decided to buy a while raft of different products that then competed for the premier spot for the BI solutions product.
They still have a raft of different tools for you to choose from.
For other companies who had fewer ERP footprints and smaller budgets they were faced with the problem that they could not dictate the BI tools the customer might use.
So many of them allowed third parties to develop BI solutions in the hope that at least one of those parties would do a good job.
Over the last 30 years we all know Excel won the battle of the desktop spreadsheet.
With the addition of the Power Query model and slicers right inside Excel 365? Excel now becomes a viable dashboarding tool.
Finally, after all these years, Excel can do pretty much everything a business analyst would like to do in data analysis…
EXCEPT GET THE DATA IN TO THE WORKBOOK EASILY!
That part is still hard and prone to problems.
Until now…
We, at Meta5, have developed a way of populating the Excel 365 Power Query models (or regions or tables) reliably, repeatedly, in a parameterized fashion, so that the workbooks created have just the data needed for the specific end user the workbooks is intended for.
By doing this we have removed the need for any “cube” product to be presented to Excel for the power pivot queries inside the workbook for all but the largest of customers.
We have nicknamed this the “Excel Data Mart Dashboard” technique.
Where all the data needed to support the dashboard is inside the Excel workbook itself.
The Meta5 “capsules” can be run in a repetitive, parameterized fashion, selecting the data needed for this particular workbook, and sending it into the work book and then forwarding to the specific business person who uses that workbook.
This is a radically different approach with a radically different cost profile for the development and maintenance of these capsules, reports and dashboards.
We would like to make it clear in our blog post that we are well aware that Excel can not scale like an OLAP database can and we are not in any way saying that this alternative is suitable for companies where the data volumes for spreadsheets would exceed the ability of the spreadsheet to process that data.
By the combination of these three things, the Meta5 Workbench for ETL, the Meta5 product for the middle layer replacing OLAP, and using Excel as a front end?
We are able to more effectively deal with the “bent ERP” problem than even the ERP Vendors themselves can.
For the various ERPs we develop solutions for, we do not have to develop a solution for all the customers of the ERP system.
We need only to develop solutions for the 80-90% of customers who do not have data volumes that exceed the ability of Excel to support.
Where the installed ERP customer has data volumes that exceed the ability of Meta5 + Excel to support?
There are other products and other approaches that can be used to link Excel to cube or relational database.
We would be willing to investigate those products and approaches as and when necessary.
However, today, for most ERP vendors, our approach will suite 80-90% of the installed customers of the ERP system.
And that is more than enough prospective clients for us to work with!
#02 – The Sheer Number Of Tables and Fields
So let us consider the issues raised above and answer each one in turn.
The sheer number of tables and field creates a number of problems for building a BI solution that is expensive to solve.
#01: The Cost Curve.
The data models for the staging area, data warehouse, and cubes have a cost curve in ETL development, model development, and report development, that is pretty much linear to the number of tables and fields that will be placed in each layer.
By using the combination of the Meta5 Workbench, Meta5, and Excel, the cost curve of development and maintenance of reports, while still linear, is far less than supporting the changes to these elements of the BI solution any other way.
Despite the very large number of tables and fields the cost of the development and maintenance by our staff is far lower and that cost saving is passed on to you as a customer in lower development and support prices.
It is now a well known and well understood fact that ERP implementers tend to make their money on the years and years of “changes” that are asked for once the ERP system is installed.
This was not so well understood 25 years ago.
It is now also well understood that when a BI solution is appended to the ERP this too becomes a place where the ERP vendor can earn significant revenues from the customer.
We believe that we, at Meta5 and partners, will be able to provide a superior end user experience for installed ERP customers than can even the vendors of the ERP systems can.
As we mentioned above we are starting with Infor FACTS and then MicroSofts Navision product.
Once we have settled the implementations of these two ERPs we plan to deliver BI solutions for more ERPs according to demand.
#02: Knowledge of the data itself.
In order to build the data models, reports and dashboards you need expert knowledge of the data itself.
This is one of the biggest inhibitors to an ERP customer building their own BI solution, especially among companies with revenues in the sub USD200 million.
It simply takes too long and costs too much to learn all about the data in the ERP system to be able to build a BI solutions.
Meta5, and partners, will invest the time and effort it takes to learn the standard ERPs, and then invest the time and effort it takes to learn what customizations have been performed by your ERP implementer to your ERP.
This investment on our part with be amortized over many customers so that you get the benefit of the lower costs of the consulting hours needed to support your ERP BI solution.
#03: Processing times.
With so many tables and fields to extract data from and so many layers to send data to, you will have real problems with your processing times unless you are able to perform this processing in a some how very effective way.
The vast majority of ETL tools take the data out of the database to process it and perform calculations on it and then put the data back in to the database.
This was normal 20 years ago when databases and machines did not have the power to perform ETL inside the database.
All the major ETL tools had to be designed to support large volume customers and so all ETL tools repeated the pattern of taking data out of the database to manipulate it and putting it back in to the database when that manipulation was completed.
But 20 years is a long time in the world of hardware and databases.
We now have machines with processing costs 1000X less than 20 years ago. Disk is also around 1000X cheaper than 20 years ago.
Indeed, we now have SSDs and “in memory” databases that did not even exist 20 years ago!
In the 2020s the place to perform ETL is inside the database because that is where the ETL processing is the fastest and most reliable.
The major BI database vendors like Oracle, Microsoft, IBM and SAP have revenue streams, development budgets and user bases that dwarf the revenue streams of their ETL tools or any other ETL tools.
Data manipulation using SQL Statements inside databases are simply far more reliable than data manipulation in ETL tools.
Because the user bases of the databases are 100X the users bases of the ETL tools, if not 1000X for many of the smaller ETL tools, and the development and testing budgets of the software vary accordingly.
ETL, in the 2020s, is going to be generated SQL run inside the BI database. This trend had been growing for the last 10 years and SQL as ETL will be dominant in BI by 2030.
The Meta5 Workbench generates ansi compliant SQL as it’s ETL software. It is then run from the Workbench scheduler.
The Meta5 Workbench has tools to load almost any form of row based data. And the Meta5 product has interfaces to load virtually any semi-structured or unstructured data.
We can even log in to web based applications with robots and screen scrape information from web applications to bring data in to a data warehouse.
If the data is useful and valuable in a BI sense? You can be absolutely certain that we can get it into the Meta5 system for analysis and send the results forward to Excel, our front end of choice.
Summary
In summary, the promises of ERP vendors in the 90s went largely unfulfilled.
Even so, ERPs have become a standard part of the IT landscape even if they are more expensive to implement and use than might have been originally promised by the vendor.
Appended to these ERP systems are the vendors BI solution, or, a market place developed BI solution.
These BI solutions have an ongoing cost profile that reflects:
- The level of customization of the ERP on implementation (How much was it “bent”?)
- The sheer number of tables and fields that are in the ERP that can be used for BI purposes.
The BI solution offered by the vendor must, by necessity, support all installed customers even though only a very small percentage of those customers will have significant volumes of data.
This is why the BI solution for almost all ERP vendors must have a “cube layer” or some form of “data mart” layer that contains summarizes information that is accessible to tools like Excel and other BI tools.
The vendors must build their BI solution to support their largest customers, and the expected growth of the largest customers.
Even though these largest customers represent a very small percentage of the installed base, the rest of the installed base has to implement the same “cube” or “data mart” layer because that is what the vendor solution demands.
We at Meta5 do not have to do this.
We will be more than happy to leave the top 10% by data volume of an ERPs installed base to the ERP vendor.
The other 90% of the installed base who would be perfectly happy to use Meta5 + Excel and adopt our proposed idea of “Excel Data Mart Dashboards” is more than we could support!
Further, the ERP Vendors must provide an ETL solution of some sort. And because of the sheer number of tables and fields in the ERP they rarely, if ever, make all that data available to the BI solution. Preferring, instead, to move just the data that is “most important” measured in some way or other.
In contrast, we at Meta5 will move ALL the data from the ERP in to the staging area where it is already visible and usable from Meta5.
And over time we will move 100% of the data in the standard edition of the ERP in to our standard data warehouse.
For ERP installed accounts that have customizations we will add those customizations to your particular version of the ERP BI Solution as an optional part of our implementation services.
The pricing for which will be much less than doing that any other way.
Because our Meta5 Workbench generates ansi SQL as the ETL subsystem we know that the performance of the ETL subsystem will be more than adequate to deliver the updated data from the ERP system in a timely manner each day. Indeed, we have customers that run ETL multiple times per day.
As part of our development processes for various ERPs we will take on the investment of learning about the often tens of thousands of fields in the ERP to understand what the data means and how it works.
You will benefit from the cost of that learning curve being amortized over many customer projects.
And so there we have it. The big issues of ERP BI are:
- #01 – The “Bent” ERP Implementation
- #02 – The Sheer Number Of Tables and Fields
And we at Meta5 have found a way to make ERP BI much better, cheaper, faster.
If you have questions or would like to take a look at what we have to offer in more detail?
Please contact us via the contact details on our web site!
We look forward to hearing from you!
Thank you for reading our blog post!
We really appreciate you!
Thank you!
Meta5: The Better Way
Thank you for your time and attention
Jim Kanzler.
Leave a Reply