openSIS Logo

Open Source Education Data Warehouse for K-12

NCLB, accountability, highly qualified teachers, student achievement…….. You will recognize these as common buzzwords around the No Child Left Behind legislation that was signed into law by President Bush in 2001. Welcomed by many and feared by many, this law accelerated accountability trends and reform efforts that were already underway in many areas of the country.

At the bottom of all of this, one thing is certain: You have to establish baselines for students, track their efforts, measure their progress and analyze how successful your efforts are in increasing student achievement. And what is at the bottom of all of that: Simply put, data. Everything you must do to comply with NCLB is centered on the collection and reporting of data.

How do you that? An education data warehouse. openIntel©, the first free open source data warehouse is now available for districts.

Lowest Total Cost of Ownership

Unlike costly commercial alternatives, openIntel is open and free and has no upfront licensing costs. The base solution is entirely free for you to use. Because it is open source, there are no other software licensing fees either. Year in and year out, openIntel is the most cost effective solution in the education data warehouse market.

Easy Management of Information

Because openIntel is centralized and web based, information is available when you need it, anywhere, anytime. Users only need a modern day web browser to access all of the functionality of the openIntel solution. The four R’s of information: The Right information for the Right person in the Right place and at the Right time. Simply put: Your needs, your way.

The Tools You Use

The tools your school or district uses for your every day reporting needs can be used with openIntel. Because openIntel is open, you can use any database, proprietary or open source. You can use any standard reporting software. An open solution that fits your tools and your skills. Don’t buy new software. Use what you have or use the free software we have used.

Decisions from Data

Access to data at your fingertips. All of the pertinent data on your student, class, school or district is available to you. No guessing. No intuition. Make decisions based on data. Pinpoint students who have issues. Identify teachers who need professional development. Discover trends and best practices to drive student achievement. openIntel gives you the blueprint to know. Increase Involvement Through Information

Studies show that increased involvement occurs when information is shared and everyone is using the same information source. Do away with you silos of data where data is duplicated or different. One central database for all reporting needs ensures that everyone has the same information. openIntel enables schools and districts to increase involvement by using data to drive achievement.

Return Instructional Time to the Classroom

openIntel automates many daily tasks associated with creating informational reports. No need to call the IT department to ask for a report. Just open a standard web browser and get your report from openIntel. In just a few clicks, teachers and administrators can view reports specific to their needs. Five minutes here, fifteen minutes there all begin to add up to real time savings that can be redirected to instruction. Time savings are a hidden benefit of openCentre that can truly benefit student achievement.

openIntel – Overview

No Child Left Behind, state reporting, board reports, test reports are all part of the growing accountability trend that K-12 education is facing. All over the country, educators are being forced to collect data and assemble it into informative reports for education stakeholders.

To help meet this demand, a variety of vendors have developed education data warehouse solutions that allow districts to integrate data from various operational systems into a central reporting repository. The problem? These solutions cost a lot of money and the majority of the nation’s school districts cannot afford a quality solution.

Vendors such as eScholar, IBM, Tetradata, Versafit Third Day Solutions, Cognos have all developed offerings specifically for K-12 education. But their solutions are built around proprietary software with perpetual licensing costs, substantial hardware requirements and an ongoing cycle of upgrades that districts become slave to by choosing these solutions based on proprietary software. Their solutions are built on software products created by companies whose business revolves around selling more licenses to school districts.

openIntel delivers the same base functionality that all of these products do and its software components are free! Not only free, but mature and robust and in use all over the world. These free software products also have an amazing support structure from companies who have built business models around supporting free software and providing advanced customization services for clients in need of those services.

Read our other pages and get an overview of our solution. It was designed by one of the original architects of education data warehouse solutions. Years after leaving coporate America, our architect generously agreed to our proposal to work with OS4ED to design a solution, openIntel, to be implemented in school districts for a fraction of the cost of commerial alternatives. We call it give back to a community that needs license free open solutions.

openIntel – Architecture

openIntel – A name describing an education data warehouse solution delivering education intelligence from mature open source, and free, software products that are widely available, have multiple support mechanisms and are being used in mission critical settings worldwide. The base components of the solution include:

  • MySQL Version 5.1

  • Business Intelligence Reporting Tool (BIRT)Runtime

  • Engine Version 2.1.1

  • BIRT Report Designer – Included in the free IBM Eclipse SW Development Kit Version 3.2

  • Apache Tomcat Application Server Version 5.5

  • Apache Web Server Version 2.2

  • Client Access – Any standard W3C compliant web browser

The components of the solution are entirely server side components that allow you to design a web interface specific to your needs. All districts have typically have an IT resource that does web development. The code necessary to call the BIRT reports is included in the solution documentation and consists of only two to three lines that can be reused within any standard HTML page.


This open solution allows school districts to completely customize this to their needs without requiring any specialized technical skills. The underlying open source software comes with complete installation and configuration documentation, help documentation and has a many organizations providing support. Free support can be found in the multitudes of discussion forums on the web where developers have formed communities of free support.

openIntel – Dashboard Reports

openIntel is a solution that allows school districts to present informative reports to its end users in a variety of ways. One of the most popular presentation methods is a dashboard showing key performace indicators (KPI). KPI’s can be determined for any quantitiative measure, such as attendance counts, test scores, discipline incidents and demographic counts. The dashboard presentation is a way to gather information at a glance. And from any dashboard report, you can design drill paths leading to more detailed information.

KPI Dashboard Report

ITBS Test Dashboard Report

These example reports designed using the BIRT Report Designer. Each dashboard consists of four individual reports. The reports were designed using the BIRT graphical reporting tool. The only technical knowledge required is constructing SQL queries, which can be learned by any average technical personnel. Districts can use the example queries and reports included in openIntel to learn. openIntel documentation includes sample queries as well.

openIntel – Mixed Format Reports

openIntel is a solution that allows school districts to present informative reports in multiple formats. Reports can contain graphs, charts, images, dynamic fields and more. The BIRT Report Designer contains a graphing engine that has a wide variety of capabilities to satisfy any reporting needs that a school district may have. Pie charts, bar charts, scater plots, line charts and more are all included in a simple intuitive wizard driven interface that makes it very easy to design reports with compelling graphs.

Advanced capabilities include value indicators for chart items, mouse over capabilities chart items, drill to other reports or external links from a chart, hyper links from specific data values that can be passed as parameters to a filtered report and more. openIntel gives school districts the same powerful reporting capabilities as costly commercialized tools. Quality open source tools enable the robust and powerful openIntel solution enabling all school districts to have a quality data warehouse. With openIntel your district can transform into a data driven decision making enterprise

openIntel – Data Model

The openIntel solution is based on the data model design work of a renowned education expert in data warehouse. This person was one of the first individuals to design data warehouse models specifically for K-12 education. His work remains the foundation for many data warehouses in use in K-12 today.

Years after our friend left corporate America, he graciously agreed to our proposal of creating a data model that could be given away to all districts. This newest design is the foundation of our first release of openIntel.

openIntel – ETL

The openIntel solution seeks to provide an easy way for districts to deploy a data warehouse without having to pay high prices for commercial solutions. To this end, we have developed Extract Transform and Load (ETL) plans using the Pentaho Data Integration tool, formerly known as Kettle. This tool allows for graphical programming of ETL routines. ETL refers to the process of extracting data from source systems, transforming it to fit into the data warehouse model and then loading the data into the data warehouse. This process is the hardest part of a data warehouse and the bulk of the work is done by district resources who know where the data is located.

In our first release, we have developed ETL plans that take data from a flat file and load it into the data warehouse. The flat file is comprised of your district’s data and constructed according to the specifications we have defined for you. You can use the ETL tool to source data directly from your source systems. In our next release, we will integrate the openIntel data warehouse directly to Centre, the open source student information system. The ETL plans will then source data directly from Centre eliminating the need for a district to build their own flat files. An example plan is shown in the image below.

If you think this is sounding hard, relax. Because we have done this before, we have given you all of the tools and documentation to get you going. Included in our download section, is a complete set of data mapping spreadhsheets for the openIntel data warehouse. These sheets contain instructions telling you exactly how to map your source data to the data warehouse model and hints and tips for making it easier. Examples of data rules are also included so that you understand any data transformations that may have to occur. An example of a data transformation would be: 0 in my SIS system = Male in the data warehouse model.

One last thing: We haven’t built any data validation routines for you yet. Data validation means checking your source data to validate that data going into a numeric field in the data warehouse is in fact numeric. Right now, the ETL plans will stop and roll back if data does not conform to the data warehouse model. There is is a reason for that and it is a very good reason. The people using your reports will be experts in the data and will recognize bad data. When that happens, they will quit using your data and all of your efforts will be wasted. So, we only let you load good data! It is not as hard as you think.