The development of the BMS has led to an increasing amount of colleges working with a standardized approach for data processing, which is centered around primary and secondary processes. The BMS system has gone live at 5 colleges, 4 others have received training and will go live quickly, 1 college has recently entered a contract to obtain the system, and another 4 to 6 colleges are in the pipeline for going live.
Soon, more than 40% of all colleges in the country will be using the system. The BMS allows the colleges to do electronic reporting as well as delivering a data set that will be used in the data warehouse. This data set that will be uploaded in the data warehouse is the prescribed format of data for all colleges to deliver data to the the client.
The scope of the data warehouse project is defined around the following deliverables:
- Delivery of the data definition for the ODS and the data warehouse that is responsive towards the KPI and reporting requirements of the the client (provincial and central), including designed formulas to calculate values from the data for the required KPIs (e.g. throughput per program)
- Accepted and feasible technical architecture for RDBMS, ODS and DWH
- Definition of the format of the Operational Data store (ODS) that prescribes for every college the data set that they should keep/provide. (The BMS users will automatically comply with that, while non BMS using colleges should themselves prepare these data sets, or start using the BMS)
- Delivery of the software programs (scripts) that allow uploading data from BMS using college databases on a frequent basis into the ODS (covering student unit data, lecturer unit data, employee information, resource usage (space), finance (assets,funding and budget))
- Delivery of the defined format of the data warehouse, data marts and selection of BI-tools
- Technical delivery of the DWH and UI, including scripts for uploading from the ODS into data warehouse
- Test and Acceptance of the Data warehouse
- Define processes for maintaining the DWH (frequency of uploading data, data quality evaluation, reporting, DB maintenance)
- Design a training- and usage plan.
In order to deliver a fully-functioning Data warehouse, for all described aspects there are a variety of activities that need to be executed. For some of the activities third parties (e.g. Oracle specialists, Sharepoint/PerformancePoint developers, BMS supplier) will be consulted or involved for deliverables. These Project activities per deliverable will constitute the basis for the terms of reference (ToR) that will be used for management of these third parties. The following paragraphs describe per deliverables the additional activities that need to be executed.
1. Data definition
Delivery of the data definition for the ODS and the data warehouse that is responsive towards the KPI and reporting requirements of the the client (provincial and central), including designed formulas to calculate values from the data for the required KPIs (e.g. throughput per program)
- Consultation of the client (and authors of the draft Indicator framework) for detailed explanation on the categories mentioned in the Draft FET Indicator framework (Current data: throughput rates, program mix/headcount, finance, teaching and learning. Pending Data: management (resource usage, curriculum delivery, budget, man. development), program innovation, assessment, HR, accessibility, employability.)
- College consultation on College management KPI’s for cross sector comparison (partly done during the BMS1 Management Training of the 3 pilot FET colleges and to be done at WC colleges during the management training on BMS)
- Preparation of a detailed set of reports with examples and definitions
- Acceptance of Indicator (KPI) list by stakeholders
- Review of Indicator (KPI) list with BMS provider and set-up of calculations and formulas based upon BMS
- Final list of data elements needed from BMS to be used in the ODS as approved by the client
2. Technical architecture
Accepted and feasible technical architecture for RDBMS, ODS and DWH
- Review/Assessment of technical feasibility using uploading mechanisms in Oracle RDBMS of colleges into ODS
- Review of link of “PerformancePoint” as “Sharepoint Service” reading from Oracle ODS and reporting tool
- Development of ToR and subsequent acceptance and technical commitment of potential ODS (and Data warehouse) builder(s) on technical architecture and proposed tooling
Note: It may be necessary to align the scope of the project in accordance with the available budget (or the client co-financing) taking into consideration other commitments on the budget (e.g. roll out to 4 new colleges)
3. Format of the ODS
Definition of the format of the Operational Data Store (ODS) that prescribes for every college the data-set that they should keep/provide. (The BMS users will automatically comply with that, while non BMS using colleges should themselves prepare these data sets, or start using the BMS)
- Delivery of a list of processes and programs usage and instructions to colleges to get and keep operational data in BMS system. On an annual basis the the client might extent their information need. To meet the information need, it might be that colleges have to use new modules in the BMS system to start capturing additional operational data. (e.g. resource usage of classroom-space).
- Define queries of BMS data by using the final list of data elements needed (under deliverable 1)
- Define format of the ODS storage (facts, time stamped, dimensional or relational)
- Define processes and frequency for BMS upload into ODS (e.g. Finance data only after period closing, student result data per semester end etc.)
- Define processes and upload frequency for compliant data from non BMS based data.
- Technical design of ODS by provider (to be selected)
4. Delivery of software
Delivery of the software programs (scripts) uploading data from BMS using colleges on a frequent basis into the ODS (covering student unit data, lecturer unit data, employee information, resource usage (space), finance (assets,funding and budget))
- Using the technical design of the ODS to define several queries into BMS
- Creation of scripts and daemons that allow upload of required data from college RDBMS into ODS.
- Calculation logic (if needed) for intermediate storage of KPI values.
- Delivery of a working ODS by provider (to be selected)
5. Format and data model for the data warehouse
Delivery of the defined format of the data warehouse, data marts and selection of BI-tooling
- Functional Design of the Data warehouse and/or data marts delivering user presentation of the proposed Framework for Indicators
- Review of the BI tools for definition of norms and presentation for users
- Indication of work for technical delivery of DWH and UI in Sharepoint/Performance Point (by external expert)
6. ETL Scripts
Technical delivery of the DWH and UI, including scripts for uploading from the ODS into data warehouse by provider (to be selected)
- Design of the DWH and design of data marts
- Design of Business Logic for KPI calculation
- UI design with PerformancePoint Common Dashboard Framework
- Sharepoint collaboration model designed (authorization, navigation)
- Prototype evaluation
- Scripts definition for upload from ODS into DWH
- Processes design for syncing frequencies with underlying data from ODS and BMS RDBMS.
7. Test and Acceptance of the Data warehouse
- Presentation to stakeholders
- First round of uploading
- Change processes documented
- Installation in the client environment
- Acceptance process
8. Define processes for maintaining the DWH
- Maintenance scheme for functional owner
- Back-up procedures documented
- Reload procedures documented
- Documentation technical structure of ODS and DWH ready
- Functional maintenance processes defined (data integrity, quality)
- Review candidates for maintenance (e.g. SITA)
9. Design a training- and usageplan.
- General training for Business Intelligence
- Training design for the Hosting and Housing owners, focused on DB-maintenance, backup, functional maintenance
- Training design for Sharepoint users (using pre-formatted graphs, gauges and reports)
- Training for incidental queries on Performance Point level
- Training for Performance Point enhancements
- Training for analysis on ODS level
- Creation and Distribution of DWH results
See also: Project plan for data warehouse