Cherry Creek School District
The combination of Sybase PowerDesigner and Mantis consulting made it possible for us to quickly design and build a data warehouse that offers capabilities for analysis and reporting that we could only dream about previously.
Data Warehouse Project Manager, Cherry Creek School District
Unlike data-intensive industries in the private-sector, school districts rarely have powerful, sophisticated data warehouses to store and report on critical data. To build such a data warehouse for the Cherry Creek School District in Colorado, the district’s IT staff worked with Mantis Technology Group and relied on Sybase PowerDesigner to quickly and accurately design the architecture for its data warehouse, rapidly create datamarts, and ensure the district would have advanced reporting capabilities to enhance many aspects of its operations. Business Challenge The district can rapidly incorporate data from different sources into its data warehouse and create ad hoc and custom reports that help identify trends, correlate performance factors, and make financial decisions. Key Benefits Sybase Technology Industry
Unlike data-intensive industries in the private-sector, school districts rarely have powerful, sophisticated data warehouses to store and report on critical data. To build such a data warehouse for the Cherry Creek School District in Colorado, the district’s IT staff worked with Mantis Technology Group and relied on Sybase PowerDesigner to quickly and accurately design the architecture for its data warehouse, rapidly create datamarts, and ensure the district would have advanced reporting capabilities to enhance many aspects of its operations.
The district can rapidly incorporate data from different sources into its data warehouse and create ad hoc and custom reports that help identify trends, correlate performance factors, and make financial decisions.
Using PowerDesigner from Day One
The Cherry Creek School District is the fourth largest in Colorado. Its 7,400 employees serve nearly 50,000 students, and district enrollment has increased 40 percent over the past decade. The district comprises 38 elementary schools, 10 traditional middle schools and six traditional high schools.
The district had a relational database, but the Cherry Creek IT unit made a persuasive business case for building a true data warehouse, importing all student data on attendance, grades, disciplinary history, medical information, schedules and demographics. Other data such as scores on standardized tests would also be added.
Acting on the advice of Mantis, a software and business systems solution provider, the IT group used PowerDesigner from day one of the project. “Acquiring PowerDesigner is always one of our first recommendations,” says Brian Vickery, principal, Mantis Technology Group. “You may have great tools for ETL, reporting and dashboarding, but that means nothing without a well-designed data warehouse and the ability to track data lineage between the data sources. PowerDesigner is excellent at providing data mapping.”
Another Datamart Every 20 Days
The group began building the data warehouse, which would eventually measure 160GB with 25 dimension tables and 13 fact tables, with the largest fact table having 320 million rows of data. To track progress and establish milestones, the group set a goal of building another datamart every 20 days. “PowerDesigner really made that possible,” says Kirk Reuter, data warehouse specialist, Cherry Creek School District. “We would conform the dimensions for the data and keep them consistent, so each time you started a new datamart, it was already partially built—and it took less and less time to build each successive datamart. PowerDesigner further helped by making it easy to track the lineage of columns from the imported source data into the new database and make sure everything transferred correctly.”
Mantis’ Vickery relates a story around one of these 20-day sprints, “We always worked in the same room during these projects, so during one of these XP pair programming sessions, two of us were focused on PowerDesigner modeling, and the other two were examining the semantic layer. We noticed one of the attributes was missing in the semantic layer, and alerted the other two team members who then used the PowerDesigner mapping editor to propagate the missing attribute from the OLTP database to the ODS and finally the data warehouse. Proper data lineage was preserved!”
The district has already loaded into the data warehouse the data residing in PowerSchool, a web-based transactional Student Information System (SIS) that provides up-to-date information to school administrators, teachers, parents and students about student attendance and grade history, as well as notes and comments from teachers. “Should there be an upgrade to PowerSchool, which is the source system for most of our data, we can use PowerDesigner to reverse engineer that database, and in just a few minutes we can see, for example, that the data type of a column has been changed and that the change needs to flow through to our data warehouse,” says Jim Egan, Mantis Data Architect and ETL Developer. “That’s a piece of cake with PowerDesigner.”
Visual Representations Add Value
“We rely heavily on the visual schemas PowerDesigner generates,” says Wes Pembrook, data warehouse project manager, Cherry Creek School District. “We would post a number of physical diagrams and star schema on the walls in our office and put the dimensions of the datamarts in the same places. It made it easy to see where data was coming from and going, and helped everyone visualize the data architecture.”
“Mantis has been extremely beneficial with their experience,” says Reuter, “and PowerDesigner has really helped us stay on schedule with those 20-day sprints. For instance, we’d project models of all three databases – the PowerSchool OLTP database, the operational data store (ODS), and the data warehouse – and we’d develop right there on the screen, using PowerDesigner’s mapping editor. It’s a very agile tool.”
Adds Egan, “At times, we would have to change data types and we could simply pull up the list of columns, sort them by data type, and immediately alter the column datatypes – across multiple tables – right there on one screen. There were so many ways we used this to stay within that 20-day window.”
The group also used the reverse engineering feature in PowerDesigner to ensure that nothing was added to the database that wasn’t in the data model. “That alone saves the tedious work of going to original sources to do that verification,” says Egan. “Many of the databases the district acquired or built from scratch do not have an accessible datamodel. We can use the reverse engineering capabilities in PowerDesigner to model them and then map the data from the transactional systems into our warehouse, where we are conforming all our dimensions.”
Looking ahead, the group anticipates that the district’s data architect team will use PowerDesigner to reverse engineer all the data residing in other databases so it can be incorporated into the central repository and documented. “That will give us a tremendous benefit in our state reporting,” says Pembrook, “because it has been hard to establish where the data in a report is coming from. That will be greatly simplified with a central repository that allows us to trace data to its source.”
Administrators Eager for New Reports
“There are many systems throughout the district that contribute data to reports,” says Pembrook, “and the district has had a difficult time identifying what the system of record is, whether it’s transportation, library, special education and so on. It’s been decided that the data warehouse we’ve built with PowerDesigner will be the primary system of record for student information used in district reporting.”
The district expects to continue adding about 30GB of new data each year. Using the data warehouse, IT staff and administrators will be able to quickly generate a wide range of reports that could be obtained previously only through tedious manual processes or not at all. “For example, we will be able to quickly generate reports to the state on the percentage of students in attendance, which the state uses to determine funding levels for our schools,” says Pembrook. “Beyond that, we will be able to create trend reports and do correlations on discipline and grades, or attendance and grades, or a student’s success correlated with the teachers he or she has had. We’ll be able to slice and dice our data in ways that school districts have never been able to do, and that will help us do a better job in many ways.”