Semantic Data Modeling:
The Key to Re-usable Data
Stephen Brobst
Chief Technology Officer
Teradata Corporation
[email protected]
617-422-0800
2
Not just a collection
of subjects...
Activity
Party
Account
Product
Single, Integrated System
...but also their
relationships
Party
Product
Account
Activity
Don’t model subjects
individually!
Model your entire
business!
Enterprise Information Management
3
Functional Views
Sales
Marketing
Finance
Rates/
Regulatory
Customer
Service
Risk
Demographics
Pricing
General Ledger
Promotions
Products
Safety
Engineering
Production
HR
Contracts
Works OK for OLTP, but causes
data chaos for BI applications.
4
Business Intelligence Requires Data Integration
Product Data Customer Data Account Data Transaction Data G/L Data Market Data External Data
5 Copyright © 2005, Stephen A. Brobst. All rights reserved.
Data Modeling Techniques
Key observation: Practitioners in the data warehousing
industry frequently confuse construction of the semantic data
model, logical data model, and physical data model.
•
A semantic data model (SDM) captures the business
view of information for a specific knowledge worker
community or analytic application.
•
A logical data model (LDM) captures the business
relationships in the enterprise information independent
of a specific analytic application or departmental view.
•
A physical data model (PDM) captures the
6
Data Model Deployment
Conceptual Data Model
Project – A ‘ Project – B Project – C
Enterprise Data Standards
Subject Area ‘A’
Enterprise Logical Data Model(3NF)
xxxxxxx xxxxxxxxxx xxxxx xxxxxxx xxxxxxx xxxxxxxxxx xxxxx xxxxxxx xxxxxxx xxxxxxxxxx xxxxx xxxxxxx xxxxxxx xxxxxxxxxx xxxxx xxxxxxx xxxxxxx xxxxxxxxxx xxxxx xxxxxxx xxxxxxx xxxxx xx
Subject Area
‘A’
Physical Model Realization
Design Meta
Data
Semantic Model Views
Subject Area
‘B’
Subject Area
‘C’
Single Physical Data Model
7 Copyright © 2005, Stephen A. Brobst. All rights reserved.
Semantic Data Modeling
•
Semantic data modeling is a logical data
modeling technique; the semantic view of
information does not necessarily need to be
physicalized in the database.
•
There may be a different semantic data model for
each department/applications that uses the data
warehouse.
•
Dimensional modeling is a common technique for
constructing the semantic data model for an
analytic application, but is not the only viable
approach.
8
Dimensional
Physical Data Extensions
Different Semantic Model Designs are Appropriate
for Different Types of Knowledge Workers
Normalized Generic Structures
Index choices &
selective table
denormalizations
9 Copyright © 2005, Stephen A. Brobst. All rights reserved.
Physical Data Model
Physical data model represents the tables constructed in
the database.
Recommendations:
•
Use the (3NF) LDM as the starting point for the PDM
with selective denormalization when appropriate for
(primarily) performance reasons.
•
Overlay (dimensional) SDM on top of PDM using
views and/or semantic metadata in your BI tool.
•
Design LDM first, then use application-specific
business requirements to derive the SDMs and
performance considerations to map into the PDM.
10
Semantic Models Should be BI Tool Agnostic
MicroStrategy
Teradata OLAP Connector
Tableau
Tier 3
Access
Tier 2
Integrated
Tier 1
Acquisition
11
•
A collection of data modeling assets that help make database
design and development faster and easier for the access
layer:
>
Access layer provides path for data from the integrated data model
to end user consumption.
>
When this layer not well-designed, it can impact speed, security,
and simplicity in developing and delivering reports, BI applications.
•
Re-usable building blocks provide flexibility and consistency
to the development process:
>
SMBBs include pre-built semantic models.
•
Focuses on a specific analytic need
in a specific industry:
>
For example, Communications Mobile
Revenue Analytics.
•
SMBBs are to the semantic layer as
iLDMs are to the integrated layer of
a data warehouse implementation.
What is a Semantic Modeling
12
Dimensional Model
Dimension Building Blocks
Dimension Building Blocks Support a
Range of Analytical Needs
Fixed, Normalized Hierarchy
Fixed, Flattened Hierarchy
Variable Depth Hierarchy
13
What are SMBBs?
How are they related to an LDM?
Building from the Foundation for your
Data Warehouse:
•
An LDM is like a blueprint for a house
that you are building. It serves as the
foundation for your integrated data
warehouse.
•
The SMBBs are like room designs that
meet specific homeowner needs.
Different rooms need different designs
based on their purpose. Similarly, for
each new business application, new
semantic models are needed.
•
SMBBs provide different designs
(building blocks) for the modeler to
choose from in building the semantic
models.
•
These flexible, reusable building blocks
14
Q: Where does it all start?
A: Business requirements drive the process!
Relationships between the
Three Types of Data Models
The Logical Model is
used to drive
generalization and
support source data
leverage and reuse.
Logical Data Model
Physical Data Model
Semantic Data Models
Data
access
patterns
Support
data
re-use
The Semantic Model
captures data
access patterns that
must be supported
by the core physical
model.
The Physical Model
provides core
support for data
integration within
the information
architecture.
15
Semantic Layer Benefits
Efficient table joins can be encouraged
inside the SDM views.
Views are low maintenance objects.
Views do not consume database space.
Join indexes (JIs) and aggregate join
indexes (AJIs) can be created based on the
access paths embedded in the SDMs.
PDM is not compromised with new
application requirements.
16 Copyright © 2005, Stephen A. Brobst. All rights reserved.
