Syed Rayhan Gurushyam Mony Scott Newby Delivering BI

Syed Rayhan, Gurushyam Mony, Scott Newby Delivering BI Projects using Agile

Agenda • Introduction • Project Summary • Goals • Context • Our Story • Challenges with Applying Agile on Our BI Project • Phase 1 • • • Our Solution Improvement Opportunities Phase 2 • • • Our Solution Improvement Opportunities Phase 3 • Our Solution • What did we take away?

Syed Rayhan • 20+ Years of Experiences in IT • 10+ Years of Experiences using Agile • Implemented Agile at organizations of all sizes and types • Government Agencies, Large Banks, Startups • Co-authored “Enterprise Java with UML”, published by Wiley & Sons • Deputy Lead, North America Agile Practice at Accenture • Current Interests • • Scaling Agile on Large Scale Complex Delivery (Io. T, SAP, Non. Software) • • Enterprise Agile Transformation Lean Startup (New Product Development) My Role on the project • Solution Architect • Agile Coach/ Delivery Lead

Gurushyam Mony • 10+ Years of Experiences in IT • 8+ Years of Experiences using Agile • Expertise in areas of test automation for large scale applications • Implemented large scale web, service and bi automation suites for government agencies • Founder of League. Plan. It. com a web app for managing sports leagues • Quality Management Architect, Department of Tax, VA • Current Interests • • Implementing enterprise level Test Automation suites for aiding TDD and BDD practices at Tax • • Enterprise Agile Transformation at Tax Building large scale re-usable data bed for Tax application development teams My Role on the project • Lead Test Engineer

Scott Newby • 20+ Years of Experiences in IT • 10+ Years of Experiences using Agile State & Federal Agencies, Healthcare, Telecom • Apply agile techniques across projects and small teams • • Senior data warehouse developer, Washington D. C. area • Current Interests • • Automated predictive analysis • • Entrepreneurship in the business intelligence space Delivering meaningful data to decision makers My Role on the project • Data warehouse architect & developer

Project Summary • Federally funded project ran by DMV • Started in 2007 and winded down in 2016 • The scope of the project was to modernize the aging Mainframe crash data collection & reporting system • The new system was built on Microsoft Platform -. Net, SQL Server, Biz. Talk, MSMQ, BRE, SSIS • The Solution has two parts – • • • A transactions system to collect crash reports from the field A BI solution to generate insights about the crashes (the focus of this talk!) A twelve member team had a Program Manager, a Project Manager, two Business Analysts, two testers, 4 developers, a BI architect, a Solution architect

Project Context • The System had upstream & downstream dependencies – DOT, State & Local Police • The upstream & down stream systems were also being modernized • Issues with the existing system • Duplicate data/data accuracy/ delay in reporting/lack of access • Inflexible reporting • Lack of integration with other data sources

Project Goals • To create a single reporting system that provides “ a 360 degree view of a crash” so that timely & flexible reporting is accessible by the right people across the state & federal government. As a result, • • DMV can put better signage at the right location, right/effective policies to improves safety on the road, effective driver education programs etc. • State and local police can better deploy police patrols on the road • DOT can pinpoint the structural issues of the road system and promptly correct them

Our Story – Why Agile • Many loosely coupled stakeholders, decentralized decision making made it difficult to gather requirements • Many moving (inter-dependent systems across agencies) parts made it difficult to do predictive planning • Limited/fixed funding required we deliver the most important capabilities first Better risk management, incremental delivery, adaptive planning led us to Agile!

Our Story – How we implemented Agile • The department followed Waterfall and was not interested in moving to Agile • The development team was new to Agile • We decided to use Agile internally and decided to keep all external reporting as is • This did not require the rest of the departments to learn Agile • We ended up following 2 -week Sprints • We completed 177 Sprints between 2007 and 2015 • The first release took 6 months (it was primarily core transactional system), followed by incremental delivery there after • The first major BI released in mid 2008

Challenges with using Agile on BI Projects • Lack of clear data ownership and governance • Constant push & pull between immediate needs & long-term priorities for customers • Correct definition of data & measures can be difficult to capture • Lack of clear ideas on grooming BI stories • Lack of consistent development patterns for BI artifacts • Lack of automation tests practice and test automation tools • Lack of CI (continuous integration)/ CD (Continuous Delivery) practices

Our Agile Journey Phase 3: Optimized and Mature Stage (2012 to 2015) Phase 1: Initial Stage (2007 to 2008) Phase 2: Managed and Defined stage (2009 to 2012)

Phase 1 – Our Solution • BI Architect performed the role of a proxy product owner • Backlog consisted of epics representing each report • • • Stories broken down in terms of development, testing and release efforts Stories were sized independently and consumed in different sprints Acceptance criteria was broad & vague • “Duplicate the crash fact report 2009” • Testing was all manual effort trailing the development sprint followed by release efforts • Team struggled to keep up with fixing bugs & delivering new reports within our sprints • Get it done under all cost mentality

Phase 1 – Improvement Opportunities • Improve on data ownership • Better backlog grooming • Better refinement of stories in sprint backlog • Better story acceptance criteria definitions • Determine consistent development practices • Move away from manual intensive testing activities • Avoid constant de-scoping and splitting of stories • Base lining of stories for better sizing estimations • Clear definition of DONE for stories

Phase 2 – Our Solution • Started to identify common patterns across many BI features • Better definition of acceptance criteria through the use of a standard template for reports and BI artifacts • Pre-conditions • Definition of data & measures • Any UI mock-ups etc. • Story base lining was followed as a reference for sizing • Epics split horizontally and at times vertically for consumption across various sprints • ‘DONE’ included acceptance tests to be completed in the same sprint • Re-usable data set designed for testing purposes, this enabled us to test early and often

BI Story Acceptance Criteria Template

Phase 2 – Improvement Opportunities • Standardize the story creation, definition and consumption practices • Leverage Behavioral specifications to capture acceptance criteria • Improve the pain of redundant programming activities with simple tools • Move away from manual intensive testing activities • Implement Test Driven Development (TDD) practices • Design test automation framework for our warehouse • Move towards continuous integration practices • Collaborate with our business customers • Help them grasp the full potential of Data Warehouse • Understand deliver the right product to them

Phase 3 – Our Solution • Stories defined such that it allowed incremental development of dimensions and facts along with any UI related feature • Implemented data ownership and governance • Used prototyping as a way to identify/clarify BI needs • Included acceptance criteria template to be filled by the product owners as part of “Definition of Ready” • Built simple tools with excel to generate complex MDX queries for both development and testing activity • Built custom SSIS ETL test automation suite • Identified and standardized the development activities (tasks) associated with implementing BI stories • Implemented behavioral specification template for defining acceptance tests

BI Story Acceptance Test Template

Our BI Acceptance Tests Flow 1 Excel Test Data Bed 2 3 4 Load Expected Result Data. Warehouse MDX Generator Crash Cube Person Cube Run MDX Expected Results 6 Transactional DB Run Application ETL MDX Queries 5 Expected Test Data Teardown all DB’s and Inject Test Data Validations Actual Results Test Results Vehicle Cube

What did we take away? • The use of templates and patterns • helped our Product Owners write better BI stories • helped the development team plan and commit accurately • Vertical slices of BI stories were possible and was preferred for incremental releases • A BDD approach to BI provided more clarity and structure for our development and testing activities • Prototyping often helped us collaborate with our customers in delivering the right features • The testing suite along with use of simple tools helped us with our quality and agility

Contact Syed Rayhan – syed. rayhan@accenture. com Gurushyam Mony – gurushyam. mony@gmail. com Scott Newby - scott. newby 74@gmail. com