See Instructions
cgrome
baltzan_2012_ch06.pdf
Confirming Pages
What’s in IT for me? This chapter introduces the concepts of information and data and their relative importance to business profession- als and firms. It distinguishes between data stored in transactional databases and powerful business intelligence gleaned from data warehouses. Students who understand how to access, manipulate, summarize, sort, and ana- lyze data to support decision making find success. Information has power, and understanding that power will help you compete in the global marketplace. This chapter will provide you with an overview of database fundamentals and the characteristics associated with high-quality information. It will also explain how the various bits of data stored across multiple, operational databases can be transformed in a centralized repository of summarized infor- mation in a data warehouse, which can be used for discovering business intelligence.
You, as a business student, need to understand the differences between transactional data and summarized infor- mation and the different types of questions you could use a transactional database to answer versus a data ware- house. You need to be aware of the complexity of storing data in databases and the level of effort required to transform operational data into meaningful, summarized information. You need to realize the power of information and the competitive advantage a data warehouse brings an organization in terms of facilitating business intelligence. Armed with the power of information, business students will make smart, informed, and data-supported managerial decisions.
■ T h e B u s i n e s s B e n e f i t s o f Da t a Wa re h o u s i n g
■ Pe r fo rm i n g B u s i n e s s A n a l y s i s w i t h Da t a M a rt s
■ U n c ove ri n g Tre n d s a n d Pa tte rn s w i t h Da t a M i n i n g
■ S u p p o rt i n g D e c i s i o n s w i t h B u s i n e s s I n te l l i ge n c e
SECTION 6.2 Business Intelligence
■ T h e B u s i n e s s B e n e f i t s o f H i g h - Q u a l i t y I n fo rm a t i o n
■ S to ri n g I n fo rm a t i o n U s i n g a R e l a t i o n a l Da t a b a s e M a n a ge m e n t Sy s te m
■ U s i n g a R e l a t i o n a l Da t a b a s e fo r B u s i n e s s Ad va n t a ge s
■ D ri v i n g We b s i te s w i t h Da t a
SECTION 6.1 Data, Information, and Databases
C H
A P
T E
R O
U T
L IN
E
Data: Business Intelligence 6 C H A P T E R
bal76825_ch06_201-240.indd 201bal76825_ch06_201-240.indd 201 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
202 Chapter 6 Data: Business Intelligence
opening case study
Business Intelligence Software’s Time Is Now
With restaurants stretching from Seattle to the Osan Air Base in South Korea, the Chili’s Grill & Bar chain gets many visitors. For parent company Brinker International, that makes many Triple Dippers to keep track of. In addition, as the owner of such chains as On The Border Mexican Grill & Cantina, Brinker has to gather information on sales, inventory, and other operations for 1,700 restaurants in 27 countries that receive more than 1 million customers a day. An even bigger challenge, though, is sifting through all that data, organizing it, and then using it to make the business run more profitably.
Making smart use of that information is all the more urgent as customers dine in more often and spend less even when they eat out. Amid falling revenue, Brinker already has resorted to cutting jobs and shutting restaurants. “At Brinker, we are not waiting for external actions to strengthen our company,” states Brinker’s CEO Doug Brooks. “We have taken considerable steps to remain competitive and position our brands for accelerated profitability once the economy eventually does improve.”
One of those steps is analyzing the reams of data the company collects at its restaurants. Using what is known as business intelligence software, Brinker gets a better handle on consumer spending patterns to make a host of decisions, from altering staff levels to moving around menu items. Interest in business intelligence software is on the rise, analysts say, as economic woes force companies to pursue profit by delving deeper into the information already at their fingertips. “There’s a tre- mendous pressure on cost containment, on developing accurate forecasts of sales and expenses, and on trying to align the expense stream with projected revenue stream,” says John Van Decker, research vice president at research firm Gartner.
Top Priority for CIOs
Business intelligence (BI) software can bring together data from disparate parts of the company. Once that happens, companies can do analysis, run reports, and make predictions based on past performance. Some BI software lets companies weave in external information, such as the price of gas or the unemployment rate in certain regions, to understand how market swings or economic trends are affecting cus- tomer behavior and the company.
Brinker used BI to help with a challenge faced by many restaurants: determin- ing how many staff should work each shift—a task that can be especially daunting during a recession. The company tapped a restaurant performance management system built with Information Builders’ WebFOCUS business intelligence software.
bal76825_ch06_201-240.indd 202bal76825_ch06_201-240.indd 202 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
203Technical Foundations of MIS Module 2
Among other things, this software can calculate, based on sales, the optimal staffing level during a particular shift at a specific restaurant.
BI software tops the list of technology spending priorities for companies in 2009, according to a Gartner survey of more than 1,500 CIOs worldwide. That priority remains, even though IT budgets are expected to be essentially flat in 2009. Market researcher Forrester Research expects the BI market to generate more than $12 billion in revenue in 2014, compared with $8.5 billion in 2008.
Expectations for increasing demand helped fuel a wave of consolidation in the BI market. Software vendors including SAP, IBM, Oracle, and Microsoft all made big BI acquisitions in recent years.
Capitalizing on Quality Data
About two-thirds of large U.S. companies believe they need to improve their analyti- cal capabilities and only half believe they are spending enough on business analyt- ics, according to an Accenture survey of 250 executives. In it, about 57 percent of companies said they do not have a beneficial, consistently updated, companywide analytical capability, and 72 percent are working to increase their company’s use of business analytics. Today, only 60 percent of major decisions are based on analytics, according to the survey, while 40 percent are based on intuition.
Business intelligence software can also help companies mine customer data that they already track to potentially sell new products or services. For example, Ingram Micro, a wholesale technology distributor, discovered it was missing out on opportunities to renew maintenance contracts. Ingram Micro sells hardware and software to smaller resellers, which sell it to businesses and consumers. “Our vis- ibility into that piece of product evaporated as soon as it left the warehouse,” says Justin Crotty, vice president for services at Ingram Micro North America. So, three years ago, the company started using online software from MaintenanceNet that analyzes data the company already captures to identify 80 to 90 percent of renewal opportunities, up from 30 percent previously. That has resulted in increased services revenues, even while hardware sales have slowed throughout the industry.
Strategic Modeling for the Future
The economic slowdown has also affected the number of bookings in the cruise industry. To cope, Carnival Cruise Lines is trying to lure back former travelers. “We’re focusing on getting them to come back, refreshing in their minds what a fantastic time they had on their last cruise,” says Shannon Balliet-Antorcha, director of data- base marketing and customer data integration for the Carnival Cruise Lines brand.
Trouble is, Carnival doesn’t have an unlimited marketing budget to create mate- rial for all of its former travelers, so it concentrates on those likely to go on a cruise during a recession. Using software from SAS, Carnival can look at internal customer information and also third-party information about household income and composi- tion. By analyzing that information, Carnival can quickly and easily create marketing campaigns for the right audience.
bal76825_ch06_201-240.indd 203bal76825_ch06_201-240.indd 203 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
204 Chapter 6 Data: Business Intelligence
“Large companies are like large ships that are difficult to turn,” says John Colbert, vice president for research and analysis at BPM Partners, a management consulting firm. Lots of companies use business intelligence to understand what has happened in the past, but it’s really important, he says, to do strategic modeling to understand how to move forward, whether it means solidifying relationships with your most prof- itable clients or figuring out which employees are the least productive and will need to go in the next wave of layoffs.
Brinker hopes better information will give way to better management of labor costs going forward. “It’s starting to help,” says Kenny Sullivan, senior director of operational and analytical systems at Brinker. “We’re starting to be more efficient.” 1
bal76825_ch06_201-240.indd 204bal76825_ch06_201-240.indd 204 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
205Technical Foundations of MIS Module 2
section 6.1 DATA, INFORMATION, AND DATABASES
L E A R N I N G O U T C O M E S
6.1 Explain the four primary traits that determine the value of information.
6.2 Describe a database, a database management system, and the relational database model.
6.3 Identify the business advantages of a relational database.
6.4 Explain the business benefits of a data-driven website.
THE BUSINESS BENEFITS OF HIGH-QUALITY INFORMATION Information is powerful. Information can tell an organization how its current opera- tions are performing and help it estimate and strategize about how future operations might perform. The ability to understand, digest, analyze, and filter information is key to growth and success to any professional in any industry. Remember that new perspec- tives and opportunities can open up when you have the right data that you can turn into information and ultimately business intelligence.
Information is everywhere in an organization. Managers in sales, marketing, human resources, and management need information to run their departments and make daily decisions. When addressing a significant business issue, employees must be able to obtain and analyze all the relevant information so they can make the best decision possible. Information comes at different levels, formats, and granularities. Information granularity refers to the extent of detail within the information (fine and detailed or coarse and abstract). Employees must be able to correlate the different levels, formats, and granularities of information when making decisions. For example, a company might be collecting information from various suppliers to make needed decisions, only to find that the information is in different levels, formats, and granularities. One supplier might send detailed information in a spreadsheet, while another supplier might send summary information in a Word document, and still another might send a collection of informa- tion from emails. Employees will need to compare these different types of information for what they commonly reveal to make strategic decisions. Figure 6.1 displays the vari- ous levels, formats, and granularities of organizational information.
Successfully collecting, compiling, sorting, and finally analyzing information from multiple levels, in varied formats, and exhibiting different granularities can provide tremendous insight into how an organization is performing. Exciting and unexpected results can include potential new markets, new ways of reaching customers, and even new methods of doing business. After understanding the different levels, formats, and granularities of information, managers next want to look at the four primary traits that help determine the value of information:
■ Information type: transactional and analytical.
■ Information timeliness.
■ Information quality.
■ Information governance.
Information Type: Transactional and Analytical
As discussed previously in the text, the two primary types of information are transac- tional and analytical. Transactional information encompasses all of the information contained within a single business process or unit of work, and its primary purpose is to support daily operational tasks. Organizations need to capture and store transactional information to perform operational tasks and repetitive decisions such as analyzing daily
LO 6.1: Explain the four primary traits that determine the value of information.
bal76825_ch06_201-240.indd 205bal76825_ch06_201-240.indd 205 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
Chapter 6 Data: Business Intelligence206
sales reports and production schedules to determine how much inventory to carry. Con- sider Walmart, which handles more than 1 million customer transactions every hour, and Facebook, which keeps track of 400 million active users (along with their photos, friends, and Web links). In addition, every time a cash register rings up a sale, a deposit or withdrawal is made from an ATM, or a receipt is given at the gas pump, capturing and storing of the transactional information are required.
Analytical information encompasses all organizational information, and its primary purpose is to support the performing of managerial analysis tasks. Analytical informa- tion is useful when making important decisions such as whether the organization should build a new manufacturing plant or hire additional sales personnel. Analytical informa- tion makes it possible to do many things that previously were difficult to accomplish, such as spot business trends, prevent diseases, and fight crime. For example, credit card companies crunch through billions of transactional purchase records to identify fraudu- lent activity. Indicators such as charges in a foreign country or consecutive purchases of gasoline send a red flag highlighting potential fraudulent activity.
Walmart was able to use its massive amount of analytical information to identify many unusual trends, such as a correlation between storms and Pop-Tarts. Yes, Walmart dis- covered an increase in the demand for Pop-Tarts during the storm season. Armed with the valuable information the retail chain was able to stock up on Pop-Tarts that were ready for purchase when customers arrived. Figure 6.2 displays different types of trans- actional and analytical information.
Information Timeliness
Timeliness is an aspect of information that depends on the situation. In some firms or industries, information that is a few days or weeks old can be relevant, while in others information that is a few minutes old can be almost worthless. Some organizations, such as 911 response centers, stock traders, and banks, require up-to-the-second informa- tion. Other organizations, such as insurance and construction companies, require only daily or even weekly information.
Real-time information means immediate, up-to-date information. Real-time systems provide real-time information in response to requests. Many organizations
FIGURE 6.1
Levels, Formats, and Granularities of Organizational Information
Information Granularities Detail (Fine), Summary, Aggregate
(Coarse)
• Individual knowledge, goals, and strategies
Departmental goals, revenues, expenses, processes, and strategies
Enterprise revenues, expenses, processes, and strategies
Letters, memos, faxes, emails, reports, marketing materials, and training materials
•
Product, strategy, process, financial, customer, and competitor
•
Sales, marketing, industry, financial, competitor, customer, and order spreadsheets
•
Customer, employee, sales, order, supplier, and manufacturer databases
•
Reports for each salesperson, product, and part
Reports for all sales personnel, all products, and all parts
Reports across departments, organizations, and companies
•
•
•
•
•
Information Levels Individual, Department, Enterprise
Information Formats Document, Presentation, Spreadsheet,
Database
bal76825_ch06_201-240.indd 206bal76825_ch06_201-240.indd 206 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
207Technical Foundations of MIS Module 2
use real-time systems to uncover key corporate transactional information. The grow- ing demand for real-time information stems from organizations’ need to make faster and more effective decisions, keep smaller inventories, operate more efficiently, and track performance more carefully. Information also needs to be timely in the sense that it meets employees’ needs, but no more. If employees can absorb information only on an hourly or daily basis, there is no need to gather real-time information in smaller increments.
Most people request real-time information without understanding one of the biggest pitfalls associated with real-time information—continual change. Imagine the following scenario: Three managers meet at the end of the day to discuss a business problem. Each manager has gathered information at different times during the day to create a picture of the situation. Each manager’s picture may be different because of the time differences. Their views on the business problem may not match because the information they are basing their analysis on is continually changing. This approach may not speed up decision making, and it may actually slow it down. Business decision makers must evaluate the timeliness for the information for every decision. Organizations do not want to find themselves using real-time information to make a bad decision faster.
Information Quality
Business decisions are only as good as the quality of the information used to make them. Data inconsistency occurs when the same data element has different values. Take for example the amount of work that needs to occur to update a customer who had changed her last name due to marriage. Changing this information in only a few organizational systems will lead to data inconsistencies causing customer 123456 to be associated with two last names. Data integrity issues occur when a system produces incorrect, inconsis- tent, or duplicate data. Data integrity issues can cause managers to consider the system reports invalid and will make decisions based on other sources.
To ensure your systems do not suffer from data integrity issues, review Figure 6.3 for the five characteristics common to high-quality information: accuracy, completeness, consistency, timeliness, and uniqueness. Figure 6.4 provides an example of several problems associated with using low-quality information including:
1. Completeness. The customer’s first name is missing. 2. Another issue with completeness. The street address contains only a number and not a
street name.
3. Consistency. There may be a duplication of information since there is a slight dif- ference between the two customers in the spelling of the last name. Similar street addresses and phone numbers make this likely.
FIGURE 6.2
Transactional versus Analytical Information
Packing Slip
Airline Ticket Sales
Receipt
Database
Transactional Information
Trends
Sales Projections
Analytical Information
Future Growth
Product Statistics
bal76825_ch06_201-240.indd 207bal76825_ch06_201-240.indd 207 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
Chapter 6 Data: Business Intelligence208
4. Accuracy. This may be inaccurate information because the customer’s phone and fax numbers are the same. Some customers might have the same number for phone and fax, but the fact that the customer also has this number in the email address field is suspicious.
5. Another issue with accuracy. There is inaccurate information because a phone number is located in the email address field.
6. Another issue with completeness. The information is incomplete because there is not a valid area code for the phone and fax numbers.
Nestlé uses 550,000 suppliers to sell more than 100,000 products in 200 countries. However, due to poor information, the company was unable to evaluate its business effectively. After some analysis, it found that it had 9 million records of vendors, cus- tomers, and materials, half of which were duplicated, obsolete, inaccurate, or incom- plete. The analysis discovered that some records abbreviated vendor names while other
FIGURE 6.3
Five Common Characteristics of High-Quality Information
Accurate
Complete
Consistent
Timely
Unique
• Is there an incorrect value in the information? • Example: Is the name spelled correctly? Is the dollar amount recorded properly?
• Is a value missing from the information? • Example: Is the address complete including street, city, state, and zip code?
• Is aggregate or summary information in agreement with detailed information? • Example: Do all total columns equal the true total of the individual item?
• Is the information current with respect to business needs? • Example: Is information updated weekly, daily, or hourly?
• Is each transaction and event represented only once in the information? • Example: Are there any duplicate customers?
FIGURE 6.4
Example of Low-Quality Information
113 114 115 116
Smith Jones Roberts Robert
First Name City
Jeff Jenny Jenny
1. Missing information (no first name)
3. Probable duplicate information (similar names, same address, phone number)
4. Potential wrong information (are the phone and fax numbers the same or is this an error?)
5. Inaccurate information (invalid email)
6. Incomplete information (missing area codes)
Street
123 S. Main 12A 1244 Colfax 1244 Colfax
Denver Denver Denver Denver
State
CO CO CO CO
Zip
80210 80224 85231 85231
Phone
(303) 777-1258 (303) 666-6868 759-5654 759-5654
Fax
(303) 777-5544 (303) 666-6868 853-6584 853-6584
[email protected] (303) 666-6868 [email protected] [email protected]
ID Last
Name
2. Incomplete information (no street)
bal76825_ch06_201-240.indd 208bal76825_ch06_201-240.indd 208 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
209Technical Foundations of MIS Module 2
records spelled out the vendor names. This created multiple accounts for the same cus- tomer, making it impossible to determine the true value of Nestlé’s customers. Without being able to identify customer profitability, a company runs the risk of alienating its best customers. 2
Knowing how low-quality information issues typically occur can help a company correct them. Addressing these errors will significantly improve the quality of company information and the value to be extracted from it. The four primary reasons for low- quality information are:
1. Online customers intentionally enter inaccurate information to protect their privacy. 2. Different systems have different information entry standards and formats. 3. Data-entry personnel enter abbreviated information to save time or erroneous infor-
mation by accident.
4. Third-party and external information contains inconsistencies, inaccuracies, and errors.
Understanding the Costs of Using Low-Quality Information Using the wrong information can lead managers to make erroneous decisions. Erroneous deci- sions in turn can cost time, money, reputations, and even jobs. Some of the serious business consequences that occur due to using low-quality information to make deci- sions are:
■ Inability to accurately track customers.
■ Difficulty identifying the organization’s most valuable customers.
■ Inability to identify selling opportunities.
■ Lost revenue opportunities from marketing to nonexistent customers.
BUSINESS DRIVEN MIS
Real People magazine is geared toward working individuals and provides arti- cles and advice on everything from car maintenance to family planning. The magazine is currently experiencing problems with its distribution list. More than 30 percent of the magazines mailed are returned because of incorrect address information, and each month it receives numerous calls from angry customers complaining that they have not yet received their magazines. Below is a sample of Real People ’s customer information. Create a report detailing all the issues with the information, potential causes of the information issues, and solutions the company can follow to correct the situation.
Determining Information Quality Issues
APPLY YOUR KNOWLEDGE
ID First Name Middle Initial Last Name Street City State Zip Code
433 M J Jones 13 Denver Denver CO 87654
434 Margaret J Jones 13 First Ave. Denver CO 87654
434 Brian F Hoover Lake Ave. Columbus OH 87654
435 Nick H Schweitzer 65 Apple Lane San Francisco OH 65664
436 Richard A 567 55th St. New York CA 98763
437 Alana B Smith 121 Tenny Dr. Buffalo NY 142234
438 Trevor D Darrian 90 Fresrdestil Dallas TX 74532
bal76825_ch06_201-240.indd 209bal76825_ch06_201-240.indd 209 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
Chapter 6 Data: Business Intelligence210
■ The cost of sending nondeliverable mail.
■ Difficulty tracking revenue because of inaccurate invoices.
■ Inability to build strong relationships with customers.
Understanding the Benefits of Using High-Quality Information High- quality information can significantly improve the chances of making a good decision and directly increase an organization’s bottom line. One company discovered that even with its large number of golf courses, Phoenix, Arizona, is not a good place to sell golf clubs. An analysis revealed that typical golfers in Phoenix are tourists and convention- eers who usually bring their clubs with them. The analysis further revealed that two of the best places to sell golf clubs in the United States are Rochester, New York, and Detroit, Michigan. Equipped with this valuable information, the company was able to strategically place its stores and launch its marketing campaigns.
High-quality information does not automatically guarantee that every decision made is going to be a good one, because people ultimately make decisions and no one is per- fect. However, such information ensures that the basis of the decisions is accurate. The success of the organization depends on appreciating and leveraging the true value of timely and high-quality information.
Information Governance
Information is a vital resource and users need to be educated on what they can and can- not do with it. To ensure a firm manages its information correctly, it will need special pol- icies and procedures establishing rules on how the information is organized, updated, maintained, and accessed. Every firm, large and small, should create an information policy concerning data governance. Data governance refers to the overall management of the availability, usability, integrity, and security of company data. A company that sup- ports a data governance program has a defined a policy that specifies who is accountable for various portions or aspects of the data, including its accuracy, accessibility, consis- tency, timeliness, and completeness. The policy should clearly define the processes con- cerning how to store, archive, back up, and secure the data. In addition, the company should create a set of procedures identifying accessibility levels for employees. Then, the firm should deploy controls and procedures that enforce government regulations and compliance with mandates such as Sarbanes-Oxley.
STORING INFORMATION IN A RELATIONAL DATABASE MANAGEMENT SYSTEM The core component of any system, regardless of size, is a database and a database management system. Broadly defined, a database maintains information about vari- ous types of objects (inventory), events (transactions), people (employees), and places (warehouses). A database management system (DBMS) creates, reads, updates, and deletes data in a database while controlling access and security. Managers send requests to the DBMS, and the DBMS performs the actual manipulation of the data in the data- base. Companies store their information in databases, and managers access these sys- tems to answer operational questions such as how many customers purchased Product A in December or what were the average sales by region. There are two primary tools available for retrieving information from a DBMS. First is a query-by-example (QBE) tool that helps users graphically design the answer to a question against a database. Second is a structured query language (SQL) that asks users to write lines of code to answer questions against a database. Managers typically interact with QBE tools, and MIS professionals have the skills required to code SQL. Figure 6.5 displays the relation- ship between a database, a DBMS, and a user. Some of the more popular examples of DBMS include MySQL, Microsoft Access, SQL Server, FileMaker, Oracle, and FoxPro.
A data element (or data field) is the smallest or basic unit of information. Data ele- ments can include a customer’s name, address, email, discount rate, preferred shipping method, product name, quantity ordered, and so on. Data models are logical data struc- tures that detail the relationships among data elements using graphics or pictures.
LO 6.2: Describe a database, a database management system, and the relational database model.
bal76825_ch06_201-240.indd 210bal76825_ch06_201-240.indd 210 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
211Technical Foundations of MIS Module 2
Metadata provides details about data. For example, metadata for an image could include its size, resolution, and date created. Metadata about a text document could con- tain document length, data created, author’s name, and summary. Each data element is given a description, such as Customer Name; metadata is provided for the type of data (text, numeric, alphanumeric, date, image, binary value) and descriptions of potential predefined values such as a certain area code; and finally the relationship is defined. A data dictionary compiles all of the metadata about the data elements in the data model. Looking at a data model along with reviewing the data dictionary provides tre- mendous insight into the database’s functions, purpose, and business rules.
DBMS use three primary data models for organizing information—hierarchical, net- work, and the relational database, the most prevalent. A relational database model stores information in the form of logically related two-dimensional tables. A relational database management system allows users to create, read, update, and delete data in a relational database. Although the hierarchical and network models are important, this text focuses only on the relational database model.
Storing Data Elements in Entities and Attributes
For flexibility in supporting business operations, managers need to query or search for the answers to business questions such as which artist sold the most albums during a certain month. The relationships in the relational database model help managers extract this information. Figure 6.6 illustrates the primary concepts of the relational database model—entities, attributes, keys, and relationships. An entity (also referred to as a table)
BUSINESS DRIVEN DEBATE
Without a data governance policy, a company might be leaving its data vulner- able to hackers and theft. Consider TJX Co., the parent company of T.J. Maxx, which had 45 million credit and debit card numbers stolen from its data sys- tems. The credit card industry provides security rules that mandate vendors encrypt credit card data and limit storage of credit card numbers from point- of-sale terminals. T.J. Maxx did not have a data governance policy adhering to these stipulations and was actively storing the customer information and credit card numbers for years. 3
Who do you think is to blame for the T.J. Maxx credit card data theft—the company for not implementing the proper data governance policies or the hackers?
Securing Credit Card Data
APPLY YOUR KNOWLEDGE
Customers Orders
Products Distributors
DBMSDatabase
1. Enter New Customer 2. Find Customer Order 3. Enter New Products
User
FIGURE 6.5
Relationship of Database, DBMS, and User
bal76825_ch06_201-240.indd 211bal76825_ch06_201-240.indd 211 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
Chapter 6 Data: Business Intelligence212
stores information about a person, place, thing, transaction, or event. The entities, or tables, of interest in Figure 6.6 are TRACKS, RECORDINGS, MUSICIANS, and CATEGO- RIES. Notice that each entity is stored in a different two-dimensional table (with rows and columns).
Attributes ( also called columns or fields) are the data elements associated with an entity. In Figure 6.6 the attributes for the entity TRACKS are TrackNumber, TrackTitle, TrackLength, and RecordingID. Attributes for the entity MUSICIANS are MusicianID, MusicianName, MusicianPhoto, and MusicianNotes. A record is a collection of related data elements (in the MUSICIANS table these include “3, Lady Gaga, gag.tiff, Do not bring young kids to live shows”). Each record in an entity occupies one row in its respective table.
Creating Relationships Through Keys
To manage and organize various entities within the relational database model, you use primary keys and foreign keys to create logical relationships. A primary key is a field (or group of fields) that uniquely identifies a given record in a table. In the table RECORD- INGS, the primary key is the field RecordingID that uniquely identifies each record in the table. Primary keys are a critical piece of a relational database because they provide a way of distinguishing each record in a table; for instance, imagine you need to find infor- mation on a customer named Steve Smith. Simply searching the customer name would not be an ideal way to find the information because there might be 20 customers with the name Steve Smith. This is the reason the relational database model uses primary keys to uniquely identify each record. Using Steve Smith’s unique ID allows a manager to search the database to identify all information associated with this customer.
A foreign key is a primary key of one table that appears as an attribute in another table and acts to provide a logical relationship between the two tables. For instance, Black Eyed Peas in Figure 6.6 is one of the musicians appearing in the MUSICIANS table. Its primary key, MusicianID, is “2.” Notice that MusicianID also appears as an attri- bute in the RECORDINGS table. By matching these attributes, you create a relationship
FIGURE 6.6
Primary Concepts of the Relational Database Model
Attributes
1
2
3
4
5
6
Pop
R&B
Rock
Country
Blues
Classical
Entities Foreign keys
TRACKS TrackNumber TrackTitle TrackLength RecordingID
1 I Won't 3:45 1
RECORDINGS RecordingID RecordingTitle MuscianID CategoryID
1 Breakthrough 1 1Primary keys
3 You Got Me 4:00 1
4 Fallin For you 3:35 1
1 I Gotta Feelin 4:49 2
2 Imma Be 4:17 2
2 The E.N.D. 2 1
3
4
5
3 Boom Boom Pow 4:11 2
4 Meet Me Halfway 4:44 2
6
Monkey Business
Elephunk
The Fame Monster
Raymond v. Raymond
2
2
3
4
1
1
1
2
MUSICIANS MusicianID MusicianName MusicianPhoto MusicianNotes
1 Colby Caillat Colby.jpg Next concertin Boston 7/1/2011
CategoryID CategoryName
2 Black Eyed Peas BYP.bmp New album due 12/25/2011
3 Lady Gaga Gaga.tiff Do not bring young kids to live shows
4 Usher Usher.bmp Current album #1 on Billboard
Records
2 Begin Again 4:14 1
CATEGORIES
bal76825_ch06_201-240.indd 212bal76825_ch06_201-240.indd 212 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
213Technical Foundations of MIS Module 2
between the MUSICIANS and RECORDINGS tables that states the Black Eyed Peas (MusicianID 2) have several recordings including The E.N.D., Monkey Business, and Elepunk. In essence, MusicianID in the RECORDINGS table creates a logical relationship (who was the musician that made the recording) to the MUSICIANS table . Creating the logical relationship between the tables allows managers to search the data and turn it into useful information.
USING A RELATIONAL DATABASE FOR BUSINESS ADVANTAGES Many business managers are familiar with Excel and other spreadsheet programs they can use to store business data. Although spreadsheets are excellent for supporting some data analysis, they offer limited functionality in terms of security, accessibility, and flex- ibility and can rarely scale to support business growth. From a business perspective, relational databases offer many advantages over using a text document or a spreadsheet, including:
■ Increased flexibility.
■ Increased scalability and performance.
■ Reduced information redundancy.
■ Increased information integrity (quality).
■ Increased information security.
Increased Flexibility
Databases tend to mirror business structures, and a database needs to handle changes quickly and easily, just as any business needs to be able to do. Equally important, data- bases need to provide flexibility in allowing each user to access the information in what- ever way best suits his or her needs. The distinction between logical and physical views is important in understanding flexible database user views. The physical view of infor- mation deals with the physical storage of information on a storage device. The logical view of information focuses on how individual users logically access information to meet their own particular business needs.
LO 6.3: Identify the business advan- tages of a relational database.
BUSINESS DRIVEN START-UP
Living in a cramped dorm room is a common college occurrence. This was the experience of Ryan Dickerson, a Syracuse University student, who found him- self wedged into a tiny dorm room with only enough room to fit a bed, desk, and chair. Spotting an entrepreneurial opportunity, Dickerson innovatively created a hybrid bed and couch, calling his new product the Rylaxer. During the day the Rylaxer functioned as a couch and during the night it transformed into a bed, solving his limited space issue. The Rylaxer is made of environmentally friendly foam and is available in two sizes and a variety of colors. Of course, you can pay extra for a custom cover with your school’s name, Greek letters, or favorite sports team’s logo. 4
Why would a spreadsheet be ineffective for running Dickerson’s business? Why would he want to create a database to support his business needs? If you were building the Rylaxer database, what are a few of the entities and associated attributes you might create? Why would you want to create primary and foreign keys? How will you use these keys to turn data into information?
The Rylaxer
APPLY YOUR KNOWLEDGE
bal76825_ch06_201-240.indd 213bal76825_ch06_201-240.indd 213 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
Chapter 6 Data: Business Intelligence214
In the database illustration from Figure 6.6 , for example, one user could perform a query to determine which recordings had a track length of four minutes or more. At the same time, another user could perform an analysis to determine the distribution of recordings as they relate to the different categories. For example, are there more R&B recordings than rock, or are they evenly distributed? This example demonstrates that while a database has only one physical view, it can easily support multiple logical views that provides for flexibility.
Consider another example—a mail-order business. One user might want a report pre- sented in alphabetical format, in which case last name should appear before first name. Another user, working with a catalog mailing system, would want customer names appearing as first name and then last name. Both are easily achievable, but different logi- cal views of the same physical information.
Increased Scalability and Performance
In its first year of operation, the official website of the American Family Immigration History Center, www.ellisisland.org , generated more than 2.5 billion hits. The site offers immigration information about people who entered America through the Port of New York and Ellis Island between 1892 and 1924. The database contains more than 25 million passenger names that are correlated to 3.5 million images of ships’ manifests. 5
The database had to be scalable to handle the massive volumes of information and the large numbers of users expected for the launch of the website. In addition, the data- base needed to perform quickly under heavy use. Some organizations must be able to support hundreds or thousands of users including employees, partners, customers, and suppliers, who all want to access and share the same information. Databases today scale to exceptional levels, allowing all types of users and programs to perform information- processing and information-searching tasks.
Reduced Data Redundancy
Data redundancy is the duplication of data, or the storage of the same data in multiple places. Redundant data can cause storage issues along with data integrity issues, making it difficult to determine which values are the most current or most accurate. Employees become confused and frustrated when faced with incorrect information causing disrup- tions to business processes and procedures. One primary goal of a database is to elimi- nate information redundancy by recording each piece of information in only one place in the database. This saves disk space, makes performing information updates easier, and improves information quality.
Increased Information Integrity (Quality)
Information integrity is a measure of the quality of information. Integrity constraints are rules that help ensure the quality of information. The database design needs to con- sider integrity constraints. The database and the DBMS ensures that users can never violate these constraints. There are two types of integrity constraints: (1) relational and (2) business critical.
Relational integrity constraints are rules that enforce basic and fundamental information-based constraints. For example, a relational integrity constraint would not allow someone to create an order for a nonexistent customer, provide a markup percent- age that was negative, or order zero pounds of raw materials from a supplier. Business- critical integrity constraints enforce business rules vital to an organization’s success and often require more insight and knowledge than relational integrity constraints. Con- sider a supplier of fresh produce to large grocery chains such as Kroger. The supplier might implement a business-critical integrity constraint stating that no product returns are accepted after 15 days past delivery. That would make sense because of the chance of spoilage of the produce. Business-critical integrity constraints tend to mirror the very rules by which an organization achieves success.
The specification and enforcement of integrity constraints produce higher-quality information that will provide better support for business decisions. Organizations
bal76825_ch06_201-240.indd 214bal76825_ch06_201-240.indd 214 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
215Technical Foundations of MIS Module 2
that establish specific procedures for developing integrity constraints typically see an increase in accuracy that then increases the use of organizational information by busi- ness professionals.
Increased Information Security
Managers must protect information, like any asset, from unauthorized users or misuse. As systems become increasingly complex and highly available over the Internet on many different devices, security becomes an even bigger issue. Databases offer many security features including passwords to provide authentication, access levels to determine who can access the data, and access controls to determine what type of access they have to the information.
For example, customer service representatives might need read-only access to cus- tomer order information so they can answer customer order inquiries; they might not have or need the authority to change or delete order information. Managers might require access to employee files, but they should have access only to their own employ- ees’ files, not the employee files for the entire company. Various security features of data- bases can ensure that individuals have only certain types of access to certain types of information.
Security risks are increasing as more and more databases and DBMS systems are moving to data centers run in the cloud. The biggest risks when using cloud computing are ensuring the security and privacy of the information in the database. Implement- ing data governance policies and procedures that outline the data management require- ments can ensure safe and secure cloud computing.
DRIVING WEBSITES WITH DATA Websites change for site visitors depending on the type of information they request. Consider, for example, an automobile dealer. The dealer would create a database con- taining data elements for each car it has available for sale including make, model, color, year, miles per gallon, a photograph, and so on. Website visitors might click on Porsche and then enter their specific requests such as price range or year made. Once the user hits “go” the website automatically provides a custom view of the requested informa- tion. The dealer must create, update, and delete automobile information as the inven- tory changes.
A data-driven website is an interactive website kept constantly updated and relevant to the needs of its customers using a database. Data-driven capabilities are especially useful when a firm needs to offer large amounts of information, products, or services. Visitors can become quickly annoyed if they find themselves buried under an avalanche of information when searching a website. A data-driven website can help limit the amount of information displayed to customers based on unique search requirements. Companies even use data-driven websites to make information in their internal data- bases available to customers and business partners.
There are a number of advantages to using the Web to access company databases. First, Web browsers are much easier to use than directly accessing the database using a custom-query tool. Second, the Web interface requires few or no changes to the database model. Finally, it costs less to add a Web interface in front of a DBMS than to redesign and rebuild the system to support changes. Additional data-driven website advantages include:
■ Easy to manage content: Website owners can make changes without relying on MIS professionals; users can update data-driven website with little or no training.
■ Easy to store large amounts of data: Data-driven websites can keep large volumes of information organized. Website owners can use templates to implement changes for layouts, navigation, or website structure. This improves website reliability, scal- ability, and performance.
■ Easy to eliminate human errors: Data-driven websites trap data-entry errors, elimi- nating inconsistencies while ensuring all information is entered correctly.
LO 6.4: Explain the business benefits of a data-driven website.
bal76825_ch06_201-240.indd 215bal76825_ch06_201-240.indd 215 11/15/10 3:23 PM11/15/10 3:23 PM
Rev. Confirming Pages
Chapter 6 Data: Business Intelligence216
Zappos credits its success as an online shoe retailer to its vast inventory of nearly 3 million products available through its dynamic data-driven website. The company built its data-driven website catering to a specific niche market: consumers who were tired of finding that their most-desired items were always out of stock at traditional retail- ers. Zappos’ highly flexible, scalable, and secure database helped it rank as the most- available Internet retailer. Figure 6.7 displays Zappos data-driven website illustrating a user querying the database and receiving information that satisfies the user’s request. 7
BUSINESS DRIVEN ETHICS AND SECURITY
Peter Warden is not your typical Facebook user. He is a young entrepreneur from Boulder, Colorado, who used the online social networking site’s data to illustrate people’s interests and common names across the United States. Warden collected data from more than 200 million Facebook profiles, and then he created a visualization map showing connections between locations that share friends.
Warden gathered the data from public profiles using “crawling” software similar to what search engines use to find content. Analyzing these data could provide useful information, such as displaying links between people with simi- lar income, employment, and social connections. When Facebook learned what Warden was doing, the company threatened to sue him unless he deleted the data immediately. Warden did not have the funds required to fight the law- suit and was forced to comply with Facebook’s request and deleted the data. 6
Do you agree or disagree that Warden’s use of the information was unethi- cal? Do you agree with Facebook’s decision to have Warden delete all the data he collected? What other social networking sites could Warden use to collect public information? How much of your personal information do you think is available to the public for data collection efforts such as Warden’s?
Facebook Fiasco
APPLY YOUR KNOWLEDGE
FIGURE 6.7
Zappos.com—A Data-Driven Website
Source: © 2011 Zappos.com, Inc. and its affiliates. Reprinted with permission.
Search query
Zappos Web Server
Results
Database
bal76825_ch06_201-240.indd 216bal76825_ch06_201-240.indd 216 12/9/10 4:36 PM12/9/10 4:36 PM
Confirming Pages
217Technical Foundations of MIS Module 2
Companies can gain valuable business knowledge by viewing the data accessed and analyzed from their website. Figure 6.8 displays how running queries or using ana- lytical tools, such as a PivotTable, on the database that is attached to the website can offer insight into the business, such as items browsed, frequent requests, items bought together, and so on.
FIGURE 6.8
BI in a Data-Driven Website
Web Page
Database
1
2
PivotTable3
section 6.2 BUSINESS INTELLIGENCE
L E A R N I N G O U T C O M E S
6.5 Define a data warehouse, and provide a few reasons it can make a manager more effective.
6.6 Explain ETL and the role of a data mart in business.
6.7 Define data mining, and explain the three common forms for mining structured and unstructured data.
6.8 Identify the advantages of using business intelligence to support managerial decision making.
THE BUSINESS BENEFITS OF DATA WAREHOUSING In the 1990s as organizations began to need more timely information about their busi- ness, they found that traditional management information systems were too cumber- some to provide relevant information efficiently and efficiently. Most of the systems were in the form of operational databases that were designed for specific business functions, such as accounting, order entry, customer service, and sales, and were not appropriate for business analysis for the following reasons:
■ Inconsistent data definitions: Every department had its own method for recording data so when trying to share information, data did not match and users did not get the data they really needed.
LO 6.5: Define a data warehouse, and provide a few reasons why it can make a manager more effective.
bal76825_ch06_201-240.indd 217bal76825_ch06_201-240.indd 217 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
Chapter 6 Data: Business Intelligence218
■ Lack of data standards: Managers need to perform cross-functional analysis using data from all departments, which differed in granularities, formats, and levels.
■ Poor data quality: The data, if available, were often incorrect or incomplete. Therefore, users could not rely on the data to make decisions.
■ Inadequate data usefulness: Users could not get the data they needed; what was collected was not always useful for intended purposes.
■ Ineffective direct data access: Most data stored in operational databases did not allow users direct access; users had to wait to have their queries or questions answered by MIS professionals who could code SQL. Users want and need answers in a timely fashion and waiting for MIS professionals to respond to queries frequently closed the window of opportunity.
During the latter half of the 20th century, the numbers and types of operational data- bases increased. Many large businesses found themselves with information scattered across multiple systems with different file types (such as spreadsheets, databases, and even word processing files), making it almost impossible for anyone to use the informa- tion from multiple sources. Completing reporting requests across operational systems could take days or weeks using antiquated reporting tools that were ineffective for run- ning a business. From this idea, the data warehouse was born as a place where relevant information could be stored and accessed for making strategic queries and reports.
A data warehouse is a logical collection of information, gathered from many different operational databases, that supports business analysis activities and decision-making tasks. The primary purpose of a data warehouse is to combine information, more specifi- cally, strategic information, throughout an organization into a single repository in such a way that the people who need that information can make decisions and undertake busi- ness analysis. A key idea within data warehousing is to collect information from multiple systems in a common location that uses a universal querying tool. This allows opera- tional databases to run where they are most efficient for the business, while providing a common location using a familiar format for the strategic or enterprisewide reporting information.
Data warehouses go even a step further by standardizing information. Gender, for instance can be referred to in many ways (Male, Female, M/F, 1/0), but it should be standardized on a data warehouse with one common way of referring to each data ele- ment that stores gender (M/F). Standardizing of data elements allows for greater accu- racy, completeness, and consistency as well as increases the quality of the information in making strategic business decisions. The data warehouse then is simply a tool that enables business users, typically managers, to be more effective in many ways, including:
■ Developing customer profiles.
■ Identifying new-product opportunities.
■ Improving business operations.
■ Identifying financial issues.
■ Analyzing trends.
■ Understanding competitors.
■ Understanding product performance.
PERFORMING BUSINESS ANALYSIS WITH DATA MARTS Businesses collect a tremendous amount of transactional information as part of their routine operations. Marketing, sales, and other departments would like to analyze these data to understand their operations better. While databases store the details of all trans- actions (for instance, the sale of a product) and events (hiring a new employee), data warehouses store that same information but in an aggregated form more suited to sup- porting decision-making tasks. Aggregation, in this instance, can include totals, counts, averages, and the like.
LO 6.6: Explain ETL and the role of a data mart in business.
bal76825_ch06_201-240.indd 218bal76825_ch06_201-240.indd 218 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
219Technical Foundations of MIS Module 2
The data warehouse modeled in Figure 6.9 compiles information from internal data- bases (or transactional and operational databases) and external databases through extraction, transformation, and loading. Extraction, transformation, and loading (ETL) is a process that extracts information from internal and external databases, trans- forms it using a common set of enterprise definitions, and loads it into a data warehouse. The data warehouse then sends portions (or subsets) of the information to data marts. A data mart contains a subset of data warehouse information. To distinguish between data warehouses and data marts, think of data warehouses as having a more organiza- tional focus and data marts as having a functional focus. Figure 6.9 provides an illustra- tion of a data warehouse and its relationship to internal and external databases, ETL, and data marts.
Multidimensional Analysis
A relational database contains information in a series of two-dimensional tables. In a data warehouse and data mart, information contains layers of columns and rows. For this reason, most data warehouses and data marts are multidimensional databases. A dimension is a particular attribute of information. Each layer in a data warehouse or data mart represents information according to an additional dimension. A cube is the common term for the representation of multidimensional information. Figure 6.10 displays a cube (cube a) that represents store information (the layers), product informa- tion (the rows), and promotion information (the columns).
After creating a cube of information, users can begin to slice-and-dice the cube to drill down into the information. The second cube (cube b) in Figure 6.10 displays a slice representing promotion II information for all products at all stores. The third cube (cube c) in Figure 6.10 displays only information for promotion III, product B, at store 2. By using multidimensional analysis, users can analyze information in a number of ways
FIGURE 6.9
Data Warehouse Model
Marketing Sales
Inventory Billing • Marketing information
• Inventory information
• Sales information
• Billing information
• Competitor information
• Industry information
• Mailing list information
• Stock market analysis
Internal Databases Data Warehouse
Industry information
Stock market
analysis
External Databases
Marketing data mart
Inventory data mart
Sales data mart
Competitor information
Mailing lists
ETL
ETL
ETL
ETL
ETL
bal76825_ch06_201-240.indd 219bal76825_ch06_201-240.indd 219 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
Chapter 6 Data: Business Intelligence220
and with any number of dimensions. Users might want to add dimensions of informa- tion to a current analysis including product category, region, and even forecasted versus actual weather. The true value of a data warehouse is its ability to provide multidimen- sional analysis that allows users to gain insights into their information.
Data warehouses and data marts are ideal for off-loading some of the querying against a database. For example, querying a database to obtain an average of sales for Product B at Store 2 while Promotion III is under way might create a considerable processing burden for a database, increasing the time it takes another person to enter a new sale into the same database. If an organization performs numerous queries against a data- base (or multiple databases), aggregating that information into a data warehouse will be beneficial.
Information Cleansing or Scrubbing
Maintaining quality information in a data warehouse or data mart is extremely impor- tant. To increase the quality of organizational information and thus the effectiveness of decision making, businesses must formulate a strategy to keep information clean. Information cleansing or scrubbing is a process that weeds out and fixes or discards inconsistent, incorrect, or incomplete information.
Specialized software tools exist that use sophisticated procedures to analyze, stan- dardize, correct, match, and consolidate data warehouse information. This step is vitally important because data warehouses often contain information from several different databases, some of which can be external to the organization. In a data warehouse, information cleansing occurs first during the ETL process and again once the informa- tion is in the data warehouse. Companies can choose information cleansing software from several different vendors including Oracle, SAS, Ascential Software, and Group 1 Software. Ideally, scrubbed information is accurate and consistent.
Looking at customer information highlights why information cleansing is neces- sary. Customer information exists in several operational systems. In each system, all the details could change—from the customer ID to contact information—depending on the business process the user is performing (see Figure 6.11 ).
Figure 6.12 displays a customer name entered differently in multiple operational sys- tems. Information cleansing allows an organization to fix these types of inconsistencies in the data warehouse. Figure 6.13 displays the typical events that occur during informa- tion cleansing.
Achieving perfect information is almost impossible. The more complete and accu- rate a company wants its information to be, the more it costs (see Figure 6.14 ). Compa- nies may also trade accuracy for completeness. Accurate information is correct, while
FIGURE 6.10
A Cube of Information for Performing a Multidimensional Analysis on Three Stores for Five Products and Four Promotions
Pr om
o I
Product A
Store 1 Store 2
Store 3
Cube a
Product B
Product C
Product D
Product E
Pr om
o II
Pr om
o III
Pr om
o IV
Pr om
o I
Product A
Store 1 Store 2
Store 3
Cube b
Product B
Product C
Product D
Product E
Pr om
o II
Pr om
o III
Pr om
o IV
Pr om
o I
Product A
Store 1 Store 2
Store 3
Cube c
Product B
Product C
Product D
Product E
Pr om
o II
Pr om
o III
Pr om
o IV
bal76825_ch06_201-240.indd 220bal76825_ch06_201-240.indd 220 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
221Technical Foundations of MIS Module 2
complete information has no blanks. A birth date of 2/31/10 is an example of com- plete but inaccurate information (February 31 does not exist). An address containing Denver, Colorado, without a zip code is an example of accurate information that is incomplete. Many firms complete data quality audits to determine the accuracy and completeness of its data. Most organizations determine a percentage of accuracy and completeness high enough to make good decisions at a reasonable cost, such as 85 percent accurate and 65 percent complete.
FIGURE 6.11
Contact Information in Operational Systems
Billing Contact: Hans Hultgren 555-1211
Customer Service Contact: Anne Logan 555-1288
Contact: Deborah Walbridge 555-6543
The billing system has “accounts payable” customer contact information
The customer service system has the “product user” customer contact information
Marketing Contact: Paul Bauer 555-2211
Contact: Don McCubbrey 555-3434
Sales Contact: Paul Bauer 555-2211
Contact: Don McCubbrey 555-3434
The marketing and sales system has “decision maker”customer contact information.
FIGURE 6.12
Standardizing a Customer Name in Operational Systems
Customers: JD0021 Jane Doe BL0557 Bob Lake JS0288 Judy Smith PB0092 Pat Burton
Customers: 10622FA Susan Brown 10472FB Judie R Smithe 10772FA Patti Burten 10922MC Larry Trump
Customers: 000980 Burton, Tricia 02670 Smith, Judie 000466 Burton, Patricia 006777 Lake, RobertP.
Sales
Customer Service
Billing
Customers: 10001 Jane Doe 10002 Robert P.Lake 10003 Judie R.Smith 10004 Patricia Burton
Customer Information
bal76825_ch06_201-240.indd 221bal76825_ch06_201-240.indd 221 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
Chapter 6 Data: Business Intelligence222
Missing records or attributes
Cleansing
Missing keys or other required data
Redundant records
Erroneous relationships or references
Inaccurate or incomplete data
FIGURE 6.13
Information Cleansing Activities
C om
pl et
en es
s
100%Accuracy
Complete but with known errors
Not very useful May be a proto- type only
Perfect information Pricey
Very incomplete but accurate
Quality Management
10 0%
FIGURE 6.14
The Cost of Accurate and Complete Information
UNCOVERING TRENDS AND PATTERNS WITH DATA MINING Companies are collecting more data than ever. Historically, data were housed function- ally in systems that were unable to talk to each other, such as customer service, finance, and human resources. Data mining is the process of analyzing data to extract informa- tion not offered by the raw data alone. Data mining can also begin at a summary infor- mation level (coarse granularity) and progress through increasing levels of detail (drilling down), or the reverse (drilling up). Companies use data-mining techniques to compile a complete picture of their operations, all within a single view, allowing them to identify trends and improve forecasts. Consider Best Buy, which used data-mining tools to iden- tify that 7 percent of its customers accounted for 43 percent of its sales, so the company reorganized its stores to accommodate those customers. 8
LO 6.7: Define data mining, and explain the three common forms for mining structured and unstructured data.
bal76825_ch06_201-240.indd 222bal76825_ch06_201-240.indd 222 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
223Technical Foundations of MIS Module 2
CUST ID First Name Last Name Address City State Zip Phone Last Order
Date
233620 Christopher Lee 12421 W Olympic Blvd Los Angeles CA 75080-1100 (972)680-7848 4/18/2010
233621 Bruce Brandwen 268 W 44th St New York PA 10036-3906 (212)471-6077 5/3/2010 233622 Glr Johnson 4100 E Dry Creek Rd Littleton CO 80122-3729 (303)712-5461 5/6/2010 233623 Dave Owens 466 Commerce Rd Staunton VA 24401-4432 (540)851-0362 3/19/2010 233624 John Coulbourn 124 Action St Maynard MA 1754 (978)987-0100 4/24/2010 233629 Dan Gagliardo 2875 Union Rd Cheektowaga NY 14227-1461 (716)558-8191 5/4/2010 23362 Damanceee Allen 1633 Broadway New York NY 10019-6708 (212)708-1576 233630 Michael Peretz 235 E 45th St New York NY 10017-3305 (212)210-1340 4/30/2010 233631 Jody Veeder 440 Science Dr Madison WI 53711-1064 (608)238-9690
X227 3/27/2010
233632 Michael Kehrer 3015 SSE Loop 323 Tyler TX 75701 (903)579-3229 4/28/2010 233633 Erin Yoon 3500 Carillon Pt Kirkland WA 98033-7354 (425)897-7221 3/25/2010 233634 Madeline Shefferly 4100 E Dry Creek Rd Littleton CO 80122-3729 (303)486-3949 3/33/2010 233635 Steven Conduit 1332 Enterprise Dr West Chester PA 19380-5970 (610)692-5900 4/27/2010 233636 Joseph Kovach 1332 Enterprise Dr West Chester PA 19380-5970 (610)692-5900 4/28/2010 233637 Richard Jordan 1700 N Philadelphia PA 19131-4728 (215)581-6770 3/19/2010 233638 Scott Mikolajczyk 1655 Crofton Blvd Crofton MD 21114-1387 (410)729-8155 4/28/2010 233639 Susan Shragg 1875 Century Park E Los Angeles CA 90067-2501 (310)785-0511 4/29/2010 233640 Rob Ponto 29777 Telegraph Rd Southfield MI 48034-1303 (810)204-4724 5/5/2010 233642 Lauren Butler 1211 Avenue Of The
Americas New York NY 10036-8701 (212)852-7494 4/22/2010
233643 Christopher Lee 12421 W Olympic Blvd Los Angeles CA 90064-1022 (310)689-2577 3/25/2010 233644 Michelle Decker 6922 Hollywood Blvd Hollywood CA 90028-6117 (323)817-4655 5/8/2010 233647 Natalia Galeano 1211 Avenue Of
The Americas New York NY 10036-8701 (646)728-6911 4/23/2010
233648 Bobbie Orchard 4201 Congress St Charlotte NC 28209-4617 (704)557-2444 5/11/2010 233650 Ben Konfino 1111 Stewart Ave Bethpage NY 11714-3533 (516)803-1406 3/19/2010 233651 Lenee Santana 1050 Techwood Dr NW Atlanta GA 30318-KKRR (404)885-2000 3/22/2010 233652 Lauren Monks 7700 Wisconsin Ave Bethesda MD 20814-3578 (301)771-4772 3/19/2005 233653 Mark Woolley 10950 Washington Blvd Culver City CA 90232-4026 (310)202-2900 4/20/2010
BUSINESS DRIVEN GLOBALIZATION
Congratulations! You have just been hired as a consultant for Integrity Information Inc., a start-up business intelligence consulting company. Your first job is to help work with the sales department in securing a new client, The Warehouse. The Warehouse has been operating in the United States for more than a decade, and its primary business is to sell wholesale low-cost products. The Warehouse is interested in hiring Integrity Information Inc. to clean up the data that are stored in its U.S. database. To determine how good your work is, the client would like your analysis of the following spreadsheet. The Warehouse is also interested in expanding globally and wants to purchase several independent wholesale stores located in Australia, Thailand, China, Japan, and the United Kingdom. Before the company moves forward with the venture, it wants to understand what types of data issues it might encounter as it begins to transfer data from each global entity to the data warehouse. Please create a list detailing the potential issues The Warehouse can anticipate encountering as it consoli- dates the global databases into a single data warehouse. 9
Integrity Information Inc.
APPLY YOUR KNOWLEDGE
bal76825_ch06_201-240.indd 223bal76825_ch06_201-240.indd 223 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
Chapter 6 Data: Business Intelligence224
To perform data mining, users need data-mining tools. Data-mining tools use a variety of techniques to find patterns and relationships in large volumes of information that predict future behavior and guide decision making. Data mining uncovers trends and patterns, which analysts use to build models that, when exposed to new informa- tion sets, perform a variety of information analysis functions. Data-mining tools for data warehouses help users uncover business intelligence in their data. Some of the key areas where businesses are using data mining include:
■ Analyzing customer buying patterns to predict future marketing and promotion campaigns.
■ Building budgets and other financial information.
■ Detecting fraud by identifying deceptive spending patterns.
■ Finding the best customers who spend the most money.
■ Keeping customers from leaving or migrating to competitors.
■ Promoting and hiring employees to ensure success for both the company and the individual.
Data mining enables these companies to determine relationships among such inter- nal factors as price, product positioning, or staff skills, and external factors such as eco- nomic indicators, competition, and customer demographics. In addition, it enables companies to determine the impact on sales, customer satisfaction, and corporate prof- its and to drill down into summary information to view detail transactional data. With data mining, a retailer could use point-of-sale records of customer purchases to send targeted promotions based on an individual’s purchase history. By mining demographic data from comment or warranty cards, the retailer could develop products and promo- tions to appeal to specific customer segments.
Netflix uses data mining to analyze each customer’s film-viewing habits to provide recommendations for other customers with Cinematch, its movie recommendation system. Using Cinematch, Netflix can present customers with a number of additional movies they might want to watch based on the customer’s current preferences. Netflix’s innovative use of data mining provides its competitive advantage in the movie rental industry. 10
Data mining uses specialized technologies and functionalities such as query tools, reporting tools, multidimensional analysis tools, statistical tools, and intelligent agents. Data mining approaches decision making with a few different activities in mind including:
■ Classification—assigns records to one of a predefined set of classes.
■ Estimation—determines values for an unknown continuous variable behavior or estimated future value.
■ Affinity grouping—determines which things go together.
■ Clustering—segments a heterogeneous population of records into a number of more homogeneous subgroups.
Data mining occurs on structured data that are already in a database or a spreadsheet. Unstructured data do not exist in a fixed location and can include text documents, PDFs, voice messages, emails, and so on. Text mining analyzes unstruc- tured data to find trends and patterns in words and sentences. Text mining a firm’s customer support email might identify which customer service representative is best able to handle the question, allowing the system to forward it to the right person. Web mining analyzes unstructured data associated with websites to identify consumer behavior and website navigation. Three common forms for mining structured and unstructured data are:
■ Cluster analysis
■ Association detection
■ Statistical analysis.
bal76825_ch06_201-240.indd 224bal76825_ch06_201-240.indd 224 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
225Technical Foundations of MIS Module 2
Cluster Analysis
Cluster analysis is a technique used to divide information sets into mutually exclu- sive groups such that the members of each group are as close together as possible to one another and the different groups are as far apart as possible. Cluster analysis segments customer information to help organizations identify customers with simi- lar behavioral traits, such as clusters of best customers or onetime customers. Clus- ter analysis also has the ability to uncover naturally occurring patterns in information (see Figure 6.15 ).
A great example of using cluster analysis in business is to create target-marketing strategies based on zip codes. Evaluating customer segments by zip code allows a busi- ness to assign a level of importance to each segment. Zip codes offer valuable insight
BUSINESS DRIVEN DISCUSSION
Often criticized for accruing large amounts of data about people, Google is now giving users an easy way to find out what information it stores about them. The company provides a service called Google Dashboard that summarizes the data that it collects in users’ accounts on their products such as Gmail, Picasa Reader, and YouTube. Using the Dashboard, users can then adjust their privacy settings for the various Google applications, even allowing users to review and delete recent Google searches. 11
Google Dashboard is giving users what they want—control over their own data. How is Google using data mining to collect information on users? What privacy issues does Google create by the massive amounts of personal data it stores? Will the Dashboard prevent Google from tracking users across the Web if it wants to?
Google Dashboard
APPLY YOUR KNOWLEDGE
FIGURE 6.15
Example of Cluster Analysis
−3
−2
−1
0
1
2
−2 −1 0 1 2 3
bal76825_ch06_201-240.indd 225bal76825_ch06_201-240.indd 225 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
Chapter 6 Data: Business Intelligence226
into such things as income levels, demographics, lifestyles, and spending habits. With target marketing, a business can decrease its costs while increasing the success rate of the marketing campaign.
Association Detection
Association detection reveals the relationship between variables along with the nature and frequency of the relationships. Many people refer to association detection algo- rithms as association rule generators because they create rules to determine the like- lihood of events occurring together at a particular time or following each other in a logical progression. Percentages usually reflect the patterns of these events; for example, “55 percent of the time, events A and B occurred together,” or “80 percent of the time that items A and B occurred together, they were followed by item C within three days.”
One of the most common forms of association detection analysis is market basket analysis. Market basket analysis analyzes such items as websites and checkout scanner information to detect customers’ buying behavior and predict future behavior by iden- tifying affinities among customers’ choices of products and services (see Figure 6.16 ). Market basket analysis is frequently used to develop marketing campaigns for cross- selling products and services (especially in banking, insurance, and finance) and for inventory control, shelf-product placement, and other retail and marketing applications.
Statistical Analysis
Statistical analysis performs such functions as information correlations, distributions, calculations, and variance analysis. Data-mining tools offer knowledge workers a wide range of powerful statistical capabilities so they can quickly build a variety of statistical models, examine the models’ assumptions and validity, and compare and contrast the various models to determine the best one for a particular business issue.
Forecasting is a common form of statistical analysis. Time-series information is time- stamped information collected at a particular frequency. Formally defined, forecasts are predictions based on time-series information. Examples of time-series information include Web visits per hour, sales per month, and calls per day. Forecasting data-mining tools allow users to manipulate the time series for forecasting activities.
When discovering trends and seasonal variations in transactional information, use a time-series forecast to change the transactional information by units of time, such as transforming weekly information into monthly or seasonal information or hourly infor- mation into daily information. Companies base production, investment, and staffing decisions on a host of economic and market indicators in this manner. Forecasting mod- els allow organizations to consider all sorts of variables when making decisions.
FIGURE 6.16
Market Basket Analysis
bal76825_ch06_201-240.indd 226bal76825_ch06_201-240.indd 226 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
227Technical Foundations of MIS Module 2
SUPPORTING DECISIONS WITH BUSINESS INTELLIGENCE Many organizations today find it next to impossible to understand their own strengths and weaknesses, let alone their biggest competitors, because the enormous volume of organizational data is inaccessible to all but the MIS department. Organization data include far more than simple structured data elements in a database; the set of data also includes unstructured data such as voice mail, customer phone calls, text messages, video clips, along with numerous new forms of data, such as tweets from Twitter.
The Problem: Data Rich, Information Poor
An ideal business scenario would be as follows: As a business manager on his way to meet with a client reviews historical customer data, he realizes that the client’s order- ing volume has substantially decreased. As he drills down into the data, he notices the client had a support issue with a particular product. He quickly calls the support team to find out all of the information and learns that a replacement for the defective part can be shipped in 24 hours. In addition, he learns that the client has visited the website and requested information on a new product line. Armed with all this information, the business manager is prepared for a productive meeting with his client. He now under- stands the client’s needs and issues, and he can address new sales opportunities with confidence.
For many companies the above example is simply a pipe dream. Attempting to gather all of the client information would actually take hours or even days to compile. With so much data available, it is surprisingly hard for managers to get information, such as inventory levels, past order history, or shipping details. Managers send their information requests to the MIS department where a dedicated person compiles the various reports. In some situations, responses can take days, by which time the information may be out- dated and opportunities lost. Many organizations find themselves in the position of being data rich and information poor. Even in today’s electronic world, managers strug- gle with the challenge of turning their business data into business intelligence.
The Solution: Business Intelligence
Employee decisions are numerous and they include providing service information, offering new products, and supporting frustrated customers. Employees can base their decisions on data, experience, or knowledge and preferably a combination of all three. Business intelligence can provide managers with the ability to make better decisions. A few examples of how different industries use business intelligence include:
■ Airlines: Analyze popular vacation locations with current flight listings. ■ Banking: Understand customer credit card usage and nonpayment rates. ■ Health care: Compare the demographics of patients with critical illnesses. ■ Insurance: Predict claim amounts and medical coverage costs. ■ Law enforcement: Track crime patterns, locations, and criminal behavior. ■ Marketing: Analyze customer demographics. ■ Retail: Predict sales, inventory levels, and distribution. ■ Technology: Predict hardware failures.
Figure 6.17 displays how organizations using BI can find the cause to many issues and problems simply by asking “Why?” The process starts by analyzing a report such as sales amounts by quarter. Managers will drill down into the report looking for why sales are up or why sales are down. Once they understand why a certain location or product is experiencing an increase in sales, they can share the information in an effort to raise enterprisewide sales. Once they understand the cause for a decrease in sales, they can take effective action to resolve the issue. Here are a few examples of how managers can use BI to answer tough business questions:
■ Where has the business been? Historical perspective offers important variables for determining trends and patterns.
LO 6.8: Identify the advantages of using business intelligence to support managerial decision making.
bal76825_ch06_201-240.indd 227bal76825_ch06_201-240.indd 227 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
Chapter 6 Data: Business Intelligence228
■ Where is the business now? Looking at the current business situation allows managers to take effective action to solve issues before they grow out of control.
■ Where is the business going? Setting strategic direction is critical for planning and creating solid business strategies.
Ask a simple question—such as Who is my best customer or What is my worst-selling product—and you might get as many answers as you have employees. Databases, data warehouses, and data marts can provide a single source of “trusted” data that can answer questions about customers, products, suppliers, production, finances, fraud, and even employees. They can also alert managers to inconsistencies or help determine the cause and effects of enterprisewide business decisions. All business aspects can benefit from the added insights provided by business intelligence and you, as a business student, will benefit from understanding how MIS can help you make intelligent decisions.
FIGURE 6.17
How BI Can Answer Tough Customer Questions
Why are sales below target?
Why did we sell less in the West?
Why did X sales drop?
Why did customer complaints increase?
Because we sold less in the Western region.
Because sales of product X dropped.
Because customer complaints increased.
Because late deliveries went up 60 percent.
AnswerQuestion
BUSINESS DRIVEN INNOVATION
Gone are the days of staring at boring spreadsheets and trying to understand how the data correlate. With innovative data visualization tools, managers can arrange different ways to view the data, providing new forms of pattern rec- ognition not offered by simply looking at numbers. Slate, a news publication, developed a new data visualization tool called News Dots that offers readers a different way of viewing the daily news through trends and patterns. The News Dots tool scans about 500 stories a day from major publications and then tags the content with important keywords such as people, places, companies, and topics. Surprisingly, the majority of daily news overlaps as the people, places, and stories are frequently connected. Using News Dots you can visualize how the news fits together, almost similar to a giant social network. News Dots uses circles (or dots) to represent the tagged content and arranges them according to size. The more frequently a certain topic is tagged, the larger the dot and its relationship to other dots. The tool is interactive and users simply click on a dot to view which stories mention that topic and which other topics it connects to in the network such as a correlation among the U.S. government, Federal Reserve, Senate, bank, and Barack Obama. 12
How can data visualization help identify trends? What types of business intel- ligence could you identify if your college used a data visualization tool to analyze student information? What types of business intelligence could you identify if you used a data visualization tool to analyze the industry where you plan to compete?
News Dots
APPLY YOUR KNOWLEDGE
bal76825_ch06_201-240.indd 228bal76825_ch06_201-240.indd 228 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
229Technical Foundations of MIS Module 2
Learning Outcome 6.1: Explain the four primary traits that determine the value of information.
Information is data converted into a meaningful and useful context. Information can tell an organization how its current operations are performing and help it estimate and strategize about how future operations might perform. It is important to understand the different levels, formats, and granularities of information along with the four primary traits that help deter- mine the value of information, which include (1) information type: transactional and analytical; (2) information timeliness; (3) information quality; (4) information governance.
Learning Outcome 6.2: Describe a database, a database management system, and the relational database model.
A database maintains information about various types of objects (inventory), events (trans- actions), people (employees), and places (warehouses). A database management system (DBMS) creates, reads, updates, and deletes data in a database while controlling access and security. A DBMS provides methodologies for creating, updating, storing, and retrieving data in a database. In addition, a DBMS provides facilities for controlling data access and secu- rity, allowing data sharing, and enforcing data integrity. The relational database model allows users to create, read, update, and delete data in a relational database.
Learning Outcome 6.3: Identify the business advantages of a relational database.
Many business managers are familiar with Excel and other spreadsheet programs they can use to store business data. Although spreadsheets are excellent for supporting some data analysis, they offer limited functionality in terms of security, accessibility, and flexibil- ity and can rarely scale to support business growth. From a business perspective, relational databases offer many advantages over using a text document or a spreadsheet, including increased flexibility, increased scalability and performance, reduced information redundancy, increased information integrity (quality), and increased information security.
Learning Outcome 6.4: Explain the business benefits of a data-driven website.
A data-driven website is an interactive website kept constantly updated and relevant to the needs of its customers using a database. Data-driven capabilities are especially useful when the website offers a great deal of information, products, or services because visitors are frequently annoyed if they are buried under an avalanche of information when searching a website. Many companies use the Web to make some of the information in their internal data- bases available to customers and business partners.
Learning Outcome 6.5: Define a data warehouse, and provide a few reasons it can make a manager more effective.
A data warehouse is a logical collection of information, gathered from many different opera- tional databases, that supports business analysis and decision making. The primary value of a data warehouse is to combine information, more specifically, strategic information, through- out an organization into a single repository in such a way that the people who need that infor- mation can make decisions and undertake business analysis.
Learning Outcome 6.6: Explain ETL and the role of a data mart in business.
Extraction, transformation, and loading (ETL) is a process that extracts information from internal and external databases, transforms it using a common set of enterprise definitions, and loads it into a data warehouse. The data warehouse then sends portions (or subsets) of
L E A R N I N G O U T C O M E R E V I E W
bal76825_ch06_201-240.indd 229bal76825_ch06_201-240.indd 229 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
Chapter 6 Data: Business Intelligence230
the information to data marts. A data mart contains a subset of data warehouse information. To distinguish between data warehouses and data marts, think of data warehouses as having a more organizational focus and data marts as having a functional focus.
Learning Outcome 6.7: Define data mining, and explain the three common forms for mining structured and unstructured data.
Data mining is the process of analyzing data to extract information not offered by the raw data alone. Data mining can also begin at a summary information level (coarse granular- ity) and progress through increasing levels of detail (drilling down), or the reverse (drilling up). Data mining occurs on structured data that are already in a database or a spreadsheet. Unstructured data do not exist in a fixed location and can include text documents, PDFs, voice messages, emails, and so on. Three common forms for mining structured and unstructured data are cluster analysis, association detection, and statistical analysis.
Learning Outcome 6.8: Identify the advantages of using business intelligence to support managerial decision making.
Many organizations today find it next to impossible to understand their own strengths and weaknesses, let alone their biggest competitors, due to enormous volumes of organizational data being inaccessible to all but the MIS department. Organization data include far more than simple structured data elements in a database; the set of data also includes unstructured data such as voice mail, customer phone calls, text messages, video clips, along with numerous new forms of data, such as tweets from Twitter. Managers today find themselves in the posi- tion of being data rich and information poor, and they need to implement business intelligence systems to solve this challenge.
O P E N I N G C A S E Q U E S T I O N S
1. Knowledge: List the reasons a business would need consistently updated, enterprisewide analytical capabilities.
2. Comprehension: Describe how Brinker’s is using business intelligence to reduce costs.
3. Application: Explain how the marketing department at Brinker’s could use a data mart to help with the release of a new product.
4. Analysis: Categorize the five common characteristics of high-quality information and rank them in order of importance to Carnival Cruise.
5. Synthesis: Develop a list of some possible entities and attributes located in a Chili’s restau- rant database.
6. Evaluate: Assess how Brinker is using BI to identify trends and change associated busi- ness processes.
K E Y T E R M S
Association detection, 226 Attribute 212 Business-critical integrity
constraint, 214 Cluster analysis, 225
Cube, 219 Data dictionary, 211 Data element (or data field), 210 Data governance, 210 Data inconsistency, 207
Data integrity issues, 207 Data mart, 219 Data mining, 222 Data model, 210 Data quality audit, 221
bal76825_ch06_201-240.indd 230bal76825_ch06_201-240.indd 230 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
231Technical Foundations of MIS Module 2
1. How does a database turn data elements into information?
2. Why does a business need to be concerned with the quality of its data?
3. How can data governance help protect a business from hackers?
4. Why would a company care about the timeliness of its data?
5. What are the five characteristics common to high-quality information?
6. What is data governance and its importance to a company?
7. What are the four primary traits that help determine the value of information?
8. What is the difference between an entity and an attribute?
9. What are the advantages of a relational database?
10. What are the advantages of a data-driven website?
11. What is a data warehouse and why would a business want to implement one?
12. Why would you need to use multidimensional analysis?
13. What is the purpose of information cleansing (or scrubbing)?
14. Why would a department want a data mart instead of just accessing the entire data warehouse?
15. Why would a business be data rich, but information poor?
Data redundancy, 214 Data warehouse, 218 Database, 210 Database management system
(DBMS), 210 Data-driven website, 215 Data-mining tool, 224 Entity, 211 Extraction, transformation, and
loading (ETL), 219 Forecasts, 226 Foreign key, 212 Information cleansing or
scrubbing, 220
Information granularity, 205 Information integrity, 214 Integrity constraint, 214 Logical view, 213 Market basket analysis, 226 Metadata, 211 Physical view, 213 Primary key, 212 Query-by-example (QBE)
tool, 210 Real-time information, 206 Real-time system, 206 Record, 212
Relational database manage- ment system, 211
Relational database model, 211 Relational integrity
constraint, 214 Statistical analysis, 226 Structured data, 221 Structured query language
(SQL), 210 Text mining, 224 Time-series information, 226 Unstructured data, 224 Web mining, 224
R E V I E W Q U E S T I O N S
C LO S I N G C A S E O N E
Data Visualization: Stories for the Information Age
At the intersection of art and algorithm, data visualization schematically abstracts informa- tion to bring about a deeper understanding of the data, wrapping it in an element of awe. While the practice of visually representing information is arguably the foundation of all design, a newfound fascination with data visualization has been emerging. After The New York Times and The Guardian recently opened their online archives to the public, artists rushed to dissect nearly two centuries worth of information, elevating this art form to new prominence.
bal76825_ch06_201-240.indd 231bal76825_ch06_201-240.indd 231 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
Chapter 6 Data: Business Intelligence232
For artists and designers, data visualization is a new frontier of self-expression, powered by the proliferation of information and the evolution of available tools. For enterprise, it is a platform for displaying products and services in the context of the cultural interaction that sur- rounds them, reflecting consumers’ increasing demand for corporate transparency.
“Looking at something ordinary in a new way makes it extraordinary,” says Aaron Koblin, one of the more recent pioneers of the discipline. As technology lead of Google’s Creative Labs in San Francisco, he spearheaded the search giant’s Chrome Experiments series designed to show off the speed and reliability of the Chrome browser.
Forget Pie Charts and Bar Graphs
Data visualization has nothing to do with pie charts and bar graphs. And it’s only marginally related to “infographics,” information design that tends to be about objectivity and clarifica- tion. Such representations simply offer another iteration of the data—restating it visually and making it easier to digest. Data visualization, on the other hand, is an interpretation, a different way to look at and think about data that often exposes complex patterns or correlations.
Data visualization is a way to make sense of the ever-increasing stream of information with which we’re bombarded and provides a creative antidote to the analysis paralysis that can result from the burden of processing such a large volume of information. “It’s not about clarify- ing data,” says Koblin. “It’s about contextualizing it.”
Today algorithmically inspired artists are reimagining the art-science continuum through work that frames the left-brain analysis of data in a right-brain creative story. Some use data visualization as a bridge between alienating information and its emotional impact—see Chris Jordan’s portraits of global mass culture. Others take a more technological angle and focus on cultural utility—the Zoetrope project offers a temporal and historical visualization of the ephemeral Web. Still others are pure artistic indulgence—like Koblin’s own Flight Patterns project, a visualization of air traffic over North America.
How Business Can Benefit
There are real implications for business here. Most cell phone providers, for instance, offer a statement of a user’s monthly activity. Most often it’s an overwhelming table of various numer- ical measures of how much you talked, when, with whom, and how much it cost. A visual representation of this data might help certain patterns emerge, revealing calling habits and perhaps helping users save money.
Companies can also use data visualization to gain new insight into consumer behavior. By observing and understanding what people do with the data—what they find useful and what they dismiss as worthless—executives can make the valuable distinction between what con- sumers say versus what they do. Even now, this can be a tricky call to make from behind the two-way mirror of a traditional qualitative research setting.
It’s essential to understand the importance of creative vision along with the technical mas- tery of software. Data visualization isn’t about using all the data available, but about deciding which patterns and elements to focus on, building a narrative, and telling the story of the raw data in a different, compelling way.
Ultimately, data visualization is more than complex software or the prettying up of spread- sheets. It’s not innovation for the sake of innovation. It’s about the most ancient of social ritu- als: storytelling. It’s about telling the story locked in the data differently, more engagingly, in a way that draws us in, makes our eyes open a little wider and our jaw drop ever so slightly. And as we process it, it can sometimes change our perspective altogether.13
Questions
1. Identify the effects poor information might have on a data visualization project. 2. How does data visualization use database technologies? 3. How could a business use data visualization to identify new trends?
bal76825_ch06_201-240.indd 232bal76825_ch06_201-240.indd 232 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
233Technical Foundations of MIS Module 2
4. What is the correlation between data mining and data visualization? 5. Is data visualization a form of business intelligence? Why or why not? 6. What security issues are associated with data visualization? 7. What might happen to a data visualization project if it failed to cleanse or scrub its data?
C LO S I N G C A S E T W O
Zillow
Zillow.com is an online Web-based real estate site helping homeowners, buyers, sellers, renters, real estate agents, mortgage professionals, property owners, and property managers find and share information about real estate and mortgages. Zillow allows users to access, anonymously and free of charge, the kinds of tools and information previously reserved for real estate professionals. Zillow’s databases cover more than 90 million homes, which repre- sents 95 percent of the homes in the United States. Adding to the sheer size of its databases, Zillow recalculates home valuations for each property every day, so they can provide histori- cal graphs on home valuations over time. In some areas, Zillow is able to display 10 years of valuation history, a value-added benefit for many of its customers. This collection of data rep- resents an operational data warehouse for anyone visiting the website.
As soon as Zillow launched its website, it immediately generated a massive amount of traf- fic. As the company expanded its services, the founders knew the key to its success would be the site’s ability to quickly process and manage massive amounts of data, in real time. The company identified a need for accessible, scalable, reliable, secure databases that would enable it to continue to increase the capacity of its infrastructure indefinitely without sacrific- ing performance. Zillow’s traffic continues to grow despite the weakening real estate market; the company is experiencing annual traffic growth of 30 percent and about a third of all U.S. mortgage professionals visit the site in a given month.
Data Mining and Business Intelligence
Zestimate values on Zillow use data-mining features for spotting trends across property valu- ations. Data mining also allows the company to see how accurate Zestimate values are over time. Zillow has also built the industry’s first search by monthly payment, allowing users to find homes that are for sale and rent based on a monthly payment they can afford. Along with the monthly payment search, users can also enter search criteria such as the number of bed- rooms or bathrooms.
Zillow also launched a new service aimed at changing the way Americans shop for mortgages. Borrowers can use Zillow’s new Mortgage Marketplace to get custom loan quotes from lenders without having to give their names, addresses, phone numbers, or Social Security numbers, or field unwanted telephone calls from brokers competing for their business. Borrowers reveal their identities only after contacting the lender of their choice. The company is entering a field of established mortgage sites such as LendingTree .com and Experian Group’s Lowermybills.com, which charge mortgage companies for bor- rower information. Zillow, which has an advertising model, says it does not plan to charge for leads.
For mortgage companies, the anonymous leads come free; they can make a bid based on information provided by the borrower, such as salary, assets, credit score, and the type of loan. Lenders can browse borrower requests and see competing quotes from other brokers before making a bid. 14
bal76825_ch06_201-240.indd 233bal76825_ch06_201-240.indd 233 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
Chapter 6 Data: Business Intelligence234
Questions
1. List the reasons Zillow would need to use a database to run its business. 2. Describe how Zillow uses business intelligence to create a unique product for its customers. 3. How could the marketing department at Zillow use a data mart to help with the release of
a new product launch? 4. Categorize the five common characteristics of high-quality information and rank them in
order of importance to Zillow. 5. Develop a list of some possible entities and attributes of Zillow’s mortgage database. 6. Assess how Zillow uses a data-driven website to run its business.
C R I T I C A L B U S I N E S S T H I N K I N G
1. Information—Business Intelligence or a Diversion from the Truth? President Obama used part of his commencement address at Virginia’s Hampton University last year to criticize the flood of incomplete information or downright incorrect information that flows in the 24-hour news cycle. The president said, “You’re coming of age in a 24/7 media environment that bombards us with all kinds of content and exposes us to all kinds of arguments, some of which don’t always rank all that high on the truth meter. With iPods and iPads and Xboxes and PlayStations—none of which I know how to work—information becomes a distraction, a diversion, a form of entertainment, rather than a tool of empow- erment, rather than the means of emancipation.” 15
Do you agree or disagree with President Obama’s statement? Who is responsible for verifying the accuracy of online information? What should happen to companies that post inaccurate information? What should happen to individuals who post inaccurate informa- tion? What should you remember when reading or citing sources for online information?
2. Illegal Database Access Goldman Sachs has been hit with a $3 million lawsuit by a company that alleges the bro- kerage firm stole intellectual property from its database that had market intelligence facts. The U.S. District Court for the Southern District of New York filed the lawsuit in 2010 claim- ing Goldman Sachs employees used other people’s access credentials to log into Ipreo’s proprietary database, dubbed Bigdough. Offered on a subscription basis, Bigdough pro- vides detailed information on more than 80,000 contacts within the financial industry. Ipreo complained to the court that Goldman Sachs employees illegally accessed Bigdough at least 264 times in 2008 and 2009. 16
Do you agree or disagree with the lawsuit? Should Goldman Sachs be held responsible for rogue employees’ behavior? What types of policies should Goldman Sachs implement to ensure this does not occur again?
3. Data Storage Information is one of the most important assets of any business. Businesses must ensure information accuracy, completeness, consistency, timeliness, and uniqueness. In addition, business must have a reliable backup service. In part thanks to cloud computing, there are many data hosting services on the Internet. These sites offer storage of information that can be accessed from anywhere in the world.
These data hosting services include Hosting ( www.hosting.com ), Mozy ( www.mozy .com ), My Docs Online ( www.mydocsonline.com ), and Box ( www.box.net ). Visit a few of these sites along with a several others you find through research. Which sites are free? Are there limits to how much you can store? If so, what is the limit? What type of informa- tion can you store (video, text, photos, etc.)? Can you allow multiple users with different
bal76825_ch06_201-240.indd 234bal76825_ch06_201-240.indd 234 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
235Technical Foundations of MIS Module 2
passwords to access your storage area? Are you contractually bound for a certain dura- tion (annual, etc.)? Are different levels of services provided such as personal, enterprise, work group? Does it make good business sense to store business data on the Internet? What about personal data?
4. Gathering Business Intelligence When considering new business opportunities, you need knowledge about the competi- tion. One of the things many new business owners fail to do is to gather business intel- ligence on their competitors, such as how many there are and what differentiates each of them. You may find there are too many and that they would be tough competition for you. Or, you may find that there are few competitors and the ones who are out there offer very little value.
Generate a new business idea you could launch on the Internet. Research the Internet to find similar business in the area you have chosen. How many sites did you find that are offer- ing the same products or services you are planning to offer? Did you come across any sites from another country that have a unique approach that you did not see on any of the sites in your own country? How would you use this information in pursuing your business idea?
5. Free Data! The U.S. Bureau of Labor Statistics states that its role is as the “principal fact-finding agency for the federal government in the broad field of labor economics and statistics.” And the data that the bureau provides via its website are available to anyone, free. This can represent a treasure trove of business intelligence and data mining for those who take advantage of this resource. Visit the website www.bls.gov . What type of information does the site provide? What information do you find most useful? What sort of informa- tion concerning employment and wages is available? How is this information categorized? How would this type of information be helpful to a business manager? What type of demo- graphic information is available? How could this benefit a new start-up business? 17
6. Explaining Relational Databases You have been hired by Vision, a start-up clothing company. Your manager, Holly Henningson, is unfamiliar with databases and their associated business value. Henningson has asked you to create a report detailing the basics of databases. She would also like you to provide a detailed explanation of relational databases along with their associated busi- ness advantages.
7. Entities and Attributes Martex Inc. is a manufacturer of athletic equipment, and its primary lines of business include running, tennis, golf, swimming, basketball, and aerobics equipment. Martex cur- rently supplies four primary vendors including Sam’s Sports, Total Effort, The Underline, and Maximum Workout. Martex wants to build a database to help it organize its products. In a group, identify the different types of entities, attributes, keys, and relationships Martex will want to consider when designing its relational database.
8. Compiling Information You are currently working for the Public Transportation Department of Chatfield. The depart- ment controls all forms of public transportation, including buses, subways, and trains. Each department has about 300 employees and maintains its own accounting, inventory, pur- chasing, and human resource systems. Generating reports across departments is a difficult task and usually involves gathering and correlating the information from the many different databases. It typically takes about two weeks to generate the quarterly balance sheets and profit and loss statements. Your team has been asked to compile a report recommending what the Public Transportation Department of Chatfield can do to alleviate its information and system issues. Be sure that your report addresses the various reasons departmental reports are presently difficult to obtain as well as how you plan to solve this problem. 18
bal76825_ch06_201-240.indd 235bal76825_ch06_201-240.indd 235 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
Chapter 6 Data: Business Intelligence236
9. Information Timeliness Information timeliness is a major consideration for all organizations. Organizations need to decide the frequency of backups and the frequency of updates to a data warehouse. In a team, describe the timeliness requirements for backups and updates to a data warehouse for each of the following:
■ Weather tracking systems. ■ Car dealership inventories. ■ Vehicle tire sales forecasts. ■ Interest rates. ■ Restaurant inventories. ■ Grocery store inventories.
10. Improving Information Quality HangUps Corporation designs and distributes closet organization structures. The com- pany operates five systems—order entry, sales, inventory management, shipping, and billing. The company has severe information quality issues including missing, inaccurate, redundant, and incomplete information. The company wants to implement a data ware- house containing information from the five different systems to help maintain a single cus- tomer view, drive business decisions, and perform multidimensional analysis. Identify how the organization can improve its information quality when it begins designing and building its data warehouse.
E N T R E P R E N E U R I A L C H A L L E N G E
BUILD YOUR OWN BUSINESS
Project Focus
1. Provide an example of your business data that fits each of the five common characteris- tics of high-quality information. Explain why each characteristic is important to your busi- ness data and what might happen if your business data were of low quality. (Be sure to identify your business and the name of your company.)
2. Identify the different entities and their associated attributes that would be found in your potential relational database model for your sales database.
3. Identify the benefits of having a data warehouse for your business. What types of data marts would you want to extract from you data warehouse to help you run your business and make strategic decisions.
A P P LY YO U R K N O W L E D G E B U S I N E S S P R O J E C T S
PROJECT I Mining the Data Warehouse Alana Smith is a senior buyer for a large wholesaler that sells different types of arts and crafts to greeting card stores such as Hallmark. Smith’s latest marketing strategy is to send all of her customers a new line of handmade picture frames from Russia. All of her information supports her decision for the new line. Her analysis predicts that the frames should sell an
bal76825_ch06_201-240.indd 236bal76825_ch06_201-240.indd 236 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
237Technical Foundations of MIS Module 2
average of 10 to 15 per store, per day. Smith is excited about the new line and is positive it will be a success.
One month later Smith learns the frames are selling 50 percent below expectations and averaging between five and eight frames sold daily in each store. She decides to access the company’s data warehouse information to determine why sales are below expectations. Identify several different dimensions of information that Smith will want to analyze to help her decide what is causing the problems with the picture frame sales.
PROJECT I I Different Dimensions The focus of data warehousing is to extend the transformation of data into information. Data warehouses offer strategic level, external, integrated, and historical information so busi- nesses can make projections, identify trends, and make key business decisions. The data warehouse collects and stores integrated sets of historical information from multiple opera- tional systems and feeds them to one or more data marts. It may also provide end user access to support enterprisewide views of information.
Project Focus You are currently working on a marketing team for a large corporation that sells jewelry around the world. Your boss has asked you to look at the following dimensions of data to determine which ones you want in your data mart for performing sales and market analysis (see Figure AYK.1 ). As a team, categorize the different dimensions ranking them from 1 to 5, with 1 indicating that the dimension offers the highest value and must be in your data mart and 5 indicating that the dimension offers the lowest value and does not need to be in your data mart.
FIGURE AYK.1
Data Warehouse Data
Dimension Value (1–5) Dimension Value (1–5)
Product number Season
Store location Promotion
Customer net worth Payment method
Number of sales personnel Commission policy
Customer eating habits Manufacturer
Store hours Traffic report
Salesperson ID Customer language
Product style Weather
Order date Customer gender
Product quantity Local tax information
Ship date Local cultural demographics
Current interest rate Stock market closing
Product cost Customer religious affiliation
Customer’s political affiliation Reason for purchase
Local market analysis Employee dress code policy
Order time Customer age
Customer spending habits Employee vacation policy
Product price Employee benefits
Exchange rates Current tariff information
Product gross margin
bal76825_ch06_201-240.indd 237bal76825_ch06_201-240.indd 237 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
Chapter 6 Data: Business Intelligence238
PROJECT I I I Understanding Search Pretend that you are a search engine. Choose a topic to query. It can be anything such as your favorite book, movie, band, or sports team. Search your topic on Google, pick three or four pages from the results, and print them out. On each printout, find the individual words from your query (such as “Boston Red Sox” or “The Godfather”) and use a highlighter to mark each word with color. Do that for each of the documents that you print out. Now tape those docu- ments on a wall, step back a few feet, and review your documents.
Project Focus If you did not know what the rest of a page said and could judge only by the colored words, which document do you think would be most relevant? Is there anything that would make a document look more relevant? Is it better to have the words be in a large heading or to occur several times in a smaller font? Do you prefer it if the words are at the top or the bottom of the page? How often do the words need to appear? Come up with two or three things you would look for to see if a document matched a query well. This exercise mimics search engine processes and should help you understand why a search engine returns certain results over others.
PROJECT IV Predicting Netflix Netflix Inc., the largest online movie rental service, provides more than 12 million subscribers access to more than 100,000 unique DVD titles along with a growing on-demand library in excess of 10,000 choices. Data and information are so important to Netflix that it created The Netflix Prize, an open competition for anyone who could improve the data used in prediction ratings for films (an increase of 10 percent), based on previous ratings. The winner would receive a $1 million prize.
Project Focus The ability to search, analyze, and comprehend information is vital for any organization’s suc- cess. It certainly was for Netflix, as it was happy to pay anyone $1 million to improve the qual- ity of its information. In a group explain how Netflix might use databases, data warehouses, and data marts to predict customer movie recommendations. Here are a few characteristics you might want to analyze to get you started:
■ Customer demographics. ■ Movie genre, rating, year, producer, type. ■ Actor information. ■ Internet access. ■ Location for mail pickup.
PROJECT V The Crunch Factory The Crunch Factory is one of the fourth-largest gyms operating in Australia, and each gym operates its own system with its own database. Unfortunately, the company failed to develop any data-capturing standards and now faces the challenges associated with low-quality enterprisewide information. For example, one system has a field to capture email addresses while another system does not. Duplicate customer information among the different systems is another major issue, and the company continually finds itself sending conflicting or competing messages to customers from different gyms. A customer could also have multiple accounts
bal76825_ch06_201-240.indd 238bal76825_ch06_201-240.indd 238 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
239Technical Foundations of MIS Module 2
within the company, one representing a membership, another representing additional classes, and yet another for a personal trainer. The Crunch Factory has no way to identify that the dif- ferent customer accounts are actually for the same customer.
Project Focus To remain competitive and be able to generate business intelligence The Crunch Factory has to resolve these challenges. The Crunch Factory has just hired you as its data quality expert. Your first task is to determine how the company can turn its low-quality information into high-quality business intelligence. Create a plan that The Crunch Factory can implement that details the following:
■ Challenges associated with low-quality information. ■ Benefits associated with high-quality information. ■ Recommendations on how the company can clean up its data.
PROJECT VI Too Much of a Good Thing The Castle, a premium retailer of clothes and accessories, created an enterprisewide data warehouse so all its employees could access information for decision making. The Castle soon discovered that it is possible to have too much of a good thing. The Castle employees found themselves inundated with data and unable to make any decisions, a common occur- rence called analysis paralysis. When sales representatives queried the data warehouse to determine if a certain product in the size, color, and category was available, they would get hundreds of results showing everything from production orders to supplier contracts. It became easier for the sales representatives to look in the warehouse themselves than to check the system. Employees found the data warehouse was simply too big, too complicated, and contained too much irrelevant information.
Project Focus The Castle is committed to making its data warehouse system a success and has come to you for help. Create a plan that details the value of the data warehouse to the business, how it can be easier for all employees to use, along with the potential business benefits the company can derive from its data warehouse.
PROJECT VI I Twitter Buzz Technology tools that can predict sales for the coming week, decide when to increase inven- tory, and determine when additional staff is required are extremely valuable. Twitter is not just for tweeting your whereabouts anymore. Twitter and other social-media sites have become great tools for gathering business intelligence on customers, including what they like, dislike, need, and want. Twitter is easy to use, and businesses can track every single time a customer makes a statement about a particular product or service. Good businesses turn this valuable information into intelligence spotting trends and patterns in customer opinion.
Project Focus Do you agree that a business can use Twitter to gain business intelligence? How many com- panies do you think are aware of Twitter and exactly how they can use it to gain BI? How do you think Twitter uses a data warehouse? How do you think companies store Twitter informa- tion? How would a company use Twitter in a data mart? How would a company use cubes to analyze Twitter data?
bal76825_ch06_201-240.indd 239bal76825_ch06_201-240.indd 239 11/15/10 3:23 PM11/15/10 3:23 PM
Confirming Pages
Chapter 6 Data: Business Intelligence240
If you are looking for Access projects to incorporate into your class, try any of the following after reading this chapter.
Project Number
Project Name
Project Type Plug-In
Focus Area
Project Level
Skill Set
Page Number
28 Daily Invoice Access T5, T6, T7, T8 Business Analysis
Introductory Entities, Relationships, and Databases
AYK.17
29 Billing Data Access T5, T6, T7, T8 Business Intelligence
Introductory Entities, Relationships, and Databases
AYK.19
30 Inventory Data Access T5, T6, T7, T8 SCM Intermediate Entities, Relationships, and Databases
AYK.20
31 Call Center Access T5, T6, T7, T8 CRM Intermediate Entities, Relationships, and Databases
AYK.21
32 Sales Pipeline Access T5, T6, T7, T8 Business Intelligence
Advanced Entities, Relationships, and Databases
AYK.23
33 Online Classified Ads
Access T5, T6, T7, T8 Ecommerce Advanced Entities, Relationships, and Databases
AYK.23
AY K A P P L I C AT I O N P R O J E C T S
bal76825_ch06_201-240.indd 240bal76825_ch06_201-240.indd 240 11/15/10 3:23 PM11/15/10 3:23 PM
