Transcript Reference Logical Data Models for Agile BI

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 Sourc e Type Cd (FK)
Party Start Dt
Party End Dt
Lifecycle Cd (FK)
Party Host Num
Provider Ind
Customer Prospec t Ind
INDIVIDUAL NAME HIST
INDIVIDUAL
ORGANIZATION
Org Party Id (FK)
Org Type Cd (FK)
has names of
Individual Party Id (FK)
has names of
MMObject Id (FK)
Ethnicity Cd (FK)
Gender Type Cd (FK)
Birth Dt
Death Dt
is addressed by
• Subject-oriented, designed in
Third Normal Form – one
fact in one place in the
right place
5 >
ORGANIZATION NAMEHIST
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
8 >
Communications
- 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
- Payor, HIPAA
Data Management Context
Three Layer Structure
Source
Core
(Enterprise)
Semantic
(Usage/Presentation)
iLDM
BIOs & User Types
drive requirements
Customer
Management
Event &
Campaign
Management
Organic
Growth
Target
Marketing
Customer
Retention
Customer
Equity
Customer
Acquisition
Operations
Management
Sales &
Performance
Reporting
Performance
Management
Payment
Analysis
Distribution
Channel
Optimization
Channel
Migration
Product
Development
& Packaging
Risk
Management
Compliance &
Disclosure
(Basel II)
Performance
Measurement &
Capital Allocation
(RAROC)
Credit Risk &
Credit VaR
Market Risk &
Market VaR
Operational
Risk &
Operational
VaR
Asset Liability
Management
Financial
Reporting &
Analysis
Improvement
Financial
Process
Optimization
Enterprise
Performance
Management
Enablement
Operational
Analysis &
Reporting
Enhancement
Consistent
Compliance
Infrastructure
Assurance
Finance &
Performance
Management
Analyze &
Design
(Logical)
Used for
customization
EDW-LDM
Semantic
Layer
Models
Active Loan
Portfolio
Limit and
Collateral
Management
Anti Fraud &
Money
Laundering
IT,
Miners
Power
Users
Business Users
Operational BI Users
Front-line Users, Customers, Partners
Implement
(Physical)
Views
Data
Integration
Source
Operational
Images
9 >
EDW-PDM
Load Once
Marts
Use Many
Enterprise Information Management
Requires A Shared
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
FINANCIAL MGMT
SUBJECT Model
The business financials
and internal accounting.
has financial reporting
is accounted for
FEATURE
defines /
is defined by
Define products, coverages,
services, amounts, rates,
terms, quantities, etc.
involves service /
is performed
AGREEMENT
applies to /
has
represents/is represented by
CLAIM
is represented on /
represents
files /
is made by
Any marketable product
or service including terms,
conditions and features.
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.
PRODUCT
is payment or payout /
has activity
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
CONCEPTUAL Model
manages/managed by
GEOGRAPHY
is location for
is marketed by/
markets
A physical address,
electronic address
or geographical area.
targets/is targeted by
CHANNEL
CAMPAIGN
A communication plan to
deliver a message.
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
SUBJECT Model
CONCEPTUAL Model
APPLICATION Layer
SEMANTIC Layer
Access
Layer
Master Data
KEY-BASED Model
CORE Layer
ATTRIBUTED Model
STAGING Layer
PHYSICAL Model
14 >
Data Warehousing
Transaction Data
Sources
Sources
Sources
Data Source Layer
Teradata
Enabled
Source
Layer
Data Management Context
Agile Development Environment
Source
Core
(Enterprise)
Semantic
(Usage/Presentation)
iLDM
BIOs & User Types
drive requirements
Customer
Management
Event &
Campaign
Management
Organic
Growth
Target
Marketing
Customer
Retention
Customer
Equity
Operations
Management
Sales &
Performance
Reporting
Performance
Management
Payment
Analysis
Distribution
Channel
Optimization
Channel
Migration
Product
Development
& Packaging
Risk
Management
Compliance &
Disclosure
(Basel II)
Performance
Measurement &
Capital Allocation
(RAROC)
Credit Risk &
Credit VaR
Market Risk &
Market VaR
Operational
Risk &
Operational
VaR
Asset Liability
Management
Financial
Reporting &
Analysis
Improvement
Financial
Process
Optimization
Enterprise
Performance
Management
Enablement
Operational
Analysis &
Reporting
Enhancement
Consistent
Compliance
Infrastructure
Assurance
Finance &
Performance
Management
Analyze &
Design
(Logical)
Used for
customization
EDW-LDM
Semantic
Layer
Models
Customer
Acquisition
Active Loan
Portfolio
Limit and
Collateral
Management
Anti Fraud &
Money
Laundering
IT,
Miners
Power
Users
Business Users
Operational BI Users
Front-line Users, Customers, Partners
Implement
(Physical)
Views
Data
Integration
EDW-PDM
Marts
Source
Operational
Images
User
External
Data
15 >
Sandbox
Data Management Context
Perceived Value from Medium to Large Scale Projects
Source
Core
(Enterprise)
Semantic
(Usage/Presentation)
iLDM
BIOs & User Types
drive requirements
Customer
Management
Event &
Campaign
Management
Organic
Growth
Target
Marketing
Customer
Retention
Customer
Equity
Operations
Management
Sales &
Performance
Reporting
Performance
Management
Payment
Analysis
Distribution
Channel
Optimization
Channel
Migration
Product
Development
& Packaging
Risk
Management
Compliance &
Disclosure
(Basel II)
Performance
Measurement &
Capital Allocation
(RAROC)
Credit Risk &
Credit VaR
Market Risk &
Market VaR
Operational
Risk &
Operational
VaR
Asset Liability
Management
Financial
Reporting &
Analysis
Improvement
Financial
Process
Optimization
Enterprise
Performance
Management
Enablement
Operational
Analysis &
Reporting
Enhancement
Consistent
Compliance
Infrastructure
Assurance
Finance &
Performance
Management
Analyze &
Design
(Logical)
Used for
customization
EDW-LDM
Semantic
Layer
Models
Customer
Acquisition
Active Loan
Portfolio
Limit and
Collateral
Management
Anti Fraud &
Money
Laundering
IT,
Miners
Power
Users
Business Users
Operational BI Users
Front-line Users, Customers, Partners
Views
80-95%
Implement
(Physical)
Data
Integration
0-1%
EDW-PDM
Marts
0-5%
Source
Operational
Images
User
External
Data
16 >
Sandbox
5-15%
Data Management Context
Development Time for Medium to Large Scale Projects
Source
Core
(Enterprise)
Semantic
(Usage/Presentation)
iLDM
BIOs & User Types
drive requirements
Customer
Management
Event &
Campaign
Management
Organic
Growth
Target
Marketing
Customer
Retention
Customer
Equity
Operations
Management
Sales &
Performance
Reporting
Performance
Management
Payment
Analysis
Distribution
Channel
Optimization
Channel
Migration
Product
Development
& Packaging
Risk
Management
Compliance &
Disclosure
(Basel II)
Performance
Measurement &
Capital Allocation
(RAROC)
Credit Risk &
Credit VaR
Market Risk &
Market VaR
Operational
Risk &
Operational
VaR
Asset Liability
Management
Financial
Reporting &
Analysis
Improvement
Financial
Process
Optimization
Enterprise
Performance
Management
Enablement
Operational
Analysis &
Reporting
Enhancement
Consistent
Compliance
Infrastructure
Assurance
Finance &
Performance
Management
Analyze &
Design
(Logical)
Used for
customization
EDW-LDM
Semantic
Layer
Models
Customer
Acquisition
Active Loan
Portfolio
Limit and
Collateral
Management
Anti Fraud &
Money
Laundering
IT,
Miners
Power
Users
Business Users
Operational BI Users
Front-line Users, Customers, Partners
Implement 4-8 weeks
(Physical)
Data
Views
EDW-PDM
2-4 Months
Marts
Integration
3-6 months
Source
Operational
Images
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
Source
Core
(Enterprise)
Semantic
(Usage/Presentation)
iLDM
Conceptual Data Architecture
Analyze &
Design
(Logical)
EDW-LDM
Used for
customization
BIOs & User Types
drive requirements
Customer
Management
Event &
Campaign
Management
Organic
Growth
Target
Marketing
Customer
Retention
Customer
Equity
Operations
Management
Sales &
Performance
Reporting
Performance
Management
Payment
Analysis
Distribution
Channel
Optimization
Channel
Migration
Product
Development
& Packaging
Risk
Management
Compliance &
Disclosure
(Basel II)
Performance
Measurement &
Capital Allocation
(RAROC)
Credit Risk &
Credit VaR
Market Risk &
Market VaR
Operational
Risk &
Operational
VaR
Asset Liability
Management
Financial
Reporting &
Analysis
Improvement
Financial
Process
Optimization
Enterprise
Performance
Management
Enablement
Operational
Analysis &
Reporting
Enhancement
Consistent
Compliance
Infrastructure
Assurance
Finance &
Performance
Management
Semantic
Layer
Models
Customer
Acquisition
Active Loan
Portfolio
Limit and
Collateral
Management
Anti Fraud &
Money
Laundering
IT,
Miners
Power
Users
Business Users
Operational BI Users
Front-line Users, Customers, Partners
Implement
(Physical)
Views
Data
Integration
Source
Operational
Images
User
External
Data
19 >
EDW-PDM
Marts
Governance-driven Integration
Sandbox
Pros and Cons of Using a Vendor Provided Analytical
Data Model in Your BI Implementation
Boris Evelson, Information Management Blogs, January 29, 2010
Let’s
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 O’Dell & C. Jackson Grayson
The Free Press, 1998
21 >