Overview
Background to Original Proposal
Worktribe, the University’s chosen research administration system, has limited reporting capability. A long-running project, RES056, is augmenting this limited capability with additional reports written against an extract of the data from the Worktribe system. That project will allow expert users in Research Management to write reports using Crystal Reports, and distribute those reports as PDF.
Worktribe is developing external reporting in Crystal reports, however, this comes with a number of constraints around updating reports, running and distribution. Moving to a BI Suite Universe would allow for easier creation, distribution and maintenance of report.
This project proposes building an additional reporting mechanism for Worktribe data, using the University’s preferred reporting tool, WebBI. This will allow more users to write and access reports, using a web browser rather than Crystal Reports.
The BI/MI Programme made a bid for a small scale enhancements budget in order to deliver some enhancement that would benefit GaSP in the first instance or the wider BI community in the second instance. After reviewing the priorities and other programmes of work, it was decided that developing a Worktribe (RMAS) reporting solution should be attempted. The intention at the proposal stage was to build a BI Universe on top of the existing database for Crystal Reports.
The project as proposed at the Annual planning stage was estimated at 60 days effort, to be funded by GASP.
Background to New Proposal
During the planning stage of STU252, input from the emerging BI Architecture project suggested taking time to reconsider the original proposal. After reviewing, it was agreed to change the approach to become more in line with the emerging business intelligence (BI) architecture for the University. This recommends the use of "Star Schemas". These comprise a central table of “facts” – typically transactions – linked to a number of “dimensions” – tables that allow the facts to be easily grouped and filtered into individual reports. Several Options were considered and the recommended option was chosen:
New Option
The advantages of a star schema are that they simplify the task of report writing, allowing many reports to be written by people who know little about databases. They also make reports run quickly, as the reports are all based about the central table.
This new proposal for STU252 requires the creation of new database tables to store data in the star schema format, and creation of appropriate scripts to transform the data into that format.
Design work will be required to determine the best technical architecture – if possible, the schema should share the existing instance, as this will reduce cost but must not interfere with the efficient running of the existing Crystal Reports solution.
Design work would also be required to determine the best approach for populating the new database tables and to finalise the design of the star schemas.
IS Applications Division will fund 50 days for this additional work.
Scope ( at end of Planning in May 2016)
The scope of this project is limited to:
- Building a Star Schema using the existing tables and views created by Research Services
- The creation of folders and permission groups in BI Suite
- The creation of a BI Suite Universe based on the RMAS staging database
- The creation of sufficient reports to fully test that the Universe can satisfy the requirements
- The creation of connections in order to access information in the various databases
- If time and budget permit, the creation of reports to satisfy the requirements fathered by research services.
- Actual spend data will be limited to that available from the Worktribe nightly extract. This is presented as aggregate level actual spend per project, by budget line.
- The project will exclude processing transaction level (i.e. per line item) actual spend data, that is currently only available via the eFinancials system.
The project will not:
- Develop the SSIS packages that generate the tables and views in RMAS staging
- Load test the BI Suite Reporting Universe (low risk)
- Provide a solution allowing reporting on historical data
Changes to scope agreed since end of planning ( as at 14th March 2017) :
1. Make changes to the SSIS packages to match the schema changes in Worktribe v2.2
2. Complete the SSIS package healthcheck tasks to bring SSIS into line with normal build standards
3. Build new IS Apps DEV server to replace use of ERI server 'Research1' to run SQL server 2014 and SSIS
The changes in scope have been agreed by project team and project sponsor and budget was increased to 160d on 10-March 2017 to complete the identified project work
Objectives
| No | Objective | Priority |
|---|---|---|
|
1 |
Build Star Schema using the existing tables and views created by Research Services | Must |
|
2 |
Creation of folders and permission groups in BI Suite (Service Management) | Must |
| 3 | Creation of connections to allow RMAS staging data to be accessed by BI Suite (this should be completed early on to ensure there are no issues connecting to SQL Server 2012) | Must |
| 4 | Creation of a BI Suite Universe with the minimal set of information required to meet the reporting requirements | Must |
| 5 | Extention of the Universe to include database fields not included in the "must have" reports | Could |
| 6 | Creation of the reports in BI Suite based on the Universes | Could |
| 7 | Seeding of the BI Suites permission group with the initial user base (Service Management) | Must |
| 8 | Exploring the use of Birst to automatically generate reports and distribute them (Service Management) | Could |
Deliverables
| No | Deliverable | Team |
|---|---|---|
| 1 | SSIS package required to create the Star Schema and to pull existing tables and views into Star Schema configuration | Development services |
| 3 | Requirement specification of the reporting Universe needs. | Craig Middlemass / Kelvin Clark |
| 4 | Folders and permission groups in BI Suite | Service Management / James Toon |
| 5 | BI Suite Universe with data and connection to RMAS staging | Development services |
| 6 | Desirable - Reports that match the specifications created by Sue Coleman | Craig Middlemass / Kelvin Clark |
| 7 | User groups updated with initial user base for Worktribe | James Toon / Service Management |
| 8 | Desirable - A schedule for generating reports for distribution.Service Management | Service Management / Craig Middlemass / Kelvin Clark |
Benefits
The primary benefit of this project is delivering a reporting capability to Worktribe users. Without a BI Suite Universe or a set of Crystal reports, users will have limited ability to report on Research Management data. For an institution that expends around a third of its income on research activity it is essential that this is in place.
Success Criteria
Worktribe users having access to reporting on Research Management data from Worktribe within BI suite.