This post describes the project approach and subsequent activities that lead to the delivery of a data warehouse representing detailed and aggregated data from colleges. This post follows the outcome of the Datawarehouse workshop earlier with the client evaluating the paper on data warehousing.
Design a data warehouse
This project plan will match the results for the Operational Data Warehouse, mentioned earlier in the document: Design a Data warehouse for colleges in order to improve governance, planning and management of the sector and enhance the flexible delivery of programmes by those colleges.
Terminology that is used
In the document we will use the following terminology:
- BMS: A Business Management System for the education sector that is Business Analytics software, processes and standard code tables supporting the operations and management of a college. The BMS that is in use, adheres to the minimum requirements on functionality and data set, as advised by the client.
- College Database: A set of operational data that is available at college level in (Oracle) database maintained with the BMS programs
- ODS: Operational Data Store, which is a database containing all college data representing both individual transactions and aggregated data.
- Data warehouse: Collection of aggregated data of colleges represented in specific formats and/or data marts for different reporting or analysis purposes.
Aggregate records for sector information
The Datawarehouse Component of the project will focus on aggregating individual college records into a format that can be used for both unit record detail analysis, as well as aggregated sector information . The Data warehouse will be built on the prescribed data set by the client that is implemented in the current BMS, that is in use at the former pilot colleges and a range of other colleges.
Framework for KPIs
The project plan is based upon the paper “College Information Architecture for Datawarehouse”, the tender proposal for BMS2 and the review workshop held with the client earlier this year. In addition the client has provided a preliminary framework for key performance indicators that can be used as a starting point for information needs analysis. Finally, the colleges have to some extent reviewed college specific indicators for performance measurement that will also serve as an input to Information needs analysis.
Status of open questions
Review of the Colleges Information Architecture for Data warehouse and status of open questions after the workshop with the client
There were assumptions and questions posed towards the inclusion of data that were not verified coming from colleges not using a BMS. As decided earlier with the client, the Datawarehouse project will prescribe the format of data for non BMS using colleges. It will not define ETL routines for conversion of college data that is not conforming this data definition. This means that:
- The uploading of college data from individual colleges into the ODS will be standardized upon the BMS available data without additional transformation
- Uploading of data from non BMS using colleges will only happen if colleges can prove that the data sets for uploading are in the same right format and that the quality of data and coding standards are identical to BMS using colleges.
Other questions that were answered at the March workshop are:
- There is a KPI set (preliminary framework) under development that will be used for the client that under the project scope will be enhanced and translated into a set of reports, graphs and dashboards.
- The users (access rights) of the data warehouse is still to be determined but will include client organization, Provincial Departments and, as agreed by the client, also teachers, researchers and analysts. There will be 2 types of users: (a) Standard (reports, graphs, gauge) users for aggregated information (presented by datamarts and KPI’s) and (b) users that will have access to underlying unit record information and who can do enhanced statistical analysis. The expected amount of users will be between 30 – 40 users that need access to (on-line/prepared) information.
- There is a preference for cost effective Business Intelligence tooling. For the ODS the technical solution will be Oracle RDBMS based, allowing uploading from individual colleges (when using BMS this will be Oracle based as well), while the BI front-end and pre-built datamarts might be preferred in Power BI and other Sharepoint services. Price inquiries and building cost will be evaluated for these preferred solutions and evaluated.