The first round of ERA was completed in 2010, followed by subsequent rounds in 2012 and 2015. The latest round of ERA commenced in March 2018.
Two key points to note:
- Field of Research (FoR): The submission looks at eligible publications and incomes for each researcher across different disciplines.
- The submission is an XML file with specific title, format and business rules for all data used.
Providing data support
Since the first round of ERA in 2010, one of our higher education clients created a dedicated team called RIO to prepare and optimise their ERA submission. After the last round in 2015, they decided to automate some of the ERA submission process (ETL, data format, XML generation, ERA Business Rules test) to help the RIO team focus on optimisation to get the best overall ranking for every FoR.
The project was been divided across 3 different teams:
- RIO team: Their task is to provide all the selection criteria and business rules that must be applied to the data. They are also responsible of any data quality issues coming from the raw data.
- ETL team: Their task is to create and scheduled all the necessary ETL process from the data in the presentation layer into the required format by ERA.
- XML team: Their task is to generate the XML file for the submission. They are also responsible to report to the ETL team any XML errors.
As part of the ETL team, our role was to transform the relevant data for ERA from our client’s data vault model in the presentation layer and load into the ERA required model in a datamart. Then to build and run ETL SSIS packages to automate the entire process.
Another objective was to check that the data in the ERA datamart was reconciling with the data source, and also respecting all of the business rules defined by ERA.
How we helped:
Our team’s contribution to the project included:
- Writing SQL queries according to RIO mapping specification to load our client’s data into the ERA datamodel (23 tables).
- Building SSIS packages to execute and automate SQL queries.
- Responding to any changes made by RIO in the mapping.
- Writing SQL queries to validate the ERA business rules (over 60 rules).
- Reconciling the data and analysing any gaps between the presentation layer and the ERA layer.
- Fixing any issues coming from the XML team.