Title: Business Intelligence/ Decision Models
1Business Intelligence/Decision Models
- Week 2
- IT Infrastructure
- Marketing Database
- Design and Implementation
-
2Outline
- Issues with Mkt Databases
- DBMS
- Database Design and Schemas
- Data Integrity and Hygiene
- Demo and Lab Table redundancy and Queries
3DB 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)
5DB Environment
6Traditional EnvironmentSilo Approach
Source Laudon and Laudon 2012
7Data Warehouse Technology
8Marketing Datamart
9Data Warehouse Architecture
10Data Warehouse Architecture
11Metadata
12(No Transcript)
13Database Management Systems (DBMS)
14Flat Files
- Sequential
- Fixed or variable length record
A
B
C
D
A
Name
Address
Transactions1 2
3 ?
15DBMS with VSAM Index
QC
TN NE NB IPE QC ON MB SK AB BC
ON
ON
QC
ON
16Hierarchical IndexedDirect Access DBMS
Cust_id
Name
Purchases
Products
Top down
17Indexed 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 .
18Reversed Hierarchical DBMS
Cust_id
Products
Name
Purchases
Psyte Code Lifestyle
Bottom up/Top down
19Reversed 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
20Relational 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
?
21Relational DBMSMultiple Tables
Source Laudon and Laudon 2012
22Relational DBMSwith Query
Source Laudon and Laudon 2012
23Relational Design
24An 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
25Normalized Tables Created From Order
- Pros Data integrity and updating
- Cons Processing speed for large data sets
Source Laudon and Laudon 2012
26Charitable Contributions
27(No Transcript)
28(No Transcript)
29The 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)
32Data Integrity and Hygiene
33Data Integrity Issues
- Duplicates (with variations)
- Individuals with similar names
- Customer reappearances
- Change of addresses
- Incomplete addresses
- Transcription errors
- Change of names
34Illustrating 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
35Data 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
36Data 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
37Wrap-up
- Issues with Mkt Databases
- DBMS
- Database Design and Schemas
- Data Integrity and Hygiene
- Demo and Lab Table redundancy and Queries
38Next Week
- Data Import
- Data Preparation
- Data Transformation