SQL Server Reference Guide

In this series on Business Intelligence I've explained a great deal about the concepts you need to keep in mind as you plan your landscape. Most of the information I've explained deals with the back-end of the system, from the sources of the data to its storage, transformation and aggregation.

But the point of a Business Intelligence system is to provide the analysis to the users. At some point you've got to present the data to the audience that requested it. For the most part, this is new territory to Database Administrators. They don't get involved in formatting and presenting data to users. It might not be all that new to Database Developers, since they do this sort of thing all the time, but then again some shops have a dedicated reporting staff that takes care of this end of the application, so the developers aren't involved with presentation either. As a Database Architect, however, the presentation layer of your BI Landscape is just as important as the rest of the system. It requires the same level of attention that transformations and aggregations demand.

Once again, it's key to involve the users as early as possible when you plan this layer of the architecture. The difficulty is that you're often dealing with several types of users in a BI audience. Not every tool is useful or even safe in the hands of every user.

A stratification of the users has been developed called the "clicks" paradigm that I've found useful over the years. It helps to divide the users into the ways that they need to see the data and what data they need to see. By combining that information you can even determine what tools each type of user needs.

The Clicks Paradigm

The "clicks" paradigm divides users into layers based on how many clicks of an input device it takes to access the data he or she needs. It progresses from a simple requirement of static data (0-clicks) where the analysis is largely completed for the user, to a full suite of programs that allow the user direct access to certain parts of the data store (3-clicks). The distribution of users forms a triangle, with most of them at the bottom in the 0-click area and only a few at the top in the 3-click tools. Let's take a look at each.

Zero Click - Data Finds You

The first level of reporting need from the Business Intelligence landscape is 0-click. At this level, the user doesn't interact directly with the Business Intelligence landscape at all. The BI team creates static reports based on user groups that need to see information on a periodic basis from the BI system. These reports are either mailed out automatically or sent to a wireless device, or even posted in paper form in a central location.

These users aren't interested in manipulating the data sets from the BI system; just in a direct interpretation of the results. We use this kind of information every day in the form of a portfolio report to see how our stocks are doing. In this case we're not doing in-depth analysis on the broader market; we're just interested in knowing how our group of stocks, bonds and funds are doing in relation to the market.

This level also includes Key Performance Indicators (KPI), the markers that show how a particular set of variables are moving. KPI normally shows a color or shape that indicates how things stand, either daily, weekly or monthly.

Several technologies exist within SQL Server 2000, 2005 and other BI systems that can produce 0-click information. It can be as simple as a script that runs a Multi-Dimensional Expression (MDX) query against the system and sends the results to a predefined mailing list for that query. Using Reporting Services, a free add-on to SQL Server 2000 and built in to SQL Server 2005, you have a complete set of wizards that will build the report and mail it out in formats ranging from XML to PDF to any group of users that you wish.

Unless the users have a proprietary set of software in the way of this process, there is normally no additional license charge to implement 0-click information.

One Click - Static Reports

The next level in the paradigm is 1-click information. Normally this is found in a somewhat smaller subset of your user community, and involves a static report that they can find either on a web page or in a file repository. They have to find and select the report, hence the 1-click name.

1-click reports don't change. You will probably create several of these for different user groups, but they are static and don't allow any customization. Once again unless you've implemented a proprietary reporting solution you shouldn't face any licensing additions at this level.

The difference between a static report mailed to users on a periodic basis (0-click) and a static report that the users open (1-click) is normally just length and complexity. In a 0-click report the information is usually short and to the point. In a 1-click static report the information might contain less graphics, more numbers, and a broader audience per report.

Two Clicks - Portals and Customizable Reports

The next level of users is once again somewhat smaller than the previous one. It usually contains some of the higher-level managers. In a 2-click environment the user is once again presented with a report of data from the BI system, but they have the ability to change the information on the report in some way. They may be able to select a date range, choose a different department or region, or even perform more advanced selections, adding and removing sections of the report.

Since this type of interaction requires a tool, depending on how you implement you may have an additional licensing cost starting at this level. If you build an interactive website such as a portal that pulls data from the BI system then you can avoid this cost, but if you connect users to a reporting system you will face the costs that system requires. It basically comes down to how much work you're willing to do to provide the information. The more you do, the less expensive the license cost is. The more you rely on canned applications to provide the functionality, the more expense you'll have.

It's also at this level that you need to consider the performance impact on the BI System and network. Although 0-click and 2-click information can be bulky, it is scheduled and pre-aggregated. You know when it will happen, and you can design the proper indexes and pre-aggregations to make it faster. In the case of a 2-click report, you're not always sure which way the users will go with the information. In simple selections (such as a carefully restricted date-range query) you can mitigate this impact, but the more flexibility the report has the higher the potential impact. You have to plan that all users may run the most complex query at the same time. While this might seem remote to you, I've seen this kind of thing bury an otherwise healthy BI landscape.

There are various tools you can use in this area, from Crystal Reports to Microsoft's Reporting Services. Reporting Services has the ability to deliver an ActiveX object through the user's browser to give them a reporting environment that allows them to customize a report published for them.

Three Clicks - Full Palette Tools

At the very top of the clicks paradigm are those users with almost full access to at least parts of the Business Intelligence landscape. They are given a development environment with a "blank slate" that has tools allowing them to access data sets to build their own queries. These tools also have formatting and graphical capabilities, allowing the users to explore the data in real-time. Although 3-click tools can create reports, they are often used interactively.

The audience for this level is severely limited for a few reasons. The first is that everyone in the organization simply doesn't need this level of access. The possibilities to explore this much data is reserved for those in strategic decision-making positions in the organization. Another reason is that the data security level in 3-click information is high. This kind of data shows the output of various plants, the sales numbers, regional distributions and so forth.

Another reason that there are fewer 3-click users is the amount of training required for the tools. Not only does the user have to be trained in the meaning of the data within the organization, but they also have to understand statistical methods and how the tool works. That normally takes days or weeks to introduce, and months or years to master.

Another consideration is cost. You will have to license a tool such as Cognos, SAP's BW, or even a client license for SQL Server to allow a user to directly interact with the BI Landscape. The final reason is of course performance. Since you're never sure what data the users in a 3-click tool want to look at, you're trusting that they don't pull back so much data that the system becomes unresponsive. That become part of the additional training in the tool that these users need.

The tools in the 3-click area include the aforementioned packages like Cognos and SAP's BW, as well as the interface for BI in SQL Server 2000. SQL Server 2005 provides an entire design and exploration environment in its Business Information Development Studio, based on Visual Studio. I'll cover those tools in future tutorials.

Presentation Considerations

Some references add a 4-click set of users, which are the ones that create the system itself. Since that's you and me, I leave that level out. This entire site is dedicated to your training and information.

As you design your presentation layer, make sure you place your users into categories like the ones I've described here. There are other ways you can divide the user community up into segments, but whichever you pick make sure you understand exactly what the group of users need, and how they need to manipulate it. I'm a big proponent of creating lots of 2-click reports for the majority of my users. I've found that by being responsive to their requests you can bypass the "need" for a 3-click tool with its expense, risks and additional training.

Informit Articles and Sample Chapters

You might have had your interested piqued about Microsoft's Reporting Services while reading this tutorial. I've written a short overview on the product and plan to return to it in more depth, but you can read more now in this free chapter.

Online Resources

I've found a useful resource for all things BI at this location. It's not specific to SQL Server, but it does have some good information.