13 - Organizational Intelligence

Answers to exercises

Identify data captured by a TPS at your university. Estimate how much data are generated in a year.

A TPS at a university may be the payroll system for the faculty and staff or the student grade system. For each system you would need to determine the number of entries per year, and the size of the fields contained in each entry. For instance, consider a simple payroll system where the data generated each pay period are just the social security number and the amount paid per employee. The social security number is a 9 digit field and the amount paid might be an 8 digit field. Given these dimensions and an employee count of 2,000, the amount of data generate each year would be :

  1. (9+8) * 2000 * 26 = 884,000

This is a very abbreviated look at a system, other fields would be included in most systems, and it is worth revisiting the data model on page 173 to get some ideas of these other fields.

If your university were to implement a data warehouse, what examples of dirty data might you expect to find?

Dirty data that may be found in a university's database system include:

Write five data verification questions for a university data warehouse.

Examples of verification questions:

Imagine you work as an analyst for a major global auto manufacturer. What techniques would you use for the following questions?
How do sports car buyers differ from other customers?
A discovery question using data mining.
Where does our major competitor have its dealers? d. How much money is a dealer likely to make from servicing a customer who buys a luxury car?
Visualization. An SQL query will give a list of the locations, but a map (visualization) is likely to give you a better feel for the competitor's impact.
What do people who buy midsize sedans have in common?
Find classification characteristics of people who buy midsize sedans.
Who are the most likely prospects to buy a luxury car?
Classify customers into luxury and non-luxury car buyers, and then examine those attributes that discriminate between the two groups.
We know a great deal about the sort of car a customer will buy based on demographic data (e.g., age, number of children, and type of job). What is a simple visual aid we can provide to sales personnel to help them show customers the car they are most likely to buy?
Decision tree. By asking a series of questions based on the decision tree, a salesperson could identify the car a person is most likely to buy.

A telephone company needs your advice on the data it should include in its multidimensional database. It has an extensive relational database that captures details (e.g., calling and called phone numbers, time of day, cost, length of call) of every call. As well, it has access to extensive demographic data so it can allocate customers to one of 50 lifestyle categories. What data would you load into the multidimensional database? What aggregations would you use? It might help to identify initially the identifier and variable dimensions (use Table 15-6 on page 450).

Identifiers When Time of day, day of week, type of day (e.g., holiday)
Where Location of calling and called phone
What Regular call, emergency, toll-free
How Cell or land line
Who Residential, business, family, lifestyle category
Variables Outcomes


Download the file exped.xls from the book's Web site and open it with MS Excel. This file is a sample of 1,000 sales transactions for The Expeditioner. For each sale, there is a row recording when it was sold, where it was sold, what was sold, how it was sold, the quantity sold, and the sales revenue. Use the PivotTable Wizard (Data>PivotTable Report) to produce the following report:
WHERE Catalog Store Web Grand Total
50,310 151,015 13,009 214,334
New York 8,712 28,060 2,351 39,123
Paris 32,166 104,083 7,054 143,303
Sydney 5,471 21,769 2,749 29,989
Tokyo 12,103 42,610 2,003 56,716
Grand Total 108,762 347,537 27,166 483,465

Continue to use the PivotTable Wizard to answer the following questions:

What was the value of catalog sales for London in the first quarter?
7,810 (assuming first quarter is Jan.- March)
What percent of Sydney's annual sales were catalog sales?


What was the value of Camel saddle sales for Paris in 2002 by quarter?

This page is part of the promotional and support material for Data Management (sixth edition) by Richard T. Watson
For questions and comments please contact the author

Date revised: 19-Oct-2016