Value-Driven Data Warehousing Engineering the Information Supply Chain

Value-Driven Data Warehousing Engineering the Information Supply Chain For Greater, More Obvious ROI 1

About Jay Foulkrod 2

Mission: Two Core Convictions 1. Better connect Business Intelligence to business Results 2. Pass this know-how on to others via a workable framework 3

Noteworthy Contributions • Creator of the Value-Driven DW Framework • Fortune 100 Retail: Delivered possibly the largest ABC / OLAP implementation on the planet • Inventor / Author: OLAP-based ABC Allocation Tool • Fortune 100 Oil & Gas: Enterprise Data Warehouse Decommission & Re-construction • Fortune 100 Computers: Supply Planning System 4

Overview 5

What this workshop will cover: Part One: The Case for a New Approach Objective: Unpack a core issue that currently besets the DW / BI industry and define criteria for a solution Part Two: An Intro to Activity Based Costing Objective: Learn what ABC is and how it works, via a hands-on exercise Part Three: The VDDW Framework Objective: Learn the elements of the VDDW Framework, the nature of the insight provided, and business initiatives enabled 6

Part One The Case for a New Approach 7

The State of the DW / BI Industry Promise or Problems? 8

Signs of Promise • Vendor Consolidations • IBM/Filenet, Oracle/Hyperion, Microsoft/Proclarity, SAP/Outlooksoft, BOBJ/ALG • A precursor to better, more interoperable tools? • Projected budget increases and market growth • Gartner: 6% growth in BI spending thru 2009 • Lexicon explosion (BI, CRM, SRM, CPM, MDM, DW 2. 0, BI 2. 0, EII, etc. ) • Innovation doesn’t happen in a vacuum 9

Signs of Problems • General Business Dissatisfaction with IT • The Offshoring Movement • HBR Commentary • Is Sarb. Ox keeping DW / BI afloat? • Internal Industry Confusion around various players’ roles & value props • Failure to articulate / prove ROI • Numerous anecdotes of customer dissatisfaction – from users to executives 10

Promise or Problems? What do you think? Let’s Discuss… 11

One Big Problem: A Critical Content Gap Is this really the state of affairs? Let’s Revisit some Basics… 12

A Quick DW / BI Overview The Information Supply Chain 13

GOAL: Process Enablement CHARACTERISTICS: • Designed for speed of data entry • Normalization & RI to protect data integrity • Unit of work is an individual transaction • Time scope is very current – data quickly stales 14

GOAL: Transform Raw Data to Information Content HOW: Integrate, stage, cleanse, structure, allocate, score / stratify, and otherwise enrich it somehow 15

CHARACTERISTICS: • Big Data, Long Processing (an assembly line) • Big Hardware • Unit of Work is the Individual Query • Read –Intensive • Time-Scope is Historical-to-Current 16

17 GOAL: Disseminate the Content

SO WHAT? The value of the Supply Chain metaphor: Not just an explanatory aid… A diagnostic aid 18

19 Let’s Zoom In: Can the ISC metaphor help explain issues?

20 Why re-manufacture content between the factory and the channel?

This means: • Additional Storage • Additional Processing Time • Additional Handoffs • More Points for Failure • Inconsistency of Metric Definitions • Additional Admin / Support Effort 21 Why re-manufacture the content between the factory and the channel?

Let’s Zoom In on this… How do users typically gain insight from the DW/BI system? 22

Answer: What are the Costs with this approach? Excel Hell! • Human Time & Effort (More Integration) • Lost Opportunities • Questions of Accuracy 23

These are symptoms of a problem, but not the root of it. What do all these layers of integration indicate? 24

The problem originates in the factory 25 Re-integration downstream indicates deficient integration upstream

Conclusion: The goods coming out of the factory are deficient The factory must somehow be: • Repaired • Retooled • Re-engineered • Or Replaced 26

Agree or Disagree? Is this a problem? Are we indeed data-rich yet content-poor? Let’s Discuss… 27

What do we do about it? First Step: Dig Deeper… …Unpack the nature of the deficiency 28

What is the nature of the deficiency? The Content Deficiency… …reflects a Design Deficiency The Design Deficiency… …Flows from a Purpose Deficiency The Purpose Deficiency …Flows from a Knowledge Deficiency 29

Content is a function of Design Content Defined… A repository of information that is: Refined Insightful Meaningful Valuable Usable Good Content requires Design Any debates on this point? 30

Design flows from Purpose Defined… A complex of human drives, including: Intentions Priorities Assumptions Goals Felt Needs Design is the product of Human Aims Any debates on this point? 31

The Purpose of the DW Some commonly proffered viewpoints: • • Decrease the cost of information Enable better-informed decisions It depends on the business need Single Version of the Truth How Compelling are these? 32

Questions for Reflection 1. What human purpose(s) are reflected in the design of my company’s DW / BI systems? 2. What are common design characteristics that flow from the views of purpose espoused on the previous slide? 3. Are there significant Unspoken purposes that also affect design? If so, what are they, and how do they affect it? 4. How are these Purposes often manifest within IT, more broadly speaking? In IT incentive structures? IT Job Performance Evaluations? 33

A Different View of DW Purpose The Business Person’s, not the Engineer’s: Show me: Where I am making and losing it Why What to do about it 34

And the people said… DUHHH !!! What’s Your Point !? 35

Why the Uninspired Response? The Engineer’s Biggest Concern: Not WHY, but HOW Get things working! Keep them working! This is our primary need… …our perceived Purpose Why disconnected from How… …does not compute! 36

But this is a serious problem! Why must govern How, for… Purpose drives Design, so… What’s the solution? 37

The Purpose Deficiency Flows from a Knowledge Deficiency What do we need? Answer: A Framework…By which to decompose the enterprise as a piece of money-making, money-spending machinery A Framework… that guides design and integration so as to close this financial intelligence content gap 38

What would such a Framework do? 1. 2. 3. 4. 5. Expose Bottom-Line Profitability Do so at the Transaction grain Expose the drivers of that performance Be valid financially and operationally Harmonize performance measurement goals and vocabulary enterprise-wide 6. Produce a system that is fast and easy to use Let’s Drill In… 39

1. Expose Bottom-Line Profitability Some Basics: What is Profit? …Revenue Minus Cost Is there a standard way of looking at Profit? … via a “P&L” or “Income Statement” One example: Wal-Mart 40

Wal-Mart’s Income Statement Let’s color-code the various elements… …Revenues, Costs, Margins 41

Which Line is the “Bottom Line”? For our purposes, NOP (Net Operating Profit) Why? • It’s the next stage of Profit insight into which companies typically don’t have deep, reliable visibility • It’s controllable with a cause-and-effect relationship to the products and partners with whom the company does business • Keep this workshop simple and focused on learning the core 42 concepts, which can be more deeply applied once learned

Let’s put back on our Engineering Hats INCOME Dim TIME Dim AMOUNT Measure Engineers, Data Architects, Modellers, what do you see here? …A Cube anyone? What are the dimensions and measures in this cube? …HINT: Two dimensions, one measure What is the granularity of this cube? 43 …What additional dimensionality would be valuable?

2. NOP at the Transaction Grain Why? BEST PRACTICE for both DW Design and ABC, because of… – Maximum reporting flexibility Value Capture – Ease of Integration – ABC Allocation accuracy & auditability 44

3. Expose Performance Drivers Don’t just show me what the numbers are …Show me why they are …Show me what’s behind them Why? – To know where and how to act upon them – Accuracy & Auditability 45

4. Financial & Operational Validity Numbers should tie back to financials… And also reflect real efficiency differences… • • • Between Products, Partners, & Transactions Validated by operational line managers With real business cause-and-effect explanation Why? Accuracy Buy-In Use Value Capture 46

5. Harmonize Perform. Measurement Enterprise decisions are made… …with Bottom-line visibility And evaluated… …for Bottom-line results Why? Value Capture 47

6. Fast & Easy to Use Why? Use Value Capture Many nuggets require Heuristic analysis requires: – Speed – Ad Hoc Slice & Dice 48

Meeting these criteria How close do companies typically get? The Current State of enterprise profit insight Options for closing the gap Associated Issues 49

The Current State • Revenue available in the GL • OPEX is and COGS are comprehensively analyzed • Availableno direct link all OPEX to individual transactions, • There is typically for of transactions, customers, & products partners, and products Why? …ERPs track this data well …The relationship of OPEX to transactions is indirect …Thisconducive to point-in-time transaction-based updates …Not means Gross Margin is deeply available …incidental to the ERP’s core job of process enablement …therefore, not captured in the ERP …And that’s OK! 50

The Current State Schematically Represented Some Disparity Some Commonality • What are the strengths and weaknesses of the current state? • What insights can and can’t be provided? • Is there value in providing deeper visibility? 51

Closing the Gap How do we present a combined view? Bottom-line Financials… Put another way, how do we combine data of With rich operational dimension disparate dimensionality & granularity? analysis (customers, products, suppliers, etc. ) 52 Two Options…

Two Options for Resolving the Granularity Disparity Aggregate Allocate 53

Aggregation Roll up data to greatest common grain BECOMES This is not helpful… …Insight reduced, not enhanced 54 (GL already has Rev & COGS anyway)

Allocation Pushing data deeper than its “natural grain” Use of “drivers” to split a measure from source to target records BECOMES 55 Allocation is the key to closing the financial-operational intelligence gap

Basic Allocation Steps Let’s Practice Locate Handout Page 10 1. 2. 3. 4. Identify the Pool of Money to allocate Determine the allocation targets Determine the allocation driver Compute the allocated results by: • • • Rolling up the driver Computing the rate Driving down the results 56

Any issues with what we just did? Some Common Allocation Issues: • Highly Political • Tend to stir up debates over accuracy • Why? • • Accountability isn’t our natural preference High Stakes Proposition Allocation methodology is often arbitrary Not based on business cause-and-effect 57

Part Two An Introduction to Activity-Based Costing 58

ABC: A Brief History • Brainchild of Robert Kaplan and Robin Cooper of Harvard Business School • Excitedly received and implemented in the late 80’s / early 90’s • Fell from favor in the 90’s. Reasons: • Too complicated to manage • Too much data • Questions of accuracy – Continual Time Surveys – Handling of Excess Capacity 59

ABC: A Brief History • Currently Undergoing a Resurgence • Reasons: • Maturation of Tools • Maturation of the Discipline – Time Driven – Transaction Based – Capacity Aware • Maturation of Information Architecture • VDDW represents a convergence of these nowmature elements 60

ABC Defined • ABC = Activity Based Costing • A methodology for allocating costs • ABC Rests on the following premise: An enterprise can be decomposed and mechanically described as follows: The enterprise spends money to employ resources with capacity to perform activities in support of partners and products 61

ABC Defined Major in ABC cost Steps Dimensions flow. . . The enterprise spends money to employ resources with capacity to perform activities in support of partners and products • This sentence describes the sequence of steps by which the ABC system apportions cost • It also indicates the major dimensional entities with which the ABC system interacts 62

ABC Decomposed An ABC system has two major components: 1. A modelling tool: • • Where allocation rules and cost flow paths are defined “design-time” 2. A calculation engine: • • • Applies the model rules to input data Generates allocated expenses as output “run-time” 63

ABC Modelling First Question – What’s a “model”? 1. A representation of reality that is: • • • Value-added Insightful Reasonably approximate 2. Something iteratively built and refined 3. Something requiring human validation • Preferably assessable from multiple angles 64

ABC Modelling But why model OPEX consumption? Why not use actual consumption instead? 1. Actual, point-in-time OPEX simply isn’t tracked 2. Doing so would be prohibitively • • • Costly Complicated And, a little spooky 3. Modelling is a faster, more cost-effective path to value capture 4. 100% Precision is not the goal. Maximum Value Capture is the goal. 65

ABC Modelling Is an ABC model a data model? …Yes & No Yes… It includes a data model …A static representation of the entities that Contribute to or Consume business processes …And therefore Costs No… It is more. It also includes dynamic elements …A mapping of how cost flows from one entity to the next …And the rules and variables which affect this flow 66

ABC Modelling An ABC Model can be thought of as a Network… …of Nodes… …with Paths defined between them downdetermine …and Rules which $ flow… $ should. Resource how much GL flow down …organized each path… Cost Target Activity 67

ABC Model Calculation Operates upon Loaded or …Resolving data implied by Referenced rules to numeric weighting factors by which to …via a batchinputs these model procedure… apportion cost… records of …that generates allocatedorder at each node in …in this cost GL the network…Activity Resource Cost Target 68

ABC’s Value Elucidating indirect costs (i. e. , OPEX) Therefore, prime candidates for ABC exhibit: • • Appreciable OPEX High Volume, High Complexity, Low Margin: • • • Where OPEX visibility is important, and… Mere intuition is not likely to improve NOP Adherence to the following description: The enterprise spends money to employ resources with capacity to perform activities in support of partners and products 69

ABC Mechanics The Ingredients Look at Handout Page 12 What are ABC’s key ingredients? 1. 2. 3. 4. A source of $ A list of corporate Resources A list of business processes / activities A supply of enterprise transaction & master data allocation targets 70

ABC Mechanics The Ingredients Look at Handout Page 12 What are ABC’s key ingredients? 4. A mapping of cost flow paths 5. Expressions which resolve to numeric weighting factors for these flow paths 6. A supply of “Drivers” that plug in as the variable values for the logical expressions in #5 above, to determine each path’s weight (consumed or contributed capacity) 71

ABC Mechanics Assembling the Ingredients (10 Steps) 1. Identify core business activities 2. Identify the Resources which perform those activities 3. Identify the Monies which pay for those resources 4. Map the contribution of Cost & Capacity from GL to Resources to Activities 5. Determine the Drivers of contributed cost 6. Compute fully-loaded activity cost Let’s Practice it Together… 72

ABC Case Study Identifying Activities, Resources, and Cost-$ources Read the Overview & Pressing Decisions sections of the Case Study (Handout Pages 14 -15) Activities • • • Selling Servicing Unloading Unpacking Put-Away Picking Packing Loading Driving Delivering Occupancy Admin Support Resources • • • Management Sales Warehouse Hands Van Drivers Vans Stock Occupancy GL • • • Salaries Commissions Rent Utilities Forklifts Vans 73

ABC Case Study GL – Resource Allocation Let’s walk thru Together, then you complete (Handout Pages 21 -22) 1. Map the GL Account to the Resources for which they pay (Handout 22, GL-Resource Assignment Matrix) 2. 3. Define the Driver for the GL Account (Handout 21, GL Accounts) Compute the Allocation: (Handout 22, GL-Resource Allocation Matrix) • • • Rollup the driver Compute the rate Drive down the $ Question: Which steps are “Modelling” vs. “Calculation”? 74

ABC Case Study Resource-Activity Allocation Let’s walk thru Together, then you complete (Handout Pages 21 & 23) 1. Map the Resources to the Activities they perform (Handout 23, Resource-Activity Assignment) 2. Determine the %-Dedicated of each Resource-Activity (Handout 23, Resource-Activity Assignment) 3. Compute the Allocation: (Handout 23, Resource-Activity Allocation) • • • 4. Rollup the driver (% is easy! … but still the same basic operation!) Compute the rate Drive down the $ Compute Capacity Contrib. : (Handout 23, Resource-Activity Capacity) • • Define Activity Capacity Units (Handout 21, Activities) Drive down the Capacity Units 75

ABC Mechanics Assembling the Ingredients (10 Steps) 7. Define the allocation target (cost consumers) 8. Declare each activity’s allocation Unit 9. Determine the drivers behind allocation units 10. Compute cost consumption down to allocation targets 76

ABC Case Study Activity-Transaction Allocation Let’s walk thru Together, then you complete (Handout Pages 16 -19, 21, 24 -25) 1. Determine each activity’s capacity allocation unit (Handout 21, Activities) 2. Define the Drivers and Formula for capacity consumption (Handout Pages 16 – 19, Process / Activity Analysis & Handout 21, Activities) 3. Compute the Allocation: (Handout Pages 24 -25, Transaction Detail) • • Resolve the Formula to Units for each transaction Rollup the Units (sound familiar? ) Compute the rate Drive down the $ Allocate Txn IDs 1, 22, 38 and, if time, 2 & 39 77

ABC Case Study P&L Analysis Take a look at the ABC P&L (Handout 26)… …What do you see? Compare to the P&L computed earlier (Handout 10)… …What do you think? Does Visibility beyond Gross Margin matter? What about how one gets there? Let’s skin another cat Elucidating Excess Capacity… 78

ABC Case Study P&L with Capacity Analysis How would we determine Resources’ productive Utilization? Utilization = Consumed Capacity / Contributed Capacity Do we have this info readily available? Handout 23, Resource-Activity Capacity Handouts 24 -25, Transactions Let’s Finish off Handout 26 Together… • • • Compute the Utilization achieved for each Operating Activity Recompute the Product P&L, excluding excess capacity Total Up the Unused Capacity 79

ABC Case Study Management Recommendations Review the Pressing Decisions section of the Case Study (Handout Pages 14 -15) What would be the impact of making the currently contemplated pricing decisions, and what would you recommend in that regard? What other recommendations might you make to Dilbert’s? 80 Does Visibility beyond Gross Margin matter?

Musings & Learnings What is the group learning about: …The requirements for implementing ABC? …The value & strengths of this approach? …The challenges & points for which to prepare? How could you envision putting this sort of insight to use in your organization? 81

Part Three The Value-Driven Data Warehousing Framework (VDDW) 82

VDDW = ABC & Dimensional Design Combined A New Breed of Analytical Data Warehouse A powerful integration 83

What is VDDW? VDDW is a target configuration …Of tools and dimensional designs …that when properly combined …Yield unparalleled financialoperational intelligence …In as streamlined a manner as possible 84

Why call it a “Data Warehouse”? 1. Comprehensiveness of the data supply required to properly execute ABC 2. Overlapping purpose with the DW 3. Similar system topologies 4. It fulfills the DW’s highest purpose eliminating the need for costly duplication 5. Practical field experience where the ABC system becomes the de facto EDW 85

Why the modifier “Value-Driven”? Plant a stake in the ground as to the guiding purpose behind the DW: Elucidate and Capture Bottom-line Value (Purpose drives design) 86

Components of the VDDW Framework 1. 2. 3. 4. 5. 6. 7. Common Dimension Bus Common Star Schemas ABC Allocation Engine The Activity Dimension Activity Contribution Schemas Activity Consumption Schemas OLAP Hyper-Cube A Picture is Worth a Thousand Words (Handout 40) 87

What VDDW does not address… Other DW / BI / CPM concerns are neither Precluded nor Presumed Rather, VDDW provides the insight by which these can be Justified and Prioritized 88

VDDW Insights • • • NOP (Handout 34) Activity Expense & Efficiency (Handout 35) Driver Analysis (Handout 36) Capacity / Utilization (Handout 37) NOP Leverage (Handout 38) Financial Traceback (Handout 39) The VDDW Hyper-Cube makes analysis of these metrics completely ad-hoc, across the superset of all dimensions 89

Categories of Action • • • Pricing Changes Process Changes Productivity Tools Policy Changes Structural Cost / Capacity Changes: – Hire / Fire / Re-Deploy / Re-Mix – Purchase or Sell Assets – Enter / Grow / Shrink / Exit 90

Business Initiatives Enabled • • • Menu-Based Pricing Supply Chain Routing Planning & Budgeting Return on Advertising Negotiating for NOP Incenting / Commissioning NOP • Sku Rationalization • Customer Profitability • Customer Segmentation • Benchmarking • Category Management • Staffing Efficiencies • Process Costing Others? ? ? 91

Q&A 92

Additional Slides 93

94