Case Assignment #1: Building Applichems

Information System Infrastructure

(This case is adapted from the Applichem (A) (Abridged) HBS Case 9-694-030)
The Unpleasant Meeting
Steve Chu, Vice President of Research and Engineering, came out of the meeting with Richard Ellison,
Chairman of the Board, and Joseph Dunn, CEO of Applichem, fuming because he had been made the
scapegoat of stagnation in the company performance and lack of information system (IS) infrastructure. A
recent study by the board has highlighted various problems in the company, which were discussed in
detail in the meeting. They were still resonating in his head:
1. Complacent management at existing plants.
2. Huge time delay in obtaining manufacturing information from the existing plants and
databases because all plants are silos and the database operator has to download the
information through manual queries, summarize the results, and send them to the head office.
3. Production at each plant is done almost independently without taking into account other
plants capacities and exchange rates.
4. Fluctuating exchange rates between countries affecting profitability.
He has been given a one-month time frame to come up with an implementable IS infrastructure plan.
Steve Chu immediately reached out to his friend Tim Wright at Trojan Business Intelligence Consulting
to study the problems and come up with an implementable IS infrastructure plan. Tim Wright has done
outstanding work in the area of business intelligence (BI) and in merging data sources, improving IT
capabilities, and re-engineering processes.
Steve Chu wants to use Tim Wright and his plan as a driver of organizational transformation, and to
realize the lofty goal of making Applichem a real-time, agile corporation.

BUAD 425 Case1

Page 1 of 9

Nevertheless, Steve realizes that many challenges remain. The IT staff under his leadership is still
learning how to develop technology in cooperation with business users. The silo nature of the current
database structure has not benefited the company. After multiple discussions with Tim Wright, Steve has
been able to understand the need for business intelligence tools and why this is a necessary but not
insufficient resource to move Applichem forward.
Steve Chu has given Tim Wright unfettered access to all levels of management and has asked all the four
plant managers to provide access to their databases. Chu has also asked Wright to build an executive-level
dash board for the CEO so that he can make informed decisions.
Tim Wrights Initial Findings
Tim Wrights discussion with Applichems plant managers helped him to understand that it is a
manufacturer of specialty chemicals and their flagship product is Release Ease, specialty chemical
manufactured at each Applichem plant, which it sells to its existing customer base directly.
They have four manufacturing plants in four regions of the worldNorth America, Europe, Latin
America, and the Pacific and the rest of the world.
Exhibit 1: Plants
Region

Name of the Plant

North America
Europe
Latin America
Pacific & rest of the world

Gary
Frankfurt
Mexico
SunChem

The Release Ease specialty chemical can be shipped from the four plants to four major markets (the four
regions are the four major markets). Currently, each plant is serving its own region, and the plants are not
running at full capacity. The plants have capacity constraints, and the amount to be produced at each plant
depends on raw material cost, operating cost, shipping cost, and exchange rate.
From each plant, the following information is gathered daily and is stored in the local database:
Exhibit 2: Variables
Number

BUAD 425 Case1

1
2
3
4
5
6
7
8

Raw Variables
Date
Plant
Number of Workers Direct
Number of Workers Indirect
Labor Cost of Indirect Labor (in local currency)
Labor Cost of Direct Labor (in local currency)
Plant Output (in million pounds of Release Ease chemical)
Plant Raw Material Used (in million pounds)
Page 2 of 9

Tim Wrights Preliminary Conclusion

For successful operation of Applichem, the scattered information has to be consolidated, and the top
management should get the relevant key performance indicators (KPIs) on the fly; the unstructured data is
hard to understand, and manual queries are resulting in huge delays in decision making. The current raw
data is not integrated and thus is not actionable; it does not provide any business insights nor trends and
patterns. For management to improve the operations of the plants and operate them efficiently it has to
convert the data into actionable form and structure the data into meaningful form, so that management can
obtain meaningful KPI, understand the patterns and trends, maximize the utilization rates of the plants,
and maximize profit for the company.
Teaching the plant managers the elements of the business intelligence tools
Tim Wrights next task was to educate plant managers and upper management in the elements of business
intelligence tools and make them realize the importance of BI tools in Applichem. As part of the training,
he collected the following information and presented it to management.
Data Warehouse
A subject-oriented, integrated, time-variant, and non-volatile collection of data in support of
managements decision-making process (Inmom, 1993).
Subject Oriented: A data warehouse is organized around high-level business groupings called
subjects.
! Integrated: The data in the warehouse must be integrated and consistent. That is, if two different
source systems store conflicting data about entities, or attributes of an entity, the differences need
to be resolved during the process of transforming the source data and loading it into the data
warehouse.
! Time Variant: One of the key characteristics distinguishing warehouses from operational
environments is the currency of the data. Operational systems require real-time views of the data.
Data warehouse applications generally deal with longer-term, historical data. They can also
provide access to a greater volume of more detailed information, as required, over the longer time
period.
! Non-Volatile: The contents of online transaction processing (OLTP) systems are, by their nature,
continuously changing. Inserts, deletes, and updates form the basis of a large volume of business
transactions that result in a very volatile set of data. By contrast, data warehouses are static. The
data in the warehouse are read-only; update or refresh of the data occurs on a periodic
incremental or full-refresh basis.
!

Star Schema
A star schema consists of fact tables and dimension tables. Fact tables contain the quantitative or factual
data about a businessthe information being queried. This information is often numerical, additive
measurements and can consist of many columns and millions or billions of rows. Dimension tables are
usually smaller and hold descriptive data that reflect the dimensions, or attributes, of a business.
Structured Query Language (SQL) queries then use joins between fact and dimension tables and
constraints on the data to return selected information.
Fact and dimension tables differ from each other only in their use within a schema. Their physical
structure and the SQL syntax used to create the tables are the same. In a complex schema, a given table

BUAD 425 Case1

Page 3 of 9

can act as a fact table under some conditions and as a dimension table under others. The way in which a
table is referred to in a query determines whether a table behaves as a fact table or a dimension table.
Even though they are physically the same type of table, it is important to understand the difference
between fact and dimension tables from a logical point of view. To demonstrate the difference between
fact and dimension tables, consider how an analyst looks at business performance:
A salesperson analyzes revenue by customer, product, market, and time period.
A financial analyst tracks actuals and budgets by line item, product, and time period.
A marketing person reviews shipments by product, market, and time period.

The factswhat is being analyzed in each caseare revenue, actual, budgets, and shipments. These
items belong in fact tables. The business dimensionsthe by itemsare product, market, time period, and
line item. These items belong in dimension tables.
For example, a fact table in a sales database, implemented with a star schema, might contain the sales
revenue for the products of the company from each customer in each geographic market over a period of
time. The dimension tables in this database define the customers, products, markets, and time periods
used in the fact table.
A well-designed schema provides dimension tables that allow a user to browse a database to become
familiar with the information in it and then to write queries with constraints so that only the information
that satisfies those constraints is returned from the database.
Exhibit 3: Sample Star Schema

BUAD 425 Case1

Page 4 of 9

Extract, transform, load (ETL) tools

Data from diverse sources are cleaned, rendered consistent, and loaded into the data warehouse by
means of an ETL tool.
Reports, Dashboards, Scorecards, and Alerts1
From the point of view of most business users, business intelligence is about asking business questions
and getting timely answers. There are a number of interfaces by which BI can satisfy their needs.
Reports can be daily, weekly, monthly, or on-demand documents that provide information in a predefined format. Dashboards and scorecards represent simple interfaces that allow users to monitor a small
set of key performance indicators (KPIs) in near real time via a computer screen, Web page, or mobile
device. Alerts are software programs that
monitor KPIs and send an e-mail or some other kind of
message when a condition is met, such as a KPI falling below acceptable levels.
Analytics/Cubes2
In addition to pre-defined reports, dashboards, scorecards, and alerts, business users also need to be able
to ask one-off, ad hoc queries of the data, and receive answers on the fly. Some of the tools for this
include data marts and online analytical processing (OLAP) cubes, bounded subsets of data prepared for
specific types of questions, which can be queried much faster than the entire data warehouse. Power users
and professional analysts have always demanded these capabilities, but the trend in BI software design is
to try to make them more accessible to common users. Reports, dashboards, scorecards, alerts, and
analytics interfaces like OLAP cubes serve different goals for different users. They may serve operational,
tactical, and strategic purposes for everyone from line managers to executives, casual users to
professional analysts.
Exhibit 4: The BI continuum

BUAD 425 Case1

Page 5 of 9

Designing Key Performance Indicators (KPIs) for Applichem

Business intelligence3 is based on the philosophy that what gets measured, gets done, and BI
capabilities are often described in terms of key performance indicators, or KPIs. KPIs are more than just
measurements; they are measurements that embody strategic objectives. They help executives to evaluate
performance but also to communicate strategic and tactical goals to their departments. For IT, the
challenge was figuring out how the data could be collected, but for the projects business participants an
equally important question was what ought to be measured, and when the data need to be available.
Tim Wright wants to show the management what can be done with integration of data and with
appropriate KPIs and charts, so that the plant managers and executive management can be empowered to
make informed decisions. He has come up with the following KPIs and charts that will help the company
to get business insights and make informed decisions.
Of the several KPIs implemented to date, four in particular represent important milestones in the
evolution of the business intelligence project, as follows.
A. Cost per Thousand Pounds of Release Ease Produced
One of the most important KPIs for manufacturing companies is cost per unit produced (CPP). It is
simple divisionthe total cost per plant divided by the amount of Release Ease produced. The challenge
is to get the right time period for calculating it. Different levels of management will need different time
BUAD 425 Case1

Page 6 of 9

period calculations, and executive management will want this number in a common currency (USD) for
easy comparison.
B. Average Output (in thousand pounds of Release Ease) per Time Period
Applichem has known for a long time that the production capacities at its plants are affected seasonally
due to various factors. Quantifying these factors will help them to better plan and meet the demand
internationally rather than meeting the demand locally. Again, it is simple divisionthe output per plant
divided by the given time period. The challenge is to get the right time period for calculating it, and
because different levels of management will need different time-period calculations, having a time-series
plot of the four plants will help them to understand the seasonality effects.
C. Average Output (in thousand pounds of Release Ease) per Worker
Applichems plants at different locations do not have the same efficiency rate. Some are labor intensive
and some are not. Also, there are two types of workers in each plantdirect and indirect workers.
Quantifying the output per worker will help the plant managers to plan scheduling better and meet
demand without delays. Once again, it is simple divisionthe output per plant divided by the number of
workers in a given time period. The challenge is to get the right time period for calculating it, and because
different levels of management will need different time-period calculations, having a time-series plot of
the four plants will help them to understand the seasonality effects.
D. Average Input (in thousand pounds of raw materials) per Time Period
Since Applichem plants at different locations do not have the same production efficiency rate, quantifying
these factors will help them to better plan sourcing of raw materials and to meet the demand
internationally. It is simple divisionthe input per plant divided by the given time period. The challenge
is to get the right time period for calculating it, and because different levels of management will need
different time-period calculations, having a time-series plot of the four plants will help them to understand
the seasonality effects.
The following table contains information that might be useful in your case analysis.

Yearly Average
Exchange Rates for 1 USD

Cost of Raw
Material
in USD per one
thousand pounds
of raw materials

Operating Cost
(Excluding Labor) in
USD per one thousand
pounds of Release Ease
produced

Country

Curre
ncy

2010

2009

Country

Cost

Country

Cost

Euro
Zone

Euro

0.755

0.719

Mexico

700

Mexico

110

BUAD 425 Case1

Page 7 of 9

Japan

Yen

87.829

93.617

Frankfurt

530

Frankfurt

90

Mexico

Peso

12.645

13.518

Gary

608

Gary

150

Sunchem

918

Sunchem

400

The actual cost is approximated by summing the cost of raw material, labor cost, and operating costs.
Case Questions (70 Points) [Please complete part 1 through 7 individually. If your team is assigned
case I, also finish part 8 in team.]
1. (10 points) For KPI #1 (Cost per Thousand Pounds of Release Ease Produced), provide the
following information:
a. A pivot table showing the average value of the KPI in each month of the year
b. Does the average cost change over across different months?
c. Are there differences in the cost across the four plants? Which plant consistently has the
lowest cost? Which one has the highest cost? Show a plot of the average cost over time
for each plant.
2. (10 points) For KPI #2 (Average Output per Time Period), provide the following information:
a. A pivot table showing the average value of the KPI in each month of the year
b. Does the average output change over across different months?
c. Are there differences in the average output across the four plants? Which plant
consistently has the lowest output? Which one has the highest output? Show a plot of the
average cost over time for each plant.
3. (10 points) For KPI #3 (Average Output per Worker), provide the following information:
a. A pivot table showing the average value of the KPI in each month of the year
b. Does the average output per worker change over across different months?
c. Are there differences in the average output per worker across the four plants? Which
plant consistently has the lowest output per worker? Which one has the highest output per
worker? Show a plot of the average cost over time for each plant.
4. (10 points) For KPI #4 (Average Input per Time Period), provide the following information:
a. A pivot table showing the average value of the KPI in each month of the year
b. Does the average input change over across different months?
c. Are there differences in the average input across the four plants? Which plant
consistently has the lowest input? Which one has the highest input? Show a plot of the
average cost over time for each plant.
5. (10 points) As we have done in the lab, create a KPI Chart (aka Radar Chart) showing the average
of the 4 KPIs for each of the plant. For graphing purposes, you might want to rescale the values
so that all four KPIs have comparable magnitude.
6. (10 points) Consider the total outputs (in thousand pounds of Release Ease product) for each plant
during each month of 2010.

BUAD 425 Case1

Page 8 of 9

a. Create a Dashboard in Excel for a production manager showing the Trendline and
Above-Below Average Sparkline Charts Your dashboard should indicate the movement
of each data point using Conditional Formatting in Excel, using appropriate icon set.
b. What insights can be obtained from the dashboard that youve created? Do see any
pattern?
7. (10 points) Tim Wright has decided to create a Star Schema with 1 FACT table containing data
about the outputs, inputs, raw materials, and wages. He has determined that Applichem should
create 3 DIMENSION tables: Plant Dimension, Time Dimension, and Worker Dimension. Based
on the case information, what variables would you put in each of the three DIMENSION tables?
8. Possible extension for team analysis. Please choose one of the following topics to extend the
analysis.
a. What other solutions are possible to overcome the IT infrastructure problem and
Company lack of communication culture.
b. Based on the data set, besides the current KPIs, do you recommend any other measure
that will get valuable information from the data?
Conclusion:
Gathering and integrating data from this extended enterprise to create a successful business intelligence
infrastructure is no easy task, and it has been an ongoing technical challenge. It is also an organizational
challenge, as the control of information is intimately tied up with decision-making authority. For the first
time, Applichem has realized the importance of information system infrastructure. Building a proper IS
infrastructure with an appropriate business intelligence decision-making tool will help them to realize
their hidden strengths and weaknesses and to optimize their resources and maximize the value of their
firm.
Reference:
1. Joseph Clark. Business Intelligence at Guthy-Renker: The Promise and Challenges of Sensing the
Pulse. V 1.2. Los Angeles: Marshall School of Business, 2010, P. 14.
2. Joseph Clark. Business Intelligence at Guthy-Renker: The Promise and Challenges of Sensing the
Pulse. V 1.2. Los Angeles: Marshall School of Business, 2010, P. 15.
3. Joseph Clark. Business Intelligence at Guthy-Renker: The Promise and Challenges of Sensing the
Pulse. V 1.2. Los Angeles: Marshall School of Business, 2010, P. 19.

BUAD 425 Case1

Page 9 of 9