Springfield Public Schools Data Warehouse Development and Implementation

1. Springfield Public Schools Data Warehouse Development and Implementation Presented by Bruce Douglas

2. Items Covered • Why a data warehouse? • Data warehouse development • Technology running the data warehouse • Data warehouse implementation and deployment • What we’ve learned, good and bad

3. Why a data warehouse? • Disparate systems • A system for student information, a system for staff information, flat files for assessment results, etc. • Inability for users to run many reports • Systems at the time had limited ad hoc reporting tools and many reports required “manipulation” • Ineffective reports • Reports that were provided by the systems did not allow for drill throughs or provide either aggregate or fine grain reporting • Lack of longitudinal reporting • Longitudinal reports had to be manually created from either existing reports or by restoring archived data

4. Selection Process • RFP Developed • A committee with representatives from across the district gave input on their needs • RFP Responses Reviewed • The committee reviewed the submissions and scored them using a standard rubric • Vendor Presentations • The top three vendors performed a multi-hour presentation displaying their product • Cognos EPMS Selected • The select vendor was the Cognos EPMS package developed by DecisionED

5. Data Warehouse Development • A standard GAP analysis was provided to us by DecisionED • Fields within the EPMS package were then mapped from our source systems • Vendor spent nine months designing our ETL, importing pre-canned reports, creating CognosPowerCubesand developing a couple dashboards • After vendor left the data warehouse went through two major iterations, one to correct issues in the ETL and another when a new student information system was implemented

6. Data Warehouse Development Methodology • Cognos provided a five day training on their ETL and design philosophy centered around Kimball’s dimensional model • The tool for ETL is Cognos Data Manager that allows you to design source extractions to the staging database, model dimensions, integrate those dimensions into fact table creation and to load your data warehouse

7. Data Warehousing Books • The Data Warehouse Toolkit by Ralph Kimball • Walks through the theory of dimensional modeling • Provides various examples for common needs (HR, Finance, Budgeting, Sales, etc.) • The Data Warehouse ETL Toolkit by Ralph Kimball • Provides techniques for good ETL practices that help cleanse data from source to data warehouse • Excellent for practical/technical side of creating a data warehouse

8. General Concepts - Dimensions • Textual information that is used for filtering and analyzing data

9. General Concepts - Dimensions • Often “wide” tables with many columns but often few rows (50-100 columns not uncommon)

10. General Concepts - Dimensions • Conformed dimensions are dimensions used by many fact tables

11. General Concepts - Dimensions • Use a surrogate primary key for dimensions to reduce impact of joins and to enable time tracking

12. General Concepts - Dimensions • Slowly changing dimensions retain historical data by date and time tracking when an attribute is changed

13. General Concepts – Facts • Numeric data that calculations are performed on (most often your KPIs)

14. General Concepts – Facts • The surrogate keys from the related dimensions comprise a composite key for each row

15. General Concepts – Facts • Fact tables are often narrow with millions of rows (our largest is currently 36 million and grows 4+ each school year, currently planned is a fact table that will contain 200+ million rows)

16. General Concepts – Facts • Creating aggregate fact tables at different grains of time help with report performance, along with the use of OLAP data sources

17. Example – Student Enrollment

18. Technology running the data warehouse • Cognos data manager (SSIS) • ETL, batch jobs • Cognos framework manager (Tabular models on steroids) • Metadata modeling • Cognos transformer (SSAS) • Cube design and creation • Cognos BI Connection (SSRS/BIDS and SSDT/Excel) • End User GUI • Contains “studios” for report creation, simple querying, analysis with cubes/dimensional models • SQL Server 2005 and 2012

19. Implementation and Deployment • Contract for RFP was awarded in early 2008 • Vendor work wrapped late 2008 • First training session occurred January 2009 • Principals and district leadership (roughly 120 individuals) were asked to attend four two hour sessions spread over six weeks called “Casual User Training”

20. Implementation and Deployment • First session focused on how to access, navigate and run reports with familiar data (state assessment) • Second session focused reports in additional data families and brought in problem based work • Third session extended the second session • Fourth session focused on recapping, discussing practical use and previewed “Analysis Studio”, the Cognos product used for cube analysis

21. Implementation and Deployment • After the completion of the “Casual User” training, a “Power User” training session was offered that focused on using “Analysis Studio” • The following school year additional staff and new administrative staff were trained in the same manner and additional sessions of the “Power User” training were offered • Year three of deployment the “Casual User” training was required of only new administrators and condensed to three sessions by removing the problem based work as it was integrated through the system • Year five has seen a revamp of the training to focus the use of all available data systems

22. What’s Worked • Cognos is an extremely powerful tool • The amount of data available compared to prior years • The capability of “Analysis Studio” to allow for easy ad hoc report creation • Commitment of district to focus on data driven decision making

23. What Hasn’t Worked • Data integrity issues due to vendor ETL and business rules • Flash in pan training not effective over time, difficult to keep user skills up to date if they are not using them

24. Questions