1
Business Intelligence
Practice
Agenda
• BI Overview • BI Current State
• Agile BI – A new Methodology
• Agile BI – Case Study – Abercrombie & Fitch • Questions
Business
Intelligence
3
Don Jackson draws on more than 27 years experience in the information systems industry to help businesses achieve
greater profitability and reduce the cost of Business Intelligence within their IT departments.
His team consists of over 70 dedicated professionals with years of BI experience and industry certifications.
We have successfully implemented large DW projects for both public and private sector clients including:
• A & F • AEP • Abbott Nutrition • Cardinal Health • Nationwide Bank • Nationwide Financial • Nationwide Insurance • NetJets
• State of Ohio Agencies
Business
Intelligence
On-Shore Business Intelligence at Off-shore Prices Agile BI Methodology •Information Factory •Agile BI Development•Business Sponsor / IT Alignment using Consensus
Centers of Excellence (COE)
•Data Architecture
•Enterprise Data Integration
•Information Delivery/Presentation/Visualization
Industry and partner certifications
•IBM, Informatica, MicroStrategy, Microsoft, Netezza, Oracle, SAP/Business Objects
•Gold level certification
•TDWI, DAMA (CBIP, CDMA industry-certified)
Applying
Agile Principles to
Business Intelligence
Agile Business Intelligence
6
Lessons learned from the trenches2009 Agile
Projects
Abercrombie & Fitch Abbott Nutrition Cardinal Health Nationwide Financial NetJets
7
A&F – Enterprise Data Warehouse – 6 months
•DataStage, Netezza, and OBIEE
Abbott Nutrition – Customer Profitability – 16 weeks
•Consensus, Informatica, Oracle, Cognos
Cardinal Health – MAPS – 12 weeks
•Consensus, SSIS, SqlServer, Performance Point
Nationwide Financial – 12 weeks
•Informatica, Teradata, MicroStrategy
NetJets – Enterprise Data Warehouse – 6 months
BI
Development
Inefficient and Expensive
Labor intensive: too much time spent gathering and
organizing data
Tools are not integrated: reporting and analysis are not
linked with the processes that ensure accuracy
Lack of consistency between monitoring, analysis, and
reporting due to poor data lineage
Inefficient, labor intensive process
without automation
9
Analysis & Reporting Data Models Data Movement & Quality Source Data Deploy Test Build Design Analysis Requirements Entity Relationship Diagram in ERWin by Data Modeler ETL Modules in PowerCenter by ETL Developer Use Case in Excel or Word by Business Analyst Manual rekeying? Manual rekeying? Business Object Definition in Excel or Word by Solution Architect Manual rekeying? Cube by OLAP Developer Manual rekeying? Tables in SQL by DBAETL Test Scripts in Excel or Quality Center Manual rekeying? Manual rekeying? Reports by Report Developer In SQL By DBA User Documentation by Business Analyst ETL Documentation In Word or Visio Operations Documentation in Word Manual rekeying? Manual rekeying?
BI SDLC:
Waterfall
1 Business Case Assessment 2 Enterprise Infrastructure Evaluation 3 Project Planning Implementation15 16 Project Evaluation 8 Database Design 12 Application Development 11 ETL Development 13 Data Mining 10 Metadata Repository Design 14 Metadata Repository Development 9 ETL Design Justification Initiate 5 Data Analysis 4 Project Requirements Definition 6 Application Prototyping 7 Metadata Repository AnalysisSolution Scoping Design Develop/Test
Implement Proceed Gate Define Gate Commit Gate Accept Gate Close Gate Begin Gate
10
Time to Value: 1 – 2 YearsStatus Quo
vs. Agile
11
Manual, Paper Based, Slow, Error
Prone
Automated, Online, Reusable, Accessible,
Agile Overview
12
Will adopting an Agile Methodology solve our issues?
The Agile
Manifesto
We are uncovering better ways of developing software by doing it and helping others do it. Through this work we have come to value: Individuals and interactions over processes and tools
Working software over comprehensive documentation
Customer collaboration over contract negotiation
Responding to change over following a plan
That is, while there is value in the items on the right, we value the items on the left more.
Scrum
Methodology
Scrum is based on a "Sprint," which is “typically” a 30-day period focused on a specific deliverable.14
1. Product backlog is determined 2. Backlog is prioritized 3. 30 day sprint is commenced 4. Daily standupmeetings are directed by the Scrum Master 5. Product is delivered
Key Agile
Principles!
The Fundamentals
To achieve breakthrough performance and cost savings would require a development process with the
following:
Mandatory Characteristics of Methodology:
As business and IT friendly as possible As lean as possible
As repeatable as possible
With the highest quality possible At the lowest cost possible
Agile Alignment
16
Information
Process
Technology
Users and Tools
Stewardship
How does ICC’s methods identify the right metrics for senior management?
How does ICC’s methods capture business rules, calculations and measures? Why is it faster?
How can you incorporate our current technology platform into an Enterprise Data Warehouse?
How does the Agile process address divergent user needs for analysis and reports?
How does the Agile process ensure data accuracy and integrity?
17
Finance
•Income Statement
•Drill-down Variance
•Operational Plan Variance
•Cash Flow/Working Capital
•Balance Sheet •CapEx/Strategic Investments •Treasury Marketing •Market Opportunities •Competitive Positioning
•Product Life Cycle Management
•Pricing •Demand Generation Sales •Sales Results •Customer/Product Profitability •Sales Tactics •Sales Pipeline
•Sales Plan Variance
Customer Service
•On-Time Delivery
•Information, Complaint, and Claims
•Service Benchmarks
•Service Value
Product Development
•Product & Portfolio Innovation
•Product Development Milestones
•Market & Customer Feedback
Operations
•Purchasing and Procurement
•Production and Capacity
•Inventory Management
•Distribution and Logistics
•Cost & Quality Management
•Process Efficiency
Human Resources
•Organization and Staffing
•Compensation
•Talent and Succession
•Training and Development
•Benefits
Information Technology
•Business Value Map
•IT Portfolio Management
•Project/SDLC Management
•IT Vendor Management
•IT Compliance Management
Executive Management
•Financial Performance • Risk Management • Compliance Management
Common
Business
Functions
18
Capital Expenditure Planning Expense Planning and Control Headcount and Compensation Planning Sales Planning and Forecasting Financial Summary Balance Sheet Income Statement Cash Flow Strategic Financial Planning & ForecastingFinance Sales HR Operations Hea d c o u n t Ex p e n s e s Rev e n u e Pl a n M a rk e t Dem a n d Cap ita l Ex p e n d itu re s Depreciation Expense Operating Expense
Financial Process Flow
Re-think
Everything!
Change the way we:
Capture and document requirements Estimate and manage work
Design the architecture and build models Develop and test ETL code
Implement data stewardship Deploy BI Systems
Build Enterprise Data Warehouse Systems
19
People Process Platform
Business and
Technical Value
Chains
A framework to organize Agile BI processes20
Value Chains
Value Chain
Deconstruction
Analysis &
Reporting
The Business Value Chain• “Objective Modeling” is used to break down (analyze) and
align broadly stated objectives (epics) into a hierarchy of manageable, easily understood questions and ideas (stories).
• Stories are shared with Data Architecture and Data
Integration and linked to the Business Value Chain to provide continuous focus on delivering Business Value. These stories go through a five-step process to transform
them into elements of a meaningful dimensional model (synthesis)
1. Business Questions Business Terms
2. Business Terms + Dimensional Functions (topic, category, measure)
3. Dimensional Functions are assembled into Multi-dimensional Information Packages
4. Queries & Dashboards are created from the Info Packages
5. Queries & Dashboards are reviewed to confirm they answer the target business questions
Data
Architecture
The Technical Value Chain
After receiving the business terms from the business value chain,
initial requirements and data architecture are determined.
Major deliverables include:
Initial Architecture Modeling (review existing models) Conceptual Data Models,
Logical Data Models, Physical Data Models, Data Model Patterns and
Reference Models (per release cycle)
Coordination with enterprise architecture group is essential
Data
Integration
The Technical Value Chain
• The business and architecture value chains are used as
inputs to create the data integration stories.
•These stories are used to identify target-to-source data
mappings and identify the elements that will comprise the ETL rules for each sprint cycle.
Major deliverables include:
• Data Quality Profiles • Target to Source Maps • ETL Process Flows
• ETL Reference Patterns • ETL Test Scripts
• ETL Code
Horizontal vs.
Vertical Slice
27
Account Payable (Mainframe) Account Receivable (Mainframe) Flat File Flat File Account Payable Staging Area (Oracle DB) Account Receivable Staging Area (Oracle DB) SOURCE PSA (Staging Database) Mickey (Lotus Notes DB)Flat File Mickey Staging Area (Oracle DB)
TIPS (Lotus Notes DB)
Excel / Flat File TIPS Staging Area (Oracle DB) Account Payable Datamart (Oracle DB) Account Receivable Datamart (Oracle DB) TARGET (Datamarts) Freight Payments Datamart (Oracle DB) Sales Activity Datamart (Oracle DB) Financial Adjustment Datamart (Oracle DB) Profitibility Datamart (Oracle DB) Cognos Frame Work Model P&L Reporting
Package P&L Report P&L Ad Hoc
Queries
Profitibility Process Flow – Logical View
COGNOS CONSENSUS
Customer
Profitability:
Reuse Example
28
1. AR Business Terms 2. AR Modeling Objects 3. AR Info Packages4. AR Source to PSA Code/Audit 5. AR Source to PSA Code Tables 6. AR PSA to EDW
7. AR EDW to AR DM Code/Audit 8. AR EDW to AR DM Codes Tables 9. AR Data Mart
10.AR Cognos Framework
1. Reset Audit Table (21)
2. Load Audit Table (21)
3. Reset PSA Table (4)
4. Load PSA Table (4)
5. Verify AR Table (6)
6. Verify AP Load (1)
7. Verify Load (13)
8. Load Staging Table (11)
9. Load Dim Table (3)
10.Final Load (9)
29
Sprint
Cycles
30
In a perfect world – 2 week Sprint cycles
In the real world – Velocity and business requirements
determine length of Sprint cycles
31
A&F - Retail Merchandising System
November 10, 2009
A&F ETL IF Project
Abercrombie & Fitch ETL Information Factory Project:
•
ETL transformations using IBM DataStage version 8•
Multiple sources systems, ranging from Oracle RMS, PeopleSoft, and DB/2. Abercrombie team populates into Staging database.•
ICC Information Factory development to transform Staging toNetezza Data Warehouse foundation target.
•
Around 300 ETL jobs delivered during the six month project.•
Sprint activities include: Mapping, Design Specifications,Maturity
and Velocity
Quality Speed People Platform Process 0 Current State 1 Defined 2 Architected 3 Implemented 4 Integrated 5 Optimized Combined VelocityEfficiency
Gains
35
0.00 0.50 1.00 1.50 2.00 2.50 3.00 3.50 4.00Sprint 2 Sprint 3 Sprint 4
2.31
3.00
3.82
ETL Jobs per Day
Jobs per Day
Productivity increased by 66% from Sprint 2 to Sprint 4 0% 30% 66% 0% 10% 20% 30% 40% 50% 60% 70%
Sprint 2 Sprint 3 Sprint 4
Efficiency
Cost
per Sprint
36
$-$50,000 $100,000 $150,000 $200,000 $250,000 $300,000 $350,000Sprint 2 Sprint 3 Sprint 4
$339,960
$279,360
$259,560
Sprint 1 – Training and Setup
Three week Sprint 1 for training and prep:
•
Information Factory members trained and educated on IBM DataStage and Netezza.•
Senior team members installed IBM DataStage environment and laptop configurations.•
Analysis & Design team members created Design Specifications and QA Test Plan documentation.•
Aggressive December timeline in order to start Development in Sprint 2. Team was flexible with their schedules.•
Helpful task: Performed team building exercises to establish group dynamics.Sprint 2 – Three ETL Subject Areas
Five week Sprint 2 on three subject areas:
•
Large hierarchy subject areas where our „preparation and setup‟ from Sprint 1 was put to the test.•
Helpful tool: Daily Scrum meeting to communicate and discuss tasks at hand.•
Helpful rule: If the source was not defined by the deadline, the ETL job was pushed into the next Sprint backlog.•
Helpful task: Unit Testing, Code Review, and plenty of QA testing to prevent defects and rework.•
Helpful communication: Mercury Interactive Quality Center to track and log defects.Sprint 3 – Four ETL Subject Areas
Six week Sprint 3 with Reusable Code:
•
Efficiencies kicked-in with consolidated A&F QA process, utilized previously developed reusable code, and split team into two „pods‟.•
Helpful activity: Added Design Review with A&F internal team to prevent downstream QA bugs.•
Helpful task: Coding re-use of existing jobs, shared containers, documentation, and standards.•
Helpful environment change: Migrated from Windows to LINUX platform to speed up development without server lockups.•
Helpful team knowledge: Project experience allowed Sprint to be completed early.Sprint 4 – Transactional Data in Subject Areas
Six week Sprint 4 to transform large data volume:
•
Large data volume in Sprint required team to monitor performance and speed within the ETL jobs.•
Overcame hurdles with unexpected source design changes via a flexible SOW schedule.•
Final game-plan established for a strong finish to deliver by the end of May.Share a strong sense of purpose with a commitment to
performance objectives:
•
Experienced Project Managers, Architects, and Sr. Developers were responsible for ensuring successful project delivery.•
Junior developers completed the ICC Apprentice Program and ETL training to become highly skilled ETL developers.•
Created “project specific library” of reusable components.•
Test-driven development reduces defect rework which saves time and money.•
Continuous visibility of progress and work product review.42
Process
Agile Business Intelligence
43
Program Management• Business Justification, Financial Management, Portfolio Management
Scrum Management
• Release Planning, Sprint Planning, Sprint Execution
Business Modeling
• Business Questions, Business Terms, Business Objectives, Business Stories
Solution Prototyping
•Topics, Categories, Measures, Information Packages
Value Chain Deconstruction
•Business Analytics, Data Integration, Data Architecture
Test-Driven Development
•Business Value validation, Technical Value Test Scripts, Automated &
Continuous Integration/Regression Testing, Business-led Acceptance Testing,
Production & Operation Support
•Training & Deployment, Service Level Agreements, Operations Support & Maintenance
Agile Alignment
44
Information
Process
Technology
Users and Tools
Stewardship
We quickly capture the right metrics and analysis requirements for each user group
Our process provides dynamic business rule and data classifications, that are reusable
We are certified in all major technologies and can assist enterprise architecture to ensure scalability and flexibility
We are experts in delivering real value in terms of analytics, dashboard and detailed drill down
reporting to all user audiences
Our process implements stewardship from the start and ensures data accuracy and integrity
ICC
Agile BI
A Game Changer
45
•We have applied Agile principles to address the current inefficiencies in BI development
•We have created an integrated process that automates many of the coding activities
•We have connected the Business value with the Technical value so all parties understand their dependent relationship
