One of the many roles I have filled in my career was that of BI technical support for several products. I do not remember a single time when someone called just to tell me that things were okay and there were no problems. There always seems to be a fire somewhere, and the person discovering it somehow always manages to find a bucket of gas, rather than water, to throw on it. Murphy and his laws reign supreme in this realm.

Failure of a critical system or a lengthy delay in delivering a new one can have a huge, negative impact on the business. Most end users haven't a clue just how demanding the role of an IT technician is. Users make assumptions about how computers work and what is required, and those assumptions are often so far afield that it is amazing they don't still believe in the Tooth Fairy. Thus, IT has grown up with a need to work cautiously and to discourage change. It is not fear of work or the unknown; it is the fear of failure with nowhere to go.

Most of the technologies that we seek for BI solutions have components in which few, if any, IT staff will have any expertise. If we narrow the scope of the solution to “simple” query and reporting, we have to dabble in the following areas, among others:

IT knows that they invariably will be thrown into the mix to sort out problems for some if not all BI elements. There is a significant risk here coupled with the chance of being made the scapegoat for snags and failure. Unlike many end-users, IT professionals are keenly aware of the pitfalls when implementing any solution. Even when you have considerable expertise in certain products or platforms, there is always a chance that a large, ugly problem will arise. Remember, Murphy lives here.

If an enterprise had a corporate strategy for BI and could clearly draw IT into the picture as a critical partner, far less internal strife would occur. The IT staff should be made to understand their role in BI solutions as it relates to the success and survival of the enterprise.

One of the only defenses that IT has against random acts of BI is to try to force an intelligent and orderly evaluation process to select a tool or solution. I referenced the RFI/RFP processes in an earlier chapter. I will reference them in other sections.

When IT helps develop a list of questions and functions that are required, they normally will be totally unrelated to the business problem(s) at hand. Their primary purpose is to cover all their bases and ensure that the selection of BI tools doesn't omit anything that may come back to haunt them later. IT will be concerned with the following:

Most enterprises rarely give kudos to IT and its role in delivering BI solutions. Dating back to the early days of the Information Center, there is often a well-established negative history between IT and the users. By placing IT in a reaction mode position, there is far less room to maneuver than when there is a well-articulated and clearly understood BI strategy for the enterprise.

Let me cite an example of an IT/user disconnect. I once worked for quite a number of years on a mainframe 4GL that allowed a user to perform just about any action and calculation on data. A customer I was training sent a number of “power analysts” to a class for this tool. The attitude within the account at the time was that it was inevitable they would “get off the mainframe and onto something more modern.”

During the class, one individual seemed to have more questions than the others and wanted to work outside the scope of the exercises I had assigned. I set aside time to work on this individual's data analysis problems and discovered he had been held at arm's length by IT during a period when they were struggling to transform existing data into a radically altered form with some heavy mathematical calculations involved. The intended target for the new data was a newly defined data mart where these rather testy calculations would be provided for the users, and their reporting requirements would be minimal.

It took about 45 minutes to create a procedure that delivered a subset of the data in its new form. In less than two hours, he was producing reports that had been on hold for more than six months. We took this exciting news to his IT support people, thinking this would be great news and an epiphany as it were. Au contraire. They were quite upset at my interference. This was not a “strategic” platform or tool in their view, and I had managed to sabotage their efforts.

Did the customer ever complete the task? I don't know. I do know that they still have a mainframe and they still run this 4GL, albeit less aggressively than before. The sad thing to me was that the results could have been stored anywhere, including the new server that they had selected. It took 45 minutes to produce a result, and IT didn't even want to discuss how it was done. I do understand IT's position, but the proposed transformation methodology was one that would constantly make changes difficult to implement.

End users simply want a better way to solve data-related business problems. They want to be able to attain some degree of self-sufficiency in many cases. Others just want to have the results dropped in their mailbox or reader and go about doing their regular functions.

The difficulty lies somewhere between the intent and actual skill of each user. Some individuals are simply not technically inclined or simply do not have the time required to learn a tool as well as they need to. Regardless of attributes of any user's profile (technical, non-technical, etc.), one of the first things that you must determine is the level of commitment they have to learning a tool and completing the necessary tasks. I will get into user segmentation later.

The impact of a well-conceived BI solution on end users can be staggering. You can search the web for success stories and discover a myriad of them. Many users learn about these successes and assume that they can produce similar or more spectacular results within their own realm.

The delivery of critical new business information can change the course of an entire corporation. Significant discoveries such as massive fraud are commonplace using data mining components. The brass ring is out there for the users, and it is possible to grab it. However, lack of skills and critical data optimized for BI often create an impenetrable barrier to success.

Most end users have Excel or some other spreadsheet installed and have some degree of facility with it. The ability of spreadsheet products to perform some relatively complex calculations and analyses is often the basis for end users' assumptions about query and reporting tools. I have heard users say, “Gee! If a little thing like a spreadsheet can do all this stuff, I can't imagine what a power tool like the one you're suggesting can do!” This reaction is a bad omen.

It is difficult to explain that a row/column-oriented tool works in a sometimes rigid and inflexible manner, whereas a spreadsheet allows you to work on several ranges and shapes of ranges independently. I ask that they not be hasty in trivializing the power of a spreadsheet. The major problem with a spreadsheet application is the limitation on the amount of data sheets can handle.

The single greatest impact on the user community is time. Users will spend a substantial amount of time trying to learn the tools and the new system that will initially be unproductive. It's a bit like learning to play a guitar. The new player can't make both hands work, everything sounds terrible, and the player seems to forget everything between attempts. Suddenly, the tool's paradigm and behavior begin to make sense and new analyses take substantially less time to perform than the early ones. However, if the user wants a quick-in and quick-out BI solution, he is not the appropriate one to be involved in the early efforts and most certainly not the one who should lead the charge.

The tools with which most users are equipped today are a result of the many “suites” provided by various PC software vendors. The typical user has a spreadsheet, a word processing package, a graphics creation and display package, and some form of browser permitting internal and possibly external site access. Many customers have implemented a portal or have developed a portal, which has a set of applications and information sites available. The population with BI query/reporting tools installed is considerably smaller…so far.

Let's assume that our enterprise (or department, functional area, etc.) has tired of its current analysis capabilities and that we have done due diligence in selecting a vendor's BI offering and can now pummel our data into submission and get the results for which we have been clamoring.

The end users finally have their “intuitive” query tool and access to the newly created data…and they can't produce the result they desperately need. They find it easy to grab a few columns and produce or total this grouped by that, but the more complex requirements appear to be a bit more difficult than they were lead to believe. Most BI solutions go through a series of phases during the cycle from pre-sales to successful implementation. The user phases traditionally follow this pattern:

This is a bit flippant and not exactly what you will see in every scenario. But I guarantee that it will happen more often than not if the end users are not intimately involved in the entire gamut of BI activities. Part of the problem lies in the users' expectations of what a query tool will allow them to do easily, if at all.

The users of BI solutions normally fall into three major categories with very different skill sets and requirements. We will deal with user segmentation in a later chapter, but for now let's acknowledge that there are distinctly different users (and abusers) of data, as depicted in Figure 4-1.

Figure 4-1. End user segmentation by technical skills.

Note that there is no indication of the roles or responsibilities of the users. It is safe to assume that the majority of managers or executives will not be among those who will “push the envelope” of technology as a hands-on user. They will, however, be those who demand the most complex results. Let's define these segments:

One consistent error that many customers make in implementing BI solutions is that they do not follow the path of information required, creation, control, and flow. Pay attention to the users who will actually interact with the tool. If they are going to be required to pummel the data in order to create the desired outcome, the length of time to affect the business with BI will be longer than necessary. You should begin with the questions that need to be answered, not a theoretical view of the data that may be required to deliver results. Cater to the business users who will spend time with the tools; they will deliver creative new information.

One of my former employers used to constantly harp, “Activity does not equal success.” In other words, just because you are running around and pumping out lots of “stuff,” your contribution to the bottom line may be nil. You may have acquired a tool and are creating a myriad of new output items, but that does not mean that you are engaging in successful BI activities. What do I mean by follow the flow?

BI output is of little value if it is not shared and produced with a specific purpose. One must follow the flow of information requests and business requirements, not just create a flurry of output that may be of interest to some.

Let's assume that someone within the enterprise has responsibility for a new product or service that is to be rolled out in the next six months. We have a definite lack of information and inadequate analysis today for our current products and/or services. Do we fix the current information deficit? Do we let the new effort continue because we have had product successes before? Do we build a new application and reporting model for the new product or service and retrofit this to our existing data later? Do we do nothing? How about a vote?

The chance of stopping any major initiative because the underpinning data and analysis are inadequate is something like none. We are going to improve or attempt to improve our product line or services. It should, however, make us pause and ponder what might be done in the six months we have until the actual rollout. Providing timely information is the core of BI. But this information should have a purpose and a goal.

End users will be far more likely to ride out the rough parts of any BI solution's implementation if they perceive that a definite benefit will be obtained. I mentioned the flow of information creation and control. Regardless of the level of technical expertise that the user may have, information flow in a BI solution must reach the decision-makers, or the perceived benefits of doing BI will fall far short of their true potential.

Using our new product example, where would a BI solution be relevant to the end users?

Note that there is nothing about what features and functions of the data and reporting are required to deliver the information. There is also no specific list of who needs to see the information. It may be that one of our “consumers” is the key individual for the project. This person may receive four small, terse reports with figures and information that were gleaned from a variety of sources. This data may have taken two heavy analysts and lots of data manipulation in which the user was never remotely involved. The critical factor is that we delivered the correct information to the key decision-maker and let him do his job. That is what BI is all about for the end user.

If I hear a litany of techno-geek requirements coming from the mouth of an “end user,” I probably have on my hands an IT person in disguise. Users rarely, if ever, care how the work is done; they just want it done. The enterprise implementing BI must be concerned with delivery of information to levels where it can make a difference. Would you consider the ability to deliver 500 reports about all your product and territory failures a mark of achieving BI success?

These are the heat-seeking missiles for BI tools. These are the two to five-percent-ers who could probably figure out how to fly an F-14 if you gave them a manual and showed them where you put the keys. They are usually the ones to deliver the greatest amount of new and creative BI information. This is especially true if the analyses are complex. They are also those most commonly tapped along with IT to define the requirements for the tools in RFP/RFI situations.

“Ease of use” is a term that everyone tosses in to any equation related to BI. This term is not really applicable to an analyst unless you have an offering that requires assembler programming. No, the key issues with this group are “How broad a set of functions does it support, and how will I learn it?” They know well the inherent pratfalls of delivering analysis and are mostly concerned with the tool's ability to help them complete a solution.

This is also the group with a keen interest in major functional enhancements or significant new analysis capabilities for products; and they would do the first testing. Data mining products and algorithms are one example of new territory that they would not fear to tread. Their eyes are on the end results, and they are willing to take extremely difficult steps to produce them.

These are also the individuals whom many vendors step lightly around due to the depth of their inquiries about products, features, and functions. If this crowd shoots down the proposed solution, there is very little chance that it will come in via the back door or any other alternate path. They are often the gatekeepers for what constitutes the accepted BI tools. The dilemma is that they often cannot relate well to users who cannot use the chosen solution components.

One key to working with this group and making them an integral part of the entire BI effort is to harness their capabilities and incredible knowledge for an application to deliver the most significant analytics. As an example, a typical consultant's exercise is to walk through the data proposed and the new values expected and determine how the results will be produced. The typical analyst can perform this as well. A consultant needs to know whether data is intended to be delivered as is, whether it will be derived, what the calculation is, how often it will be recalculated and refreshed, and so on. If we put this in tabular form, it might look something like Table 4-1.

This is not an all-encompassing representation, but you get the idea. A technical user would try to figure out how to do all steps on his own with the tools provided. IT would have a massive and thorough flowchart to capture all the salient points in data transformation, including key metadata capture.

ETL (extraction, transformation, load) functions should be left in the hands of IT. Numerous tools can perform these functions. This is an area in which you can create synergy points among the IT staff, analysts, and end users because the many data-related steps must be articulated.

Rather than provide a tabular format, I find it to be a tremendously positive exercise to walk through a step-by-step exercise of data transformation and building with all associated players (as illustrated in Figure 4-2).

Figure 4-2. Talking about data and how information is derived.

I remember walking through a proposed OLAP solution with a large financial institution in early 2001. The session had been arranged after a series of conference calls with IT and some of the proposed end users.

What emerged very early in the chalk-talk was the fact that critical historical data had never been loaded and verified as accurate in the customer's emerging data warehouse. The usage scenario required the users to drill-back to underpinning detailed data; without it, the value of the solution was simply not adequate. As we went deeper into the proposed solution, we realized that the timing was wrong. It's not pleasant as a software vendor when you have to just walk away from a sale. But it was the proper thing to do. Eventually, we will deliver an OLAP solution with the proper business value and necessary functions to this customer. They, however, have lots of work to do, and it has not been completed as yet.

What typically happens in walk-through scenarios is that the interaction among the key players will create a common ground for discussing what values will be created, how they will be derived, and how they will be used. For the analysts, it quickly shows them where derivations versus statically created and stored values will occur. Both the analysts and end users are able to determine whether the values that they require will be available in the data itself or whether they have to be derived.

Despite the seemingly global “cratering” of the dot.com industry, the majority of us have to do business on the Internet. For those of us who utilize a vendor's online services, we will be picky and demanding and seldom forgiving.

For example, I am a guitarist by hobby, and I tend to buy lots of gadgets. Many of these items are available via a multitude of providers who sell via the Internet. I shop for the best price, but I also shop based on speed of delivery. That is, not delivery of the items, but delivery of the pages in my browser. I have visited several recommended sites where I couldn't get in, had difficulty navigating, or worst of all, couldn't check out! Here I was ready and willing to risk my entire financial security system by providing personal data on the Net, and the site had me trapped in the “What in the $$#@@# do I do next!” zone.

Your success or failure in delivering BI via an extranet architecture will be measured on your ability to deliver timely and accurate information in the format that the recipient wants. If there are changes required, you had best be able to deliver them and do so in a manner that does not impact the existing install base. It's amusing at times to see all the endorsements some companies put on their web sites. “Powered by Bob's Buggy Whips” or some such meaningful information. “Outsiders” don't care.

Today, many firms are allowing external customers to perform their own queries and produce their own reports. In such an environment, the data had best be accurate and easy to work with. External customers may have some very specific requirements, such as a quarterly sales report. You must incorporate the external users' requests as early in the data creation cycle as possible.

The vendors selling query and reporting tools today will have to provide a solid extranet strategy and set of deliverables. What should you look for? What is the most important set of parameters for evaluating such tools? First and foremost, the tool has to fit in with your data infrastructure. You cannot take chances with new database technologies that are required by a particular tool, even if it is the most attractive one that you are considering. You are looking at a 24x7 world, and you simply have to be up and available.

Next, the tool must be able to easily create the output and styles required without having to apply lots of IT resources to customize it. You do not have the time to constantly react to subtle changes. Unlike internal users who may have a legacy of reports and styles about which they can get a bit vociferous, external users will take what you give them as long as it clearly delivers meaningful information.

Security, security, security. There are tools that provide the ability to create a single report and “burst” it into multiple reports for different users and customers. Woe unto you should the security fail and you deliver to a customer information that she should not see. The emergence of extranet delivery for BI has placed a severe strain on vendors and their customers. The ultimate judge—the consumer—is not even remotely involved in the process, other than to like what he receives with no thought of how it got there. Consumers are extremely unforgiving and critical.

Unlike EDI (electronic data interchange) systems, much of the external customer interaction with BI will be random, ad hoc, and unpredictable. Just like any query into a database system, the result could be a small set of data or it could be a large one. If you have a set of external customers with known, predictable output requirements, it is in your best interest to examine “push” technologies with which you create the results at one time and deliver at another.

Because even your internal users are mostly the BI consumer type, why risk extensive ad-hoc efforts on external customers? It seems to me that the best approach is to assign a resource to be responsible for identifying what external customers most commonly need. If there are a few very critical and “picky” ones to which you have to cater, do this as a special project.

It is better to proactively push information to your external customers and survey them to see if it meets their needs. If some customers require ad hoc, you may be able to deliver an analysis-oriented dataset in the form of a spreadsheet or some other format and work with them on how it may be manipulated.

One additional consideration is the very strong need to ensure data integrity and accuracy upon input from external sources. Sometimes, it is impossible to correct information that has been entered in the past and that contains flaws or inaccuracies. If this data is going to be used in other BI related applications, get it accurate the first time.

I am still receiving two expensive catalogs from the same provider because I misspelled my last name on input while performing an Internet inquiry. I have notified the provider of the error, but they cannot seem to correct it. I get one catalog for Mike Biere and another for Mike Beire. These are costly items, and I can't help but wonder how many duplications and redundant shipments they make because there appears to be no way to correct this error!

It has been my experience that few, if any, BI ventures are made and executed at the enterprise level. Yes, I know I keep saying that. The prevalence of multiple tools and multiple databases in multiple departments and functional areas is indicative that there is no central control point. There is no “common good,” so we witness multiple acts of random analysis with little or no thought of central coordination.

You say, “Wait! We have a data warehouse in operation. Isn't that the heart of establishing an enterprise-wide view?” Perhaps, but I submit that if you have a core source of data, but you perform massive numbers of extractions and embellishments of information (we're not talking about data marts here), you have a disconnected analytics environment.

I have seen countless situations in which a client has different extraction jobs for the same data for different tools. In most cases, the extraction is stored in the tool's proprietary data format, so we are looking at nn copies of the same data. Different tools are being used to analyze the same records, and I would bet a reasonable amount of money that the same results would not emerge from two or more different tools performing similar analyses. Somewhere the “extra” steps of transform, cleanse, and adjust will have to be done, and the success will vary by tool.

If you have a value held in one area that is not to be available to another, you must have a driving business reason to do so. If you have multiple sources of the same information created and captured in different areas, you are asking for trouble. If you are not monitoring and working BI at an enterprise level, there will be islands of information.

However, there are also situations in which various groups will define and determine the same value differently. How does an enterprise handle this? An enterprise may decide to use a prefix to delineate the fact that derivations exist. It is not a quality business practice to allow the proliferation of the same value name, such as profit among varying groups, and say, “Oh well, that's the way we do things here to keep the users happy.”

Have you been involved in a situation where a vendor's tool is so well entrenched and used to deliver results outside the scope of IT that there is no way to substitute it if there were a business need to do so? Several mainframe software vendors have been under pressure to do something with their prices. There has been a trend to charge more for some of these host-based tools, and the customer's view is that it is being held hostage. It is difficult to displace a tool in which you might have thousands of analytic “objects” (queries, reports, procedures, etc.) used all over the place. You don't know exactly what many of them do (nor do the users), but you are afraid to delete them.

IT staff members will sometimes state, “We want users to have the freedom to secure their own solutions and tools. We in IT have been a bit heavy-handed in the past, so we're not going to engage in contention and arguments as we used to.” This is not a high-school popularity contest; this is an area where an end-to-end architecture can change your business. It is an area in which you may outstrip the competition and possibly weather a difficult business climate that others do not survive.

The end users will rail, “We are tired of waiting for the data! We're always an afterthought, and we can't wait anymore. We know the data is there, some power tools are out there, and we are going to control our own destiny!” In every BI scenario, there will be IT involvement, and few users will be able to solve their word problems…end of story.

At the enterprise level, there isn't time for this. We have to look at how we structure our core information and how it is used across the business units. We need to know where enhancements and embellishments are required and how to handle them. Our goal should be a single version of the truth for analysis. The problem of providing a central BI competency is the same for any enterprise, regardless of scope or industry type. There is no central plan. No one “owns” BI at the corporate level.

The most common level dealing with these issues is the Manager of Data Warehousing. Such managers have a series of tasks that seem impossible due to the sheer volume of data and sources to pull together. There are additional pressures applied due to some high-powered vendor solutions (e.g., CRM or ERP) that have been purchased and must be rolled into existing systems or vice versa.

What's the impact of BI at the enterprise level? Probably nil, you might say. Can this change? Yes. What do we do to affect this? Read on.

Let's spend some time on segmentation of end users by type and build a pyramid of both users and the tools that they may require. Remember that we need to clearly delineate between what a tool is designed to deliver and what skills and time investment the user is willing and capable of providing.