Logical Data Models for Agile BI
David D. Schoeff
Teradata - EDW Data Architect & Principal Consultant
Not Designing a Data Architecture is a
2 >
Why do we need an LDM?
Data Warehouse with LDM
3 >
Data Warehouse Without LDM
What is the Purpose of a Data Model?
A visual business representation of how
data is organized in the enterprise
It provides discipline and structure to the
complexities inherent in data management
Can you imagine building a house
without a blueprint?
Or driving across the country without a
map?
It facilitates communication within the
business (e.g. within IT and between IT and
the business)
It facilitates arriving at a common
understanding of important business
concepts (e.g what is a customer?)
4 >
Logical Data Model Components
LDM graphically represents
the data requirements and
data organization of the
business
> Identifies those things about which
it is important to track information
(entities)
> Facts about those things
(attributes)
> Associations between those things
(relationships)
PARTY
Party Id
Party Type Cd (FK)
Party Info Source Type Cd (FK)
Party Start Dt
Party End Dt
Lifecycle Cd (FK)
Party Host Num
Provider Ind
Customer Prospect Ind
INDIVIDUAL NAME HIST
INDIVIDUAL
ORGANIZATION
Org Party Id (FK)
Org Type Cd (FK)
has names of
Individual Party Id (FK)
MM Object Id (FK)
Ethnicity Cd (FK)
Gender Type Cd (FK)
Birth Dt
Death Dt
is addressed by
ORGANIZATION NAME HIST
Subject-oriented, designed in
Third Normal Form one
fact in one place in the
right place
5 >
has names of
Org Party Id (FK)
Name Type Cd (FK)
Org Name Start Dt
Org Name
Org Name Desc
Org Name End Dt
NAME SALUTATION
Individual Party Id (FK)
Salutation Role Cd (FK)
Salutation Type Cd (FK)
Individual Party Id (FK)
Name Type Cd (FK)
Individual Name Start Dt
Given Name
Middle Name
Family Name
Name Prefix Txt
Name Suffix Txt
Individual Name End Dt
Reference Models
Lots of Detail / Expertise Behind Models
6 >
Reference Model Sources
Data Warehousing Vendors
>
>
>
>
IBM
Oracle
Teradata
Tool Vendors
> Embarcadero
>
Service Vendors
> EWSolutions
>
Industry/Standards Associations
> ARTS (Association for Retail Technology Standards)
>
7 >
Teradata Industry Logical Data Models - iLDMs
Financial Services
- Banking, Investments
Financial Services
- Wireline, Wireless,
Cable, Satellite
Travel
- Insurance
- Travel, Hospitality,
Gaming
Retail
Transportation
- Retail Store,
Food Service
- 3PL, 4PL,
Air, Truck, Rail, Sea
Manufacturing
Healthcare
- CPG, High Tech
Automotive
8 >
Communications
- Payor, HIPAA
Data Management Context
Three Layer Structure
Source
Core
(Enterprise)
Semantic
(Usage/Presentation)
iLDM
Analyze &
Design
(Logical)
Implement
(Physical)
Used for
customization
Views
Data
Integration
Source
Operational
Images
9 >
EDW-LDM
Semantic
Layer
Models
EDW-PDM
Load Once
Marts
Use Many
BIOs & User Types
drive requirements
Enterprise
Enterprise Information
Information Management
Management
Requires
Requires AA Shared
Shared
VOCABULARY
VOCABULARY
The biggest problem with
communication
is the
illusion
that it has taken place.
Experts estimate that the 500 most
commonly used words in the English
language have an average of 28
definitions each.
10 >
Enterprise Data Management Objectives
that are enabled by Enterprise Logical Data Modeling :
> Build a Common Business Vocabulary for the enterprise.
> Develop an EDW Data Structure that is Neutral from All the
Sources that populate it.
> Develop an EDW Data Structure that will Support All
Business Requirements While Not Being Constrained by any
specific requirement.
i.e. Neutral from use by multiple functional areas
Supports operational and analytical uses
11 >
Data Modeling Structure
Data Modeling
SUBJECT Model
CONCEPTUAL Model
KEY-BASED Model
12 >
A model of the high level data concepts that
define the scope of the Data Architecture.
An entity-relationship model that identifies the elements
of the Business Vocabulary and Business Rules.
A refinement of the Conceptual Model that identifies the
natural and surrogate keys for all entitles and relationships.
This the foundation of the Enterprise Business Vocabulary.
ATTRIBUTED Model
A detailed model that identifies the non-key attributes for the
entitles. Attribution also leads to refining the Key-Based Model
PHYSICAL Model
A model that is the design for a database. The Attributed Model
is transformed for Sourcing and Accessing performance.
Data Modeling Structure Purposes
Architecture
SUBJECT Model
FINANCIAL MGMT
has financial reporting
is accounted for
FEATURE
defines /
is defined by
Define products, coverages,
services, amounts, rates,
terms, quantities, etc.
involves service /
is performed
represents/is represented by
CLAIM
is represented on /
represents
A contract or any type
of agreement of interest
between Parties.
is processed through /
is vehicle for
Information Requirements
is made against /
may have
A request for payment by
an insured for adjudication
by the healthcare enterprise.
files /
is made by
Any marketable product
or service including terms,
conditions and features.
is payment or payout /
has activity
AGREEMENT
applies to /
has
PRODUCT
The business financials
and internal accounting.
defines /
is defined by
Data Modeling
has activity of/involves
uses or manages/is used or managed by
offers or services/
is offered or serviced by
PARTY
An individual, business
or group of individuals
of interest to the
healthcare enterprise.
involves /
provides services, is patient
EVENT
is involved with/involves
Something of interest that
involves the healthcare enterprise.
It may not always be related
to a customer.
can be contacted at/is contact for
CLINICAL
is vehicle for/is conducted via
Patient encounters with healthcare
service providers.
is target for/targets
GEOGRAPHY
is location for
is marketed by/
markets
manages/managed by
A physical address,
electronic address
or geographical area.
targets/is targeted by
CHANNEL
CAMPAIGN
A communication plan to
deliver a message.
CONCEPTUAL Model
The vehicle by which a
party may interact
with the healthcare enterprise.
is delivered via/is vehicle for
Reference Model
KEY-BASED Model
ATTRIBUTED Model
Implementation
PHYSICAL Model
13 >
Business Improvement Opportunities
Business Questions
Key Performance Indicators
Legacy Reporting/Analysis
Data/Information Management
Data Modeling
Data Warehousing
APPLICATION Layer
SUBJECT Model
CONCEPTUAL Model
CORE Layer
ATTRIBUTED Model
STAGING Layer
DD
L
KEY-BASED Model
PHYSICAL Model
14 >
SEMANTIC Layer
Access
Layer
Master Data
Transaction Data
Sources
Sources
Sources
Data
Source Layer
Teradata
Enabled
Source
Layer
Data Management Context
Agile Development Environment
User
External
Data
15 >
Sandbox
Data Management Context
Perceived Value from Medium to Large Scale Projects
80-95%
0-1%
User
External
Data
16 >
0-5%
Sandbox
5-15%
Data Management Context
Development Time for Medium to Large Scale Projects
4-8 weeks
2-4 Months
3-6 months
User
External
Data
17 >
1-5 days
Sandbox
Data Integration
1st Sandbox
Application
Local
2nd Sandbox
Application
Shared
Local
Common
Shared
Shared
3rd Sandbox
Application
18 >
Local
Data Management Context
Integration in an Agile Development Environment
Conceptual Data Architecture
Governance-driven Integration
User
External
Data
19 >
Sandbox
Pros and Cons of Using a Vendor Provided Analytical
Data Model in Your BI ImplementationBoris Evelson, Information
Management Blogs, January 29, 2010
Lets
discuss.
Pros:
Leverage vendor knowledge from prior experience and other customers
May fill in the gaps in enterprise domain knowledge
Best if your IT dept does not have experienced data modelers
May sometimes serve as a project, initiative, solution accelerator
May sometimes break through a stalemate between stakeholders failing to agree on
metrics, definitions
Cons:
May sometimes require more customization effort, than building a model from scratch
May create difference of opinion arguments and potential road blocks from your own
experienced data modelers
May reduce competitive advantage of business intelligence and analytics (since
competitors may be using the same model)
Goes against agile BI principles that call for small, quick, tangible
deliverables
Goes against top down performance management design and modeling best practices,
where one does not start with a logical data model but rather
>
>
>
>
Defines departmental, line of business strategies
Links goals and objectives needed to fulfill these strategies
Defines metrics needed to measure the progress against goals and objectives
Defines strategic, tactical and operational decisions that need to be made based on
metrics
> Then, and only then defines logical model needed to support the metrics and decisions
20 >
Cooking Something New ...
Change without a recipe is a recipe for chaos.
The transformation model must describe
not only
the steps in the process,
but also
the enabling context that is critical to its success.
If Only We Knew What We Know
Carla ODell & C. Jackson Grayson
The Free Press, 1998
21 >
