Home > Articles > Data > SQL Server

SQL Server Reference Guide

Hosted by

BI Explained

Last updated Mar 28, 2003.

When most technical professionals think of Business Intelligence, they think of OLAP, or On-Line Analytical Processing. While that technology is often a component of Business Intelligence, it's only that – a component.

True business intelligence attempts to take the data available to the organization, compile it into information, and then use that information as knowledge. Each of these steps involves culling the data, combining and transforming it into some meaningful form, and then strategically applying that information to the decisions the organizations face.

Another way of looking at Business Intelligence is to make the distinction between goals, strategies and tactics.

A goal is a target that you want to hit. For instance, your goal might be to make more income. Put more correctly, you should define precisely what that means. "More income" is far too broad. You should put a number on what that means, and a date that you want to reach it.

A strategy is the long-term plan for how you are going to meet the goal. One strategy for making more money might be to get a higher-paying job; another might be to get an additional job. It's interesting that a true strategy might also be turned into a goal itself.

Tactics, then, are the steps you take to satisfy a strategy. Regularly reading this guide on databases is a tactic to help you learn more about SQL Server, thereby enabling you to get a better job, or even and additional one. Tactics are discernable because they often don't serve a particular function within themselves. While reading this guide is a very commendable step, if you're a seamstress and have no interest in technology whatsoever, it doesn't serve you well to spend your time learning SQL.

Business Intelligence is like that. Once the business clearly defines a goal, the proper knowledge can help them put the right strategies into place to form a plan of action to get there. Business Intelligence provides the data manipulation to get that plan started.

Any organization can use Business Intelligence. While the name tends to indicate more of a profit-motive, science endeavors, educational institutions, even not-for-profit systems have goals, strategies and tactics. The Business Intelligence processes and procedures I'll cover here will help any organization type to reach its goals.

And that is usually the crux of the matter. I've been a part of several BI initiatives, and the ones that have had the greatest difficulty by far are the ones that have the most nebulous goals. Many companies want BI systems because they think they need them, or worse, because some competitor has one. They think that BI systems will help them in some generic way. The point is that you must have a clear goal defined before you implement a BI system. It's not that the effort involved is too great on the part of IT to make it worthwhile, it's that the IT staff cannot create the need for the BI system.

Let me clarify that a bit. The process for creating an effective BI system begins far before you start the BI project. First, the organization needs to have a coherent data strategy.

A data strategy answers questions such as "what data do we want to gather?", "Why?", "Where and how do we store it?" and "How long do we keep it?", in addition to "How do we secure it?" If you don't have these questions answered first, stop and fix those issues. You can't make a defendable decision if the base data is incoherent, incomplete, or unavailable.

Next, get your reporting strategy in order. This step builds on the previous one. This strategy answers the questions of "what do we report on?", "How often do those reports run?", "Are we duplicating reports?", "What is the meaning of each field on the reports?" and so forth. I've seen vendors get the same report data from a single supplier with different numbers on them, simply because the reports were developed by two different groups at the supplier. Which to believe?

With reliable data and reports, now you can begin to plan your Business Intelligence layout.

Business Intelligence is a series of processes and technologies to help you get at the answers you need found in the data you have. To begin arranging your strategy, call a series of meetings with the functional area managers in both operations and management. You'll need input from all regions you operate in because your first big challenge is to decide what the data in each region means. What you're after here is to define the levels for each data set.

The levels specify the range for the data. Let's take a concrete example. Assuming that you're a manufacturing organization, you probably have plants in several locations. Depending on how they are managed (central or franchise models) you'll have different data needs at the plant than the region and at headquarters. For instance, the plant manager wants to know machine utilization and line speed, while the region management might want to know about inventory turns and quality information. Headquarters, in turn, might want to know about vendor performance by region and plant. All of these "buckets" of data will have to be worked out by the business, since the technology group won't know what the business is looking for.

Once you've defined the levels the data live in, you can tell which data elements will need to be rolled up. If the data is staying local, analytical reports might satisfy the needs the organization has with simple aggregations and analysis.

If you need the data regionalized, you'll have to consider language constraints and meaning differences. What happens when you need to combine Japanese and Mandarin Chinese data when the Japanese plants consider "excess" inventory as what's paid for but still on the shelf, and the Chinese plants consider excess as items that are on the shelf and not slated for an assembly, paid for or not? Again, the business staff will need to tell you how to get a regional answer to "what is our excess position?"

With all of that sorted out, now you can begin to consider the three basic technologies that will help the business get the numbers. The first is analytical reports, the second is Online Analytical Processing, and the third is Data Mining. There are lots of ways to implement these, but they comprise the current categories of analyzing data.

In the next series of articles, I'll explain when you use each of these methods, and how you can use SQL Server to handle them.

Online Resources

Some of the best resources for Business Intelligence is on various vendors' sites, but make sure you investigate several. They will always slant the whitepapers and information based on the capabilities of their software. Cherry Tree has a good overall description here.

Informit Articles and Sample Chapters

In the bookstore you'll find an excellent reference book by Jill Dyché called e-Data: Turning Data Into Information With Data Warehousing.