Design%20of%20Multidimensional%20Data%20Models%20for%20Data%20Warehouses%20and%20OLAP

1
Design ofMultidimensional Data Models forData
Warehousesand OLAP

• Thomas Frisendal

2
Who I am

• Freelance Data Base Consultant
• More than 30 years of Experience with DBMSs as a
  Vendor Person and as Freelance Consultant
• 10 years of Experience with Data Warehouse
  construction
• Have trained more than 350 persons in
  Multidimensional Modelling and Star Schema Design
• Charter member of the IAIDQ
• Board member of The Data Warehouse Institute
  Denmark
• Locations Nordic Countries (principal residence
  in Denmark) and Côte dAzur (secondary residence
  in Antibes)

3
Acknowledgements

• Based on
• Ralph Kimballs books The Data Warehouse
  Toolkit, Wiley 1996, ISBN 0-471-15337-0, The
  Data Warehouse Lifecycle Toolkit ), Wiley 1998,
  ISBN 0-471-25547-5, The Data Webhouse Toolkit
  ), Wiley 2000, ISBN 0-471-37680-9, and selected
  parts of Ralphs published papers
• Some examples from Data Warehouse Design
  Solutions, Christopher Adamson and Michael
  Venerable, Wiley 1998, ISBN 0-471-25195-X.
• Microsoft OLAP Solutions, Erik Thomsen, George
  Spofford and Dick Chase, Wiley 1999, ISBN
  0-471-33258-5
• Telco example from The Official Guide to
  Informix/Red Brick Data Warehousing, MT Books
  (IDG) 2000, ISBN 0-7645-4694-5
• DWLIST discussions
• My own practical experiences and readings
• ) With Laura Reeves, Margy Ross and Warren
  Thornthwaite.
• ) With Richard Merz

4
Agenda

• History
• Data Warehousing Objectives and Architecture
• Dimensional Design Basics
• Industry examples
• The Data Webhouse (Clickstream Analysis)
• Advanced Design Issues
• The bigger picture
• Data Quality
• The future of the Data Warehouse
• Literature and web-adresses

5
History

• How the Multidimensional Model came into existence

6
The early years of database
Online! Pioneers General Electric, IDS, Charlie
Bachman Rockwell/IBM, IMS Techniques Hashing,
pointers, physical colocation, and concurrency /
transaction control
7
The VisionThe Information System
8
Relational The Codd Date Seminars
9
Relational / SQL

• Database language standard
• The Query Optimizer (automated navigation)
• DBMSs become commodities
• End-user tools by the hundreds

10
Around 1987 Getting closer
Tactical
Operational
The Information Warehouse Concept
11
Innovation for AnalystsThe Multidimensional
Database
Sales Districts
Products
Timeperiods
12
IT Business OpportunitiesDecision Support
Systems
Executive Information Systems
Strategic
On-Line Analytical Processing
Tactical
On-Line Transaction Processing
Operational
13
OLTP and OLAPConflict of Purpose
(millions of records)
(hundreds of records)
14
OLAP Categoriesand Sample Products

• ROLAP Relational DBMSs with Star Schema support
  and specific tools
• MOLAP Multidimensional Databases
• HOLAP Hybrid OLAP, the combination of the two

• ROLAP Most RDBMSs, tools like Microstrategy,
  Business Objects, Informix Metacube, IBM DB2 OLAP
  Server, Oracle 9i OLAP Server and many more
• MOLAP Hyperion Essbase, Applix TM1, Cognos,
  Microsoft OLAP Services, IBM DB2 OLAP Server
• HOLAP Microsoft SQL Server with OLAP Services,
  (IBM DB2 OLAP Server, Hyperion Essbase 7)

15
ROLAP vs. MOLAP

• Hot debate in the 90-es (The Shootout at the
  OLAP Corral)
• The major strength of ROLAP
• Large volumes of data (billions of rows,
  terabytes of data)
• The major weakness of ROLAP
• Performance on large result sets
• The major strength of MOLAP
• Fast response times, also on large result sets
  (aggregated queries)
• The major drawbacks of MOLAP
• Pre-calculation times
• Scalability (the cubes explode as the volume and
  complexity increase)
• So, what to do?
• Use both (HOLAP)

16
The Power of the 2
MOLAP / HOLAP
ROLAP Star Schema
Smooth, often automatic/transparent,
integration Detailed levels in ROLAP (many
rows) Two good examples - Microsoft SQL Server
with OLAP Services - IBM DB2 OLAP Server
17
Its here!
Analytical Applications in OLAP
Distributed On-Line Transaction Processing
18
Data Warehousing Objectivesand Architecture
19
Data Warehouse Objectives

• Provide one data source for reporting, analysis,
  and mining
• consistent answers across the organization or
  organizational unit (data marts)

20
The Feeding System(Extract, Transformation and
Load - ETL Tools)
Operational systems
Extracting Cleaning Standardizing Consolidating Ag
gregating Transforming Keygenerating Reformatting
and much more
Datawarehouse
21
ETL where the time is spent!

• Do it yourself (SQL, scripts, COBOL etc.)
• ETL tool products (e.g. Informatica)
• 80 of your time is spent here!
• Buy and read The Data Warehouse ETL Toolkit,
  Ralph Kimball and Joe Caserta, Wiley 2004, ISBN
  0-7645-6757-8

22
Data Warehouse statistics

• Development time
• lt 6 months 16
• 6-12 months 32
• 12-24 months 26
• 24-60 months 20
• gt 60 months 6
• 50 of all are over 3 years of age (9 over 10
  years)
• 33 over 1 TB (10 over 10 TB)

Source Business Intelligence Journal, Fall 2005
23
The Points of Measurements in Business Processes

• Facts are numerical
• Counts
• Volumes
• Money
• and represent the key subject areas in business.
• Each point of measurement becomes a star.

24
One, big Data Warehouse
One global DW
Finished goods inventory
Customer dimension
Shipments
Distribution inventory
Product dimension
Depletions
Store inventory
Etc. etc. etc.
Salestransactions
Time dimension
25
Anarchistic Data Marts
Data Mart
Data Mart
Data Mart
Data Mart
Data Mart
Data Mart
26
Coordinated Data Marts
Enterprise Data Warehouse (maybe also an
Operational Data Store)
Data Mart
Data Mart
Data Mart
Data Mart
Data Mart
Data Mart
27
Top 10 reasons for a layer of atomic data in
front of data marts

• Coordinated transformations at mart level
  (consistency)
• Minimized impact on source systems (one extract)
• Temporal integrity across marts
• Single source for cross-subject mining
• Allows smaller marts - with drill-down to atomic
  level
• Coordinated meta data across entire DW
• Scalability of entire DW (quick population of new
  marts)
• Facilitates building stars
• Mart recovery
• If volumes not too high, operational reporting
  may take place

Doug Laney, Prism Solutions on DWLIST
28
The Data Warehouse Bus Architecture
Design Conformed Dimensions and Conformed
Facts Physical Data Staging facilities as
necessary
Data Mart
Data Mart
Data Mart
Data Mart
Data Mart
Data Mart
29
Roles of ROLAP, MOLAPand HOLAP
Data Staging Area in Relational DBMS E/R May be
used for data cleansing Star Schema For all
facts and dimensions
Data Mart 1, ROLAP
Data Mart 2, MOLAP
Data Mart 3, HOLAP
Data Mart 4, MOLAP
Data Mart 5, ROLAP
Data Mart 5, MOLAP
30
Components and structures in Decision Support
Systems
Multidimensional Cube(s)
User Interface, ie. Windows or Web
Ad hoc OLAP tools
Own applications developed with OLAP tools
Off the shelf applications
Typically on aggregated levels
Metadata
Relational Star Schemas
SQL
Typically on the atomic, event level
Datawarehouse database
31
A few words on Meta Data

• You need it and it is important
• Read Meta Meta Data Data, Ralph Kimball
  (www.dbmsmag.com/9803d05.html)
• Comes with client tools, data transformation
  tools, stand-alone tools, modelling tools, DBMSs
  etc.
• Microsoft Repository Open Information Model
• Meta Data Coalition OIM 1.1 (www.mdcinfo.com )
• Parallel work in the OMG (Common Warehouse
  Metamodel) (www.omg.org/technology/cwm )
• September 2000 MDC integrates into OMG CWM, the
  two standards become one.

J
32
Common Warehouse Metamodel

• Expressed in UML
• XML for metadata interchange (XMI)
• Check www.cwmforum.org
• CWM 1.0 February 2001
• Partners IBM, Unisys, NCR, Hyperion, Oracle,
  UBS, Genesis, Dimension EDI
• Supporters Deere, Sun, HP, Data Access, InLine,
  Aonix, Hitachi, SAS, Meta Integration, Adaptive
• Common Warehouse Metamodel, John Poole, Dan
  Chang, Douglas Tolbert, and David Mellor, OMG
  Press / Wiley 2002, ISBN 0-471-20052-2
• Actual product support? Still maybe a little too
  early to tell

33
Data Warehousing Objectives

• Publish what is important
• Provide the means to find out why
• Promote well-informed decisions

34
Multidimensional Design Basics

• The Art of Constructing the Stars!

35
Dimensional Design Methodology

• Design begins
• with business requirements gathered from the
  decision makers and analysts
• and data sourced from
• the corporations operational systems
• external data sources
• Design requires
• user involvement at all stages
• Design resembles
• a series of successive approximations (3-4
  revisions)

36
Simple, E/R model for OLTP
37
Problems with E/R

• Humans cant navigate or remember an E/R
• Software cant navigate an E/R
• Every path gives a different answer
• The shortest path is meaningless
• Bad performance

38
Do you need a warehouse E/R model

• Not necessarily
• the data relationships in the enterprise E/R
  model can suffice

39
Can I model by subject area with a phased approach

• Yes
• models are extensible
• the key is conformable dimensions
• dimensions can then be shared and are extensible
  themselves

40
Heart of the matter

• Business Views
• Must look like the business
• Recognized by business types
• Relevant for business types
• Three design rules
• Simplicity
• Simplicity
• Simplicity

K.I.S.S. !
41
Design objectives

• Business View Schema must be readily understood
  and navigatable by the users
• Important information must not be obscured by
  unimportant detail and complexity
• The implementation(s) must provide rapid response
  time against large volumes of historical data
• The implementation(s) must be legible and
  navigatable for extract processing mining

42
Classic definition, ROLAP

• STAR schema
• A relational schema organized around a central
  table joined to smaller tables using foreign key
  references

43
Dimensional Model
Facts/Measures 95 of data base storage
Dimensions
Dimensions
most of the fields
44
Terminology Facts, Measures, Accounts

• ROLAP The Fact Table(s)
• Contains Facts and (foreign key) references to
  the dimensions
• MOLAP The Measures Dimension
• Contains Measures and references to the lowest
  level Member Keys of the dimensions
• Essbase (MOLAP) Measures Dimensions are called
  Accounts Dimensions

45
Advantages over classic datamodels
(Entity/Relationship)

• Humans can navigate remember a multidimensional
  star or cube
• Software can navigate deterministically
• The two major purposes of Multidimensional
  Modelling are
• Reducing complexity
• Deliver good response times also for large
  aggregations

46
The Dimensions

• Dimensions
• Define the business dimensions, in terms already
  familiar to users, by which the central table is
  to be analyzed
• Numerous columns of text, highly descriptive
• Represent the hierarchies of different levels of
  reporting (eg. Year-gtQuarter-gtMonth-gtDay)
• Usually less than a million rows, can be much
  larger in some businesses

47
Technically speaking (ROLAP)

• Dimension tables
• Must have primary key
• Joined to fact table through foreign key
  reference
• Typically represent ninety percent of the data
  elements
• Commonly occurs in constraints and GROUP BY
  clauses
• Heavily indexed

48
Typical dimensions

• Generic Industry specific
• Time period(s) Frequent flier, stayer
• Geographic region Service level,
  procedure, (markets, cities) operation
• Products Room type, service,
• (also in bank/insurance) classification,
  seat
• Promotions/campaign Drug, medicine
• Customers Vendors, distributor, (Account
  number) warehouse
• Sales rep, buyer,
• organisation

49
The Time Dimension

• Problem SQL and many OLAP products do not
  support date arithmetic well enough
• How many working days in a month?
• How many days in the Easter season?
• When is the industrial vacation period?
• How to calculate AVG(xxx) per working day?

50
The Time Dimension

• Give it all the attributes you need to make life
  easy for the end-user
• Daynumber
• Date
• Day in week
• Name of day
• Type of day
• Season
• Week in year
• Workingdays in week
• Month

Name of month Type of month Day in month Days
in month Workingdays in month End of Month
Flag Quarter Year Day in year Workingdays in
year
Think about this - it is worth the effort!
51
The Time of Day Dimension
52
The Status Dimension
S-key Status Fulfilled-flag Ordertype 1 Current
Yes Mail 2 Current No Mail 3 Old Yes Ma
il 4 Old No Mail 5 Current Yes Phone 6 Curr
ent No Phone etc...
(Cartesian product of all values) Is a real
dimension
53
Hierachies within Dimensions
ROLAP One table (denormalised)
Company Divison Region Business Unit CC
Country CC State CC City Cost Center (PK)
MOLAP One or two dimensions, depending on your
product(s)
(Dimension diagram technique proposed by Ralph
Kimball et al in The Data Warehouse Lifecycle
Toolkit using eg. Microsoft Visio)
54
Fact of life (1) Ragged Hierarchies
55
Fact of life Ragged Hierarchies




Data enters at this level
56
ROLAP Solution to the Ragged Hierarchy Problem
Base table
End-user and/or MOLAP view Select Category_Descr
iption, Case when Account_Description is not
null then Account_description else
Category_Description end as Account_Description f
rom Chart_of_Accounts order by Category_ID
57
MOLAP Solutions to the Ragged Hierarchy Problem

• Depends on your product
• Might require manual definitions
• Supported in at least
• Microsoft SQL Server 2000
• Hyperion Essbase
• IBM DB2 OLAP Server
• Applix TM1

58
Fact of Life (2)Unique Members

• Member Often used in OLAP to designate the
  individual entries on the individual levels (eg.
  Country France, Year 2001 etc.).
• Some products require that members (values) be
  unique across
• The whole level
• The whole dimension(!)
• Check your selected product(s) before getting too
  deep into the implementation!

59
Fact of Life (2)Unique Members

• All parent-child relationships MUST be
  one-to-many
• Checking your levels
• select quarter, count(distinct year) as count_col
  from time_period group by quarter having
  count_col ltgt 1
• The result set of the query above must be empty!
• If your product needs global uniqueness, you must
  do similar checking across all levels
• If you dont do this, your users will get
  meaningsless answers to their queries!

60
Fact of Life (3) Sparsity

• Some dimension members may occur quite
  infrequently (eg. demographic data only available
  on 10 percent of your customers)
• This is called sparsity (a sparse dimension)
• Also look for dimensions, which do not intersect
  with other dimensions in a star they are not
  interesting only take up space
• ROLAP Not a big problem, some wasted disk space
• MOLAP A very big problem leads to cube
  explosion (many unused cells)
• Try to eliminate as much as possible
• Make sure that you tune your product
  configuration well (many MOLAP products are
  sparsity aware)

61
Fact of Life (4) Flat Dimensions

• E.g. A dimension on Standard Industry Codes
  (SIC)
• In ROLAP just another attribute on your customer
  (maybe)
• In MOLAP, a member attribute on the lowest level
  of your customer hierarchy (if your product
  permits it)
• Keep the number of dimensions down!

62
Dimension Data Become Row-/Column Headers in
Reports
Star Schema
Customers
Product Xxx Yyy Zzz Jan - -
- Febr - - - Mar -
- - Apr May ....
Products
age
Productkey Productname etc.
gender
Sales Detail
income level
education
(billions of records)
Not only your model, but also your data must
look good!
Time
Markets
Time_key Day Month Year ...
Marketkey Sales_area ....
63
Slowly Changing Dimensions

• When data is changed, what can you do?
• Overwrite (if you dont care loosing the history)
• Create another dimension record (if for instance
  a customer moves) - (what about the keys? --gt use
  surrogate keys!)
• Create current and previous value fields (for
  instance changing sales territories)

64
Surrogate keys

• Because the meaning of IDs change (SKUs,
  moving customers etc.)
• Because concatened primary keys are impractical
• Keep external keys as (a) dimension field(s)
• Use plain integers for data warehouse keys (users
  shouldnt see them, they are just used for joins)
• In short Always repeat ALWAYS use them!
• You will want to hide them from the users by way
  of using views (ROLAP) and external, natural key
  values, which the users are familiar with

65
SCD Type 2 The Past
Customer dimension
Sales facts
66
The Change The customer moves
Customer dimension
Sales facts
SCD Type 2 History is preserved, but how many
customers do we have?
67
Effective Dates?

• Effective_begin_date effective_end_date
• Might be the only way to deal with late arriving
  records
• But
• What is the meaning of Manufactured from/to
  versus Sold from/to?
• Which attributes are affected?
• Makes query construction complicated
• If you use them, use a current_flag also!

68
Type 6 (231)

• When sales districts change randomly
• Sales Team Key
• Sales Team Name
• Sales Physical Address
• Begin Effective Date
• End Effective Date
• Is_current_flag (type 2)
• Current District (type 1)
• Old district (type 3)
• .

69
Impact of SCD gt 1

• Small matter of programming
• How to detect changes?
• Cyclic Redundancy Check (CRC) is a possibility
• Which changes are important?
• Type 6 requires many updates
• Changes can cascade

70
Pragmatic preservation of history

• Make historical copies of your MOLAP cubes or
  ROLAP databases per year
• Make copies of the complete dimension, when major
  changes occur, and use those copies for
  historical analysis, maybe in a separate
  environment

71
The Facts/Measures

• Mostly raw numeric items, relevant measures, and
  dimension keys only. Can signify events or
  coverage
• Try to use as few measures as you can get away
  with, these are costly
• From some million to more than a billion
  observations
• Items are typically additive. May be
  semi-additive or non-additive in special cases
• Access primarily via dimensions
• Families of facts are common

72
Value of additivity

• Prevent incorrect computations
• Percentages and other statistical measures cannot
  always be simply added together
• For example, average bank balances
• Good advice
• Store base measures
• Calculate percentages and other statistics when
  facts are retrieved
• Be careful with NULLs in the database!

73
Additive measures

• Numeric datatypes
• Units sold
• Dollars sold versus per unit dollars
• Claim amount
• Discount dollars
• Profit before tax
• Tax dollars
• Service charges
• Number of calls
• Number of transactions

74
Typical facts

• Sales and purchases
• Daily, weekly, monthly, quarterly sales
• Policies sold, claims sold
• Orders, shipments
• Budget forecasts, actuals

75
Reasons for going to the transaction level

• Behavior analysis
• Time-of-day analysis / queue analysis
• Time gap analysis
• Sequential behavior
• Fraud detection
• Cancellation warnings
• Basket analysis

76
Technically speaking (ROLAP)

• Fact table
• Must have primary key
• Joined to dimensions through foreign key
  references
• Usually physically sorted by time dimension and
  the primary analysis path

77
Mystery fields in the fact records

• Facts Only measures and keys to dimensions
• Sometimes you see fields, which are not that and
  which also not appear to be textual attributes of
  dimensions or other foreign keys.
• Most often these fields are codes and are sparse
• If they are really necessary, try to create one
  or more mystery dimensions out of them
• Look at correlations between values of the
  mystery fields
• Assume X has 200 values and Y has 1000 values
• If 1000 combinations of X, Y exist, then X is a
  parent of Y
• If 100000 combinations exist, then they are
  completely uncorrelated, ie. two dimensions.

78
MOLAP Hierarchies in the Measures Dimension

• Some (most) MOLAP products allow you to set up
  hierarchies within the measures dimension (a.k.a.
  the Accounts Dimension in Essbase)
• This is particularly useful in financial
  reporting
• Requires some level of manually entered
  definitions
• ROLAP
• Push down parent to its children
• More than one fact table
• A helper table (discussed later)

79
An Essbase Example
80
Using degenerate dimensions(ROLAP)

• Unique, primary key of a sales fact table
• Time
• Product
• Store/Register
• Promotion
• Customer
• Employee
• Ticket (degenerate)
• Line (degenerate)

81
Good reasons for getting the fact table primary
key right

• Global Warming
• Avoid more rows than necessary (if granularity of
  fact and dimensions do not match)
• Lost dimensions
• Could be the reason for the problem
• Lost attributes
• Not getting a dimension detailed enough
• Low-cost Insurance
• For avoiding duplicate rows
• Kids and Matches
• Nobody will be tempted to join two fact tables
• (Thanks to Jim Stagnitto of Questral, Inc.)

82
Technically speaking (ROLAP)

• Fact table
• non key columns are usually not indexed, rapid
  access is through the dimensions
• Columns often occur in sum(), rank(), min()
  functions

83
Reliable relations(ROLAP)

• All joins between dimensions and fact tables
• Completely understood
• One to many (dimension to fact) relation based on
  foreign key references of the fact tables
  multi-part key
• Referential integrity enforced. Always

84
Oops - forgot one thingThe Indexes!
Rate period
Cust
In an ordinary (universal) database, you will
have 10 indexes on the fact table 1 PK 9 FKs.
Discount type
Call type
Call detail
Access method
Batch
Juris- diction
Hour
Day
85
Sample ROLAP Index Calculation

• You want to
• Keep the
• number of
• dimensions down!
• Use integer keys!
• Find alternatives
• to B-trees!

86
Audit, Balance and Control

• Source feed file name for the row
• Job instance that processed the row
• Record number in the feed file
• Can also contribute to a unique key for the row

87
Families of Facts

• Related facts
• Aggregated facts

88
Related Facts

• Shared dimensions must be the same
• Value chain, eg. in Manufacturing

Manufactoring Inventory
Manufactoring Shipments
Distribution Inventory
Distribution Shipments
Store Inventory
Store Sales
Flow of Product Each Process a set of Facts
89
Drilling Across The Value Chain
Customer Dimension
Time Dimension
Manufactoring Inventory
Manufactoring Shipments
Distribution Inventory
Distribution Shipments
Store Inventory
Store Sales
Product Dimension
90
Drilling AcrossThe Budgetting Chain
Time Dimension (Month)
Department Dimension
Budget Foreign keys Budget amount
Line Item Dimension
Account Dimension
Commitments Foreign keys Commitment Amount
Payment Dimension
Commitment Dimension
Payments Foreign keys Payment Amount
91
Building SupermartsThe Data Warehouse Bus
Architecture

• Conformed Dimensions
• Standard Fact Definitions
• Revenue, Profit, Price, Cost etc.
• Granularity at the lowest level in front of the
  data marts
• Data Marts constructed from these standard
  sources as necessary

92
Aggregated Facts (ROLAP)

• Multiple fact tables
• Share one or more dimensions
• Daily fact table
• Monthly fact table
• Monthly Category fact table
• Caveat What keys to use in dimension tables?
• Do not use level fields!
• Use MOLAP or HOLAP whereever possible!

93
Drawbacks of aggregate tables

• There are so many of them!!!
• You is the one, who must manage them!
• All they do for you is enabling you to get better
  performance in routine queries
• The application is programmed to your aggregation
  scheme if that changes, then .
• Digression If you also use logical partitioning,
  you will have hundreds of tables!

94
Aggregate Tables in ROLAP

• Be careful out there!
• You must use an aggregate navigator

Client tool
Plain SQL
Metadata and statistics
Navigator
Aggregate aware SQL
Data and aggregates
DBMS
95
Families of Facts

• Heterogeneous Productlines, such as in Banking

Month_key Product_key Customer_key Status_key Earn
ed_dollars Paid_dollars Average_balance ----------
--- Num ATM Trans Num Branch Trans Num
Overdrafts Tot Overdraft Fees Overdraft
Limit Declined Trans
Core keys and facts, kept in one common
table Facts applicable only to checking
accounts
96
Families of Facts

• Transactions
• Date_key
• Product_key
• Sales_person_key
• Customer_key
• Transaction_key
• Amount
• And/or snapshots
• Month_key
• Product_key
• Sales_person_key
• Customer_key
• Status_key
• Earned_dollars
• Paid_dollars
• Average_balance

Why not both?
97
Using snapshots

• When transactions are not pieces of revenue
• Deposits / withdrawals
• Payments in advance
• Insurance coverage premiums
• Consider a current rolling snapshot (period to
  date)
• Consider a status dimension
• Many fields in the snapshot fact table, some
  possibly semi-additive

98
Related Facts in MOLAP

• MOLAP products allow only one measures dimension
  per cube
• You must, if necessary at all, combine related
  facts into one measures dimension
• Use a Version (Scenario) dimension with
  values like eg.
• Actual
• Budget 2001-01
• Prognosis 2001-05-01
• Maybe also a Type dimension, eg.
• Income
• Expenses
• Taxes
• Or build several cubes, one for Income, one for
  Expenses etc.
• Many MOLAP products allow you to combine cubes
  into Virtual Cubes using a library of shared
  dimensions

99
Similar models in each industry

• A common framework for every industry
• Retail
• Telecommunications
• Transportation
• Insurance
• Healthcare
• Manufacturers
• Banking
• Government
• Websites
• E-business

100
Retail (1) The Grocery Store
101
Retail (2) Orders
102
Telco The Billing CDR
103
Transportation
104
Insurance (1) Bus Architecture
105
Insurance(2) Some fact tables
106
Manufacturing (1)
107
Manufacturing (2)
108
Manufacturing (3)
109
Inventory
110
Deliveries
111
Drilling Across The Value Chain
Customer Dimension
Time Dimension
Manufactoring Inventory
Manufactoring Shipments
Distribution Inventory
Distribution Shipments
Store Inventory
Store Sales
Product Dimension
112
Banking
113
Website Analysis

• Going beyond log file statistics
• WebTrends, Analog, NetTracker etc.
• Time-series analysis is necessary
• What happened during a site visit?
• Why was the visit abandoned?
• What is the effectiveness of a targetted
  promotion?
• What is the trend in the above over time?
• The Clickstream Data Warehouse

114
So, whats new?

• From Sales Facts to User Activity Facts
• (How) do we know the User (customer)?
• Sessions
• Pages
• Events
• Probable cause of the visit/sale
• Where did the customer come from?
• The World Wide Web (247365, multiple languages,
  cultures, timezones )
• From CRM to eRM electronic Relationship
  Management

115
Website Design also matters

• The clickstream data warehouse needs information
  about
• Pages
• Cookies
• Users
• Clickable objects
• URLs
• Events
• Etc. Etc.
• This content and event information must be
  presentable to end-users!
• Some can be obtained by using log file processors
• It is likely you will have to do a considerable
  amount of data clean-up, if the website is not
  well-designed!

116
Web effects on old dimensions

• Calender
• Local time, global time
• Time of Day
• Customer
• Becoming a user dimension
• Cookies
• Named users
• Integration with eg. CRM
• The global perspective
• Promotion
• Must take web advertising into consideration
• Dynamic, individual, targeted special offers,
  maybe in real time

117
New Web Dimensions

• Page
• Event
• e.g. Open Page, Refresh Page, Click Link, Enter
  Data
• Session
• Type of session, context/mission, success etc.
• Referral
• How did the customer/visitor arrive?

118
Choosing the grain

• Page event
• Session
• Aggregated levels

119
E-business
120
The Data Webhouse

• Coined by Ralph Kimball in The Data Webhouse
  Toolkit, Ralph Kimball and Richard Merz, Wiley
  2000, ISBN 0-471-37680-9
• The Data Warehouse on the Web
• The Data Warehouse as the driver of the website
  (closing the loop)

121
The Birth of the Data Webhouse
122
Synonyms

• Basic function
• avoids multiple join paths between two tables
• makes schema more legible and thus less prone to
  query formulation errors
• use view to rename columns for ease of use with
  query tools
• Rationale over separate dimension tables
• Reduces need to duplicate data
• Simplifies administration
• CREATE FIRST_OPEN_TIME AS SYNONYM FOR TIME
• Not all databases support this - you may use
  views instead

123
Typical synonyms

• City tables
• origin and destination (travel facts)
• Period tables
• order date and ship date
• Customer tables
• customers for ship to and bill to

124
Factless Fact Tables
Promotion Coverage Fact Table time_key product_key
store_key promo_key
Time
Product
Promotion
Store
125
A Factless snapshot table
126
The Effect of Multidimensional Design on the ETL
system

• Existence checks
• Denormalisation (N-way, multilevel merge)
• Lookup values
• Deal with missing values for foreign keys

127
Missing Values

• Which are the problems?
• Default values in source data
• Dummy values in source data
• Missing values in source data
• What can be done?
• Fix the source system(s) it is a data quality
  issue
• Try to infer values
• Use a dummy value
• More than one dummy value? (Unknown, Unavailable,
  Not applicabale)
• Missing dimension keys?
• (The dummy dimension record Cust.No. 1, Name
  Unknown)
• What about dummy dates? And numeric values?
• (See Dealing with Missing Values In The Data
  Warehouse from www.sbti.com (Author John Hess,
  Stonebridge Technologies 1998, now to be found on
  www.olap.it/articles !)

128
The 38 subsystems of ETL!
From the new book The Data Warehouse ETL
Toolkit, Ralph Kimball and Joe Caserta, Wiley
2004, ISBN 0764567578

• Extract
• Change data capture
• Data profiling
• Data cleansing
• Data conformer
• Audit dimension
• Quality screener
• Error event handler
• Surrogate key creation
• Slowly Changing Dims
• Late arriving dims
• Fixed hierarchy dims
• Variable hierarchy dims

• Multivalued dims
• Junk dimensions
• Facttable transaction load
• Periodic snapshot load
• Accum. Snapshot load
• Surrogate key pipeline
• Late arrivals handler
• Aggregate builder
• Cube builder
• Partition builder
• Dimension manager
• Facttable provider
• Job scheduler

• Workflow monitor
• Recovery and restart
• Parallelizing system
• Problem escalation
• Version control
• Version migration
• Lineage dependencies
• Compliance reporter
• Security
• Backup
• Metadata repository
• Project management

129
Documentation

• Data Mart structure
• Logical Model
• Read The Data Warehouse Lifecycle Toolkit for the
  complete picture!

130
Advanced Design Issues

• When the going gets tough,
• the tough get going!

131
Tricky stuff

• Monster Dimensions
• Multivalued Dimensions
• Multilevel Hierarchies
• Really Difficult Business Questions

132
Monster Dimensions

• Some dimension tables grow VERY big, eg.
  customers, who also have many attributes
• Some demographic attributes change often
  (income, number of children etc.)
• Some demographic attributes are not used very
  much
• Result A lot of wasted space, indexes and
  complexity in keeping up to date

133
Dealing with Monster Dimensions
Demographics dimension
Customer dimension
customer_key name address birth date other
stable attributes
demographics_key income_band purchases_band no_chi
ldren education_level etc.
Sales facts
Customer_key Demographics_key ......
Note Contains all possible combinations predefin
ed and preloaded!
134
Multivalued Dimensions

• Healthcare billing fact
• Date
• Patient
• Doctor
• Location
• Service performed
• Diagnosis
• Payer
• etc.

Do people only have one illness at a time?
135
What are the alternatives?

• Forget the dimension!
• Choose one value (Primary Diagnosis)
• Fixed number of diagnoses
• Going MM?

136
Helper Tables
Payer
Patient
Doctor
Diagnosis Group diagnosis_group_key diagnosis_key
weight_factor
Bill fact . diagnosis_group_key . . .
Location
Diagnosis Dimension diagnosis_key description type
category
Service
Day
137
Solving a Multivalued Dimension with a Helper
Table

• Use only when absolutely necessary
• Weight factors usually equal size within a group
  and should always add up to one
• May use a view to hide the helper table
• Healthcare, Retail Banks, SIC-codes

138
Customers Hierarchies
The Problem is The Customer decides how many
levels there are!!!
139
The Customer Hierarchy Model
customer_key name address base_org always
populated level5_org level4_org level3_org level2_
org populated if 2 or more top_org always
populated other attributes
140
Using a Helper Table
Service
Seller
Customer Dimension customer_key etc. etc.
Customer_tree_path parent_customer_key child_custo
mer_key depth_from_parent lowest_flag topmost_flag
Bill fact . customer_key . . .
Contains one record for each separate path from
each node to itself and to every node below it
Day
141
Pros and cons

• Works like a normal dimension constraint
• Use depth_from_parent to eg. get only immediate
  subsidiaries
• Use lowest_flag to get only leaf nodes
• Reversing the joins will take you upwards
• Maybe add begin_effective_date and
  end_effective_date, but be careful
• Maybe add weighting factor to support partially
  owned subsidiaries
• May grow very big, quickly!

142
The bigger picture

• Classification of analytical applications
• Data Mining besides SQL and OLAP
• International issues
• Recommandations

143
Really DifficultBusiness Questions

• Simple Constraints
• Simple Subqueries
• Correlated Subqueries
• Simple Behavioral Queries
• Derived Behavioral Queries
• Progressive Subsetting Queries
• Classification Queries

144
1) Simple Constraint

• Constraints against literal constants
• Show the sales of candy products in September 1997

145
2) Simple Subquery

• Constraints against a global value found in the
  data
• Show the sales of candy products in September
  1997 in those stores that had above average sales
  of candy products

146
3) Correlated Subquery

• Constraints against a value defined by each
  output row
• Show the sales of candy products for each month
  of 1997 in those stores that had above average
  sales of candy in that month

147
4) Simple Behavioral Query

• Constraints against values resulting from an
  exception report or a complex series of queries
  that isolate desired behavior
• Show the sales of those candy products in
  September 1997 whose household penetration for
  our grocery chain in the 12 months prior to
  September were more than two standard deviations
  less than the household penetration of the same
  products across our 10 biggest retail competitors

148
5) Derived Behavioral Query

• Constraints against values found in set
  operations on more than one complex exception
  report or series of queries
• Show the sales of those candy products identified
  in example 4, and which also experienced a
  merchandise return rate of more than two standard
  deviations greater then our 10 biggest retail
  competitors
• (Intersection of two behavioral queries)

149
6) Progressive Subsetting Query

• Constraints against values, as in number 4, but
  temporally ordered so that membership in an
  exception report is dependent on membership in a
  previous exception report
• Show the sales of those candy products in example
  number 4 that were similarly selected in August
  1997 but were not similarly selected in either
  June or July 1997

150
7) Classification Queries

• Constraints on values that are the results of
  classifying records against a set of defined
  clusters using nearest neighbor and fuzzy
  matching logic
• Show the percentage of low-calorie candy sales
  contained in the 1000 market baskets whose
  content most closely matches a young,
  health-conscious family profile

151
Challenges

• Most query tools do not support the more complex
  query types
• You may build in support for those, which your
  users need

152
Multi-step approach (SQL)
Query
Query
Result table
Query
Build result table(s) that contains only keys
(and maybe also time information) of those
objects, who display the desired, special
behavior(s)
Query
153
Extended ROLAP or MOLAPfor Behavioral Analysis
Sales facts
Regular dimensions
Special behavior dimension(s) )
Time_key Product_key Customer_key Store_key Promot
ion_key Ticket_number Line_number Units_sold Dolla
rs_sold
Time dimension
Product_key defined by complex behavior study
Product dimension
Customer dimension
Store dimension
Promotion dimension
) May be hidden in views
154
Preparing for Data Mining

• Rigorous quality assurance on the values, which
  you are going to mine
• Supply values as text, not codes
• Eliminate context dependencies
• Flag normal, abnormal, out of bound or impossible
  facts
• Mask out random or noise values
• Uniform treatment of NULL/missing/unknown
• Use Status dimensions (eg. Customer about to
  cancel etc.)
• Find training, test and evaluation sets
• Supply computed values (eg. Profit)
• Band continous values

155
Data Mining Algorithms

• Clustering
• Decision Trees
• Neural Networks
• Statistics
• Fuzzy Logic
• Genetic Algorithms
• Ants
• Hybrids

156
Integrated Data Mining

• The Multidimensional Model is ideal as a source
  for Data Mining
• ROLAP is necessary for time-series / sequential
  analysis
• Ideal case for integration of software tools
• Microsoft Analysis Services 2000
• Oracle 9i
• Others?

157
Microsoft SQL Server 2000 Analysis Services
158
International Issues

• Languages
• Alphabets and character sets
• Names
• Adresses
• Numbers
• Telephone numbers
• Currencies
• Time of day
• Calendars
• Handling unsupported characters
• Collating sequencies
• See The Data Webhouse Toolkit, Ralph Kimball
  and Richard Merz, Wiley 2000, ISBN 0-471-37680-9

159
Comments on MS SQL Server 2005

• Unified dimensional model
• Multivalued dimensions
• Possible to have many flat dimensions
• No need to build cubes can put a layer on top
  of a non-multidimensional schema
• But
• Users need the ease of use
• Machines need the speed
• We will see, just how well this works in a short
  period of time

160
The biggest challenge Data Quality
161
What is Data Quality?

• Accuracy
• Does the data accurately represent reality?
• Integrity
• Is the structure of data and relationships among
  entities and attributes maintained consistently?
• Consistency
• Are data elements consistently defined and
  understood?
• Completeness
• Is all necessary data present?
• Validity
• Do data values fall within acceptable ranges
  defined by the business?
• Timeliness
• Is data available when needed?
• Accessibility
• Is the data easily accessible, understandable,
  and usable?

162
Data quality and integration issues

• Legacy data issues
• Data accessibility availability
• Insufficient time to analyse
• Inaccurate Metadata, documentation
• Lack of resources
• Disparate systems
• Data ownership issues
• Semantic differences
• Structure violations
• Rule violations

Home-grown applications
Web applications
ERP
CRM
Legacy applications
Data Warehouse
163
Why do data integration projects fail?

• The source data is not fully understood
• Complexity is underestimated
• Planning is actually guesswork
• Systems do not join as expected
• Delays when analysts/programmers need to
  interpret results
• Poor data analysis leads to complex development
  cycle and unpredictable rework
• Problems are uncovered ad-hoc and late
• Manual analysis on samples is time consuming,
  laborious and inaccurate
• Full volume testing is done too late

164
Data profiling analysis

• Data profiling analysis is critical to
• Understand the scope and nature of the problem
• Determine success criteria
• Accurate planning
• Automated tools are available
• Do it right the first time
• Then keep on doing it!

165
Examples of how to find data quality problems
using Data Profiling

• How do you identify those customer records where
  values are missing or incomplete ?
• Are the product codes correct in my order entry
  system?
• Will my data actually integrate?
• Are all the order shipment dates correct?
• Are my supplier details correct?
• How do I find misspellings of any attribute
  values?
• What about redundant data how do I find it?
• Are the relationships held within my data
  consistent ?
• Will my data support the new business
  requirements?

166
Benefits of automated Data Profiling

• Improve Business responsiveness
• Time to market reduced
• Enhance Data Quality
• Ensure data is accurate and fit-for-purpose
• Project Planning
• Early Accurate
• Reduce Risks
• Identify ALL data-related issues at the start
• Manage Resources
• Deliver with less effort
• Reduce Costs
• Reduce cost of analysis and build

167
Data Quality is a Business Issue

• The Business owns the data
• Set data quality standards across the company
• Build company-wide metadata knowledge
• Data Quality must be managed

168
Data Warehouse in the future?

• Real-time?

169
Data Warehouse andthe Enterprise Nervous System

• Contemporary Enterprise Information Architecture
  calls for
• Realtime
• Integration
• Message brokers
• Service Oriented Architectures etc.
• What is the role of the Data Warehouse in this?
• This is based on an actual and recent
  architecture study for a Danish government body

170
Characteristics of a mature Data Warehouse

• Vision One environment, one version of the Truth
• Integration of data from disparate sources
• Refinement of data
• Searching and browsing
• Production data
• Other data (partners, public / purchased
  information)
• Master data
• The Historical Data Warehouse
• Management Information
• Statistics
• Data Exchange
• Production reporting
• Ad hoc

171
More characteristics

• Much multidimensionality
• Refined data, both details and aggregations
• 1000 frequent users
• Also external recipients
• New systems The interface(s) to the DW should be
  defined
• Development projects Decide for either a
  production system or a Data Warehouse (based on
  technical feasibility)
• Standardised data model (most often not
  documented, frequently changed)
• Analysis
• Reporting
• Ad hoc tasks (incl. operational one-time systems)

172
Components of a matureData Warehouse

• Database(s)
• Normalised
• Multidimensional
• Load processes
• Predominantly batch
• Homegrown (COBOL)
• ETL jobs in e.g. Informatica
• BI tools such as e.g. Business Objects
• Applications
• Predefined reports
• Ad hoc environments
• Statistics
• SAS

173
Business benefits from a DW

• Holistic view of data across disparate systems
• Integration of data from different sources,
  including external partners
• History
• Refinement of data
• Presentation of data for business people
• Analysis, incl. decision support
• Reporting
• Ad hoc solutions
• Data foundation for statistics
• Data exchange

174
The Enterprise Nervous System
Portal platform
Intranet (internal portal)
Internet (external portal)
Portlet api
Portlet api
CMS- modules
Email/ Calendar
Workflow
New services
Data mining
Eksternal WS UDDI
Internal WS UDDI
Info services
New services
CMS- modules
Info services
Web Service
OCES certificate handling
Service- and Integration platform (ENS)
Common services
Analysis
Update
Query
Statistics
Report
Infrastructure services - single
signon security, administration, mv.
Business Activity Monitoring and Workflow
management
Integration platform with persistence
Integration Broker
User catalog
New systems data
Data- Warehouse
Messages
CMS data and metadata
175
The Data Warehouse hasserved us well

• The concept of a centralised database is maybe
  not as necessary from now on
• But the practises of Data Warehousing are
  important
• Good Data Management
• Good performance
• Data refinement
• Data presentation
• Realtime integration is certainly useful and
  practical
• Business Intelligence is evolving, Business
  Activity Monitoring is a natural next.
• We have learned a lot now is the time to do it
  right!

176
What is going on in data modelling?
strong semantics
Source Leo Obrst, The Mitre Corp.
weak semantics
177
Taxonomies The unstructured world of documents
178
Conceptual model (ORM)
Syntax defined in Nijssen, G.M. and T.A. Halpin.
Conceptual Schema and Relational Database Design
- A fact oriented approach. Prentice Hall 1989.
179
RDF based schema for Family
Property labels t rdftype s
rdfssubClassOf d rdfsdomain r
rdfsrange et rdfsxcollectionElementType
Kilde Stephen Cranefield, Journal of Digital
Information, Volume 1 Issue 8, Article No. 44,
2001-02-15
180
UML Class diagrams
Kilde Stephen Cranefield, Journal of Digital
Information, Volume 1 Issue 8, Article No. 44,
2001-02-15
181
Web Ontology Language (OWL)

• lt?xml version"1.0"?gt
• ltrdfRDF
• xmlns"http//mySite.com/myOntology"
• xmlnsrdf"http//www.w3.org/1999/02/22-rdf-synt
  ax-ns"
• xmlnsrdfs"http//www.w3.org/2000/01/rdf-schema
  "
• xmlnsowl"http//www.w3.org/2002/07/owl"
• xmlbase"http//mySite.com/myOntology"gt
• ltowlClass rdfID"Person"/gt
• ltowlClass rdfID"Mother"gt
• ltrdfssubClassOf rdfresource"Person"/gt
• lt/owlClassgt
• ltowlObjectProperty rdfID"hasChild"gt
• ltrdfsdomain rdfresource"Mother"/gt
• ltrdfsrange rdfresource"Parent"/gt
• lt/owlObjectPropertygt

• ltowlequivalentClassgt
• ltowlClassgt
• ltowlintersectionOf rdfparseType"Collection"
  gt
• ltowlClass rdfabout"Mother"/gt
• ltowlRestrictiongt
• ltowlonPropertygt
• ltowlObjectProperty rdfabout"hasChild"/gt
  
• lt/owlonPropertygt
• ltowlsomeValuesFromgt
• ltowlClass rdfID"Parent"/gt
• lt/owlsomeValuesFromgt
• lt/owlRestrictiongt
• lt/owlintersectionOfgt
• lt/owlClassgt
• lt/owlequivalentClassgt

182
Information Management II

• Data Warehouse showed us how to
• Semantics is the key
• Ontologies is the foundation
• Repositories is the technology
• Gartner Enterprise Information Architecture
• The sponsor is The needs for integration!

183
Wrap up
184
Listen to Ralph

• Embed all knowledge of the data in the data
• Stick to one level of dimension tables
• Aggregates should be separate tables
• Use an aggregate navigator (serverside)
• Even better Use MOLAP or HOLAP
• Stick to simple star schemas
• Properly design conformed dimensions and
  conformed facts, first!

185
Listen to me

• K.I.S.S.
• Keep It Simple, Stupid!

186
Ralph Kimballs 20 criteria
187
Literature

• The Data Warehouse Toolkit Second Edition,
  Wiley 2002, ISBN 0-471-20024-7
• The Data Warehouse Lifecycle Toolkit, Ralph
  Kimball, Laura Reeves, Margy Ross and Warren
  Thornthwaite, Wiley 1998, ISBN 0-471-25547-5
• The Data Webhouse Toolkit, Ralph Kimball and
  Richard Merz, Wiley 2000, ISBN 0-471-37680-9
• Data Warehouse Design Solutions, Christopher
  Adamsan and Michael Venerable, Wiley 1998, ISBN
  0-471-25195-X
• Microsoft OLAP Solutions, Erik Thomsen, George
  Spofford and Dick Chase, Wiley 1999, ISBN
  0-471-33258-5
• Improving Data Warehouse and Business
  Information Quality, Larry P. English, Wiley
  1999, ISBN 0-471-25383-9

188
Usefull web-adresses

• www.ralphkimball.com (Ralph Kimball)
• www.dwinfocenter.org (Larry Greenfields Data
  Warehouse Info Center)
• www.intelligententerprise.com (Intelligent
  Enterprise Magazine, previously DBMS magazine -
  many articles by Ralph Kimball and other fine
  people - for example August 97 A Dimensional
  Modelling Manifesto)
• www.datawarehousing.com (home of DWLIST)
• www.tdwi.org - The Data Warehouse Institute
• www.iaidq.org, The International Association for
  Information and Data Quality (IAIDQ)
• http//www.tondering.dk/claus/calendar.html,
  everything you would ever want to know about
  calendars!

189
What to do first?

• Buy and Read Ralph Kimballs The Data Warehouse
  Toolkit Second Edition, Wiley 2002, ISBN
  0-471-20024-7
• Buy and read The Data Warehouse Lifecycle
  Toolkit, Wiley 1998, ISBN 0-471-25547-5, Ralph
  Kimball, Laura Reeves, Margy Ross and Warren
  Thornthwaite
• Buy and read Data Warehouse Design Solutions,
  Christopher Adamson and Michael Venerable, Wiley
  1998, ISBN 0-471-25195-X.
• Buy and read The Data Warehouse ETL Toolkit,
  Ralph Kimball and Joe Caserta, Wiley 2004, ISBN
  0-7645-6757-8
• Just Do It!

190
Thank You!
thomasf_at_tf-informatik.dk 45-40 54 83 40
(GSM) 04.93.33.88.93 (occasionally) 45-49 70 83
40 (Landline)