This article originally appeared on the BeyeNETWORK.
You may remember a rather obscure law of science from your high school days in physics called the conservation of energy:
The total amount of energy in a closed system remains constant. In other words, energy can be converted from one form to another, but it cannot be created or destroyed.
I don’t claim to understand this principle fully, but essentially it’s saying there is a finite amount of energy in any given system and that amount cannot be changed. All you can do is change the way it’s packaged. I guess it’s a little like the mess in my boys’ room – it never really goes away; it only changes form now and then.
I would like to offer a corollary to the conservation of energy that I call the “conservation of complexity,” which states:
The total amount of complexity in an information system remains constant. In other words, complexity can be transferred from one party to another, but it cannot be created or destroyed.
This principle states that in any given information system, there is a finite amount of complexity that cannot be removed. All you can do is determine who deals with the complexity. With any given business intelligence (BI) system, the options have traditionally been the BI software/system vendor, IT “back-end” resources, IT “front-end” resources and the end user.
Going back into ancient history (10 – 20 years ago), Figure 1 shows how the typical transactional system might have distributed complexity.
Figure 1: Distributed Complexity of Transactional System of 10-20 years ago
Essentially, this figure illustrates that in this type of system, the bulk of the complexity fell on the IT front-end resources, or the report writer/developer. In most situations, the IT back-end resources were only responsible for providing simple database views or perhaps some stored procedures to simplify data retrieval; and, in almost all cases, it fell upon the report developer(s) to deal with most of the complexity in the system. The primary problem with this arrangement was the bottleneck created by the IT front end – it simply wasn’t feasible to have enough resources on hand to keep up with demand.
I experienced this arrangement firsthand when working at my first real job after college in the mid to late ‘80s. I worked at the data processing center at Texas A&M, and part of my job was to produce IBM VM/SP (mainframe) reports for college staff. I can assure you that there was a lot more demand for information than I could possibly provide; and while the end users were certainly shielded from much of the complexity of the system, they certainly experienced more than their share of frustration waiting for their ration of data from the mainframe gods.
Once SQL-based relational systems came of age, one of the first attempts to resolve this issue was to provide the end users with their own query and/or report writing tools with the hopes that they would become more self-sufficient (ad hoc queries). The distribution in this type of system is illustrated in Figure 2.
Figure 2: Distributed Complexity with SQL-Based Relational Systems
Essentially, this arrangement eliminated the bottleneck created by the IT front-end resource. It also shifted some of the complexity to the back-end resources (primarily due to the need for additional and more complex views and reporting structures). The reason this was a colossal failure for the vast majority of end users is easy to understand (at least now): the typical end user proved incapable of dealing with such a high level of complexity. It quickly became obvious that shifting the bulk of the complexity onto the end user was not the path to take.
Once again, I experienced this misguided effort firsthand. I worked with more than one client in the mid to late ‘90s that attempted to put reporting/query tools in the hands of their end users and almost died trying. I remember one particular client, a large hospital in Denver, Colorado, that had purchased more than 400 copies of Crystal Reports and installed them on user’s PCs in the hopes they would somehow manage to find their way to their data. Wishful thinking. Fortunately for them, we were able to later work a trade for a true enterprise reporting system; but back then, a lot of end users simply put their “off the shelf” software back on the shelf.
About this same time, the data warehouse was born. The idea behind the data warehouse was to provide a back-end data structure that essentially would absorb much of the complexity associated with traditional SQL transaction systems. The distribution of complexity with a data warehouse is shown in Figure 3.
Figure 3: Distributed Complexity with a Data Warehouse
In this scenario, the warehouse vendor takes on some additional complexity by providing “out-of-the-box” data structures and tools for gathering, cleansing and organizing the data. In most cases, it still requires IT front-end resources to create queries (in the case of SAPBW) and other custom interfaces to allow users to access data stored in the data warehouse.
You may notice the end user is shown dealing with more complexity than the IT front-end resource. On the surface, this contradicts one of the stated benefits of a data warehouse – that the end user can easily access the data in the warehouse via a simple GUI interface. In other words, the user can be self-servicing. The reason for this is that we are making the assumption that many end users are not only interested in accessing the data but also inpresenting the data.
Using the standard BEx interface, it is fairly simple for an end user to access SAP BW data. All the user needs to do is open the query, respond to the necessary prompts and then run the query. The data is then retrieved into the Excel spreadsheet. If this were the end of the story, there would be very little complexity for the end user to deal with.
However, in many cases, the end user does not simply want to leave the data in the spreadsheet “as is.” The most common need is to produce printed output. Because the BEx interface is basically a “dump” of data into a spreadsheet, it requires a considerable amount of work to format the output to make it “fit for print.” Something as simple as page headers and footers are quite difficult to accomplish. Pagination and labeling are also a challenge. Therefore, when the user requires formatted output of SAP BW data, the level of complexity that the user must deal with is often quite high.
In the typical rollout of SAP BW, the initial end-user community is quite small and unusually advanced in both their data access requirements and capabilities. These are the power users. While the standard BEx interface may prove quite useful for these more self-sufficient users, it is almost a certainty that as the reports are rolled out to a wider base of users that many users will find BEx to be insufficient in meeting their needs. For these users, the distribution of complexity needs to be more like that shown in Figure 4.
Figure 4: Distributed Complexity with SAP BW
This shift in complexity is primarily the result of moving the responsibility for the finished (or formatted) output from the end user back to IT front-end resources or, as it says in the Figure 4, the “information broker.” I use this term deliberately to get away from the mind-set that it is up to official IT “front-end” resources to help users produce finished output.
I’ve worked on many business intelligence projects throughout the years, and one thing that’s always puzzled me a bit is how little companies invest into front-end resources in IT. In almost every case, even with the largest companies, you can count the front-end resources on one hand – or finger. Even then, they are almost never dedicated to assisting the end users in accessing and formatting data. They typically have other responsibilities that dominate their time and attention. The end users get the leftovers, and they get a lot of complexity dumped in their laps.
I’ll save this idea of an “information broker” for a future article. For now, my point is simply this: If your business users are looking to IT “front-end” resources to take on some of the complexity they are dealing with, they are likely going to be waiting a long time. There has to be another way.
At first glance, this can appear to be a step back to the days of transactional reporting where the bulk of the complexity fell upon the IT front-end resource, and in some ways this is true. I’ve been reading a lot lately about the need for real return on investment (ROI) with the tremendous investments companies have been making in their information infrastructure, in particular with data warehouses such as SAP BW. In order to insure maximum ROI, it is imperative that companies find creative ways to reduce the amount of complexity presented to the broader end-user base. I am convinced that a little extra investment in the right resources can, in this case, reap tremendous bottom-line benefits.