The Business Case
for
Data Warehouse
by
Mike Ferguson
~
DataBase Associates Intamational
PO Box 29 PoyntDn. SlDckport,Cheshlre SK12 1WZ
TellFax (+44) 1625 520700
:. , ..
".,,,.,0' .-
Busi_s Ca..far Data warehousing copyright C Databa.. Associat" 1995 Page 2DWOVR. 1
40
Decision Support Processing to Date
operational
data
database gateway
(middleware)
~Oo.mN .. _ _ l88S
P1Ige2DWOVR.2
I ..
41
The Middleware Approach
• Difficult to integrate data from operational
multiple sources data
• Ad hoc ·pull" approach to
accessing operational data
server
• Performance of SQl interface to interface
non-relational data
• Umited data clean-up and SQl
enhancement - limited to SQl request data
manipulation unless 3GU4Gl
code written data client
• No historic data manager interface
• Umited metadata management
• Solution is to copy data to a
separate data warehouse
Cop"''-'' 0 Dahibue _ _ 1895 Pllge 2DW¢VR.3
42
The Data Warehouse Approach
operational or
external
Pluses
• integrated data
• consistent data
• enhanced data
• managed copies
• reduced load on
operational systems
Types of Data
• near real-time
• point-in-time snapshot
• continuous history
• detailed
• summarized
Business C•••1or Oat. W.rehausing Copyright 0 oataba.. Atsociat.. 1885 Pago 20w0vR.4
!";
~
~.
r·
l'
\
!..
~
!
[
~:
ft~
Z".
~
J':
t,:!
~;
t
F
~:
£p
;;;
~
~
43
~
r
~,
Banking - Can you answer these· questions?
Business Casetor Oata Warehousing Copyrigl1l c> Oolall... Associol" 1995 Page 20WOVR.5
44
Risk Management - Data Integration Impact
• Single view of the customer customer
• inCluding other credit accounts data
• 36 months detailed payment history data
gives good picture
• See life-style changes
• Acquire new customers if they match with
detailed 'good' profile history data
• inCluding other credit accounts
Bottom line benefit
• Accurate information leads to higher acceptance rate and more revenue
• More cross selling opportunities and lower risk exposure
• Risk management NOT Risk avoidance
Loss reduction of 1.0% on a $5 billion credit base = $50M
POII"2DWOVR.8
45
Real Example: Credit Card Usage
• A bank pays $2 per card per month to VISA for the use of their
logo and services .' ,
• $24 per year for EVERY card holder
• Question (ad hoc SQl query)
How many people didn't use their VISA card in the last year?
• Answer 500000
• 500000x$24=$12M
Decision
• Cancel the customers card and put $12M on the bottom line
COllYrifllllODalobl.. _ _ '885
Poge20W0VR.7
46
Retail - Can you answer these questions?
~o~ .. _ _ ,_
Page 2DWOVR.8
47
Retail - Business Challenges
• Get closer to the customer
• increase loyalty, analyse their buying habits
• Partner and understand your suppliers
• create WINJWIN relationship
• Profile existing and new customers + households
• cut distribution costs and achieve JUST IN TIME delivery
for EVERY store
• Precise knowledge of DAILY Item level sales
• Efficient Merchandise management
• PUSH strategy for store replenishment
• BUYERS decide when and what to put in each store thru
sales analysis
_. . . C._DoI·_no Copyrilti o DoIobo.. _ _ n 1l1li5 Poge 20W0VR.8
Item Level Sales - Detailed Daily Sales in All Outlets
Sign a stock redistribution contract with 3rd Party
• give them access to the data warehouse
• they get paid based on a %age of redistributed items sold with no
markdown
• they Don't get paid if they redistribute and items and they do not sell
• WINJWIN situation
Eliminate most of your distribution costs
• Give suppliers access to the data warehouse
.'
•. they can see exactly how their products are doing in each store daily
• THEY deliver you more stock as and when needed
• let suppliers look at their competition!! Let them fight for your shelf·
space
( Stop buying stock from your suppliers
'1J i • Send them 1 check a day for everything of their's that you sell and
•• <> NOT what you buy!
• Eliminates your costs on held stock, makes you the master of
negotiations and forces JUST IN TIME delivery
Page2DWOVR.·10
\.,
49
r'-
~
1
1.
Data Warehouse Challenges
• Requires management support
• Business case and cost justification - hardware, software, and staffing
costs
• Managing expectations of users and management
• Education and training of end-users and IT staff
• Data warehouse design and development
• Performance and volume of data
• Systems management
Cap,.tght co o.mba.. _ _ l985
"-2DWOVR.ll
j
.j
~~
50
I
I···
I·
Typical Warehousing Environment
• Information 'created' from multiple disparate operational systems
into a central integrated meaningful form for historical analysis
• Must manage volumes of data i.e. large tables
• Warehouse built in small fast steps with ROI at each step
• Requires modular scalable growth
• Remote data access required from anywhere inside and often
outside the enterprise
• access to information from heterogeneous desktop
community
• clientiserver and Data Warehousing integration
• Complex analytical queries on large volumes of data
• users don't know what they want
• they need to see the warehouse data model as 1: 1 mapping
of the business model
• GW interface and generated queries
• requires complex query performance and scalability
_C._Dato_OII PlI1I'I2OWOVR.12
51
Data Warehouse Components
Data
i--~'"
'" __. . ,,*" Development
Distribution
Component
¢
_-
, ... ... Component
(. ... J- - J Data Information r __f' ...
Management Directory
•-- ..., ,
~
Component Component
¢
Data
AcqUisition .-. I,
Component
Dr0 ,,• • i1
1
'I ••
_J
"
Data
Access
Component ¢
r-"'
warehouse warehouse
data metadata
Page 2DWOVR.13
.~ 52
Warehouse Data Structure
highly-summarized data
(e.g., sales by month for 1990-1994)
lightly-summarized data
(e.g., sales by week for 1992-1994)
iii
metadata
current detail data
(e.g., sales detail for 1994)
warehouse data may be
stored in one or more
physical databases
managed by one of more
database systems
.older detail data
(e.g., sales detail for 1985-1993)
CGpJriFI 0 o.tua.. _ _ ll185
Page2DWOVR.14
~:
!~:
r\
\;
.'
t'
I':
t
f
,t:.~;
f-'
e
~.;
!~.
h
~
~:
t.·
t:
f·
.)",
l'
~.
~:.
~~.
t:
"
~,
~
"f.
t
t·
't':
~.
s·
10
~:
"
j~
~.
:t;:
Ji
~ \.
~
j{.
!
-;(
;t'
~
~
fi,
~
:~ 53
~
~
i:
Getting Started - How?
• End-to-end architecture critical to success
• Automate data acquisition
• Most corporations already have the information Gathering
dust in tape silos and tape libraries
• often not in the form needed for information systems
• needs to be cleaned and re-conditioned to enhance it
• Need to evaluate tools to help with this process
iii iii
iiij
iii Massive data vOlumes.....- - - - - '
scalable computing platform
~~
regionaillocal
operational
clean information data marts
systems read only environment
(capture (copy subsets)
complex business wide queries local queries
updates)
read only
Business Casetor Data Warehousing Copyright <C> Database Associat.. 1995 Page2DWOVR.15
54
~---
Building a Data Warehouse
1. Identify and prioritize enterprise decision support areas - select an area
for development based on return on investment to the business
2. Recruit data warehouse team and select products
3. Determine the business entities of interest for the area
4. Find matching entities in existing operational systems
5. Perform logical and physical design of data warehouse database
6. Determine location of warehouse database: central. local or distributed?
• Who will use the data? What networking capabilities exist?
• How much data exists? How will the data grow over time?
• What is the query performance workload? How will the workload grow
over time?
Business Casefor Data Warehousing C,,!>yright C Database Associat•• 1995 Page 2DWOVR.1 e
55
Building a Data Warehouse
7. Design and develop data acquisition programs - capture, cleanup,
enhancement, mapping and apply
• Detailed data (continuous history or snapshot) - data restructuring,
data cleanup, data type conversion, add derived values and time
element)
• Summarized data (cumulative summary, multi-level cumulative
summary, rolling summary)
8. Select and install data access tools
9. Build information directory and information navigator
CapyriItilCDahl..... _ _ l885 Page 2DWOVR.11
56
Warehouse Topologies: Long Term Solutions
operational systems central data warehouse
[[[ffi! 2> II [[[ffi
t2 Vnca~re
query
results ~
DSS application departmental
data mart
Co\IIIWItO _ _ _ 1 .
Page 2DWOVft.18
57
Data Access -The Problem of Getting Data Out
• Concentration on warehousing technologies has been "associated with
integrating data from" various operational systems to get" data in the
form required by end users and then leaving them to a! "
• Focus on getting data in rather than getting data()ut!
• Ease of Data Access" and "Information Discovery is however is the key
to successful decision making and business benefit , I,
• Information should therefore be available
• at personal, group and enterprise levels
• at various levels of detail
• from anywhere
• in the form that the user needs
• in a timely fashion
CopyrighlO~ _ _ I885
Pqe2DWOVR.19
j
.'
58
Business Information Directories
Supporting End User Business Tasks
target discover
~====~I~ __ ~~~~~ what data exists
rules
,,
,, understand
source; , what data means
query create
DSS object
report
analysis \ ~ publish
other DSS
,,,,
,\ DSS object
\'
object type
run
DSS object
, ' - -_ _ _ _--J
subscribe to
~O_ . DSS object
_ll1115 Pllge2DWOVR.2D
59
The Importance of Metadata
Data About Data
• Metadata is fundamental to data warehouse development and operation -
stored in the data warehouse Information Directory
• The information directory contains information about
• The structure of the data (operationallinformational)
• History of how data structures and data relationships change over
time - ·versions· of metadata
• Mapping of operational data to warehouse informational data
• Details about data summarization algorithms
• Data extract (acquisition) history and statistics
• etc.
• The information directory also contains an Information Navigator that
assists the business user in warehouse usage
Page 2DWOVR.21
60
Information Directories and Tools for Data Access
• Information directories will ultimately become the end users tool box
• containing objects residing in many different query, reporting and
analysis tools that have been created by themselves and others,
• it is these objects that will become the screw drivers, hammers and
wrenches needed to get to the information required
• as with any toolbox the workman will have several sizes and types of
screwdriver, hammer and wrench
• this should also be the case for the information user
. • the ability to publish the existance of a query, report, analysis etc. by
exporting metadata from the tool to the information directory is
equivalent to putting a new screwdriver in the toolbox
• this will be critical to the reuse of other people's analyses .
• The powers of freedom will mean that many tools may exist in an
organisation for data access, analysis and reporting
The user. should NOT HAVE TO KNOW which tool to use to
get to and run the objects needed to gather the information required
Page 2DWOI/R.22
\"
61
Conclusions
• Get sponsorship. backing from a senior business manager e.g.
Marketing VP
• Pilot project for proof of concept
• Start small loading up information from one business area, store,
branch etc..
• Quantify return on investment from information discovery on an
iterative basis
• Underlying architecture essential for long term end-to-end solution
and technology selection
• Business Information Direcctory critical to success
• Data access tools classification scheme needed
• Users should be given the data they need, and not have to gather it
•BUILD it and they will come"
Kevin Costner· Field of Dreams (the movie)
Poll" 2D1N011R.23
62
