Data Warehouse project update 5

• PI Report for Senior Managers (PRISM) (formerly known as PI report for divisions)
  • still facing challenges of no agreed definition of what a PI is across the University and none of the central administrative systems, other than X5, using an attribute or classification for 'PI'
  • working to develop a first iteration of a list of PIs from information in Oracle, X5 and PeopleXD but there are anomalies that require investigation
  • however, a very early version of this report has been developed for initial feedback from stakeholders
• Professional Services Dashboard
  • a report has been developed and reviewed by relevant committees and is widely available but not yet housed in the Data Warehouse
• Data Warehouse processes: Engagement, Release and Route-to-live
  • engagement: introducing triage and streamlining routes into the team
  • release cycle: incorporating a structured calendar, submission meetings, release channel and status update channel
  • route to life: including quality gates around different environments, version control, automation where possible and obfuscation so team members do not handle real sensitive data

Work on other reports and dashboards will be picked up again in future sprints. 

Detailed sprint report

• Recruitment (formerly Associate Professor) monitoring report:
  • Systems Testing (ST) has begun, which required creation of new PowerBI workspaces for the Test team
  • User Acceptance Testing (UAT) will follow once ST is complete, but this is expected to require minimal extra subject matter expert (SME) time
• Internal Mobility report:
  • People team continue to expand the functionality and scope of this report, which they will test and publish themselves with Data Warehouse project support as needed
• Health and Safety dashboard:
  • the team continues to work on sourcing the new datasets needed for this report, which will require the creation of new Application Programming Interfaces (APIs) to load data that support the Health and Safety Key Performance Indicators (KPIs)
• PI report for divisions:
  • significant progress has been made to match Principal Investigator (PI) and projects data across X5, PeopleXD and Oracle, but challenges remain
  • we will seek input from division SMEs to help us resolve data anomalies once we complete our review with assistance from Finance and People SMEs
• Security model:
  • IT Services recommends we move from using Microsoft 365 groups to Security Groups for access control in Power BI
  • the Data & Report Sharing Framework has been updated to reflect this new approach and will be reviewed with key stakeholders in sprint 7

Following reduced capacity due to Easter and illness, the following Goals have been rolled over to sprint 7:

• PACS modelling support 
• Research Excellence Report
• Student data domain

Detailed sprint report

• Internal Mobility report: documented processes and progress made towards testing 
• Associate Professor (AP) recruitment monitoring report:
  • renamed to recruitment report because it will be used more widely than just for Associate Professor recruitment
  • further discussion on how to progress this to be incorporated into sprint 6
• Project Overheads report (formerly known as the PI Report for Divisions): progress has been made in mapping the data required from People and Finance source systems, but the team are now focused on resolving the issue of mapping PI data across the two systems
• Expenses report: paused as the project team focusses on other work for the remaining sprints
• Development of processes and standards: additional documentation has been produced for review by divisional colleagues
• Data report sharing framework: collaborating with Information Compliance and Information Security on underlying processes and infrastructure required for data guardians to be able to approve access to reports and datasets in the future
• UAS central register of large ongoing projects: initial report developed

Detailed sprint report 

• Internal Mobility report: analysis of data completed and data tables created in bronze layer. This dashboard is owned by the People department which will manage access, agree the look and feel and manage the communications and engagement with University colleagues when it is ready to share
• Associate Professor (AP) recruitment monitoring report:
  • initial population of Data Dictionary: the first stage in developing a central resource which defines what data means, for example explaining what an employee number is, what is meant by a specific field of data 
  • created logic objects in silver layer: setting up predefined rules to allow users to access different breakdowns or views of data
• Project Overheads report (formerly known as the PI Report for Divisions): gained intelligence about how the data is being modelled so the project can build the appropriate elements in support of divisional reports
• Expenses report: have set up secure access to the database and attempted to get feeds of data but this has proved problematic due to the breadth of the data so now looking for a technical solution to fix that   
• Health & Safety dashboard: have elicited information from source systems so now able to analyse how best to access the data
• Development of processes and standards: continued developing and documenting the processes and standards for reporting to adhere to (test processes, naming conventions, etc.) BI team in PACs to lead and work with DRAM and Test teams
• Data report sharing framework: continued engagement with Information Compliance and Information Security to build a risk assessment framework and classification capability.  More detailed conversations with individual data teams eg People and Finance 

Detailed sprint report ​

• Data report sharing framework using Associate Professor (AP) recruitment monitoring report:
  • Created tables in bronze layer: inputting data from source into the Data Warehouse
  • Created base tables in silver layer: beginning to format that data to improve its appearance for users
  • Created schema in silver layer: grouping tables so they make more sense to users and are easier to find
  • Produced first iteration of report (using a sample dataset of People data)
• PI report technical discovery using the Medical Sciences division PI reporting tool from which we identified what data is being used and the sources from which it is derived
• Development of processes and standards initially for HR and Finance reporting through creation of a template for end-to-end report or dashboard development, which should save time and effort for report-writers and act as a checklist to ensure all relevant information is included

Detailed sprint report

• Set up the medallion layers (see explanation below) in the development environment (where software applications and services are created)
• Set up medallion layers in the test environment (where the performance, functionality and reliability of software applications or services are tested)
• Created Power BI workspaces in the development and test environments
• Created tables of data in the bronze layer
• Created curated data in the silver layer​
• Produced the first iteration of standards and guidelines documentation for the Data Warehouse medallion architecture and for the Power BI workspace approach

Detailed sprint report ​

• Agreed ways of working through setting up sprint management processes
• Agreed the medallion architecture approach for the Data Warehouse, whereby the University’s centrally managed administrative data will be organised in three layers as outlined below.  For more detail see the Medallion Architecture Overview slide deck. 
  • Bronze: ingesting data from source systems in its raw format for use by data developers
  • Silver: inspects, cleanses and curates data from the bronze layer, for use by data developers and BI developers
  • Gold: takes silver data and further cleanses it, delivering analytics-ready data for reporting and creating dashboards by BI developers and report writers

Detailed sprint report