Business intelligence

UW Certificate Program in Business IntelligenceBusiness Intelligence: Project Planning, Analysis & Design Spring Term, 2007Course ObjectivesThis course focuses on how to design and build a Business Intelligence solution. We will introduce and compare various BI technologies across key BI Platform, BI Pure Play, and ERP vendors. You will also learn how to design and build a data warehouse within the context of student BI projects. Students can develop their own projects within collaborative teams or be assigned an existing data source to develop a project. To ensure success during the implementation phase in course 3, students will plan for and gather business requirements, as well as design the data warehouse in order to develop an effective BI plan. Other topics include: Defining the BI project Scoping the project down Asking the right questions Data modeling Analysis of reports & metrics Gathering business requirements Project analysis Defining needed dimensional measures Warehouse design Sourcing the data and source mapping to the target location _Required Textbook(s) Data Warehouse Lifecycle Toolkit Microsoft Data Warehouse Toolkit InstructorElizabeth Vitt evittAssessment CriteriaStudents will be assessed based on class participation and project work. Final class deliverables include Project Planningo Prioritization Matrix o Project Plan o Team Plano Biz Requirements o Issues List (if necessary) Data Deliverableso Bus Matrix o Data Element Inventoryo Data Profiling Resultso Data Model (3rd Iteration)o Source to Target Mapping Technical Functional Specso Overall Architecture o ETL Functional Speco OLAP Functional Spec o Data Mining Functional Speco Reporting Functional SpecStudents will implement projects in Course 3. WKCLASS MTGFOCUSTOPICSREADING *ASSIGNMENT / DELIVERABLES- Current week assignment organized by Folder Number11/07BI Strategy Course Overview Kimball Lifecycle Methodology BI / DW Architecture Framework Enterprise vs. Departmental BI Dimensional Modeling Process Review Business Process / Analytic Themes Bus Matrix Prioritization Matrixo Business Value o Feasibility Project Introduction General BI/DW Conceptso MSTK Introduction o MSTK Ch 1: Biz Reqs pg. 3 21 o DWTK Ch 3: Project Planning pg 42 63 Adventure Works Project Info o MSTK Ch 1: Company Overview pg. 21 37 o Custom - Original AW Interview Summaries o Custom - New AW Interview Summaries o Custom - New AW Organizational Chart o Custom - AW Data Source Information (File Layout and Example Data)o Custom AW Example Reports Prepartion Reading for 1/14 Class Mtgo MSTK Ch 2: Dim Model Basics pg. 39 49 o MSTK Ch 2: Dim Model Process pg. 72 91 o DWTK Ch 3: Project Planning pg. 64 94 o DWTK Ch 4: Biz Reqs pg. 95 136 o DWTK Ch 7: Building Dim Models (265 312)Assignment Details Provided on 1/07 01 - Environment Set Upo Restore a New OLTP 02 Adventure Works Synthesis Questionso Assignment List 03 Adventure Works Deliverables o AW Analytic Themes & Processes o AW Bus Matrix o AW Prioritization Matrix o Issue / Question List21/14Project Planning and Business Requirements Project Planning and Scoping Iterative Development Implementation Team Risk Management Architecture Planning Technology Selection Business Requirements Gathering Data Profiling Data Element Inventory BI Requirements Document Logical / Physical Dimensional Modeling Review o Fact Table o Dim Table Source to Target Mapping Adventure Works Project Info o Detailed Interview Summaries o Original Data Mart Review Prepartion Reading for 1/28 Class Mtgo MSTK Ch 2: Dim Model pg. 49 72 o DWTK Ch 5: Intro Dim Modeling (139 216)o DWTK Ch 6: Grad Dim Modeling (217 - 263) Additional Preparation Reading for 1/28 Class MtgKimball Online - Design Tips: o Tip 25 - Dimensional Models for Parent Child Applications o Tip 37 - Modeling a Pipeline with Accumulating Snapshotso Tip 42 - Combining Periodic and Accumulating Snapshotso Tip 46 - Another Look at Degenerate Dimensionso Tip 86 - Reference Dimensions for Infrequently-Accessed Degenerateso Tip 95 - Patterns to Avoid when Modeling Header/Line Item TransactionAssignment Details Provided on 1/14 Business Requirements Summary Existing Data Mart Review Interview Questionnaire Review Team Evaluation Review Project Plan Review Data Profiling Reports Data Element Inventory Source To Target Mapping Iteration 1 Data Model As Necessary o Updated Bus Matrix o Issues Questions ListWKCLASS MTGFOCUSTOPICSREADING *ASSIGNMENT - Current week assignment organized by Folder Number31/28Dimensional Modeling Iteration 2 Dimension Design Techniques o Heterogeneous o Date / Time o Degenerate o Role Playing o Snowflake vs. Staro Recursive Fact Table Design Techniques o Allocationso Granularity o When to Use Different Fact Table Typeso Types of Measures o Workflow Calculations Techniques to Validate the Design Prepartion Reading for 2/04 Class Mtgo MSTK Ch 3: Toolset (sections TBD)o MSTK Ch 4: Setup and Physical Design (sections TBD)o DWTK Ch 8: DW Arch (317 334)o DWTK Ch 13: Arch Plan (449 499) Assignment Details Provided on 1/28 Iteration 2 of the Modelo Major Design Push V2 Source To Target Mapping As Necessary o Updated Bus Matrix o Updated Biz Reqs Doco Issues Questions List42/04Dimensional Modeling Iteration 3 and Architecture Planning Continuation of topics from the prior week plus the following: Many-to-Many Junk Aggregate Dimensions Audit Dimensions Reference Dimensions Usage of Outrigger Tables Slowly Changing Dimensions Physical Design Considerations Prepartion Reading for 2/11 Class Mtgo MSTK Ch 5: Design ETL Systemo MSTK Ch 6: Develop ETL System (sections TBDo DWTK Ch 9 Back Room Arch (335 - 372) o DWTK Ch 16 Data Staging (609 661)Assignment Details Provided on 2/04 Iteration 3 of the Modelo Many-to-Many o Outrigger table o SCD Support V3 Source To Target Mapping As Necessary o Updated Bus Matrix o Updated Biz Reqs Doc o Updated Issues List52/11Extract Transform and Load (ETL) Data Quality Assessments Data Loading Techniques Detecting Data Changes Historical vs. Incremental Data Loads Data Load Auditing Late Arriving Attributes Common ETL Challenges ETL Evaluation Criteria Prepartion Reading for 2/25 Class Mtgo MSTK Ch 7: OLAP and SSAS (sections TBD)o DWTK Ch 14 Aggregates (543 567)Assignment Details Provided on 2/11 ETL Functional Spec ETL Prototype As Necessary o Updated Biz Reqs Doc o Updated Issues ListWKCLASS MTGFOCUSTOPICSREADING *ASSIGNMENT - Current week assignment organized by Folder Number62/25Online Analytical Processing (OLAP) Dimension & Cube Design Principles Data Aggregation & Storage Modes Cross Fact Table Analysis Loading Data At Different Levels of Detail Advanced Calculations Time Analysis KPIs Drill-through OLAP Data Refresh Strategies OLAP Evaluation Criteria Prepartion Reading for 3/03 Class Mtgo MSTK Ch 8: BI Apps o MSFT Ch 9: SSRS (sections TBD)o DWTK Ch 10: Front Room Arch (373 409)o DWTK Ch 17: Building End User Apps (665 686)Assignment Details Provided on 2/25 OLAP Functional Spec OLAP Prototype As Necessary o Updated Biz Reqs Doc o Updated Issues List 73/03Reporting User Community Segmentation Report Design & Delivery Paradigms Summary vs. Drill-through Reports Parameters / Filters Data Visualization BI Portals Reporting Evaluation Criteria Prepartion Reading for 3/10 Class Mtgo MSTK Ch 10: Data Mining o MSTK Ch 17: Real Time BI (sections TBD)Assignment Details Provided on 3/03 Reporting Functional Spec Reporting Prototype As Necessary o Updated Biz Reqs Doco Updated Issues List83/10Advanced Analytics Data Mining Algorithms Decision Trees Nave Bayes Clustering Sequence Clustering Time Series Association Neural Network Data Mining Development Process Real Time BI (time permitting) Tech Evaluation Criteria Prepartion Reading for 3/17 Class Mtgo MSTK Ch 12: Security o MSTK Ch 13: Metadata o MSTK Ch 18: Present and Future o DWTK Ch 11: Metadata 435 447 Optional Reading o DW Maturity from TDWIAssignment Details Provided on 3/10 Data Mining Functional Spec Data Mining Prototype As Necessary o Updated Biz Reqs Doc o Updated Issues ListWKCLASS MTGFOCUSTOPICSREADING *ASSIGNMENT - Current week assignment organized by Folder Number93/17Finalizing the Project Report Security Data Security Metadata Management Designing Subsequent Data Marts BI Maturity Models Revisited Final Technical Evaluations Not ApplicableFinal Course Deliverables Project Planningo Prioritization Matrix o Project Plan o Team Plano Biz Requirements Doco Issues List (if necessary) Data Deliverableso Bus Matrix o Data Element Inventoryo Data Profiling Resultso Data Model (3rd Iteration)o Source to Target Mapping Technical Functional Specso Overall Architecture Speco ETL Functional Speco OLAP Functional Spec o Reporting Functional Speco Data Mining Functional Spec* READING Legend DWTK = Data Warehouse Lifecycle ToolkitMSTK = Microsoft Data Warehouse ToolkitCustom = Custom Course Project Content Available in Student Files