Dimensional models for K-12 education

Hello everyone,
As a DBA I'm just starting out in the data warehousing field and I'm having difficulty locating dimensional model examples for education. The examples that I have come across pertain to sales, manufacturing, billing, etc and I'm looking to find some examples regarding grades and attendance. Does anyone on the forum have experience with modeling dimensions for education or have you found any good resources?

Thanks,
Chad.

I haven't seen any openly available dimensional model examples for K-12 out there, but I have seen several for higher education. For example, MIT has made its data warehouse data models available here.

There are lots of companies in the business of selling K-12 data warehouses, many of whom profess to using a dimensional model. Try googling some combination of these words to get a sense for who is in this market: "Education K-12 data model star schema framework". Several of the consulting arms of the major tool vendors also have offerings in this space (Cognos and Oracle are two I've run across).

While looking at someone else's data model can help you understand where you need to head, in my experience, it is only a starting point at best. First you need to figure out what their model means. Then you need to map your source systems to their model, and figure out what business rules you need to apply to transform your data into what they are expecting. It can be as much work to try and implement one of these packaged data warehouses as it is to build your own. See Margy's article on using industry standard data models here.

I have worked on education BI system for state government for just over a year. The system is supposed to use dimensional model and OLAP cube to analyse enrolments, attendance and absence for public schools.

The up-front challenge is to remodel a legacy data warehouse system that contains snapshot based historical dimension and fact data. The only relevant reading resource for me was the Education chapter 12 in Kimball's famous book 'The Complete Guide to Dimensional Modeling'. In the chapter, Kimball explained how the accumulating snapshot can be used to track course application life cycle and attendance events in an university.

However when dealing with students from a thousand state schools, the demographic factor and regional structure are the main source of dimensional contexts for predominantly factless facts such as student and day counts for enrolments, attendance and absence, which form the core business of performance monitoring and analysis.

Like most OLTP systems, the source data is likely mixed in a single table, eg. student enrolment, that contains different versions of student details in different historical snapshots, or even in different schools at the same time. So you need to rebuild dimensional and fact history based on SCD 2 process.

Another challenge is the size of student dimension, a true monster dimension with many million records. So using Kimball's mini-dimension concept is extremely effective to minimise the monster dimension size and yet satisfy most analytic requirements at demographic level and school/regional levels.

The project could look daunting initially, but once you sorts out dimension model and ETL your data using Kimball's methodology, the OLAP cube can be really fun opening up endless analysis possibilities which the old technology would never been able to achieve.