狠狠撸

狠狠撸Share a Scribd company logo

SR5 Workshop.ppt

?Download as PPT, PDF?
?
Pablo Fernández-Castanys
Pablo Fernández-Castanys

The document outlines the agenda for a workshop on I-PoS (SR5) in EDW. The agenda includes an overview of the workshop, introductions, discussions on the discovery and prototype approach, business reporting models, reviewing reporting requirements, and interactive requirements sessions. There will also be breaks for coffee and lunch. The goal is to gather requirements for an EDW system to support business reporting needs.

1 of 29
I-PoS (SR5) in EDW Design Workshop Thursday 25th October 2007
2 ? Overview of the Workshop (10:00 to 10:15) ? Introductions (10:15 to 10:30) ? Approach to the Discovery and Prototype (10:30 to 10:45) ? Approach to Business Reporting Models (10:45 to 11:15) ? Coffee Break (11:15 to 11:30) ? SR5 Reporting Requirements Review (11:30 to 12:00) ? Interactive Requirements Session Part 1 (12:00 to 13:00) ? Lunch (13:00 to 13:45) ? Interactive Requirements Session Part 2 (13:45 to 16:00) ? Close (16:00 to 16:30) Agenda
3 ? Overview of the Workshop (10:00 to 10:15) ? Introductions (10:15 to 10:30) ? Approach to the Discovery and Prototype (10:30 to 10:45) ? Approach to Business Reporting Models (10:45 to 11:15) ? Coffee Break (11:15 to 11:30) ? SR5 Reporting Requirements Review (11:30 to 12:00) ? Interactive Requirements Session Part 1 (12:00 to 13:00) ? Lunch (13:00 to 13:45) ? Interactive Requirements Session Part 2 (13:45 to 16:00) ? Close (16:00 to 16:30) Agenda
4 Overview of Workshop ? Review of the approach we will taking over the next 8 weeks ? Brief view on Business Reporting Models, so we are talking the same language ? Review of the reporting requirements gathered thus far, including a skeleton model and a breakdown of data requirements
5 ? Overview of the Workshop (10:00 to 10:15) ? Introductions (10:15 to 10:30) ? Approach to the Discovery and Prototype (10:30 to 10:45) ? Approach to Business Reporting Models (10:45 to 11:15) ? Coffee Break (11:15 to 11:30) ? SR5 Reporting Requirements Review (11:30 to 12:00) ? Interactive Requirements Session Part 1 (12:00 to 13:00) ? Lunch (13:00 to 13:45) ? Interactive Requirements Session Part 2 (13:45 to 16:00) ? Close (16:00 to 16:30) Agenda
6 ? Overview of the Workshop (10:00 to 10:15) ? Introductions (10:15 to 10:30) ? Approach to the Discovery and Prototype (10:30 to 10:45) ? Approach to Business Reporting Models (10:45 to 11:15) ? Coffee Break (11:15 to 11:30) ? SR5 Reporting Requirements Review (11:30 to 12:00) ? Interactive Requirements Session Part 1 (12:00 to 13:00) ? Lunch (13:00 to 13:45) ? Interactive Requirements Session Part 2 (13:45 to 16:00) ? Close (16:00 to 16:30) Agenda
7 Approach to the discovery and prototype Business Requirements And Reporting Data ETL And EDW Application Design Workshop Objectives 22/10/2007 Design Workshop Review 13/11/2007 Design Workshop Review 20/11/2007 Report Requirement Sign Off 29/11/2007 Report Build Iteration 1 Data Mart & HW design Iteration 1 Data Mart & HW design Iteration 2 Data Mart & HW design Iteration 3 Review Sources Define Data Requirements Finalise Data Contracts Finalise Data Model ETL & EDW Design Iteration 1 Finalise ETL & EDW Design Review Sources Define Data Requirements Review Sources Define Data Requirements Report Build Iteration 2 Report Build Iteration 3 ETL & EDW Design Iteration 2 ETL & EDW Design Iteration 3 ETL HLD Sign Off 14/12/2007 Workshops and Prototypes ETL & EDW Requirements Elaboration Build of Priority Components Elaboration Build of Priority Components Data Contract Data Model Sign Off 06/12/2007 Project & Stakeholder Management 34 weeks 10 weeks Construction Elaboration Transition 8 weeks
8 ? Overview of the Workshop (10:00 to 10:15) ? Introductions (10:15 to 10:30) ? Approach to the Discovery and Prototype (10:30 to 10:45) ? Approach to Business Reporting Models (10:45 to 11:15) ? Coffee Break (11:15 to 11:30) ? SR5 Reporting Requirements Review (11:30 to 12:00) ? Interactive Requirements Session Part 1 (12:00 to 13:00) ? Lunch (13:00 to 13:45) ? Interactive Requirements Session Part 2 (13:45 to 16:00) ? Close (16:00 to 16:30) Agenda
9 What needs reporting? ? Ideal approach is to extract the required reports from a “business reporting model” ? What is a business reporting model in essence it has two parts: – What we want to add up? – What we want to analyse by? ? So how do we go about getting the requirements right?
10 Will this “Business Reporting Model” Work ? We create the “business reporting model” simply on a flip chart, ? Then we walk thru the model to see if we can get the reports out! ? Simple, really! ? Here is how we do it …using Motorcar Insurance
11 What will this model look like? What we want to add up? Analyse by Analyse by Analyse by Analyse by Analyse by Analyse by Analyse by Analyse by … now for a worked example
12 What we want to Add Up? ?Premium ?Claim ?Loss Ratio [Claim/Premium] ? Typical questions: – How much premium did we collect? – How much claims did we pay? – What Loss Ratio are we running at? … so now lets see what we want to analyse by
13 What we want to analyse by? ?Premium ?Claim ?Loss Ratio [Claim/Premium] Analyse by Time Analyse by Car Analyse by Customer Analyse by Broker Analyse by Geography ? Typical questions: – What is the average age of drivers? – What models of cars do we insure? – What is the longest policy period? – What locations do we insure? – Who is the top broker? … so now lets join things up
14 Now we can ask better questions ?Premium ?Claim ?Loss Ratio [Claim/Premium] Analyse by Time Analyse by Car Analyse by Customer Analyse by Broker Analyse by Geography ? Typical questions: – What location is the worst insured area based on claims paid for the 2007 year? – What colour car in London driven by males is the most profitable in terms of Loss Ratio? – Who are our top ten brokers based on Premium for last year? – What is the premium trend over the last five years in terms of Geography?
15 More detail on Analyse by Time ?Premium ?Claim ?Loss Ratio [Claim/Premium] Analyse by Time ? Each of the items is a level, these levels allow you to group up the data for example ? Year 2007 – Quarter 1 – 2007 – January 2007 – 01/01/2007 ? 01/01/2007 12:00:00
16 Now we will look at some of elements in a little more detail, What we want to Add Up? ? What we want to Add Up? – Things like Premium and Claim are measures – Things like Loss ratio (Claim/Premium) are calculated measures – By default measures are usually additive but you can do other domain aggregate functions such as average, count etc. – We store them in the database as facts, because that’s what they are facts about an action
17 Now we will look at some of elements in a little more detail, What we want to analyse by? ? What we want to analyse by? – We call the “analyse by” a dimension – Levels within the dimension allow the facts to be grouped up and summarised – The groups are called hierarchies
18 What have we done? ? We have created a dimensional model called a star schema ? It contains facts and dimensions ? We now have a business reporting model that can be easily
19 ? Overview of the Workshop (10:00 to 10:15) ? Introductions (10:15 to 10:30) ? Approach to the Discovery and Prototype (10:30 to 10:45) ? Approach to Business Reporting Models (10:45 to 11:15) ? Coffee Break (11:15 to 11:30) ? SR5 Reporting Requirements Review (11:30 to 12:00) ? Interactive Requirements Session Part 1 (12:00 to 13:00) ? Lunch (13:00 to 13:45) ? Interactive Requirements Session Part 2 (13:45 to 16:00) ? Close (16:00 to 16:30) Agenda
20 20 Refund Report ? Measure – Refund Value ? Calculated Measures – Elapsed Time between purchase and refund – % Variance on last week – % Variance on last year ? Dimensions – Store/Department – Refund Code
21 Refund Reports for the 23/10/2007 - Marble Arch - London Reason Refund Date Sale Date Elapsed Days Value %Week Wrong Size 15/10/2007 11/10/2007 4 ?100 5 Late arrival 15/10/2007 13/10/2007 2 ?200 7 Sub Total ?300 12 Trousers - Mens Trousers - Women Reason Refund Date Sale Date Elapsed Days Value %Week Wrong Size 15/10/2007 11/10/2007 4 ?180 8 Late arrival 15/10/2007 13/10/2007 2 ?230 12 Sub Total ?410 20 Grand Total ?710 32
22 22 Promotional Reports – Missed Opportunity ? Measure – Promotion Value ? Dimensions – Date/Time – Employee – Till – Missed Promotion Code / Description – Qualifying Flag – Store
23 Report of Promotional Missed Opportunity for the week ending 19/10/2007 Till Employee Transaction Date/Time Promotion Code Promotion Value Clothes Spencer 11/10/2007 PCD4 ?100 Furniturel Smith 13/10/2007 PCD2 ?200 Sub Total ?300 Marble Arch - London Woodgreen - London Till Employee Transaction Date/Time Promotion Code Promotion Value Clothes Spencer 11/10/2007 PCD212 ?180 Furniture Smith 13/10/2007 PCD318 ?230 Sub Total ?410 Grand Total ?710
24 24 Collect by Car ? Measures – Total Value – Number of collections ? Calculated Measures – Average Basket, week total, % on last day/week – Value, week total, % on last day/week – Number, week total, % on last day/week ? Dimensions – Date / Time – Employee – Till / personality / section – Store/Department – Refund Code
25 Report of Collect By Car for the week ending 19/10/2007 Till / Personality Employee Transaction Date/Time No.of Transactions Transaction Value Clothes Spencer 11/10/2007 10:35 1 ?100 Furniturel Smith 13/10/2007 18:35 1 ?200 Sub Total ?300 Marble Arch - London Woodgreen - London Till / Personality Employee Transaction Date/Time No. of Transactions Transaction Value Clothes Spencer 11/10/2007 11:24 1 ?180 Furniture Smith 13/10/2007 19:34 1 ?230 Sub Total ?410 Grand Total ?710
26 26 VAT Reporting ? Measure – Sales Value – VAT Value ? Dimensions – Product (UPC) – Date – Store/Department
27 Report on VAT paid for the week ending 19/10/2007 UPC / Description Actual Sale Value VAT Value Clothes 150 26.25 Furniturel 200 35.00 Sub Total 61.25 Marble Arch - London Woodgreen - London UPC / Description Actual Sale Value VAT Value Clothes 400 70.00 Furniture 300 52.50 Sub Total 122.50 Grand Total 183.75
28 28 Potential Measures and Dimensions ? Measure – Refund Value – Total Value of transaction – Number of collections – Promotion Value – Sales Value – VAT Value ? Dimensions – Product (UPC) – Date – Store/Department
29 ? Overview of the Workshop (10:00 to 10:15) ? Introductions (10:15 to 10:30) ? Approach to the Discovery and Prototype (10:30 to 10:45) ? Approach to Business Reporting Models (10:45 to 11:15) ? Coffee Break (11:15 to 11:30) ? SR5 Reporting Requirements Review (11:30 to 12:00) ? Interactive Requirements Session Part 1 (12:00 to 13:00) ? Lunch (13:00 to 13:45) ? Interactive Requirements Session Part 2 (13:45 to 16:00) ? Close (16:00 to 16:30) Agenda

More Related Content

SR5 Workshop.ppt

  • 1. I-PoS (SR5) in EDW Design Workshop Thursday 25th October 2007
  • 2. 2 ? Overview of the Workshop (10:00 to 10:15) ? Introductions (10:15 to 10:30) ? Approach to the Discovery and Prototype (10:30 to 10:45) ? Approach to Business Reporting Models (10:45 to 11:15) ? Coffee Break (11:15 to 11:30) ? SR5 Reporting Requirements Review (11:30 to 12:00) ? Interactive Requirements Session Part 1 (12:00 to 13:00) ? Lunch (13:00 to 13:45) ? Interactive Requirements Session Part 2 (13:45 to 16:00) ? Close (16:00 to 16:30) Agenda
  • 3. 3 ? Overview of the Workshop (10:00 to 10:15) ? Introductions (10:15 to 10:30) ? Approach to the Discovery and Prototype (10:30 to 10:45) ? Approach to Business Reporting Models (10:45 to 11:15) ? Coffee Break (11:15 to 11:30) ? SR5 Reporting Requirements Review (11:30 to 12:00) ? Interactive Requirements Session Part 1 (12:00 to 13:00) ? Lunch (13:00 to 13:45) ? Interactive Requirements Session Part 2 (13:45 to 16:00) ? Close (16:00 to 16:30) Agenda
  • 4. 4 Overview of Workshop ? Review of the approach we will taking over the next 8 weeks ? Brief view on Business Reporting Models, so we are talking the same language ? Review of the reporting requirements gathered thus far, including a skeleton model and a breakdown of data requirements
  • 5. 5 ? Overview of the Workshop (10:00 to 10:15) ? Introductions (10:15 to 10:30) ? Approach to the Discovery and Prototype (10:30 to 10:45) ? Approach to Business Reporting Models (10:45 to 11:15) ? Coffee Break (11:15 to 11:30) ? SR5 Reporting Requirements Review (11:30 to 12:00) ? Interactive Requirements Session Part 1 (12:00 to 13:00) ? Lunch (13:00 to 13:45) ? Interactive Requirements Session Part 2 (13:45 to 16:00) ? Close (16:00 to 16:30) Agenda
  • 6. 6 ? Overview of the Workshop (10:00 to 10:15) ? Introductions (10:15 to 10:30) ? Approach to the Discovery and Prototype (10:30 to 10:45) ? Approach to Business Reporting Models (10:45 to 11:15) ? Coffee Break (11:15 to 11:30) ? SR5 Reporting Requirements Review (11:30 to 12:00) ? Interactive Requirements Session Part 1 (12:00 to 13:00) ? Lunch (13:00 to 13:45) ? Interactive Requirements Session Part 2 (13:45 to 16:00) ? Close (16:00 to 16:30) Agenda
  • 7. 7 Approach to the discovery and prototype Business Requirements And Reporting Data ETL And EDW Application Design Workshop Objectives 22/10/2007 Design Workshop Review 13/11/2007 Design Workshop Review 20/11/2007 Report Requirement Sign Off 29/11/2007 Report Build Iteration 1 Data Mart & HW design Iteration 1 Data Mart & HW design Iteration 2 Data Mart & HW design Iteration 3 Review Sources Define Data Requirements Finalise Data Contracts Finalise Data Model ETL & EDW Design Iteration 1 Finalise ETL & EDW Design Review Sources Define Data Requirements Review Sources Define Data Requirements Report Build Iteration 2 Report Build Iteration 3 ETL & EDW Design Iteration 2 ETL & EDW Design Iteration 3 ETL HLD Sign Off 14/12/2007 Workshops and Prototypes ETL & EDW Requirements Elaboration Build of Priority Components Elaboration Build of Priority Components Data Contract Data Model Sign Off 06/12/2007 Project & Stakeholder Management 34 weeks 10 weeks Construction Elaboration Transition 8 weeks
  • 8. 8 ? Overview of the Workshop (10:00 to 10:15) ? Introductions (10:15 to 10:30) ? Approach to the Discovery and Prototype (10:30 to 10:45) ? Approach to Business Reporting Models (10:45 to 11:15) ? Coffee Break (11:15 to 11:30) ? SR5 Reporting Requirements Review (11:30 to 12:00) ? Interactive Requirements Session Part 1 (12:00 to 13:00) ? Lunch (13:00 to 13:45) ? Interactive Requirements Session Part 2 (13:45 to 16:00) ? Close (16:00 to 16:30) Agenda
  • 9. 9 What needs reporting? ? Ideal approach is to extract the required reports from a “business reporting model” ? What is a business reporting model in essence it has two parts: – What we want to add up? – What we want to analyse by? ? So how do we go about getting the requirements right?
  • 10. 10 Will this “Business Reporting Model” Work ? We create the “business reporting model” simply on a flip chart, ? Then we walk thru the model to see if we can get the reports out! ? Simple, really! ? Here is how we do it …using Motorcar Insurance
  • 11. 11 What will this model look like? What we want to add up? Analyse by Analyse by Analyse by Analyse by Analyse by Analyse by Analyse by Analyse by … now for a worked example
  • 12. 12 What we want to Add Up? ?Premium ?Claim ?Loss Ratio [Claim/Premium] ? Typical questions: – How much premium did we collect? – How much claims did we pay? – What Loss Ratio are we running at? … so now lets see what we want to analyse by
  • 13. 13 What we want to analyse by? ?Premium ?Claim ?Loss Ratio [Claim/Premium] Analyse by Time Analyse by Car Analyse by Customer Analyse by Broker Analyse by Geography ? Typical questions: – What is the average age of drivers? – What models of cars do we insure? – What is the longest policy period? – What locations do we insure? – Who is the top broker? … so now lets join things up
  • 14. 14 Now we can ask better questions ?Premium ?Claim ?Loss Ratio [Claim/Premium] Analyse by Time Analyse by Car Analyse by Customer Analyse by Broker Analyse by Geography ? Typical questions: – What location is the worst insured area based on claims paid for the 2007 year? – What colour car in London driven by males is the most profitable in terms of Loss Ratio? – Who are our top ten brokers based on Premium for last year? – What is the premium trend over the last five years in terms of Geography?
  • 15. 15 More detail on Analyse by Time ?Premium ?Claim ?Loss Ratio [Claim/Premium] Analyse by Time ? Each of the items is a level, these levels allow you to group up the data for example ? Year 2007 – Quarter 1 – 2007 – January 2007 – 01/01/2007 ? 01/01/2007 12:00:00
  • 16. 16 Now we will look at some of elements in a little more detail, What we want to Add Up? ? What we want to Add Up? – Things like Premium and Claim are measures – Things like Loss ratio (Claim/Premium) are calculated measures – By default measures are usually additive but you can do other domain aggregate functions such as average, count etc. – We store them in the database as facts, because that’s what they are facts about an action
  • 17. 17 Now we will look at some of elements in a little more detail, What we want to analyse by? ? What we want to analyse by? – We call the “analyse by” a dimension – Levels within the dimension allow the facts to be grouped up and summarised – The groups are called hierarchies
  • 18. 18 What have we done? ? We have created a dimensional model called a star schema ? It contains facts and dimensions ? We now have a business reporting model that can be easily
  • 19. 19 ? Overview of the Workshop (10:00 to 10:15) ? Introductions (10:15 to 10:30) ? Approach to the Discovery and Prototype (10:30 to 10:45) ? Approach to Business Reporting Models (10:45 to 11:15) ? Coffee Break (11:15 to 11:30) ? SR5 Reporting Requirements Review (11:30 to 12:00) ? Interactive Requirements Session Part 1 (12:00 to 13:00) ? Lunch (13:00 to 13:45) ? Interactive Requirements Session Part 2 (13:45 to 16:00) ? Close (16:00 to 16:30) Agenda
  • 20. 20 20 Refund Report ? Measure – Refund Value ? Calculated Measures – Elapsed Time between purchase and refund – % Variance on last week – % Variance on last year ? Dimensions – Store/Department – Refund Code
  • 21. 21 Refund Reports for the 23/10/2007 - Marble Arch - London Reason Refund Date Sale Date Elapsed Days Value %Week Wrong Size 15/10/2007 11/10/2007 4 ?100 5 Late arrival 15/10/2007 13/10/2007 2 ?200 7 Sub Total ?300 12 Trousers - Mens Trousers - Women Reason Refund Date Sale Date Elapsed Days Value %Week Wrong Size 15/10/2007 11/10/2007 4 ?180 8 Late arrival 15/10/2007 13/10/2007 2 ?230 12 Sub Total ?410 20 Grand Total ?710 32
  • 22. 22 22 Promotional Reports – Missed Opportunity ? Measure – Promotion Value ? Dimensions – Date/Time – Employee – Till – Missed Promotion Code / Description – Qualifying Flag – Store
  • 23. 23 Report of Promotional Missed Opportunity for the week ending 19/10/2007 Till Employee Transaction Date/Time Promotion Code Promotion Value Clothes Spencer 11/10/2007 PCD4 ?100 Furniturel Smith 13/10/2007 PCD2 ?200 Sub Total ?300 Marble Arch - London Woodgreen - London Till Employee Transaction Date/Time Promotion Code Promotion Value Clothes Spencer 11/10/2007 PCD212 ?180 Furniture Smith 13/10/2007 PCD318 ?230 Sub Total ?410 Grand Total ?710
  • 24. 24 24 Collect by Car ? Measures – Total Value – Number of collections ? Calculated Measures – Average Basket, week total, % on last day/week – Value, week total, % on last day/week – Number, week total, % on last day/week ? Dimensions – Date / Time – Employee – Till / personality / section – Store/Department – Refund Code
  • 25. 25 Report of Collect By Car for the week ending 19/10/2007 Till / Personality Employee Transaction Date/Time No.of Transactions Transaction Value Clothes Spencer 11/10/2007 10:35 1 ?100 Furniturel Smith 13/10/2007 18:35 1 ?200 Sub Total ?300 Marble Arch - London Woodgreen - London Till / Personality Employee Transaction Date/Time No. of Transactions Transaction Value Clothes Spencer 11/10/2007 11:24 1 ?180 Furniture Smith 13/10/2007 19:34 1 ?230 Sub Total ?410 Grand Total ?710
  • 26. 26 26 VAT Reporting ? Measure – Sales Value – VAT Value ? Dimensions – Product (UPC) – Date – Store/Department
  • 27. 27 Report on VAT paid for the week ending 19/10/2007 UPC / Description Actual Sale Value VAT Value Clothes 150 26.25 Furniturel 200 35.00 Sub Total 61.25 Marble Arch - London Woodgreen - London UPC / Description Actual Sale Value VAT Value Clothes 400 70.00 Furniture 300 52.50 Sub Total 122.50 Grand Total 183.75
  • 28. 28 28 Potential Measures and Dimensions ? Measure – Refund Value – Total Value of transaction – Number of collections – Promotion Value – Sales Value – VAT Value ? Dimensions – Product (UPC) – Date – Store/Department
  • 29. 29 ? Overview of the Workshop (10:00 to 10:15) ? Introductions (10:15 to 10:30) ? Approach to the Discovery and Prototype (10:30 to 10:45) ? Approach to Business Reporting Models (10:45 to 11:15) ? Coffee Break (11:15 to 11:30) ? SR5 Reporting Requirements Review (11:30 to 12:00) ? Interactive Requirements Session Part 1 (12:00 to 13:00) ? Lunch (13:00 to 13:45) ? Interactive Requirements Session Part 2 (13:45 to 16:00) ? Close (16:00 to 16:30) Agenda