The following is an excerpt from Business Intelligence with Microsoft PerformancePoint Server 2007, by Craig Utley. It is reprinted here with permission from McGraw-Hill; Copyright 2008. Read the book excerpt below to learn business intelligence (BI) basics -- such as who consumes BI and how to deliver BI -- or download a free .pdf of the chapter to read later: "Business Intelligence Basics with Microsoft PerformancePoint Server 2007."
Business intelligence is a concept that can cover many technologies. A business intelligence solution usually covers a wide range of processes, software, and techniques, from retrieving the data from source systems to delivering business value to end users. In order to help deliver end-to-end business intelligence, Microsoft has created a suite of products that covers the entire gamut, of which PerformancePoint Server 2007 is an important part. This chapter, however, will focus on business intelligence in general, including the users of business intelligence and the kinds of tasks they'll perform in their daily jobs. In this chapter, I'll first describe business intelligence from a high level, and then define what it is and why it's important. Next I'll describe the various consumers of business intelligence, and as you'll see, it can encompass virtually everyone in an organization. Finally, I'll conclude the chapter with a variety of ways to deliver data to the various classes of users.
What is business intelligence?
Business intelligence (BI) is more of a concept than a single technology. The goal is to gain insight into the business by bringing together data, formatting it in a way that enables better analysis, and then providing tools that give users power—not just to examine and explore the data, but to quickly understand it. Many in the information technology field are familiar with the saying, "Data is not information." This phrase underscores the idea that data points by themselves fail to impart much useful information and that data must be put into context to be meaningful. A list of sales numbers is not helpful unless it includes the products sold, when they were sold, where they were sold, and so on. It is important to include context when looking at data in order to turn it into information.
While obtaining information is important, information is only useful if it is easy to grasp so that people can use it to make decisions. There is much information in books on nuclear physics or Cycladic statuary and burial rites, but without the proper context and training such information can be hard to comprehend. It is therefore the goal to make data easy to comprehend; a quick grasp of the trends, relationships, and relative strengths and weaknesses is essential to delivering a usable system that truly delivers business value.
Building a system that allows users to easily grasp what is presented and turn it into easily comprehended, actionable business information requires a number of steps. First, the business problems to be solved must be identified. Then, the data must be located in the various source systems and consolidated in such a way that it is consistent and accessible. This is the process of building a data warehouse or data mart, and is covered in detail in Chapter 3. This is often a challenging process, with many companies seeming to make the mistake of believing that this is the beginning and end of their BI project. I have personally been into numerous companies that have built warehouses and then provided completely inadequate tools for letting people actually use the information contained in the warehouse. This is like buying a car and then locking it in a garage so it can never be driven; the potential value will never be realized.
After building the warehouse, there must be a mechanism to retrieve the data and present it to business users so that they can understand it and act upon what they see. This is where PerformancePoint Server and ProClarity come in, because they are tools built to provide businesses with the ability to monitor and analyze the data. Much of the ability to easily grasp data is because of visualization capabilities available in the products, such as charts, graphs, scorecards, decomposition trees, performance maps, and so on. The data in warehouses can also be used in traditional reports, which, when viewed online, may include interactivity for performing data analysis.
PerformancePoint Server adds another piece to the mix: modeling. Modeling can encompass planning, budgeting, and forecasting. While these three items can certainly be done without a warehouse in place, the warehouse provides two major benefits: first, historical data is easily accessible and can be examined for trends and past results; second, the budgets and forecast can be put back into the warehouse and actual results can be tracked against the budget or forecast in the warehouse as time moves on.
There are numerous pieces to a business intelligence solution. The term business intelligence, or BI, is used in this book to indicate the entire process. The entire process of business intelligence can be broken into the following steps:
- Identifying the business problem(s) to be addressed by the warehouse and the data needed to address those problems.
- Identifying the location for all necessary data and extracting it from those sources.
- Transforming the data from various sources into consolidated, consistent data.
- Loading the transformed data into a centralized location.
- Building a data warehouse (or data mart) with the data from the centralized location. The structure being built is called a cube.
- Putting in place commercial products or custom applications that give access to the data in the cubes. There are many different ways of working with cube data, and different approaches make sense for different roles within an organization.
Step 1 requires you to identify the business problems to be solved and is beyond the scope of this book except for casual mention. Steps 2-5 are discussed in some detail in Chapter 3, which addresses the overall process of building a data warehouse (or data mart or cube; the terms are clearly defined there.) Step 6 is what is covered in the rest of the book, including the PerformancePoint Server product and its capabilities. Figure 1-1 shows these various steps in an overall business intelligence process. It should now be obvious that business intelligence covers more than just building a data warehouse; indeed, it includes one or more ways to access and analyze data that deliver value across the business.
While the process of building a data warehouse is critical to the success of the project, it is a subject best handled by books dedicated to the tools used for data warehouse creation: SQL Server Integration Services (SSIS) and SQL Server Analysis Services (SSAS). Books such as Hands-On SQL Server 2005 Integration Services and Delivering Business Intelligence with Microsoft SQL Server 2005 cover these topics in great detail. This book, on the other hand, focuses on the tools that allow companies to use the warehouse and to achieve the benefits of being able to monitor the health of the organization, perform complex analysis of their data, and plan for the future using the strength of the warehouse. In order to better understand these pieces, however, it is important to understand the various roles played by the users of the system.
Figure 1-1 The business intelligence process from end to end
The consumers of business intelligence
People perform vastly different roles within organizations and therefore have varying needs for how and why they consume information. It is important to understand these roles and the business reach and impact they have. After identifying four roles of an organization, I will delve into the ways these roles can interact with data and why there is no one approach that satisfies all business needs.
The four roles that will be examined include
- Business Decision-Makers
- Knowledge Workers
- Line Workers
Obviously, roles are not always so cut and dry, and one person may well have responsibilities in more than one role. However, it is important to understand the business focus of each role and their corresponding need for information.
The role of business decision-maker encompasses several titles in an organization: executives, directors, and managers. These various jobs are different in important ways, but all have one thing in common: the need for timely information is critical so that these individuals can make decisions that drive the business.
Executives are those at the top of an organization. Their view is often quite wide; they must understand all facets of the business and how they relate. A CEO is concerned with revenue and expenses, but also with staffing, manufacturing, customer satisfaction, supply chains, and so forth. Executives rarely have much time to dig into the numbers so they need very broad, high-level measures of the overall health of a business. Vice presidents and directors of certain business functions may get more detailed information about their area, but they still take a broad view and need high-level information. Managers often manage at a much more granular level, but are still making business decisions that can impact the profit and loss of their specific areas. When the information upon which a business decision-maker relies can have an impact on the profitability of a functional unit, it is critical that the information be timely and accurate. The presentation of the data must make it very easy to grasp quickly and provide unambiguous information about the health of that information.
The actual data needed by business decision makers varies based on their functional role: A vice president of finance will look at different metrics than the vice president of human resources, for example. Chapter 4 will delve more into different business areas and what some of the key metrics are for various departments within a company.
Knowledge workers make up the bulk of non-management office staff. Software developers, marketing personnel, human resources staff, financial accountants, and other such workers are professional staff that have the need for information, but often do not have the ability to make decisions that have a large business impact. A marketing person, for example, may be tasked with creating marketing materials to sell a new product or service. This person may do research by examining the market place, look at competing products, conduct focus groups, study internal quality control procedures, and more, but the end result is materials that must be approved by management—the money for creation is allocated by someone other than that marketing person.
Such knowledge workers have a real need for data, but often the data needed is at a lower level of detail than that needed by business decision-makers. In addition, the data needed may be completely different for each request, while business decision-makers tend to look at the same set of numbers on a regular basis.
Knowledge workers may need to do some data exploration, but this is often not at a particularly deep level. For example, a sales representative might well need to know the previous sales to a particular client, returns by that particular client, and the projected sales to that client over the next several months. The drivers behind the projection are probably known to the sales representative and might include items such as new products in the pipeline or promotions that are in the works, but the statistical models used to forecast the projected product mix or future sales for that particular client are often of little concern. Such details are often left in the hands of analysts.
Analysts are a special breed of knowledge worker who often work directly with business decision makers. Analysts, whether they're business analysts, financial analysts, or some other kind of analyst, often spend time performing very detailed analysis of data. They may delve deeply into vast quantities of information looking for root causes to problems or trying to uncover trends in the data that are not already obvious.
Many analysts employ advanced statistical techniques in order to analyze the data. These models may be used for forecasting purposes or to examine the numbers in relation to norms and standards throughout the industry. One is example is when a business manages pensions or other retirement funds for its employees. The performance of these funds can easily be compared to a number of standard financial indexes, and future performance can be forecasted and compared against future payout forecasts.
Many manufacturing and transportation businesses also employ models that help predict costs based on the price of oil. Some of these models might also have to include currency fluctuations if some of the raw materials are purchased from foreign businesses. These complex statistical models require flexible software that gives analysts nearly unrestricted access to the data. Some of what these analysts do is considered data mining, a technology which uses statistical models to examine data for relationships or make predictions based on existing data.
Line workers are those workers on the front lines and may include assembly line workers in a manufacturing company and cashiers in a retail establishment. Many such workers benefit from business intelligence although they may not realize it. Data derived from a business intelligence process may be integrated into line of business applications that allow line workers to better do their jobs. Cashiers in a retail establishment might be able to recommend complimentary or replacement items based on customer preferences. Call center workers can see the order and return history of customers calling in with questions. Assembly line workers can obtain real-time statistics on the number of units produced, quality control, and inventory levels. Such workers may not know the term "business intelligence" or have any idea that the data they are viewing comes from a data warehouse. Nonetheless, they are still consumers of data and therefore part of the business intelligence process.
Delivering data to business intelligence consumers
The previous section described four types of consumers of business intelligence information, ranging from executives to sales clerks and assembly line workers. This shows that the end result of a business intelligence project can provide benefits to nearly every facet of an organization. More importantly, a data warehouse can be designed so that a single warehouse can serve the needs of all the consumers, greatly simplifying the creation and maintenance of the warehouse.
While a single warehouse may be able to handle the data needs for many or all consumers, the same is not true of the different methods used to display and navigate that information. In other words, there are many different techniques that can be used to present data to consumers, and thus there are different tools available to provide this data display. It would be great to be able to say that PerformancePoint Server can handle every situation. In fact, PerformancePoint Server and ProClarity combine to provide a wealth of different display methods, although custom applications are sometimes the best way to get information to consumers. This section focuses on different ways to present the data and the ways that users benefit from those different presentations.
Business scorecards, sometimes called just scorecards, are a means of quickly showing someone the overall health of the business or a particular division. Scorecards typically show Key Performance Indicators, or KPIs, which are the primary numbers upon which the organization or person is focused. For example, the vice president of manufacturing might be interested in such items as
- The number of units produced
- The number of items failing quality control
- The amount of scrap metal generated
- Current inventory levels
- Current raw materials inventory
- The current price of steel
These items are the KPIs that might appear on a scorecard for the vice president of manufacturing. Each KPI is typically displayed as a symbol indicating the health of that particular number. For example, the number of items failing quality control might be 50. Is this good or bad? It depends on many factors. If the company is making two million items a day and 50 fail the quality control check, this is a low percentage and might be acceptable. If, on the other hand, the company is producing only 75 expensive products a day and 50 fail the quality control check, there is likely a serious issue that needs to be addressed. The idea behind scorecards is not to necessarily display the number, but to provide an icon that indicates whether the number is good or bad. This icon can take the form of colored indicators (green/yellow/red), smiley faces, gauges, dials, and so forth. This way, at a glance, an individual can determine whether things are good (or not) for each particular KPI without having to see the exact number or translate a number into an indicator of overall health.
In addition to the health of a KPI, many KPIs also have a trend indicator, showing whether they are improving or slipping. In the case of the number of items failing a quality control audit, lower numbers are better, so a decline in the number would actually be a positive trend. Because the trend and the health of a KPI are tracked separately, the health might be good but show a downward trend, or a KPI with poor health might be holding steady, indicating that efforts to improve it are not having any impact. Figure 1-2 shows a sample scorecard in PerformancePoint Server.
The name scorecard also implies some form of score. Many organizations do not go through this process, but KPIs can be weighted so that an overall score is generated. This is useful in situations where a company wants to apply the same scorecard to each of several locations. Using a score, it becomes simple to rank each location based on this score.
Figure 1-2 A scorecard created in PerformancePoint Server
Scorecards can certainly be used by anyone in an organization, as everyone appreciates a quick glance summary of the data. However, scorecards are aimed at higher levels of an organization: the business decision-makers. Executives and others with decision-making authority in an organization are typically very busy people, and they don't have time to plumb the data for hidden gems of information; instead, they need a quick overview of the organization's health. Scorecards work perfectly for this by providing that at-a-glance update on the most important business metrics. If any numbers are off, the business decision maker can sometimes perform some analytics to uncover the cause, but most often they have an analyst on staff that will be given the task for investigating the issue. The analyst has tools far more adept at performing complex analysis than can be done with a scorecard.
Scorecards are often placed on a dashboard. A dashboard may contain one or more scorecards, some KPIs independent of a scorecard, reports from Reporting Services, and charts from ProClarity. To add another layer to this, a portal is the top of the heap and may contain one or more dashboards as well as a copy of the person's calendar, e-mail, and so forth. Dashboards are what are ultimately deployed by PerformancePoint Server, and the primary deployment target for these dashboards is SharePoint. SharePoint is an excellent tool for bringing together information from multiple applications and displaying it in a centralized location.
The creation of scorecards, as well as some common KPIs, will be examined in depth in Chapters 4 and 5.
Reports are typically boring collections of numbers in rows and columns. Despite their generally dull nature, reports are still in huge demand across many organizations. Fortunately, reports don't have to be as dull as they have been in the past. Thanks to online report viewing, reports can now be interactive, providing end users with the ability to drill down and obtain additional information, or choose values from a filter list to narrow the scope of what they see in the report. Understand that when discussing reports, the terms filters and parameters are often used interchangeably, and that will be the case in this book. Figure 1-3 shows a report that allows users to narrow the data using drop-down list boxes, as well as drill into more detail by expanding regions of the report.
SQL Server Reporting Services allows for the creation of reports against a data warehouse, regardless of whether the data are stored in a relational format or in a cube. These reports can be interactive; users can choose values from a parameter list and the reports can allow for limited drill down and other analytic operations. Reports can also link to other reports so that data may be examined in more detail.
Standard reports are not natively part of PerformancePoint Server, but PerformancePoint integrates well with Reporting Services. Various actions in PerformancePoint can link to reports hosted in Reporting Services, allowing end users to find more detailed information. For example, a KPI in a scorecard might show that employee retention is below desired levels. Clicking on that KPI could open a report that lists each department ordered by the percentage turnover that department has experienced in the last quarter.
Reports do not need to be only a device reached through other tools, however; for many organizations, reports are the only tool widely distributed. Reports are a staple of any organization and—thanks to intranets and ubiquitous connectivity—online reports can be distributed with ease. Anyone can log onto a centralized report server and view real-time reports. In addition, reports can be generated on a scheduled basis and distributed via e-mail or printed and distributed.
For example, imagine a company in the US that has a number of stores throughout the US and Canada. Each store manager has access to six predefined reports. Most store managers start their day by viewing online reports showing how their particular store is doing compared to the other stores in their district and to other stores companywide. These reports are simple, static reports and the managers can flip through them quickly. This is close to being suitable for a scorecard, but the reports contain more detailed information than is normally found in a scorecard scenario.
Reports do not have to be static. Interactive reports provide tremendous flexibility by allowing end users to narrow the scope through parameters by selecting values from drop-down list boxes. In addition, reports can provide drill-down capabilities, either by linking to other reports or providing expanding and collapsing regions directly in the report. Adding such interactivity allows end users to answer many questions themselves without having to learn complex analytic applications or how to perform their own queries.
Analytic applications are specialized tools that enabled detailed analysis on data in a warehouse. In this book I specifically about tools that interact with Analysis Services, the cube-building engine Microsoft provides with SQL Server. Surprisingly, most readers will already be using the most popular analytic application, Microsoft Excel. Excel has had the capability to interact with Analysis Services cubes for some time, but Excel 2007 makes a huge leap forward in the capabilities and usability of Excel as a cube browsing tool.
For years, the most popular third-party tool for browsing cubes was ProClarity. ProClarity Corporation created a thick, desktop client called ProClarity Professional, as well as a web-based tool they called ProClarity Analytics Server, or PAS. PAS included a thin client for browsing cubes called ProClarity standard, and it mimicked most, but not all, of the functionality found in ProClarity Professional. With Microsoft's acquisition of ProClarity in 2006, the groundwork was laid to merge the strong analytic tools from ProClarity with some of Microsoft's tools, including the Business Scorecard Manager. The merger of these tools, as well as the addition of a planning piece, has led to PerformancePoint Server.
Analytic applications such as ProClarity are designed for analysts who need to perform complex analysis on data. This doesn't mean that such tools are limited only to analysts—I've have taught numerous ProClarity classes to end users over the years. However, these tools are more specialized than standard reports and therefore don't have the same broad reach. They are designed primarily for someone who wants to work with the data without restrictions and follow any train of thought through iterative analysis of the data.
Many of these tools also include advanced visualizations in the form of different graph and chart types. Figure 1-4 shows an example of some of ProClarity's advanced views of the data and how they can be used to quickly identify problem areas, outliers, and so forth.
In many organizations, the number of people needing a full-blown analytic tool compared to reports with limited analytics is anywhere from 1:5 to 1:10; in other words, one user will need a true analytic tool for every five to ten users that consume reports. However, Excel skews this number by allowing for the creation of canned reports against the data while still allowing for a fairly detailed analysis. Therefore, assume that the line between true analytic tools and more general tools is continually blurring.
Line-of-business applications are not business intelligence tools in a true sense. Instead, they're the applications that allow a business to run its day-to-day operations. However, integrating data from a business intelligence solution into line of business applications can provide line workers with tremendous benefits. Call center employees can immediately see charts or numbers representing all past business from a customer. Online stores can recommend complementary products when a person is making a purchase, based on the customer's previous purchases and the habits of similar customers. A medical center could use statistics covering a wide range of lab values and a large number of patients to identify whether a patient currently being treated might be at an elevated risk for a certain types of disease. Data input applications can validate data in real time against the known universe of good data. It can also flag potentially bad data at the time of data entry, before it makes its way into the system.
This ability to use the data from a data warehouse provides benefits all the way to the workers on the front lines of dealing with customers or products. These individuals may be totally unaware of the existence of a data warehouse, but they know that they use the numbers shown in the applications to get their job done more efficiently. Such integration between data in Analysis Services and line-of-business applications is provided by such enabling technologies as OLE DB, ADO.NET, XML web services, XMLA, and more. This alphabet soup means one thing: accessing the data and bringing it into client applications is well supported in a variety of ways and in many different application formats. Whether a company is using Windows applications or web applications, the data can easily be integrated into the daily workflow of all users.
More about business intelligence
- Access the SearchDataManagement.com complete business intelligence tutorial
- Listen to a podcast about the secrets to successful business intelligence applications
Read more excerpts from business intelligence books in the Chapter Download Library
More about PerformancePoint Server
- Microsoft Office PerformancePoint Server 2007 hits the street
- With PerformancePoint, Microsoft is content to play follow the leaders