Data Warehouse Designs: Achieving ROI with Market Basket Analysis and Time Variance

285 Pages·2011·2.69 MB·English

by  Fon Silvers

Information Technology / BI Reporting S
i
l
v
Market Basket Analysis (MBA) provides the ability to continually monitor the
e
affinities of a business and can help an organization achieve a key competitive
r
advantage. Time variant data enables data warehouses to directly associate s
events in the past with the participants in each individual event. In the past,
however, the use of these powerful tools in tandem led to performance
degradation and resulted in unactionable and even damaging information.
D
Data Warehouse Designs: Achieving ROI with Market Basket Analysis
and Time Variance presents an innovative, soup-to-nuts approach that a
successfully combines what was previously incompatible, without degradation,
t
and uses the relational architecture already in place. Built around two main a
chapters, Market Basket Solution Definition and Time Variant Solution

Definition, it provides a tangible how-to design that can be used to facilitate W
MBA within the context of a data warehouse.
a
• Presents a solution for creating home-grown MBA data marts r
e
• Includes database design solutions in the context of Oracle, DB2,
SQL Server, and Teradata relational database management h
systems (RDBMS) o
• Explains how to extract, transform, and load data used in MBA u
and time variant solutions
s
e
The book uses standard RDBMS platforms, proven database structures,

standard SQL and hardware, and software and practices already accepted
D
and used in the data warehousing community to fill the gaps left by most
conceptual discussions of MBA. It employs a form and language intended for a e
data warehousing audience to explain the practicality of how data is delivered, s
stored, and viewed. Offering a comprehensive explanation of the applications i
that provide, store, and use MBA data, Data Warehouse Designs provides g
the language and concepts needed to require and receive information that is n
relevant and actionable.
s
K13245
ISBN: 978-1-4398-7076-1
90000
www.crcpress.com
9 781439 870761
www.auerbach-publications.com
K13245 cvr mech.indd 1 10/31/11 2:58 PM
Data
Warehouse
Designs
Achieving ROI with Market Basket Analysis
and Time Variance
TThhiiss ppaaggee iinntteennttiioonnaallllyy lleefftt bbllaannkk
Data
Warehouse
Designs
Achieving ROI with Market Basket Analysis
and Time Variance
Fon Silvers
CRC Press
Taylor & Francis Group
6000 Broken Sound Parkway NW, Suite 300
Boca Raton, FL 33487-2742
© 2012 by Taylor & Francis Group, LLC
CRC Press is an imprint of Taylor & Francis Group, an Informa business
No claim to original U.S. Government works
Version Date: 20111107
International Standard Book Number-13: 978-1-4398-7077-8 (eBook - PDF)
This book contains information obtained from authentic and highly regarded sources. Reasonable
efforts have been made to publish reliable data and information, but the author and publisher cannot
assume responsibility for the validity of all materials or the consequences of their use. The authors and
publishers have attempted to trace the copyright holders of all material reproduced in this publication
and apologize to copyright holders if permission to publish in this form has not been obtained. If any
copyright material has not been acknowledged please write and let us know so we may rectify in any
future reprint.
Except as permitted under U.S. Copyright Law, no part of this book may be reprinted, reproduced,
transmitted, or utilized in any form by any electronic, mechanical, or other means, now known or
hereafter invented, including photocopying, microfilming, and recording, or in any information stor-
age or retrieval system, without written permission from the publishers.
For permission to photocopy or use material electronically from this work, please access www.copy-
right.com (http://www.copyright.com/) or contact the Copyright Clearance Center, Inc. (CCC), 222
Rosewood Drive, Danvers, MA 01923, 978-750-8400. CCC is a not-for-profit organization that pro-
vides licenses and registration for a variety of users. For organizations that have been granted a pho-
tocopy license by the CCC, a separate system of payment has been arranged.
Trademark Notice: Product or corporate names may be trademarks or registered trademarks, and are
used only for identification and explanation without intent to infringe.
Visit the Taylor & Francis Web site at
http://www.taylorandfrancis.com
and the CRC Press Web site at
http://www.crcpress.com
This book is dedicated to Deborah,
my wife, my love, my best friend,
and my biggest fan.
TThhiiss ppaaggee iinntteennttiioonnaallllyy lleefftt bbllaannkk
Contents
Preface ...................................................................................................xv
Acknowledgments ..............................................................................xvii
The Author ...........................................................................................xix
Chapter 1 Data Warehouse ROI .........................................................1
A Data Warehouse Needs a Purpose ........................................1
A Data Warehouse Needs an ROI .............................................3
Gravitational Pull of a Data Warehouse ..................................4
Purpose and ROI .........................................................................6
Not Quite a Victim of Success ...................................................7
Performance ............................................................................8
Relational Integrity .................................................................9
Data Quality ............................................................................9
Purpose .......................................................................................10
Chapter 2 What Is Market Basket Analysis? ....................................13
Analysis versus Reporting ........................................................13
Elements of Market Basket Analysis .......................................15
Itemset ....................................................................................15
Object .....................................................................................18
Aἀn ity ...................................................................................19
Statistics in Market Basket Analysis .................................20
Logic of Market Basket Analysis .............................................23
Enterprise Relationships ......................................................24
Outside the Basket ................................................................26
What Market Basket Analysis Is Not ....................................28
Aἀ nity Is Not Causality .....................................................28
Aἀ nity Is Not Intent ............................................................29
Market Basket Analysis as an Activity ..................................30
Back to the Basket.....................................................................30
vii
viii  •  Contents
Chapter 3 How Does Market Basket Analysis Produce ROI? .........33
Analytic Structure .....................................................................33
Analytic Skills ...........................................................................34
Actionable Knowledge ..............................................................35
Complements ........................................................................35
Substitutes ..............................................................................37
Independents .........................................................................38
KPIs ........................................................................................39
ROI..............................................................................................40
Chapter 4 Why Is Market Basket Analysis Difficult? .....................41
Noise ...........................................................................................42
Large Data Volumes .................................................................44
Time .......................................................................................46
Groups ....................................................................................47
Completeness and Data Sampling ......................................47
Data Sample Integrity ..........................................................49
Data Warehouse Data Structures ............................................49
Flexibility of the Itemset ......................................................50
Lack of Control .....................................................................51
Recursive Nature of Market Basket Analysis....................51
On Your Mark…Get Set…Go! ................................................52
Chapter 5 Market Basket Analysis Solution Definition ..................55
Market Basket Scope Statement ..............................................55
Definition of the Itemset .....................................................56
Definition of the Driver Object ..........................................58
Definition of the Correlation Object ..................................59
Key Definitions .........................................................................60
Itemset Key Definition .........................................................61
Object Key Definition ..........................................................61
Attribute Definitions ............................................................62
Itemset Metric Definitions ..................................................63
Market Basket Table ..................................................................63
Market Basket Query ................................................................65
Itemset ...................................................................................66
Driver Object ........................................................................66
Contents  •  ix
Correlation Object ................................................................67
Recursive Join .......................................................................68
Single Object Itemset ............................................................69
Market Basket BI Table .............................................................71
Market Basket Analysis BI View .............................................73
DRIVER_OBJECT_KEY .....................................................79
CORR_OBJECT_KEY .........................................................79
DRIVER_QUANTITY_SUM .............................................79
DRIVER_DOLLARS_SUM...............................................80
DRIVER_COUNT_SUM ...................................................80
CORR_QUANTITY_SUM .................................................81
CORR_DOLLARS_SUM ....................................................81
CORR_COUNT_SUM ........................................................81
QUANTITY_RATIO ...........................................................82
DOLLARS_RATIO ..............................................................82
COUNT_RATIO ..................................................................82
Additional Group by Data Elements ..................................82
Beyond the Market Basket Analysis BI View ........................83
Object Groups ......................................................................84
Market Basket Reporting in Batch ....................................84
Chapter 6 Market Basket Architecture and Database Design ........87
Market Basket Analysis Architecture ...............................88
Data Warehouse ...............................................................89
Market Basket Table ........................................................89
Market Basket BI Table ..................................................90
Analysts .............................................................................91
Market Basket Reporting Architecture .............................91
Data Warehouse ...............................................................92
Market Basket Table ........................................................93
Market Basket BI Table ...................................................93
Market Basket KPI Exception Reporting .....................94
Market Basket Data Definition Language (DDL) .................95
Construction of the Market Basket Table .........................95
Teradata ............................................................................96
Oracle ................................................................................96
DB2 ...................................................................................96
SQL Server ........................................................................97