Business Intelligence Methodology www Instant BI com 132012

Business Intelligence Methodology www. Instant. BI. com 1/3/2012

Introduction ®History of the Methodology ®Overview of the Methodology ®Discussion points on the Methodology

History of the Methodology ® First project Peter undertook in 1991 was a disaster ² “Everything I know should stand me in good stead”. BIG mistake! ® In 1993 introduced to the Metaphor Consulting Methodology ² ² Based on a book called “The Consultants Methodology Handbook” or similar This contained a lot of great ideas and pointers to great ideas ® In 1995 asked to write a BI Methodology by SAS ® In 1997/8 Exposed to the Pw. C BI Methodology ® In 1999/00 Exposed to the Prism Iterations Methodology ® In 2001/05 Exposed to the Sybase Systems Integrators Methodology ® They all have strengths and weaknesses ® Even with the advent of Se. ETL/BI 4 ALL in 2005/11 we Public Information. Copyright 2012 – Instant Business Intelligence. have been working to these older techniques. 3

Overview of the Methodology ® We have made some “radical changes” in processes ² Our experience and tools allow us to work smarter ® Se. ETL now allows us to map far more data than ever before ® The data models allow us to design databases faster than ever before ® The data warehouses are covering more areas than ever before ® This is created a new problem…too much data to comprehend ® We now focus on understanding data, all other issues are solved ® Our experience also tells us what is most important in our Public Information. major industry areas like telco and retail 4 Copyright 2012 – Instant Business Intelligence.

Methodology Phases ® Phase A - Prototype, Presentation and Proposal ® Phase B - Tender Response ® Phase C – IBI Internal Project Review ® All Phases - Project Management ® Phase 1 – Hardware/Software Installation for Pilot ® Phase 2 – Source Data Cataloguing, Profiling, Analysis ® Phase 3 - Requirements Gathering ® Phase 4 - Extract Subsystem Design Public Information. Copyright 2012 – Instant Business Intelligence. 5

Methodology Phases ® Phase 5 - Detailed Data Warehouse Analytical Apps Design/Build ² ² ² Phase 5 A - Data Warehouse Database Design/Build Phase 5 B - Data Preparation and Loading Design/Build Phase 5 C Implement Initial End User Applications Phase 5 D – Pilot System Test Phase 5 E – Initial End User Training ® Phase 6 – Pilot Implementation ² ² Phase 6 A – Pilot Implementation Phase 6 B – Pilot Review, Analysis, Updates ® Phase 7 – ETL Migration and Testing Public Information. Copyright 2012 – Instant Business Intelligence. 6

Methodology Phases ® Phase 8 – Hardware/Software Installation for Rollout ® Phase 9 – Scale Up – Data Warehouse Volumes ® Phase 10 – End User Training ® Phase 11 – Scale Up – Roll Out to End Users ® Phase 12 – Data Warehouse Exploitation Projects ® Phase 13 – IBI Client Project Review Public Information. Copyright 2012 – Instant Business Intelligence. 7

Discussion Points ® The number and diversity of fields is now the #1 problem ® Integration of the data is the #2 problem ® Fast databases like Netezza and Sysbase IQ mean that sample data can be loaded very fast and analysed very fast. ® We have moved data analysis to the front of the process. Note. ² ² Phase 1 – Hardware/Software Installation for Pilot Phase 2 – Source Data Cataloguing, Profiling, Analysis ® The idea is to get production level volumes of data into the staging area as soon as possible and run the new data profiling tools ® Only after the data has been profiled and come to be understood do we go into requirements Phase 3 - Requirements Gathering Public Information. ² Copyright 2012 – Instant Business Intelligence. 8

Discussion Points ® We now build the base layer of the data warehouse according to the BI 4 ALL data models as non lossy ® We then build derivation fact tables as needed ® “Everything is connected to everything” builds a “mesh” of joins between all the transaction level fact tables and many summaries too ® We can start on this work prior to Requirements Gathering as well ® The speed of mapping development and prototype development means that 4, 000 or so data fields is quite feasible for a 1. 0 DW ® We propose Stored Procedures as optional extra to turn the data warehouse into a Q&A machine. SPs being able to be called from any tool and for the results to be Public Information. 9 Copyright 2012 – Instant Business Intelligence. reliable and consistent

Discussion Points ® Once the prototypes are accepted and agreed to be deployed? ² Then we migrate the ETL to the “standard” if Se. ETL is not to be used. ® The report development can start as soon as early portions of the ETL and data model are delivered ® It is far easier to accommodate change ® Later releases are far easier to accommodate ® The Metadata dictionary is used throughout for control and analysis ® Data linearage can be established via the Metadata dictionary ® The following two diagrams explain the situation in more detail Public Information. Copyright 2012 – Instant Business Intelligence. 10

Different Approaches Traditional ETL approach Each phase must produce ‘cast in concrete’ outputs as they cannot be easily changed Reqts Model Design Data Mapping Some vendors overlap activities and do iterations of smaller projects. ETL Design/Build Apps Build Changes we would like to make later in the project go into Release 2. 0 or never get implemented Approach Using prototype tools - Se. ETL and BI 4 ALL Reqts Model Design Data Mapping ETL Design/Build Convert Se. ETL to Tool No longer on critical path Changes We retain the ability to change the database/ETL Apps Build We do all modelling, mapping, ETL design/build and a lot of apps development at the same time with the ability to generate all ETL related objects. Public Information. Copyright 2012 – Instant Business Intelligence. 11

A Perspective on BI Modeling Techniques And ETL Complexity 3 NF Models Complex to query v Little or no history v Cartesian Products v Lost Rows v No time variance in the model itself v Limited use v Breakthrough of their time 3 NF Models Time Variance + Stability Analysis v Complex to query v Lots of history v Cartesian Products v Lost Rows v Great archives v Really useful v v v Where we Started 1987 -1990 Eg NCR Models Leading companies doing dimensional models (Metaphor) Trailing Edge 1990 -5 Less Public Information. Copyright 2012 – Instant Business Intelligence. Combine 3 NF + TV + SA and dimensional models v Rich history v Great archives v Great performance v No gaps v No Cartesian products v No lossy joins v Life is good v DWs are expensive v Eg IBM Models v Industry ‘Best Practice’ 1996 - now No 3 NF data anywhere v Archive using dimensional models v Functionally equivalent but no archive layer required v Suffers slow down of history in type 2 dimensions v What Ralph Kimball talks about v Has evolved since 1994. v Industry ‘Leading Edge’ 1994 - now Data Model Sophistication and Functionality ETL Complexity Generally speaking No 3 NF data anywhere v Archive using dimensional models v Functionally equivalent but no archive layer required v Significant reuse of tables v Field names have meaning v Data types have meaning v Eg Sybase Models v Industry ‘Leading Edge’ 1999 - now No 3 NF data anywhere v Archive using dimensional models v Functionally equivalent but no archive layer required v Very high reuse of tables v Field names are meaningless v Data types are meaningless v Eg BI 4 ALL v Thought Leadership 2005 - now More 12