Business Intelligence/ Decision Models

1
Business Intelligence/Decision Models

• Week 2
• IT Infrastructure
• Marketing Database
• Design and Implementation

2
Outline

• Issues with Mkt Databases
• DBMS
• Database Design and Schemas
• Data Integrity and Hygiene
• Demo and Lab Table redundancy and Queries

3
DB Marketing Problems

• Lack of a marketing strategy.
• Focus on promotions instead of relationships.
• Failure to have a 3600 picture of every customer.
• Failure to personalize your communications.
• Building a DB and sending e-mails in house.
• Getting the economics wrong.
• Failure to use tests and controls.
• Lack of a forceful leader.
• Bad DB architecture
• Corrupted data  

4
(No Transcript)
5
DB Environment
6
Traditional EnvironmentSilo Approach
Source Laudon and Laudon 2012
7
Data Warehouse Technology
8
Marketing Datamart
9
Data Warehouse Architecture
10
Data Warehouse Architecture
11
Metadata
12
(No Transcript)
13
Database Management Systems (DBMS)
14
Flat Files

• Sequential
• Fixed or variable length record

A
B
C
D
A
Name
Address
Transactions1 2
3 ?
15
DBMS with VSAM Index
QC
TN NE NB IPE QC ON MB SK AB BC
ON
ON
QC
ON
16
Hierarchical IndexedDirect Access DBMS
Cust_id
Name
Purchases
Products
Top down
17
Indexed Direct Access DBMS

• Key Record
• 107 4
• 110 6
• 145 1
• 167 2
• 234 5
• 267 3

• Records
• 1 145 .
• 2 167 .
• 3 267 .
• 4 107 .
• 5 234 .
• 6 110 .

18
Reversed Hierarchical DBMS
Cust_id
Products
Name
Purchases
Psyte Code Lifestyle
Bottom up/Top down
19
Reversed Hierarchical DBMS

• NAME PSYTE PURCHASES
• Dubé 18 120
• Smith 34 130
• Bertrand 18 150
• White 56 200
• Harris 34 50
• Habib 18 300
• Jones 34 430

• PSYTE NAMES
• 18 Dubé Bertrand Habib
• 34 Smith Harris Jones
• 56 White

20
Relational Database
CUSTOMERS   ORDERS   PRODUCTS
Customer ID PK Order ID PK Product ID PK
Cust First Name Customer ID FK Product Name
Cust Last Name Product ID FK Product Description
Street Order Date  
City Order Amount
State  
Zip

1
?
21
Relational DBMSMultiple Tables
Source Laudon and Laudon 2012
22
Relational DBMSwith Query
Source Laudon and Laudon 2012
23
Relational Design
24
An Unnormalized Relation For Order (flat file)

• An unnormalized relation contains repeating
  groups. For example, there can be many parts and
  suppliers for each order. There is only a
  one-to-one correspondence between Order Number
  and Order Date.

Source Laudon and Laudon 2012
25
Normalized Tables Created From Order

• Pros Data integrity and updating
• Cons Processing speed for large data sets

Source Laudon and Laudon 2012
26
Charitable Contributions
27
(No Transcript)
28
(No Transcript)
29
The Classic Star Schema

• A single fact table, with detail and summary data
• Fact table primary key has only one key column
  per dimension
• Each key is generated
• Each dimension is a single table, highly
  de-normalized

• Tradeoff between data integrity, updating and
  speed
• Some alternatives Star and Snowflake structure
• Benefits Easy to understand, easy to define
  hierarchies, reduces of physical joins, low
  maintenance, very simple metadata

Source Kishore-jaladi-DW.ppt
30
(No Transcript)
31
(No Transcript)
32
Data Integrity and Hygiene
33
Data Integrity Issues

• Duplicates (with variations)
• Individuals with similar names
• Customer reappearances
• Change of addresses
• Incomplete addresses
• Transcription errors
• Change of names

34
Illustrating Data Hygiene
  Quantities   Response Response Rate
Customers 2,000,000   29,000 1.45
Undel. 15 1,700,000 15 29,000 1.71
Dup. 20 1,360,000 20 29,000 2.13

    Cost   CPO
CPM 500 2,000,000 1,000,000 29,000 34.48
  1,700,000 850,000 29,000 29.31
  1,360,000 680,000 29,000 23.45
    Revenue   Profit ROI
Price 60 2,000,000 870,000 29,000 -130,000 -13
GM 50 1,700,000 870,000 29,000 20,000 2
  1,360,000 870,000 29,000 190,000 28

BE FC / (P-C) 1,000,000 / 30 33,334
BE FC / (P-C) 850,000 / 30 28,334
BE FC / (P-C) 680,000 / 30 22,667
35
Data Hygiene Processes (1)

• Standardize names
• Title, First name, Initials, Family name, Suffix
• Standardize addresses
• Address 1, Address 2, City, Province, Postal Code
• Abbreviations (apt., ave, p.o., province)
• Replace prestige names with postal addresses
  (i.e. Commerce Court)
• Scrubbing
• Ex. c/o, co, c/o
• Delivery
• FSA/LDU, Postal walk
• Address change database

36
Data Hygiene Processes (2)

• Data Comparison
• Duplicate (cost, abuse)
• Householding
• Hyphenated Names, Maiden Names, Spouses Name
• Recomposed Families, Roommates
• Consolidation (merge/purge)
• Multiple Accounts (financial Services)
• Multiple policies (insurances)
• Multiple phone numbers (telco)
• Multiple divisions within firm

37
Wrap-up

• Issues with Mkt Databases
• DBMS
• Database Design and Schemas
• Data Integrity and Hygiene
• Demo and Lab Table redundancy and Queries

38
Next Week

• Data Import
• Data Preparation
• Data Transformation