MIS 385/MBA 664 Systems Implementation With DBMS/ Database Management

MIS 385/MBA 664
Systems Implementation with DBMS/

Database Management
Dave Salisbury
salisbury@udayton.edu (email)
http://www.davesalisbury.com/ (web site)
Objectives
Definition of terms
Reasons for information gap between
information needs and availability
Reasons for need of data warehousing
Describe three levels of data warehouse
architectures
Describe two components of star schema
Estimate fact table size
Design a data mart
Develop requirements for a data mart
Definition
Data Warehouse:
A subject-oriented, integrated, time-variant, nonupdatable collection of data used in support of

management decision-making processes
Subject-oriented: e.g. customers, patients, students,
products
Integrated: Consistent naming conventions, formats,
encoding structures; from multiple data sources
Time-variant: Can study trends and changes
Nonupdatable: Read-only, periodically refreshed
Data Mart:
A data warehouse that is limited in scope
History Leading to Data

Warehousing
Improvement in database technologies,

especially relational DBMSs
Advances in computer hardware, including
mass storage and parallel architectures
Emergence of end-user computing with
powerful interfaces and tools
Advances in middleware, enabling
heterogeneous database connectivity
Recognition of difference between operational
and informational systems
Need for Data Warehousing
Integrated, company-wide view of

high-quality information (from
disparate databases)
Separation of operational and
informational systems and data
(for improved performance)
Need for Data Warehousing
Data warehouse versus Data

mart
Issues with Company-Wide View
Inconsistent key structures

Synonyms
Free-form vs. structured fields
Inconsistent data values
Missing data
cf. Figure 11.1
Examples of heterogeneous data
Organizational Trends Motivating

Data Warehouses
No single system of records

Multiple systems not synchronized
Organizational need to analyze
activities in a balanced way
Customer relationship
management
Supplier relationship management
Data Warehouse Architectures
Generic Two-Level Architecture

Independent Data Mart
Dependent Data Mart and Operational
Data Store
Logical Data Mart and Real-Time Data
Warehouse
Three-Layer architecture
All involve some form of extraction,
transformation and loading (ETL)
Generic two-level data

warehousing architecture
L
T
One,
companywide
warehouse
E
Periodic extraction data is not completely current in warehouse
Independent data mart data

warehousing architecture
Data marts:
Mini-warehouses, limited in
scope
T
E
Separate ETL for each
independent data mart
Data access
complexity due to
multiple data marts
Dependent data mart with

operational data store: a three-level
architecture
ODS provides option
for obtaining current
data
T
E
Single ETL for
enterprise data warehouse
(EDW)
Simpler data access

Dependent data
marts loaded from
EDW
Logical data mart and real time

warehouse architecture
ODS and data warehouse
are one and the same
T
E
Near real-time ETL for
Data Warehouse
Data marts are NOT separate

databases, but logical views of the
data warehouse Easier to create
Three-layer data architecture for

a data warehouse
Data Characteristics
Status vs. Event Data
Status
Event = a database
action
(create/update/delete)
that results from a
transaction
Status
Transient vs. Periodic Data
With
transient
data,
changes
to
existing
records
are
written
over
previous
records,
thus
destroyin
g the
previous
data
Transient vs. Periodic Data
Periodic
data are
never
physicall
y
altered
or
deleted
once
they
have
been
added
to the
store
Other Data Warehouse Changes
New descriptive attributes

New business activity attributes
New classes of descriptive
attributes
Descriptive attributes become
more refined
Descriptive data are related to one
another
New source of data
Derived Data
Objectives
Ease of use for decision support applications

Fast response to predefined user queries
Customized data for particular target
audiences
Ad-hoc query support
Data mining capabilities
Characteristics
Detailed (mostly periodic) data

Aggregate (for summary)
Distributed (to departmental servers)
Star schema
Most common data model for data marts (also

called dimensional model)
Fact tables contain factual or quantitative data
Dimension tables contain descriptions about
the subjects of the business
Dimension tables are denormalized to
maximize performance
1:N relationship between dimension tables and
fact tables
Excellent for ad-hoc queries, but bad for online
transaction processing
Star schema components

Fact tables contain
factual or quantitative
data
1:N relationship between

dimension tables and fact
tables
Dimension tables are

denormalized to maximize
performance
Dimension tables contain

descriptions about the subjects
of the business
Star schema example

Fact table provides statistics
for sales broken down by
product, period and store
dimensions
Star schema with sample data
Issues Regarding Star Schema
Dimension table keys must be

surrogate (non-intelligent and
non-business related),
because:
Keys may change over time
Length/format consistency
Granularity of Fact Tablewhat

level of detail do you want?
Transactional grainfinest level

Aggregated grainmore summarized
Finer grains better market basket
analysis capability
Finer grain more dimension tables,
more rows in fact table
Duration of the databasehow

much history should be kept?
Natural duration13 months or 5
quarters
Financial institutions may need
longer duration
Older data is more difficult to
source and cleanse
Fact table can get huge

(monstrous)
Depends on the number of dimensions

and the grain of the fact table
Number of rows = product of number of

possible values for each dimension
associated with the fact table
For example, take Figure 11.11
Assume only half the products record

sales for a given month, the total rows
would be calculated as:
1000 stores X 5000 active products X 24
months = 120,000,000 rows (yikes!)
Modeling dates
Fact tables contain time-period data

Date dimensions are important
Variations of the Star Schema
Multiple Facts Tables
Can improve performance

Often used to store facts for different combinations
of dimensions
Conformed dimensions
Factless Facts Tables
No nonkey data, but foreign keys for associated

dimensions
Used for:
Tracking events
Inventory coverage
Normalizing Dimension Tables
Multivalued Dimensions
Facts qualified by a set of values for the same business

subject
Normalization involves creating a table for an associative
entity between dimensions
Hierarchies
Sometimes a dimension forms a natural, fixed depth

hierarchy
Design options
Include all information for each level in a single denormalized

table
Normalize the dimension into a nested set of 1:M table
relationships
32
Slowly Changing Dimensions

(SCD)
Need to maintain knowledge of the past

One option: for each changing attribute,
create a current value field and many
old-valued fields (multivalued)
Better option: create a new dimension
table row each time the dimension object
changes, with all dimension
characteristics at the time of change
33
The User Interface Metadata

(data catalog)
Identify subjects of the data mart

Identify dimensions and facts
Indicate how data is derived from enterprise data
warehouses, including derivation rules
Indicate how data is derived from operational
data store, including derivation rules
Identify available reports and predefined queries
Identify data analysis techniques (e.g. drill-down)
Identify responsible people
On-Line Analytical Processing

Tools
The use of a set of graphical tools that

provides users with multidimensional views of
their data and allows them to analyze the data
using simple windowing techniques
Relational OLAP (ROLAP)
Multidimensional OLAP (MOLAP)
Traditional relational representation

Cube structure
OLAP Operations
Cube slicingcome up with 2-D view of data

Drill-downgoing from summary to more detailed
views
Slicing a data cube
Example of drill-down
Starting with summary
data, users can obtain
details for particular cells
Summary
report
Drill-down
with color
added
Data mining & visualization
Knowledge discovery using a blend

of statistical, AI, and computer
graphics techniques
Goals:
Explain observed events or conditions

Confirm hypotheses
Explore data for new or unexpected
relationships
Data mining & visualization
Techniques
Statistical regression
Decision tree induction
Clustering and signal processing
Affinity
Sequence association
Case-based reasoning
Rule discovery
Neural nets
Fractals
Data visualizationrepresenting data in

graphical/multimedia formats for analysis

MIS 385/MBA 664 Systems Implementation With DBMS/ Database Management

Uploaded by

Copyright:

Available Formats

MIS 385/MBA 664 Systems Implementation With DBMS/ Database Management

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

MIS 385/MBA 664 Systems Implementation With DBMS/ Database Management

Uploaded by

Copyright:

Available Formats

MIS 385/MBA 664

Systems Implementation with DBMS/

A subject-oriented, integrated, time-variant, nonupdatable collection of data used in support of

A data warehouse that is limited in scope

History Leading to Data

Improvement in database technologies,

Need for Data Warehousing

Integrated, company-wide view of

Need for Data Warehousing

Data warehouse versus Data

Issues with Company-Wide View

Inconsistent key structures

Examples of heterogeneous data

Organizational Trends Motivating

No single system of records

Data Warehouse Architectures

Generic Two-Level Architecture

Generic two-level data

Independent data mart data

Dependent data mart with

Simpler data access

Logical data mart and real time

Data marts are NOT separate

Three-layer data architecture for

Other Data Warehouse Changes

New descriptive attributes

Ease of use for decision support applications

Detailed (mostly periodic) data

Most common data model for data marts (also

Star schema components

1:N relationship between

Dimension tables are

Dimension tables contain

Star schema example

Star schema with sample data

Issues Regarding Star Schema

Dimension table keys must be

Issues Regarding Star Schema

Granularity of Fact Tablewhat

Transactional grainfinest level

Issues Regarding Star Schema

Duration of the databasehow

Fact table can get huge

Depends on the number of dimensions

Number of rows = product of number of

For example, take Figure 11.11

Assume only half the products record

Fact tables contain time-period data

Variations of the Star Schema

Multiple Facts Tables

Can improve performance

Factless Facts Tables

No nonkey data, but foreign keys for associated

Normalizing Dimension Tables

Facts qualified by a set of values for the same business

Sometimes a dimension forms a natural, fixed depth

Include all information for each level in a single denormalized

Slowly Changing Dimensions