MD703

1
MD703

• Final Class
• Database
• Internal / External Integration
• Outsourcing

2
Database Technologies

• Relational Concepts
• Data Warehouses Marts
• Queries, OLAP, Data Mining

3
Terms/Examples
Server - responds to client requests
DBMS - the program. Manages interaction with
databases.
request
response
Client - makes requests of the DBMS server
database - the collection of data. Created and
defined to meet the needs of the organization.

• Database
• a collection of related data. Usually organized
  according to topics e.g. customer info,
  products, transactions
• Database Management System (DBMS)
• a program for creating managing databases ex.
  Oracle, MS-Access, Sybase

4
A Simple Database

• File/Table
• Customers
• Field/Column
• 5 shown CUSTID, FIRST, LAST, CITY, STATE
• Record/Row
• 5 shown one for each customer

5
A More Complex Example

• Entry Maintenance is complicated
• redundant data exists, increases chance of error,
  complicates updates/changes, takes up space

6
Normalize DataRemove Redundancy
Customer Table
Transaction Table
One
Many
7
Key Terms

• Relational DBMS
• manages databases as a collection of files/tables
  in which all data relationships are represented
  by common values in related tables (referred to
  as keys).
• a relational system has the flexibility to take
  multiple files and generate a new file from the
  records that meet the matching criteria (join).
• SQL - Structured Query Language
• Most popular relational database standard.
  Includes a language for creating manipulating
  data.

8
Now With More Data
One
Many
Many
One
9
Meta-Data
Customer Table
Transaction Table
m
1
m
Broker Table
1

• Data that describes the characteristics of stored
  data
• Enterprise Data Model
• consistent, cross-functional, shareable meta-data
  model
• standardization increases flexibility use (data
  to info)
• facilitates the creation of data warehouses

10
Management Levels of IS
Strategic Planning
DSS MIS TPS
Management Control
Operational Control
11
Warehouses Marts

• Data Warehouse
• a database designed to support decision-making in
  an organization. It is batch-updated and
  structured for fast online queries and
  exploration. Data warehouses may aggregate
  enormous amounts of data from many different
  operational systems.
• Data Mart
• a database focused on addressing the concerns of
  a specific problem or business unit (e.g.
  Marketing, Engineering). Size doesnt define
  data marts, but they tend to be smaller than data
  warehouses.

12
Data Warehouses Data Marts
3rd party data
Data Mart (Marketing)
TPS other operational systems
Data Warehouse
Data Mart (Engineering)
operational clients
query, OLAP, mining, etc.
13
Differing System Demands
Operational Systems
network traffic processor demands
time
Managerial Systems
network traffic processor demands
time
14
Transform Data from TPS to Warehouse

• Consolidate data
• e.g. from multiple TPS around the country/world
• Scrub the data
• keep definitions consistent (e.g. translate part
  numbers/product names if they differ per country)
• Calculate fields (decrease processor load)
• Summarize fields (decrease processor load)
• De-normalize data (ease of use)

15
Calculated Fields
Customer Service Application Customer support
person TPS - focuses on customer info Total is
calculated on the fly
Database Query Application Marketing
manager Aggregate reporting of business
intelligence Total calculated in advance
16
Query Tools OLAP

• Query Tools
• user-lead discovery. Can return individual
  records or summaries. Requests are formulated in
  advance (e.g. show me all delinquent accounts in
  the northeast region during Q1).
• OLAP - Online Analytical Processing
• user-lead discovery. Data is explored via drill
  down into the data by selecting variables to
  summarize on. Results are usually reported in a
  cross-tab report or graph (e.g. show me a
  tabular breakdown of sales by business unit,
  product type, and year).

17
OLAP

• Online Analytical Processing. (example of
  cross-tab results presented below)

1. business unit
2. product type
3. year
18
Data Mining

• automated information discovery process, uncovers
  important patterns in existing data
• can use neural networks or other approaches.
  Requires clean, reliable, consistent data.
  Historical data must reflect the current
  environment.
• e.g. What are the characteristics that identify
  when we are likely to lose a customer?

19
Data Mining Uses

• Market Segmentation - e.g. Target
• Direct Marketing - e.g. Chase
• Market basket analysis - e.g. Wal-Mart
• Customer Churn - e.g. Fleet Bank
• Fraud Detection - e.g. Bank of America
• Cost Reduction Prospecting - e.g. Merk Medco.

20
Stupid Data-Miner Tricks

• Ad-Hoc Theories
• when an oddity jumps out of the data, its
  tempting to develop a theory for it. Sometimes
  findings are just statistical flukes.
• Using Too Many Variables
• the more factors considered, the more likely a
  relationship will be found - valid or not.
• Not Taking No for an Answer
• its OK to stop looking if you cant find
  anything. There are no silver bullets.
• Limited or incorrect interpretation

21
External Internal Integration

• EDI XML
• Enterprise Resource Planning (ERP)
• Outsourcing

22
External Integration

• EDI - Electronic Data Interchange
• uses standard formats to pass data between
  disparate systems
• US format - X.12, European format - UN/EDIFACT
• Cost Savings
• paper order 50 - 70
• EDI order 2.50 (VANs / private networks)
• I-EDI order lt 1 (Internet)
• XML - eXtensible Markup Language
• tagging language for the web

23
Challenges Facing IS Depts.

• Y2K Legacy Systems
• Globalization (euro, currency issues)
• Rapid Technology Advancement
• e.g. Client/Server Internet
• IS Staffing Retention
• Changing Organizational Structures
• e.g. Owens Corning
• Tighter Integration with Buyers Suppliers

24
Legacy Systems
Many firms have limited to no integration
across geographic areas functional areas
(v-chain) products, plants, business units
Buyers
Suppliers
25
What is ERP?

• ERP - Enterprise Resource Planning Software
• sometimes called Enterprise Applications/Packages/
  Suites/Systems
• connects all of the information which flows
  through a company to a single integrated set of
  systems
• implemented in modules which can be integrated
  (all at once or at a later date) e.g. Financials,
  Logistics, HR
• may work with a wide variety of databases,
  hardware, and operating systems
• Leading Vendors
• SAP, Oracle, JD Edwards, Baan, Peoplesoft

26
ERP in Action
Sales
27
ERP in Action
Inventory
Sales
28
ERP in Action
Production
Inventory
Sales
29
ERP in Action
Production
Staffing
Inventory
Sales
30
ERP in Action
Production
Staffing
Inventory
Sales
Purchasing
31
ERP in Action
Production
Staffing
Inventory
Sales
Purchasing
Order Tracking
32
ERP in Action
Production
Staffing
Inventory
Sales
Purchasing
Planning
Order Tracking
Source BusinessWeek Intl, 1997
33
The Benefits

• Systems integration - enterprise data model
• squeeze out waste enable strategies
• Standard software enables -
• inter-organizational systems (easier if buyers
  suppliers use the same system, e.g. petrochem.
  ind.)
• broad selection of add-on packages (e.g. data
  warehouses, etc.)
• Package upgrading and new technology development
  is handled by vendor
• Speed of deployment

34
The Risks

• Staff retention
• Tied to a single vendor
• Flexibility limited by options offered by the
  vendor
• may inappropriately force generic processes
• may inappropriately force structure
• Complexity - particularly regarding mapping and
  standardizing processes across the organization.

35
Make vs. Buy
Adapted from Applegate et al., p. 61.
36
Successful Deployment of ERP

• Business Case
• benchmark, cost justify (e.g. unplug mainframes)
• Leadership
• from the highest levels (e.g. success at Owens
  Corning, failure at Westinghouse)
• Staffing
• largely from business, not IT (users know the
  process)
• compensation handcuffs (e.g. end of deployment
  bonuses, training payback agreements)
• experienced consultants - check refs., clients
• Execute with proven methodologies

37
When Outsourcing is Attractive

• IS Function
• commodity services are more easily outsourced
  (networking, operations, PC maintenance,
  training)
• Project Structure
• structured projects are most appropriate
• Profit / MA Concerns
• outsourcing generates cash, liquefies assets, and
  may make some firms (e.g. banks) more attractive
  to suitors (no IS org. to integrate)