07 November 2008

Enterprise reporting

    Supposing that your company invested lot of money in an ERP system, moreover around the nice expensive setup it needed to customize many of the existing functionality. So you end up with an ERP system which provides more or less the functionality you needed, though in order to increase its value and make accurate decisions you need some reports out of it. What you do then?

    From my point of view there are 4 types of reporting needs: - OLTP (On-Line Transaction Processing) system providing reports with actual (live) data; - OLAP (On-Line Analytical Processing) reports with drill-down, roll-up, slice and dice or pivoting functionality, working with historical data, the data source(s) being refreshed periodically;
- ad-hoc reports – reports provided on request, often satisfying one time reports or reports with sporadic needs;
- Data Mining tool(s) focusing on knowledge discovery;
    Actually I would have put a 5th requirement related to direct data access and analysis.


    ERP systems like Oracle Applications or SAP come by default with a set of (predefined) standard reports, which in theory they satisfy the prerequisites of an OLTP system. Unfortunately the standard reports satisfy only basic needs and are not as flexible as expected – it could happen that they can be exported only to text, moreover they being in a non-tabular format, and therefore impossible to reuse for detailed analysis. Another problem could be caused by inadequate filtering constraints, behavior or scope. If existing functionality has been customized, most probably existing reports need to be adapted to the new logic. In the end you realize that you either change the existing reports or adopt an OLTP solution.
    The bad news don’t stop here, as the vendors try to keep the secrecy about their solutions, the information about ERP’s internals is limited, good developers hard to find or really expensive, and often they needing to reinvent the wheel. It’s true that Oracle provided through Metalink a nice set of documentation about Oracle Application’s internals, and even if they updated currently web site’s look and feel, there are still having many gaps concerning tables’ structure and functionality. Fortunately armed with enough patience and some knowledge about existing business processes and databases, a developer can reengineer an important part of the logic. Other good news is that the increasing number of posting focusing on ERP solution, however few are the source that bring something new.

    OLAP solutions presume the existence of a data warehouse that reflects the business model, and when intelligently built it can satisfy an important percentage from the business intelligence requirements. Building a data warehouse or a set of data marts it’s an expensive and time consuming endeavor and rarely arrives to satisfy everybody’s needs. There are also vendors that provide already made models, and at a first view they look like an important deal, however in time excepting the fact that vendor detains the ownership of the model, I bet that such models have many gaps. Again you end up by customizing and extending the model, running in all kind of issues involving model’s design, flexibility, quality, resources and costs.     There are many ways of making things go wrong, one of the interesting situations being when an OLAP system is used to satisfy OLTP reporting needs. It’s like using a city car in a country cross race – you might make it to compete or even end the race, if you are lucky enough, but don’t expect to make a success out of it!

    The need for ad-hoc reports will be there no matter how complete and flexible are your existing reports. There are always new requirements that must be fulfilled in utile time and not rely on the long cycle time needed for an OLTP/OLAP report. Actually many of the reports start as ad-hoc reports and once their scope and logic stabilized they are moved to the reporting solution. Talking about new reports requirements, it worth to mention that many of the users don’t know exactly what they want, what is possible to get and what information it makes sense to show and at what level of detail in order to have a report that reflects the reality. In theory is needed a person who facilitate the communication between users and development team, especially when the work is outsourced. Such a person should have in theory a deep understanding of the business, of the ERP system and reporting possibilities, deeper the knowledge, shorter the delivery cycle time. Maybe such a person could be dispensable if the users and development have the required skill set and knowledge to define and interpret clearly the requirements, however I doubt that’s achievable on large scale. On the other side such attributions could be taken by the IM or functional leaders that support the ERP system, it might work, at least in theory.

    Data Mining tools and models are supposed to leverage the value of an ERP system beyond the functionality provided by analytic reports by helping to find hidden patterns and trends in data, to elaborate predictions and estimates. Here I resume only saying that DM makes sense only when the business reached a certain maturity, and I’m considering here mainly the costs/value ratio (the expected benefits needing to be greater than the costs) and effort required from business side in pursuing such a project.

    There are situations in which the functionality provided by reporting tools doesn’t fulfill users’ requirements, one of such situations being when users need to analyze data by themselves, to link data from different sources, especially Excel sheets. It’s true that vendors tried to address such requirements, though I don’t think they are mature enough, easy to use or allow users to go beyond their skills and knowledge.
    Most of such scenarios resume in accessing various sources over ODBC or directly using Excel or MS Access, such solutions being adequate more for personal use. The negative side is that people arrive to misuse them, often ending up by having a multitude of such solution which maybe would make sense to have implemented as a report.
    There are managers who believe that such tools would allow eliminating the need for ad-hoc reports, it might be possible in isolated cases though don’t expect from users to be a Bill Inmon or Bill Gates!

    All the tools have their limitations, no matter how complex they are, and I believe that not always a single tool addresses all requirements. Each of such tools need a support team and even a center of excellence, so assure yourself that you have the resources, knowledge and infrastructure to support them!

No comments: