25 March 2010

A Reporting Guy’s Issues

      For more than 6 years, between many other tasks, I was also the “reporting guy”, taking care of the ad-hoc reporting requirements, building one or two data warehouses and even two reporting frameworks in order to fulfill the reporting requirements within the enterprise I was working for. 99% of the reports were based on the two ERP systems (Oracle e-Business Suite & IFS-iV) the customer had in place during this time, fact that helped me learn a lot about the data architecture of such systems, about processes, data quality and many other issues related to data, how to do and how not do things. In this post I just want to highlight some of the issues I was confronted with and I don’t intend to point with the finger to anybody, so I apologize if anybody will be offended.

     Even if it’s hard to believe, the main issue I had during this time was the lack of relevant documentation on applications, especially on database models and processes, or even if there were such documents they were not updated and the value of true of the information contained were supposed to be always checked against the data. Of course, there are always the users, the knowledge workers from whom valuable information could be obtained though they are not always available and many of them are highly specialized in their field that you need to interview multiple users in order to built a close to complete picture, and even then you have to check the new acquired information against the data. From time to time you may even find out that the information you got from the users don’t entirely match the reality, that there are exceptions and other rules not already discussed. Sometimes it’s even easier to derive knowledge directly from the data, table structure and other developers’ experience (e.g. blogs, books, forums) rather than hunting down the workers who detain knowledge about the topic in scope. Actually things don’t look so bad, in the end you reach to accomplish the job though it takes more time than if you were having adequate documentation. Trying to give a roughly estimation I would say that when starting to work with a new system I was spending 2-3 more time to accomplish a task than if I were having the right documentation, while in time, accumulating more experience I arrived to be even proactive by exploring unknown “territories” about the respective systems, fact that allowed me to easier fulfill users’ reporting requests.

      Because in the past 3 years I was supporting mainly Oracle eBusiness Suite (EBS) users with reports and knowledge about the system, several of the issues I had are related to it. In addition to its metadata system implemented in system table structure, Oracle tried to build an external metadata system for its ERP system, namely Metalink (I think it was replaced last year), though there were/are still many gaps in documentation. It’s true that many such gaps derive from the customizations performed when implementing the ERP system, though I would estimate that they qualify only for 20% of the gaps and refer mainly to the Flex Fields used for the various purposes. A second important issue I had with Oracle database engine itself was a bug that didn’t allowed me to use SQL ANSI 92 syntax for linking more than 6-7 tables to a parent table, fact that made me abuse of inline views in order to solve this issue; even if Oracle had a already a patch to address this issue since several years it seems that it wasn’t run by admin, maybe from well supported reasons. A third issue is related to the different naming conventions used for the attributes from EBS modules, mainly a result of the fact that solutions brought from other bought vendors were integrated with a minimum of changes. A fourth issue is related to the EBS UI, pretty poor if we consider the advances in web technologies, on the other side I have to admit that given the complexity of an ERP system it’s almost impossible to change the UI at this scale.

    Occasionally I saw many people believing that any user could write a query, that often equating with the creation of an ad-hoc report. It’s true that writing a query is a simple task and in theory anybody could learn to do that without much of effort, though there are other aspects than need to be considered, aspects like choosing the right data source, right attributes and level of detail, design the query or solution for the best performance (eventually building an index or using database objects that allow better performance) and reuse, use adequate business rules (e.g. ignoring inactive records or special business cases), synchronize the logic with other reports otherwise two people will show the management distinct numbers, mitigate the Data Quality and Deliverables Quality issues, identify the gaps between reports, etc. In addition having users creating personal solutions instead of using a more standardized approach is quite risky because you’ll arrive to have in your organization a web of such solutions over which you have no control from a strategic and/or data security point of view. On the other side users will need the possibility to analyze the data by themselves, to join data coming from multiple sources, therefore special focus should be given also to such requirements, though once their reporting needs were stabilized they should be moved, if possible, to a more standardized solution.

      When multiple developers are approaching reporting requirements they should work as team and share knowledge not only on the legacy system but also on users’ requirements, techniques used and best practices. Especially when they are dispersed all over the globe, I know it’s difficult to bring cohesion in a team, make people produce deliverables as if they were written by the same person, though not so impossible to achieve with a little effort from all the parties involved. Why I’m mentioning this? The problem is that the more perceived variability you introduce in deliverables, greater the risk to have the quality of deliverables questioned, in time leading to users not to adopt a system or prefer to use one resource in the detriment of another. In addition must be considered also the effort needed in order to find the gaps between reports, to modify deliverables to expectations, etc. From this perspective is always a good idea to document at least at minimum all deliverables, detailing the scope and particularities of the respective request. I know that many believe that code is self-explanatory and needs no additional documentation, though not everybody is a genius and frankly no matter how much I try I can’t always intuit the context, why a technique or a certain level of detail was preferred, or why some constraints were used. 

     Outsourcing is a hot topic these days, especially when is requested to reduce the headcount and cut down costs, thus it has inevitably touched also the reporting area. I can’t say I’m against outsourcing as long such initiatives are well designed/driven, and above the many benefits and issues they come with, people have to consider that for a developer to create a report is needed not only knowledge about the legacy systems and tools used to extract, transform and prepare the data, but also knowledge about the business itself, about users expectations and organization’s culture, the latter two points being difficult to experience in a disconnected and distributed environment. Of course, even if delivering the same result and quality is possible as if the developers were onsite, in the end it implies additional iterations and overwork, the users need to be trained in order to specify the reporting requirements adequately or a special resource needs to be available in order to translate the requirements between the parties involved, lot of back and forth communication and all the other issues deriving from it. There are several other aspects that need to be considered and therefore I can’t decide whether outsourcing really makes sense from a reporting perspective, anyway there are high level decisions and considerations that go maybe way above my knowledge, though, as developer, if I consider the amount of additional effort and time I have to spend in order to deliver at the same quality, I would definitely say no to an outsourcing model, and this because is my time at stake, time I could better use in order to learn something new or do to something more constructive than modifying code over and over again, or writing emails after emails for issues that could have been solved in a 10 minutes meeting. 

No comments: