This chapter from Delivering Business Intelligence with Microsoft SQL Server 2008 discusses how business intelligence (BI) can help companies evaluate previously-made decisions and answer questions through layout-led and data-led discovery and data mining. This chapter also explains how different levels of management and employees should utilize BI to reach their daily, short-term and long-term goals and provides definitions of data mining, key performance indicators (KPI) and latency.
Delivering Business Intelligence with Microsoft SQL Server 2008
Chapter 2: Making the Most of What You've Got—Using Business Intelligence
Out of clutter find simplicity.
From discord find harmony.
—Albert Einstein's First Two Rules of Work
In the previous chapter, we discussed the importance of effective decision making to the success of any organization. We also learned that effective decision making depends on specific goals, concrete measures to evaluate our progress toward those goals, and foundation and feedback information based on those measures. The latter two items, concrete measures and foundation/feedback information, we referred to as business intelligence.
In this chapter, we take a look at the types of questions this business intelligence can help us answer. We also discuss the types of business intelligence that are needed at various levels of an organization. The chapter ends by talking about Maximum Miniatures, Incorporated, the company we are going to use for our examples throughout the book.
In Chapter 1, we saw how business intelligence is used to support effective decision making. It provides foundational information on which to base a decision. Business intelligence also provides us with feedback information that can be used to evaluate a decision. It can provide that foundational and feedback information in a number of different ways.
When We Know What We Are Looking For
In some cases, we know what information we are looking for. We have a set of particular questions we want answered. What is the dollar amount of the sales or services our organization is providing in each region? Who are our top salespeople? In some of these situations, we not only know what we are looking for, but we also have a good idea where to find the information when we design the business intelligence solution.
When we know the question we want answered and have a good idea where that answer is going to be found, we can use printed reports to deliver our business intelligence. This is the most common form of business intelligence and one we are all familiar with. For many situations, this format works well.
For example, if we want to know the dollar amount of the sales or services provided in each region, we know where to find this information. We can design a report to retrieve the information, and the report will consistently deliver what we need. The report serves as an effective business intelligence tool.
This is an example of layout-led discovery. With layout-led discovery, we can only learn information that the report designer thought to put in the report layout when it was first designed. If the information wasn't included at design time, we have no way to access it at the time the report is read.
Suppose our report shows the dollar amount for a given region to be unusually low. If the report designer did not include the supporting detail for that region, we have no way to drill into the region and determine the cause of the anomaly. Perhaps a top salesperson moved to another region. Maybe we have lost a key client. The report won't give us that information. We quickly come to a dead end.
In some cases, we know the question, but we don't know exactly where to look for our answer. This often occurs when the information we initially receive changes the question slightly. As in the example from the previous section, an anomaly in the information may cause us to want to look at the data in a slightly different way. The unusually low dollar amount for sales or services provided in a specific region led us to want detailed numbers within that region.
In other cases, we know where to look, but it is not practical to search through all of the detailed information. Instead, we want to start at an upper level, find a number that looks interesting, and then drill to more detail. We want to follow the data that catches our attention to see where it leads.
This is data-led discovery: The information we find determines where we want to go next. The developer of this type of solution cannot know everywhere the report user may want to go. Instead, the developer must provide an interactive environment that enables the user to navigate at will.
To implement data-led discovery, we need some type of drilldown mechanism. When we see something that looks interesting, we need to be able to click that item and access the next level of detail. This is, of course, not going to happen on a sheet of paper. Data-led discovery must be done online.
Discovering New Questions and Their Answers
In some cases, our data may hold answers to questions we have not even thought to ask. The data may contain trends, correlations, and dependencies at a level of detail that would be impossible for a human being to notice using either layout-led or data-led discovery. These relationships can be discovered by the computer using data mining techniques.
Where layout-led discovery and data-led discovery usually start with summarized data, data mining works at the lowest level of detail. Highly sophisticated mathematical algorithms are applied to the data to find correlations between characteristics and events. Data mining can uncover such nuggets as the fact that a customer who purchased a certain product is more likely to buy a different product from your organization (we hope a product with a high profit margin). Or, a client receiving a particular service is also likely to need another service from your organization in the next three months.
This type of information can be extremely helpful when planning marketing campaigns, setting up cross-product promotions, or doing capacity planning for the future. It can also aid in determining where additional resources and effort would produce the most effective result.
In Chapter 1, we discussed the fact that business intelligence should be utilized at all levels of an organization to promote effective decision making. While it is true that business intelligence is useful throughout the organization, the same type of information is not needed at each level. Different levels within the organization require different types of business intelligence for effective decision making.
As we look at what is required at each level, keep in mind the Effective Decisions Triangle from Figure 1-1. We will transform that triangle into a pyramid as we examine the specific goals, concrete measures, and the timing of the foundation and feedback information required at each level. (See Figure 2-1, Figure 2-2, and Figure 2-3.)
The Top of the Pyramid
Decision makers at the upper levels of our organizations must look at the big picture. They are charged with setting long-term goals for the organization. Decision makers need to have a broad overview of their area of responsibility and not get caught up in the minutiae.
Figure 2-1: Specific goals at each level of the organization
Highly Summarized Measures
The business intelligence utilized at this level needs to match these characteristics. The measures delivered to these decision makers must be highly summarized. In many cases, each measure is represented, not by a number, but by a status indicator showing whether the measure is in an acceptable range, is starting to lag, or is in an unacceptable range. These highly summarized measures are known as Key Performance Indicators.
Figure 2-2: Concrete measures at each level of the organization
Figure 2-3: Timing of the foundation and feedback information at each level of the organization
KPIs are used to provide these high-level decision makers with a quick way to determine the health of the essential aspects of the organization. KPIs are often presented as a graphical icon, such as a traffic light or a gauge, designed to convey the indicator's status at a glance. We discuss KPIs in greater detail in Chapter 10 of this book.
Because these upper-level decision makers are dealing in long-term policies and direction, they do not need up-to-the-minute business intelligence. Another way to state this is to say they can have more latency in their business intelligence. These decision makers need to see downward trends in time to make corrections. They do not need to see the daily blips in the organization's operation.
Mid-level decision makers are managing the operation of departments and other working units within the organization. They are setting short-term goals and doing the planning for the functioning of these areas. Mid-level decision makers are still at a level where they should not be in the details of day-to-day processes.
Summarized Measures with Drilldown
These mid-level decision makers need business intelligence that is still summarized, but they often need to drill down into this information to get at more detail. Therefore, these decision makers can utilize printed reports, along with interactive systems, allowing data-led discovery. These decision makers can also make use of information from data mining.
Some Latency Acceptable
Because these decision makers are closer to the everyday functions, they may require business intelligence with less latency. In some cases, they may need to see measures that are updated daily. In other cases, these decision makers are looking for trends discernable from weekly or monthly loads.
The Broad Base
At the broad base of our business intelligence pyramid are the forepersons, managers, and group leaders taking care of daily operations. These people are setting daily operational goals and making decisions on resource allocation for the next week, the next day, or perhaps the next shift. They are planning the next sales campaign or maybe just the next sales call. These decision makers usually need business intelligence systems with high availability and high responsiveness.
Measures at the Detail Level
These decision makers are dealing with the details of the organization's operations. They need to be able to access information at the detail level. In some cases, the work groups these decision makers are responsible for are small enough that they can see the detail for the work group directly without being overwhelmed. In other cases, measures need to be summarized, but drilldown to the detail level will probably be required. These decision makers may utilize some forms of data mining to help discern trends and correlations in daily information.
Because these low-level decision makers are managing day-to-day operations, they need to react quickly to changes in feedback information. For this reason, they can tolerate little latency. In some cases, these decision makers require data that is no more than one day old, one hour old, or even less.
Throughout the remainder of this book, Maximum Miniatures, Incorporated serves as the basis for all of our examples. Maximum Miniatures, or Max Min, Inc., as it is referred to by most employees, manufactures and sells small, hand-painted figurines. It has several product lines, including the Woodland Creatures collection of North American animals; the Mythic World collection, which includes dragons, trolls, and elves; the Warriors of Yore collection, containing various soldiers from Roman times up through World War II; and the Guiding Lights collection, featuring replica lighthouses from the United States. The miniatures are made from clay, pewter, or aluminum.
Max Min markets these miniatures through three different channels. It operates five of its own "Maximum Miniature World" stores dedicated to selling the Max Min product line. Max Min also operates MaxMin.com to sell its products online. In addition, Max Min sells wholesale to other retailers.
Max Min, Inc. has experienced rapid growth in the past three years, with orders increasing by over 300%. This growth has put a strain on Max Min's only current source of business intelligence, the printed report. Reports that worked well to support decision making just a few years ago now take an hour or more to print and even longer to digest. These reports work at the detail level with little summarization. Max Min's current systems provide few, if any, alternatives to the printed reports for viewing business intelligence.
In addition, Max Min, Inc. is facing tough competition in a number of its product areas. This competition requires Max Min to practice effective decision making to keep its competitive edge. Unfortunately, Max Min's current business intelligence infrastructure, or lack thereof, is making this extremely difficult.
Because of these issues, Max Min has launched a new project to create a true business intelligence environment to support its decision making. This project includes the design of a data warehouse structure, the population of that data warehouse from its current systems, and the creation of analysis applications to serve decision makers at all levels of the organization.
The new business intelligence platform is based on SQL Server 2008. After an extensive evaluation, it was decided that the SQL Server 2008 platform would provide the highest level of business intelligence capability for the money spent. SQL Server 2008 was also chosen because it features the tools necessary to implement the data warehouse in a relatively short amount of time.
We will examine each step of Max Min's implementation project as we learn about the various business intelligence tools available in SQL Server 2008. Before we begin, let's take a quick look at Max Min's current systems.
Max Min has five data processing systems that are expected to serve as sources of business intelligence (see Figure 2-4).
Figure 2-4: Sources of business intelligence at Max Min, Inc.
The manufacturing automation system tracks the materials used to make each product. It also stores which products are manufactured on which production lines. Finally, this system tracks the number of items manufactured during each shift.
The manufacturing automation system uses a proprietary data-storage format. Data can be exported from the manufacturing automation system to a comma-delimited text file. This text file serves as the source for loading the manufacturing data into the business intelligence systems.
The order processing system manages the inventory amounts for all products. It tracks wholesale orders placed by non–Max Min retailers. The system also records product amounts sold through the Max Min retail stores and the Max Min online store to maintain inventory amounts.
The order processing system tracks order fulfillment, including product shipping. It also generates invoices and handles the payment of those invoices. In addition, this system records any products returned from the retailer.
The order processing system uses a Microsoft SQL Server database as its backend.
Point of Sale
The point of sale (POS) system manages the cash registers at each of the five Max Min–owned retail stores. This system also tracks the inventory at each retail store using Universal Product Code (UPC) barcode stickers placed on each item. The POS system handles both cash and credit card transactions. It also tracks information on any products returned by the customer.
Information from each of the five POS systems is exported to an XML file. This XML file is transferred nightly, using File Transfer Protocol (FTP), to a central location. These XML files serve as the source for loading the POS data into the business intelligence systems.
The MaxMin.com online store is an ASP.NET application. It uses SQL Server as its backend database. All sales through the online store are paid with a credit card. All customers of the online store must provide name, address, phone number, and e-mail address with each purchase.
The online store tracks the shipping of orders. It also handles any products returned by customers. Finally, the online store saves information on product promotions and discounts that are run on the store site.
The accounting system tracks all the financial transactions for Max Min, Inc. This includes the purchase of raw materials for manufacturing. The accounting system uses a SQL Server database for its backend.
Building the Foundation
In Chapter 3, you will learn more about the foundations of our business intelligence systems. We explore possible sources for our business intelligence data. We also look at what the structure of those data sources might look like.
- Intrigued by this chapter excerpt? Download a free PDF of this chapter: Chapter 2: Making the Most of What You've Got—Using Business Intelligence
- Read more excerpts and download more sample chapters from our Data Management bookshelf
- To purchase the book or similar titles, visit McGraw-Hill Professional