Esra Vural

Holger Günther

CiteSeerX

 
Volume VII, No. 2, 2006 29 Issues in Information Systems 
BUSINESS INTELLIGENCE FOR A SUPPLY CHAIN MANAGEMENT SYSTEM 
 
Esra Vural, University of Cologne, esra.v@arcor.de 
Özgür Sengül, University of Cologne, ozgur.sengul@gmx.de 
Steve Davis, Clemson University, davis@clemson.edu 
 Holger Günther, University of Cologne, guenther@gm.fh-koeln.de 
 
 
ABSTRACT 
 
Business intelligence is becoming more necessary for 
supply chain managers to make good decisions. 
Because software tools are becoming less expensive, 
it is more practical to set up analysis capabilities for 
supply chain information, but there is little available 
guidance on how to design these capabilities. 
Therefore we present an example based on real 
supply chain software that illustrates a worthwhile 
approach. 
 
Keywords: Information Systems, Business 
Intelligence, Supply Chain Management 
 
INTRODUCTION 
 
Business Intelligence (BI) is a relatively new term for 
the development of knowledge about the enterprise. 
BI systems typically support querying, reporting, and 
multidimensional analysis of company data. It could 
be considered a performance management framework 
that helps companies set their goals, analyze their 
progress, gain insight, take action, and measure their 
success. BI is especially relevant to management of 
supply chains, an issue that has become increasingly 
important in commerce [2, 3, 8]. Because there is 
little available guidance on how to set up business 
intelligence capabilities for supply chain 
management, this paper presents a practical approach 
based on an application to a real supply chain.  
 
Organizations often employ BI tools to assess the 
business environment in ways such as marketing 
research [4], competitor analysis [1], business 
process reengineering [6], and customer relationship 
management [5]. In this paper we are interested in 
using BI to assess performance of an organization, 
specifically with respect to supply chain issues. 
 
There are more than two dozen commercial tools for 
business intelligence, some of which are appropriate 
for supply chain management [9]. For example 
Ottawa-based Cognos is an industry leader, and they 
address performance measurement, the focus of this 
paper [7].  
 
Some organizations have hesitated to develop BI 
systems because of the high cost and complexity of 
software tools. For example, a recent study found the 
average 3-year cost of a Cognos deployment was 
almost $1 million, including the costs of software 
licenses, software maintenance, consulting, 
personnel, and training [7].  
 
 Recently lower cost alternatives have become 
available. For example, the project reported here 
employed the BI tools built in to SQL Server 2005. 
To a company who uses this database management 
software already, there is little marginal cost for 
employing the BI features and no need to buy a 
separate BI software package.  
 
Because products like SQL Server 2005 can be 
provided to students at very low cost, university 
professors should consider including BI in the 
computer information systems curriculum. One can 
see from the example reported here that it is not very 
complex to set up a BI system that illustrates the 
main concepts and could provide real value to a 
company. 
 
EXAMPLE SYSTEM 
 
For this project we wanted an example system whose 
database would be available to us to add BI. We 
chose software from Balanced Flow, LLC that 
manages a supply chain. This software is produced 
by a small company building upon concepts 
developed in DoD-funded projects at Clemson 
University. One of its applications is a supply chain 
providing uniforms to the U.S. armed forces that 
involves manufacturers, distribution centers, and 
retailers. For brevity, we consider the business 
intelligence needs of only one of the participating 
firms—a manufacturer. 
 
The Balanced Flow software supports managing a 
supply chain on a days-of-supply basis. Each 
participating firm decides on an appropriate target 
level for days of supply for their inventories. This 
decision is influenced by factors such as their 
confidence in the forecast and in the variability of 
lead times for suppliers. The software recommends 
Business Intelligence for a Supply Chain Management System 
Volume VII, No. 2, 2006 30 Issues in Information Systems 
what to make (for manufacturers) or order (for 
distribution centers and retailers) to maintain the 
specified days of supply. There are features for 
exchanging information among firms in the supply 
chain to help keep overall supply chain inventories 
balanced. For example, if the supply chain handles 
apparel, the software avoids large differences in 
days-of-supply for the various styles and sizes. 
 
The overall business intelligence question for a 
particular firm such as a manufacturer is “how well 
are we doing?” The Balanced Flow software does not 
provide a satisfactory answer. Every time it runs, it 
reports the current status, but there is no provision for 
analyzing trends or for doing any analysis over time. 
Therefore there is not a satisfactory way to evaluate 
performance or to decide whether system parameters 
such as the target days-of-supply need to be changed. 
 
PERFORMANCE INDICATORS 
 
One of the difficulties in getting started is 
determining what information is useful and relevant 
to a decision. BI solutions at the enterprise level are 
charged with collecting and reporting a company’s 
most important metrics, sometimes called key 
performance indicators (KPIs). 
 
For a manufacturer in a supply chain, performance 
indicators include measures of stock outages and 
measures of balance. Compare the situations in 
Figure 1, showing stock outages and imbalance, and 
Figure 2, showing no stock outages and good 
balance. In the figures the lower level dark bars 
represent inventory on hand, the middle light colored 
bars are work-in-process, and the upper dark bars are 
production orders already released. The black 
horizontal line represents the target inventory level, 
30 days-of-supply. Figure 2 shows a more desirable 
situation, but in Figure 1 several items are below zero 
(back ordered) and the overall height of some of the 
bars is well above the target level while some are 
below. 
 
We could set up performance measures as follows. 
For stock outage we could compute the percentage of 
items having a stock outage. For balance we could 
compute the number of items whose virtual on hand 
(actual on hand plus work-in-process plus released 
production orders minus due-outs) is within certain 
percentages of the target. For example we could 
count how many items have virtual on hand up to 5% 
less than the target, 10% less than the target, etc  
 
 
 
Figure 1. Example of Unbalance Among SKUs 
 
  
 
 
 
Figure 2. Example of Balance Among SKUs 
 
 
DATA STRUCTURE 
 
Because a company’s supply chain management 
systems generally focus on operational control of 
supply processes the databases of these systems are 
not suitable for supporting business analysis, 
especially when the analysis requires compiling data 
from multiple data sources. 
 
To overcome the limitations of operational systems, a 
company may gather data from their operational 
systems and store it in a collective repository called a 
data warehouse that is designed to support BI. 
 
For this project we employed a data warehouse, 
because the main database was not designed to store 
time-varying data. We set up the database to handle 
multiple firms in a supply chain, although here we 
are discussing a single firm. We adopted a star 
schema that is typical for databases supporting BI 
Business Intelligence for a Supply Chain Management System 
Volume VII, No. 2, 2006 31 Issues in Information Systems 
systems (Figure 3). In the middle is a fact table that is 
surrounded by dimension tables. (Thus such a 
schema is sometimes called a snowflake.) In this case 
the fact table stores summary information about the 
firm. Specifically, it stores summary information 
about the stock keeping units (SKUs). Dimension 
tables include the following: Date, Firm, 
SupplyChain. The purpose of the star schema is to 
support fast queries and analyses that would be 
slower if applied to normalized data in the original 
system database. 
 
This rather simple schema is sufficient to support 
many useful BI features. For example a manager 
could ask to see for a selected moment of time the 
percentage of SKUs having a stock outage 
(PctSkuStockOut). Also a manager could ask to see 
these values during a specified period of time. 
Similarly a manager could see the percentage of 
SKUs that were a specified percentage below the 
target level 
(PctSkuVirtualOnhand_0_to5_PctBelowTgt), either 
at a specific time or during a period of time. Using 
queries like these can help identify trends that call for 
management action. They can help determine aspects 
of the performance of the supply chain.  
 
 
 
Fact Table
PK,FK2 Date_ID
FK3 Firm_ID
 TargetValue
 PctSkuStockOut
 TotQtyStockOut
 NumberOf_SKU
 PctSkuVirtualOnhand_0_to5_PctBelowTgt
 PctSkuVirtualOnhand_6_to20_PctBelowTgt
 PctSkuVirtualOnhand_21_to40_PctBelowTgt
 PctSkuVirtualOnhand_40_to100_PctBelowTgt
 PctSkuVirtualOnhand_0_to5_PctAboveTgt
 PctSkuVirtualOnhand_6_to20_PctAboveTgt
 PctSkuVirtualOnhand_21_to40_PctAboveTgt
 PctSkuVirtualOnhand_40_to100_PctAboveTgt
Firm
PK Firm_ID
 Firm_Name
FK1 SupplyChain_ID
Date
PK Date_ID
 Year
 Quarter
 Month
 Day
SupplyChain
PK SupplyChain_ID
 SupplyChain_Name
 
Figure 3. Star Schema for Data Warehouse 
 
 
 
SYSTEM DEVELOPMENT 
 
We used new business intelligence features of 
Microsoft SQL Server 2005. To extract data from the 
original supply chain management system and put it 
into the star schema (Figure 3), we used SQL Server 
2005 Integration Services that can import, export and 
transform data. The first step is to graphically outline 
the data extraction and conversion. 
 
Next we created a cube using Analysis Services. A 
cube is a star shaped schema that facilitates analysis. 
 
After connecting to the database, we created the data 
source view, which is very similar to our star shaped 
schema. A wizard allows us to select tables we are 
interested in working with. Then a cube wizard 
allows us to specify facts and dimensions. In our 
example a fact is a column (other than foreign keys) 
of our main table “Firm.” A dimension is a measure 
such as “Date” that may be used in analysis to 
identify trends or to do classifications. The facts are 
also called measures. One may define measure 
groups of related measures.  
 
A time dimension is a special one in SQL Server. The 
Business Intelligence Wizard allows defining this 
dimension and associating it with a table, the Date 
table in this project.  
 
SQL Server allows defining key performance 
indicators (KPIs) by specifying for each four values: 
Business Intelligence for a Supply Chain Management System 
Volume VII, No. 2, 2006 32 Issues in Information Systems 
Value Expression, Goal Expression, Status and 
Trend. We defined several associated with the 
aforementioned measures in our star schema. SQL 
Server supports sophisticated reporting of KPIs in 
ways such as the “traffic light.” For each KPI a report 
could show a traffic light whose color represents the 
status (where green means satisfactory). 
 
Finally, we used the Reporting Services designer that 
is hosted in the Business Intelligence Development 
Studio, to develop reports. This designer works much 
like the one provided by Microsoft Access that is 
familiar to most information systems professionals. 
 
Access to a report can be fine-tuned for specific 
groups or users by creating item- level roles that 
grant different permissions to different users. Roles 
define what item-level actions and permissions a 
group of users is allowed. This includes permissions 
to manage a report’s history, subscriptions, data 
sources, folders and security. 
 
CONCLUSIONS 
 
The BI tools in SQL Server are certainly appropriate 
for supporting the needs of many companies. In 
particular they are appropriate for supporting supply 
chain management. Because they are easily available 
to students and not difficult to learn, they are 
appropriate for students in information system 
courses. 
 
One of our difficulties was the lack of detailed 
guidance on the business intelligence features. 
However, this problem will be resolved when new 
books are available.  
 
If a company or an academic department already has 
SQL Server 2005, then the marginal cost and 
marginal difficulty of installing and learning BI is 
very low compared to acquiring a separate BI 
software package. 
 
REFERENCES 
 
1. Bouthillier, F & Shearer, K.(2003). Assessing 
Competitive Intelligence Software: A Guide to 
Evaluating CI Technology. Medford: 
Information Today Inc. 
2. Choy, K.L., Lee, W.B., Lau, H., Dawei, L. & Lo, 
V. (2004). Design of an intelligent supplier 
relationship management system for new product 
development, International Journal of Computer 
Integrated Manufacturing 17(8), 692-715.  
3.  Fairchild, A. (2005). Intelligent matching: 
integrating efficiencies in the financial supply 
chain. Supply Chain Management: An 
International Journal 10(4), 244-248.  
4. Frates, J. & Sharp, S. (2005). Using business 
intelligence to discover new market 
opportunities. Journal of Competitive 
Intelligence and Management 3(2), 16-28. 
5. Kelly S. (2000). Analytical CRM: The fusion of 
data and intelligence. Interactive Marketing 1(3), 
262-267. 
6. Malhotra, Y. (1998). Business process redesign: 
An overview. IEEE Engineering Management 
Review 26(3). 
7. Nucleus Research, Inc. (2006). The real ROI 
from Cognos business intelligence. 
http://www.nucleusresearch.com/research/c61.pd
f 
8. Shobrys, D. (2003). Business intelligence and 
supply chain management, Peer Publishing. 
http://supplychain.ittoolbox.com/documents/peer
-publishing/business-intelligence-and-supply-
chain-management-1948. 
9. Wikipedia (2006). Business intelligence tools. 
http://en.wikipedia.org/wiki/Business_intelligenc
e_tools 
 
 
 


BUSINESS INTELLIGENCE FOR A SUPPLY CHAIN MANAGEMENT SYSTEM

http://citeseerx.ist.psu.edu/viewdoc/download;jsessionid=8F1B433BADAE081A4BC2002AA8FAD90C?doi=10.1.1.1055.1152&rep=rep1&type=pdf

BUSINESS INTELLIGENCE FOR A SUPPLY CHAIN MANAGEMENT SYSTEM

Abstract

Similar works

Full text

Available Versions

CiteSeerX