agile bi - from business models to bi models · date agile data warehouse design
TRANSCRIPT
Business Models
A business model describes the rationale of how an organization creates, delivers and captures value. – Alexander Osterwalder, Business Model Generation
.
– Tim Clark, Business Model You
DW/BI Challenges (and Opportunities)
Capturing detailed BI requirements
Responding to Agile Software Development
Incremental development
Proactive design – parallel development
© Copyright Lawrence Corr 2012
Dimensional Model
CALENDAR
Date Key
Date Day Day in Week Day in Month Day in Qtr Day in Year Month Qtr Year Weekday Flag Holiday Flag
PRODUCT
Product Key
Product Code Product Description Product Type Brand Subcategory Category
PROMOTION
Promotion Key
Promotion Code Promotion Name Promotion Type Discount Type Ad Type
SALES FACT
Quantity Sold Revenue Cost Basket Count
Date Key Product Key Store Key Promotion Key
STORE
Store Key
Store Code Store Name URL Store Manager Region Country
Entity Relationship Modeling – 3NF
Dimensional Modeling – Star Schema
Data Driven(Supply)
ReportingDriven
(Demand)Analysis
De
sign
Dimensional
Data
Warehouse
1st Generation
Data
Warehouse
© Copyright Lawrence Corr 2012
Reporting-Driven Analysis
Data Modeler
BI Stakeholders
Business Analyst
BI Stakeholders
BI Stakeholders
Interview notes
Requirement Document
Data Models
Database Schemas
© Copyright Lawrence Corr 2012
2003
Reactive Data Warehouse Design
OLTPDesign
OLTP Development
OLTPDeployment
BI Backlog
ETL Development
DWDesign
DWDeployment
DWDWLTLT
1997 1999 200320012000
DATA
© Copyright Lawrence Corr 2012
OLTP Development
OLTPDeployment
LTOLTPDesign
LT
Now
DATA
Next YearThis Year
ETL Development
DWDesign
DW OLTP & DWDeployment
Proactive Data Warehouse Design
© Copyright Lawrence Corr 2012
Data Modeling
“Data modeling is the act of exploring data-oriented structures. Evolutionary data modeling is data modeling performed in an iterative and incremental manner. Agile data modeling is evolutionary data modeling done in a collaborative manner.”
– Scott Amblerwww.agiledata.org
Why Model with BI Stakeholders?
Scope and Prioritisation
Discover how business events are described Dimensions
Determine how they are measured
Unearth budgets, forecasts, targets and other user-controlled data sourcesExtra data, Common summarization levels: Physical Optimization
Creates business ownership and pride in the DW design
© Copyright Lawrence Corr 2012
Techniques and Terminology
Scrum – Project Management
ScrumMaster, Product Owner, Daily Scrum, Sprint, Product Backlog, Sprint Backlog, Burndown Chart
Extreme Programming (XP) – Software Engineering
User Stories, Daily Stand-Up, Iteration, On-Site Customer, Pair Programming, Test-Driven Development (TDD), Continuous Integration
What is a dimensional model, really?
CALENDAR
Date Key
Date Day Day in Week Day in Month Day in Qtr Day in Year Month Qtr Year Weekday Flag Holiday Flag
PRODUCT
Product Key
Product Code Product Description Product Type Brand Subcategory Category
PROMOTION
Promotion Key
Promotion Code Promotion Name Promotion Type Discount Type Ad Type
SALES FACT
Quantity Sold Revenue
Date Key Product Key Store Key Promotion Key
STORE
Store Key
Store Code Store Name URL Store Manager Region Country
SALES FACT
Date Key Product Key Store Key Promotion Key
Verb
uantity Sold evenue
Adverbs
Noun T
ey
ode
Noun
y
de
Noun y
Noun
Promotion Name Promotion Type Discount Type Ad Type Adjectives
Product Type Brand Subcategory Category Adjectives
Day in Month Day in Qtr Day in Year Month Qtr Year
Adjectives
Store Code Store Name URL Store Manager Region Country
Adjectives
© Copyright Lawrence Corr 2012
Modelstorming: Data Modeling + Brainstorming
Data Modeler
Developers BI Stakeholders
QuickInclusive
Interactive Fun© Copyright Lawrence Corr 2012
Using the 7Ws to tell Data Stories
Who
What
When
Where
How Many
Why
How
Who does what?
© Copyright Lawrence Corr 2012
Who does what? (that we need to measure next)
(Responsible) Subject Verb Object
Customer buys product
BEAM✲ Modeler BI Stakeholders
1. Modelstorm a Business Event
© Copyright Lawrence Corr 2012
Model it using a BEAM✲ Table – an Example Data Model
buys
CUSTOMER PRODUCT
[who] [what]
Details
Event Stories(4-6 rows)
Subject Column Name
Object Column Name
Verb
Column Type
Event Name (filled in later)
© Copyright Lawrence Corr 2012
Example Stories
buys on
CUSTOMER PRODUCTSALEDATE
[who] [what], MD [when], MDElvis Priestley iPip Blue Suede 18-May-2011Vespa Lynd POMBook Air 29-Jun-2011Elvis Priestley iPip Blue Suede 18-May-2011Phillip Swallow iPOM Pro 14-Oct-2011Unknown iPip G1 10 Years AgoUS Senate iPOM + Printer YesterdayUS Senate iPip Touch Yesterday
Missing Value Possible
own
Typical Event Story
011Repeat of Typical Story used to discover what makes each story unique 2011
-20112011
Oldest Event Story Deleted PRODUCT? Do we need to go back this far?
rs A
Group Stories CUSTOMER can be an organisation. PRODUCT can be a bundle of products
nate nate
Most Recent Stories use Yesterday rather than date to show the urgency of the required data
dayday
Print h
ir
o Ago
MD: Mandatory Detail
MD-2
D01
© Copyright Lawrence Corr 2012
Durations: Modeling with Timeline
Order Date
Delivery Due Date
Packing Time
Delivery Delay
Delivery Date
Ship Date
Delivery Time
Variable Milestone
Repeating Milestone
Fixed Milestone
© Copyright Lawrence Corr 2012
Adding a Second Who Detail
buys on from
CUSTOMER PRODUCTSALEDATE SALESPERSON
[who] [what], MD [when], MD [who]Elvis Priestley iPip Blue Suede 18-May-2011 James BondVespa Lynd POMBook Air 29-Jun-2011 N/AElvis Priestley iPip Blue Suede 18-May-2011 James BondPhillip Swallow iPOM Pro 14-Oct-2011 George Smiley Unknown iPip G1 10 Years Ago UnknownUS Senate iPOM + Printer Yesterday Capital TeamUS Senate iPip Touch Yesterday Capital Team
from Preposition CUSTOMER buys PRODUCT (on SALE DATE) from SALESPERSON
frrfrrfrooroorrrr
Second who detail
ALESPERSO
Event without a SALESPERSON is possible
N/A
SALESPERSON can be a group or team l Team
l Team
New and old SALESPERSON
James George
known
], ay
MD01
MD-20
© Copyright Lawrence Corr 2012
Completed Event Table
CUSTOMER ORDERS [DE]
orders on for delivery on from at for delivery to
CUSTOMER PRODUCTORDER DATE
DELIVERY DUE DATE SALESPERSON
SALES LOCATION
DELIVERY ADDRESS
[who] [what] MD, GD [when] MD [when] [who] [where] [where]Elvis Priestley iPip Blue Suede 18-May-2011 22-May-2011 James Bond POMStore NYC Memphis, TNVespa Lynd POMBook Air 29-Jun-2011 4-Jul-2011 N/A store.POM.com London UKElvis Priestley iPip Blue Suede 18-May-2011 22-May-2011 James Bond POMStore NYC Memphis, TNPhillip Swallow iPOM Pro 14-Oct-2011 Not Applicable George Smiley POMStore London Not ApplicableUnknown iPip G1 10 Years Ago Not Applicable Unknown Amazon.com Not ApplicableUS Senate iPOM + Printer Yesterday 3 Days from now Capital Team 1-800-MY-POM Washington, DCUS Senate iPip Touch Yesterday 3 Days from now Capital Team 1-800-MY-POM Washington, DC
for on with usingORDER QUANTITY REVENUE PROMOTION DISCOUNT ORDER ID[Retail Units] [$, £, !, ¥] [why] [$, £, !, ¥, %] [how] GD1 $249 No Promotion 0 ORD12341 £1,400 Launch Event 10% ORD0071 $249 No Promotion 0 ORD43211 £2,500 Star Coupon £150 ORD000150 $20,000 Trial Price $2,500 ORD0012100 $150,000 New Deal $20,000 ORD5466100 $25,000 New Deal $1,000 ORD5466
1
CU DE]
1
GD [w de 18
2
r delive
ys from ys from
DISCOUNT[$ £ ! ¥
OUNTGD347
ers
© Copyright Lawrence Corr 2012
Event Matrix: Data Modeling and Planning
Dimension
Im
EVENT (who does what)
Importance
Estimate
MANUFACTURING PLANSPlant builds Product
BILL OF MATERIALSProduct made from Component
PURCHASE ORDERS Employee purchases Component
COMPONENT DELIVERIESSupplier delivers Component
SUPPLIER PAYMENTSEmployee pays Supplier
COMPONENT INVENTORY LEVELSPlant stocks Component
QA TESTSEmployee tests Product
MANUFACTURING PROCESSESPlant runs Process
PRODUCT INVENTORY LEVELSWarehouse stocks Products
WAREHOUSE SHIPMENTSCarrier ships Product
ons
Importance
Estimate
EMPLOYEE
SUPPLIER
RESELLER
CARRIER
COMPONENT
PRODUCT
PROCESSTEST
PLANT
WAREHOUSE
STORE
CONTRACT
SHIP MODE POSales
Marketing
Finance
Product Development
Manufacturing
why & howwherewhatwho stakeholder group
90 100
40 15 10 15
2 ! ! ! ! ! "
2 100 30 " " ! "
6 ! " " ! ! " " !
6 ! " ! ! " ! ! "
4 ! ! ! ! "
2 ! ! ! "
6 20 ! ! ! ! " !! ! "
5 ! ! ! " ! ! "
2 ! ! ! ! !
7 ! " ! ! ! ! " !
90 4 4 1 2 0 7 6 1 1 0 6 2 1 0 2 2 3 0 2 2 6 4 7 0
why & howwherewhatwho stakeholder group
Even
ts (Tim
e/
Valu
e S
eq
uen
ce)
Details/Dimensions (BEAM* Sequence) Departments/Groups
: Event creates dimension
"
* : Event references dimension
!
!!
: Event Owner
"
*
! !
: Interested Stakeholder !!
Process Sequence
CO Sup
© Copyright Lawrence Corr 2012
Business Models
A business model describes the rationale of how an organization creates, delivers and captures value. – Alexander Osterwalder, Business Model Generation
.
– Tim Clark, Business Model You
What are the most important costs inherent in our business model? Which Key Resources are most expensive? Which Key Activities are most expensive?
Through which Channels do our Customer Segments want to be reached? How are we reaching them now?How are our Channels integrated? Which ones work best?Which ones are most cost-efficient? How are we integrating them with customer routines?
For what value are our customers really willing to pay?For what do they currently pay? How are they currently paying? How would they prefer to pay? How much does each Revenue Stream contribute to overall revenues?
For whom are we creating value?Who are our most important customers?
What type of relationship does each of our CustomerSegments expect us to establish and maintain with them?Which ones have we established? How are they integrated with the rest of our business model?How costly are they?
What value do we deliver to the customer?Which one of our customer’s problems are we helping to solve? What bundles of products and services are we offering to each Customer Segment?Which customer needs are we satisfying?
What Key Activities do our Value Propositions require?Our Distribution Channels? Customer Relationships?Revenue streams?
Who are our Key Partners? Who are our key suppliers?Which Key Resources are we acquiring from partners?Which Key Activities do partners perform?
What Key Resources do our Value Propositions require?Our Distribution Channels? Customer Relationships?Revenue Streams?
Day Month Year
No.
This work is licensed under the Creative Commons Attribution-Share Alike 3.0 Unported License. To view a copy of this license, visit http://creativecommons.org/licenses/by-sa/3.0/
or send a letter to Creative Commons, 171 Second Street, Suite 300, San Francisco, California, 94105, USA.
WhoWho
Who
What
WhoWhat
When When
For what value are our customer For what do they currently pay? How are they currently paying? How would they prefer to pay? How much does each Revenue S
When
WhatWhere
How Many
How Many
en
W
How Many
How Many
What type of relationship does each of our CustomerSegments expect us to establish and maintain with them?Which ones have we established? How are they integrated with the rest of our business model?How costly are they?Why
How
What Key Activities do our Value Propositions require?Our Distribution Channels? Customer Relationships?Revenue streams?
How
HowM
wWhere
Star Schema Layout
When Who
Where What
How
Why
Customer
Who
Transaction Type/Method
How
Date/Month/Period
When
Product/Service
What
Promotion/Reason
Why
Location/Store/Hospital
Where
Event/Fact
How
How Many
Employee/Organisation
Who
Time
When
© Copyright Lawrence Corr 2012
WhenWhen does it happen?
DateTimeTime ZonePeriodTimeline: Milestones
HowHow does it happen?How do we know it happened?How do we uniquely identify a fact/event?
Activity, Process, EventEffect, OutcomeTransaction TypeTransaction # [Degenerate Dimension]Step #
[Granularity, Event Type: Discrete, Evolving, or Recurring]
WhoWho does what? How do we organize them?How do they change? Who else is involved?
Customer: Business, Consumer, SegmentEmployeeSupplierPartnerThird Party
How ManyHow many/much is involved? How long doe it take?
RevenuesCostsQuantities, BalancesActivity/Status CountsDurations
[UoM, Fact Type: Fully Additive, Semi-Additive, Non-Additive]
WhereWhere does it happen? Where doe it refer to?
LocationBranch, Store, Facility, ChannelURLMap: Start, Previous, Current, Next, Last
WhatWhat is involved/used? How are they organised?How do they change?
Value PropositionProductServiceResource
WhyWhy does it happen? Can it be different? If so, why?
Cause, ReasonPromotion, Deal
The BI Model Canvas
Agile Data Warehouse Design: www.agiledw.info Inspired by The Business Model Canvas (www.businessmodelgeneration.com/canvas), this work is licensedunder a Creative Commons Attribution-ShareAlike 3.0 Unported License. 2012, Lawrence Corr
Title, Author(s), Date/Version
WhenWhen does it happen?
DateTimeTime ZonePeriodTimeline: Milestones
HowHow does it happen?How do we know it happened?How do we uniquely identify a fact/event?
Activity, Process, EventEffect, OutcomeTransaction TypeTransaction # [Degenerate Dimension]Step #
[Granularity, Event Type: Discrete, Evolving, or Recurring]
WhoWho does what? How do we organize them?How do they change? Who else is involved?
Customer: Business, Consumer, SegmentEmployeeSupplierPartnerThird Party
How ManyHow many/much is involved? How long doe it take?
RevenuesCostsQuantities, BalancesActivity/Status CountsDurations
[UoM, Fact Type: Fully Additive, Semi-Additive, Non-Additive]
WhereWhere does it happen? Where doe it refer to?
LocationBranch, Store, Facility, ChannelURLMap: Start, Previous, Current, Next, Last
WhatWhat is involved/used? How are they organised?How do they change?
Value PropositionProductServiceResource
WhyWhy does it happen? Can it be different? If so, why?
Cause, ReasonPromotion, Deal
The BI Model Canvas
Agile Data Warehouse Design: www.agiledw.info Inspired by The Business Model Canvas (www.businessmodelgeneration.com/canvas), this work is licensedunder a Creative Commons Attribution-ShareAlike 3.0 Unported License. 2012, Lawrence Corr
Title, Author(s), Date/Version
Key Resources (other)
em? d?
nt Customer Segments
ere
Customer Relationships
Channels
w are they organised?
Value Propositions
Key Partnerships
Cost Structures
i-Additive, Non-Additive]Revenue Streams
g, or Recurring]
Key Activities
hips
Key Resource
s
(human)
How do we uniquely identify a fact/event?
Activity, Process, EventEffect, OutcomeTransaction TypeTransaction # [Degenerate Dimension]Step #
[Granularity, Event Type: Discrete, Evolving
How Many
Channels
Key Activities
Channel
urrent, Next, LastKey Resources (location-based)
y Acti
Channels
meline: Milestones Act
Revenue Streams
lsvenue eCost Structures
Cost StructuresCustomer Relationships
AgileData Warehouse Design Dimensional Modeling,from to Star SchemaLawrence Corr
with Jim Stagnitto
Agile Data Warehouse DesignLawrence Corr, Jim Stagnitto, Decision Press, November 2011
Online
decisionone.co.uk and agiledw.info – Agile data warehouse design resources include BEAM✲ tools, templates, reference card and links to books and articles
Lawrence Corr: [email protected]
@LawrenceCorr
Connect with me, if you read ADWD (and you like it)