Transcription
From Business Models to BI ModelsLawrence Corr
Essentially, all models arewrong, but some are useful.— George E. P. Box
Business Models-A business model describes the rationale of how an organization creates, delivers andcaptures value.– Alexander Osterwalder, Business Model Generation-!& %&B/& ?#64*/&44 .0%&-@ "4 /# '*" 4 2# # ) )/ - - . .0./ ). /. '! 8) ) ''4.– Tim Clark, Business Model You
What . .* !8 0'/ *0/
DW/BI Challenges (and Opportunities)- Capturing detailed BI requirements- Responding to Agile Software Development- Incremental development- Proactive design – parallel development Copyright Lawrence Corr 2012
What& ) *! (* '. *0' # ' 0.
Entity Relationship Model
Data Vault Model
Dimensional ModelCALENDARPRODUCTDate KeyProduct KeyDateDayDay in WeekDay in MonthDay in QtrDay in YearMonthQtrYearWeekday FlagHoliday FlagProduct CodeProduct DescriptionProduct TypeBrandSubcategoryCategorySALES FACTDate KeyProduct KeyStore KeyPromotion KeyQuantity SoldRevenueCostBasket CountSTOREPROMOTIONStore KeyPromotion KeyStore CodeStore NameURLStore ManagerRegionCountryPromotion CodePromotion NamePromotion TypeDiscount TypeAd Type
Dimensional Modeling – Star ouseAnalysisReportingDriven(Demand)1st GenerationDataWarehouseEntity Relationship Modeling – 3NF Copyright Lawrence Corr 2012
Reporting-Driven AnalysisBI StakeholdersBI StakeholdersDataModelerBusinessAnalystInterview notesData Models&1035 1& *B "5*0/4Database SchemasBI StakeholdersRequirement Document Copyright Lawrence Corr 2012
Reactive Data Warehouse DesignBI Backlog1997OLTPLTDesignOLTP Development1999LTOLTPDeployment2000DATA2001ETL Development2003DWDesign2003DWDeployment Copyright Lawrence Corr 2012
Proactive Data Warehouse DesignThis YearNext YearNowOLTP DevelopmentOLTPLTDesignLTOLTP& DWOLTPDeploymentDWDesignDATAETL Development Copyright Lawrence Corr 2012
Data Modeling“Data modeling is the act of exploring data-oriented structures.Evolutionary data modeling is data modeling performed in aniterative and incremental manner.Agile data modeling is evolutionary data modeling done in acollaborative manner.”– Scott Amblerwww.agiledata.org
Why(* ' *'' *- / 1 '4
Why Model with BI Stakeholders?- %&/5*': 5)& 4*(/*B "/5 #64*/&44 &7&/54 8035) .&"463*/( Scope and Prioritisation- Discover how business events are describedDimensions- Determine how they are measured .0- . - - # . *( - .*). .- 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
What #) ,0 . " ' *0' # '
Techniques and Terminology-Scrum – Project Management--ScrumMaster, Product Owner, Daily Scrum, Sprint, Product Backlog, Sprint Backlog,Burndown ChartExtreme Programming (XP) – Software Engineering-User Stories, Daily Stand-Up, Iteration, On-Site Customer, Pair Programming,Test-Driven Development (TDD), Continuous Integration
User StoriesUsage Requirements
Data StoriesData Requirements
What& ) *! (* ' ) - - . )/ / ./*- .
What is a dimensional model, really?NounCALENDARDate KeyDateDayDay in WeekDay in MonthDay in QtrDay in YearMonthQtrYearWeekday FlagHoliday FlagProduct KeyeySALES FACTAdjectivesSTOREPRODUCTTDate KeyProduct KeyStore KeyPromotion KeyVerbNounProduct CodeodeProduct DescriptionProduct ity SoldevenueRevenueNounAdverbsPROMOTIONStore KeyyPromotion KeyyStore CodeStore NameURLStore ManagerRegionCountryPromotion CodedePromotion NamePromotion TypeDiscount TypeAd TypeAdjectivesNounAdjectives Copyright Lawrence Corr 2012
How) 4*0 (* ' / 2 /# 0. ) . * '
Modelstorming: Data Modeling taModelerBI StakeholdersFun Copyright Lawrence Corr 2012
henHowManyWhyWerheHowWhoWWhat
HowhenreWheHowManyhoWWhaWhyBEAMWBusiness Event Analysis & Modelingt
Using the 7Ws to tell Data StoriesWhoWhatWhenWhere-Who does what?HowManyWhyHow Copyright Lawrence Corr 2012
Using the 5Ws and 2Hs to tell Data StoriesWiewatwanneerwaarhoeveelwaaromhoe-Wie doet wat?
1. Modelstorm a Business EventWho does what?(that we need to measure next)Customer buys productBEAM Modeler(Responsible) Subject Verb ObjectBI Stakeholders Copyright Lawrence Corr 2012
Model it using a BEAM Table – an Example Data ModelEvent Name (filled in later)Subject Column NameVerbObject Column NamebuysCUSTOMERPRODUCT[who][what]Column TypeDetailsEvent Stories(4-6 rows) Copyright Lawrence Corr 2012
When* . 0./*( - 04 -* 0 / # / * 4*0 '' /# / / / (
Example StoriesMissing Value PossiblebuysonSALECUSTOMERPRODUCTDATE[ who ][ what ], MD[when], MDDElvis Priestley iPip Blue Suede 18-May-2011-201011PO MBook Airir29-Jun-20112011Vespa LyndElvis Priestley iPip Blue Suede 18-May-2011-2011Phillip Swallow iPOM Pro14-Oct-20112011UnknownowniPip G110 Yearsrs AAgoonateiPOM PrintPrinter YesterdaydayUS SenateUS SenatenateiPip TouchhYesterdaydayGroup StoriesCUSTOMER can be anorganisation.PRODUCT can be abundle of productsMD: Mandatory DetailTypical Event StoryRepeat of Typical Story used todiscover what makes each story uniqueOldest Event StoryDeleted PRODUCT?Do we need to go back this far?Most Recent Stories use Yesterdayrather than date to show the urgencyof the required data Copyright Lawrence Corr 2012
WhenAre there any other dates/times closely.* / 2 /# /# . ./*- .
Durations: Modeling with TimelineFixed MilestoneVariable MilestoneDeliveryTimeRepeating DeliveryDue DateDeliveryDate Copyright Lawrence Corr 2012
Who* . 0./*( - 04 -* 0 / !-*()4*) '. )1*'1
Adding a Second Who Detailfrom PrepositionCUSTOMER buys PRODUCT (on SALE DATE) from SALESPERSONSecond who detailbuysonSALECUSTOMERPRODUCTDATE[ who ][ what ], MD[when],], MDElvis Priestley iPip Blue Suede 18-May-2011ay-2001Vespa LyndPO MBook Air29-Jun-2011Elvis Priestley iPip Blue Suede 18-May-201114-Oct-2011Phillip Swallow iPOM ProUnknowniPip G110 Years AgoiPOM Printer YesterdayUS SenateUS SenateiPip TouchYesterdayfromfrrroofroSALESPERSONALESPERSO[ who]James BondN/AJames BondGeorge SmileyUnknownknownCapitall TeamCapitall TeamEvent without aSALESPERSON ispossibleSALESPERSON canbe a group or teamNew and old SALESPERSON Copyright Lawrence Corr 2012
What'. . )1*'1
Where* . 0./*( - 04 -* 0 /)4 */# - '* / *). )1*'1
How Many?
Why* . 0./*( - 04 -* 0 / *)
How* . 0./*( - 04 -* 0 / *2 * 4*0 &)*2 / # ) *2 ) 4*0 / '' *) ./*-4 !-*( )*/# -
Completed Event TableCUSTOMER ORDERS what] MD, GD [when][wMDforr delivedelivery onDELIVERYDUE DATE[when]atSALESSALESPERSON LOCATION[who][where]for delivery toDELIVERYADDRESS[where]Elvis PriestleyVespa LyndElvis PriestleyPhillip SwallowUnknownUS SenateUS SenateiPip Blue SuededePOMBook AiriPip Blue SuedeiPOM ProiPip G1iPOM PrinteriPip Touch22-May-20114-Jul-201122-May-2011Not ApplicableNot Applicableys from now3 Daysys from now3 DaysJames BondN/AJames BondGeorge SmileyUnknownCapital TeamCapital TeamPOMStore NYCstore.POM.comPOMStore NYCPOMStore LondonAmazon.com1-800-MY-POM1-800-MY-POMMemphis, TNLondon UKMemphis, TNNot ApplicableNot ApplicableWashington, DCWashington, DCforonwithusingREVENUE[ , , !, ] 249 1,400 249 2,500 20,000 150,000 25,000PROMOTION[why]No PromotionLaunch EventNo PromotionStar CouponTrial PriceNew DealNew DealDISCOUNTOUNT[ [ , , !!, , %]010%0 150 2,500 20,000 1,000ORDER ID[how] 1110 Years AgoYesterdayYesterdayORDERQUANTITY[Retail Units]111150100100from Copyright Lawrence Corr 2012
Event Matrix: Data Modeling and PlanningDetails/Dimensions (BEAM* Sequence)Departments/GroupsodPr!!!""!" !!"!"!! !!! ! "! : Event references dimension1207"!! "!! "! ! !!!! !110"!!6g!!4t! ! ! "! "! !4rinene!"! older group* : Event Owner!90E7DWAREHOUSE SHIPMENTSCarrier ships ProductT2OPRODUCT INVENTORY LEVELSWarehouse stocks ProductsC5MMANUFACTURING PROCESSESPlant runs Processwhy & how" "3020A6IPQA TESTSEmployee tests ProductTR2NCOMPONENT INVENTORY LEVELSPlant stocks ComponentSH! !15E410RSUPPLIER PAYMENTSEmployee pays livers ComponentSupU! "N690where!100OPURCHASE ORDERSEmployee purchases ComponentA2EHBILL OF MATERIALSProduct made from ComponentPLEvents (Time/Value Sequence)2OR15MANUFACTURING PLANSPlant builds RUDRENIERERELERNOPORAELLIYEeatEVENT (who does ocess Sequence62* : Event creates dimension1! "022!30226470! : Interested Stakeholder Copyright Lawrence Corr 2012
Business Models-A business model describes the rationale of how an organization creates, delivers andcaptures value.– Alexander Osterwalder, Business Model Generation-!& %&B/& ?#64*/&44 .0%&-@ "4 /# '*" 4 2# # ) )/ - - . .0./ ). /. '! 8) ) ''4.– Tim Clark, Business Model You
CWT 1dbX]Tbb ST[ 2P]ePb3TbXV]TS U a) ])3TbXV]TS Qh)8cTaPcX ]):Th ?Pac]Tab:Th 0RcXeXcXTbEP[dT ?a bXcX ]b2dbc \Ta AT[PcX ]bWX bWho 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 Activities do our Value Propositions require?Our Distribution Channels?Customer Relationships?Revenue streams?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?\ cXePcX ]b U a Pac]TabWX b) cX\XiPcX ] P]S TR ] \h ATSdRcX ] U aXbZ P]S d]RTacPX]chATSdRcX ] U X Z P S d]RTacPX]ch0R dXbXcX ] U PacXRd[Pa aTb daRTb P]S PRcXeXcXTb0R dXbXcX XXRPcTV aXTb?a SdRcX ]?a Q[T\ B [eX]V?[PcU a\ Tcf aZRWPaPRcTaXbcXRb Tf]Tbb?TaU a\P]RT2dbc \XiPcX ]{6TccX]V cWT 9 Q 3 ]T 3TbXV]1aP]S BcPcdb?aXRT2 bc ATSdRcX ]AXbZ ATSdRcX ]0RRTbbXQX[Xch2 ]eT]XT]RT DbPQX[XchWhat 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?WhoHowWhen:Th ATb daRTbHowWhoMany WhatWhereTgP\ [Tb?Tab ]P[ PbbXbcP]RT3TSXRPcTS ?Tab ]P[ 0bbXbcP]RTBT[U BTaeXRT0dc \PcTS BTaeXRTb2 \\d]XcXTb2 RaTPcX ]2dbc \Ta BTV\T]cbFor whom are we creating value?Who are our most important customers?WhyHowWhat Pbb PaZTc XRWT PaZTcBTV\T]cTS3XeTabX TS d[cX bXSTS ?[PcU a\WhoWhen2WP]]T[bHowHowManyWhereWhat Key Resources do our Value Propositions require?Our Distribution Channels? Customer Relationships?Revenue Streams?Through which Channels do our Customer Segmentswant 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?ch Tb U aTb daRTb?WhbXRP[8]cT[[TRcdP[ QaP]S PcT]cb R haXVWcb SPcP 8]cT[[TRcdP[ [ QaP]S P 7d\P]5X]P]RXP[RWP]]T[ WPbTb) 0fPaT]Tbb 7 f S fT aPXbT PfPaT]Tbb PQ dc da R \ P]hzb a SdRcb P]S bTaeXRTb.! 4eP[dPcX ] 7 f S fT WT[ Rdbc \Tab TeP[dPcT da aVP]XiPcX ]zb EP[dT ?a bXcX ]." ?daRWPbT 7 f S fT P[[ f Rdbc \Tab c daRWPbT b TRX R a SdRcb P]S bTaeXRTb.# 3T[XeTah 7 f S fT ST[XeTa P EP[dT ?a bXcX ] c Rdbc \Tab. 0UcTa bP[Tb U 7 f S fT a eXST bc daRWPbT Rdbc \Ta bd ac. 7 f S fT a eXST bc daRWPbT Rdbc \ When2 bc BcadRcdaTWhat are the most important costs inherent in our business model?Which Key Resources are most expensive?Which Key Activities are most expensive?Xb h da QdbX]Tbb \ aT)2 bc 3aXeT] [TP]Tbc R bc bcadRcdaT [ f aXRT eP[dT a bXcX ] \PgX\d\ Pdc \PcX ] TgcT]bXeT dcb daRX]V EP[dT 3aXeT] U RdbTS ] eP[dT RaTPcX ] aT\Xd\ eP[dT a bXcX ] bP\ [T RWPaPRcTaXbcXRb)5XgTS 2 bcb bP[PaXTb aT]cb dcX[XcXTb EPaXPQ[T R bcb4R ] \XTb U bRP[T4R ] \XTb U bR Tfff QdbX]Tbb\ ST[VT]TaPcX ] R \ATeT]dT BcaTP\bATeT]dT BcHowManyDaycustomers really willing to pay?For what value are our customerFor what do they currently pay?How are they currently paying?How would they prefer to pay?How much does each Revenue SStream contribute to overall revenues?ch Tb)0bbTc bP[TDbPVT UTTBdQbRaX cX ] 5TTb;T]SX]V AT]cX]V ;TPbX]V;XRT]bX]V1a ZTaPVT UTTb0SeTacXbX]VgTS aXRX]V;Xbc ?aXRT?a SdRc UTPcdaT ST T]ST]c2dbc \Ta bTV\T]c ST T]ST]cE [d\T ST T]ST]cSh]P\XR aXRX]V TV cXPcX ] QPaVPX]X]V HXT[S P]PVT\T]cATP[ cX\T PaZTcHowManyThis 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.MonthNo.Year
Star Schema LayoutWhenWhoHowDate/Month/PeriodTransaction e/OrganisationHowWhenHow uct/ServiceWhereWhyWhatWhereWhatWhy Copyright Lawrence Corr 2012
NWES
The BI Model CanvasTitle, Author(s), Date/VersionWhenHowWhoWhen does it happen?How does it happen?How do we know it happened?How do we uniquely identify a fact/event?Who does what? How do we organize them?How do they change? Who else is involved?DateTimeTime ZonePeriodTimeline: MilestonesCustomer: Business, Consumer, SegmentEmployeeSupplierPartnerThird PartyActivity, Process, EventEffect, OutcomeTransaction TypeTransaction # [Degenerate Dimension]Step #[Granularity, Event Type: Discrete, Evolving, or Recurring]How ManyHow many/much is involved? How long doe it take?RevenuesCostsQuantities, BalancesActivity/Status CountsDurations[UoM, Fact Type: Fully Additive, Semi-Additive, Non-Additive]WhereWhatWhere does it happen? Where doe it refer to?What is involved/used? How are they organised?How do they change?LocationBranch, Store, Facility, ChannelURLMap: Start, Previous, Current, Next, LastValue PropositionProductServiceResourceWhyWhy does it happen? Can it be different? If so, why?Cause, ReasonPromotion, DealAgile Data Warehouse Design: www.agiledw.infoInspired 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
The BI Model Canvas When does it happen? sed) Cost esStructur Value PropositionProductService Resource Value Propositions KeyResources (other) 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 b ) (human What is involved/used? Howw are they organised?How do they change? What Customer Why does it happen? Can it be different?ere If so, why?RelationshipsCause, Reason Promotion, Deal Agile Data Warehouse Design: www.agiledw.info CustomerSegmentsKeypsshiipPartn ersh KeyesResourc Channels Why RevenueStreams [UoM, Fact Type: Fully Additive, Semi-Additive,i-Additive, Non-Additive] Customer: Business,Consumer, SegmentntEmployeeSupplier Partner Third Party Who does what? How do we organize them?em?How do they change? Who else is involved?d?Key ActivitiesHow many/much is involved? How long doe it take? (location- ba Who Revenues Costs Quantities,Balances Activity/Status CountsDurations Title, Author(s), Date/Version How Many KeyResources Channels Evolving,[Granularity, Event Type: Discrete, Evolvingg, or Recurring] LocationBranch, Store, Facility, Channel URLMap: Start, Previous, Current,urrent, Next, Last Wheredoes it happen? Where doe it refer to? Where CustomerRelationships Effect, OutcomeTransaction TypeTransaction # [Degenerate Dimension]Step # Activity, Process, Event KeyActivities How How does it happen?How do we know it happened?How do we uniquely identify a fact/event?Revveennuuee ChannellssStreeamsCost Structures b DateTimeTime ZonePeriod Timeline:meline: Milestones When
If you have been affected byany of the issues raisedin this presentation:
AgileData WarehouseDesignfromLa w re nc e Corrw ith Ji m St agni tt oD im e n s io n al M o d e li n g ,to Star SchemaAgile Data Warehouse DesignLawrence Corr, Jim Stagnitto,Decision Press, November 2011
Online decisionone.co.uk and agiledw.info – Agile data warehouse design resourcesinclude BEAM tools, templates, reference card and links to books and articles Lawrence Corr: Lcorr@decisionone.co.uk @LawrenceCorr Connect with me, if you read ADWD (and you like it)
- MODELSTORM: Don’t model alone! Model with examples!
-Agile Data Warehouse Design Coursewith Lawrence Corr15-17 April, Utrecht
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 Ambler www.agiledata.org
