Site in transition - https://wiki.rice.edu/confluence/display/RAD/RADAR+Home
The Interdisciplinary Research Support Office (IRSO) is based in the Office of Research at Rice. One of the core offerings of the department is post award support to institutes and centers. In recent years, IRSO has also been involved in providing departmental support for the management of certain individual faculty portfolios.
IRSO holds three core values; Quality, Innovation, and Integrity.
https://research.rice.edu/irso/overview
With specific regard to Innovation, IRSO encourages curiosity, analytical thinking, and adaptability, in alignment with its mission goal to provide outstanding service to faculty, staff, and other key stakeholders. Through a commitment to innovation, IRSO hopes to positively impact research at Rice by reducing faculty burden and facilitating researchers, staff, and students to fulfill their roles.
Prior to iO, IRSO always produced a monthly report for all supported faculty, institutes, and centers to provide a common basis from which to interact with stakeholders and manage awards. The monthly preparation and circulation of these reports was consistent; it encouraged timely communication with faculty and their administrators about their awards, ensured accountability, and facilitated good management. With the advent of iO (SPFF) and ROBI, the process of building each of these reports has been taking significant time (hours in some cases), with data downloads out of iO, updating pivot tables, ROBI exports, and the use of Adobe Acrobat to build and edit the report PDFs produced.
AUTOMATION
To generate a superior and reliable reporting tool that automates preparation and analysis of research administration data into monthly reports.
IMPROVE STAFF EFFICIENCY
Reducing the time required for an administrator to conduct a meaningful analysis of award activity and identifying needed management actions will bring a significant staff efficiency benefit.
REDUCE FACULTY BURDEN
Clear and pertinent analyses of award/portfolio data supported by informed advisories will facilitate succinct and productive faculty/staff award management interactions.
In addition to IRSO supported entities, the reporting product could be tailored for new institutes and centers into a standardized report product to them from the Office of Research. Potentially, if successful (and considered useful on a broader level) it could be adapted to iO and used campus-wide.
Access to the Tableau tool is currently controlled via the creator of the tool, Andrew Medrano (aam11@rice.edu) of IRSO. All award participants relative to an award being reported upon are generally given access to view the corresponding Tableau report URL upon request. Monthly report e-mails sent out by IRSO also include further information on accessing and using the tool
The scoping stage for this project began in April 2023.
Kick off meeting 4/17/23
IRSO prepares periodic reports and sends them to faculty and their admins to advise them on arising matters. This enables them to meet their obligation to be responsible for the management of their awards, projects, and funds.
All those present agreed that period reporting is a necessary part of the research administration monitoring and management process. Such reports are the logical intermediary between dashboard data raw exports and informed/effective faculty oversight/management.
IRSO team members met to review report methods to say what we think is useful, not useful, and how we can improve utility for the following stakeholders.
Ourselves. Can we develop a method/product that lowers the amount of time we spend building reports, while also increasing the effectiveness (and scope) of our analysis, and correspondingly improve the advisories that come from our analysis?
Faculty. Can we convey succinct, actionable, and clear information that meets their objective need to be both well informed about their portfolio and the (more subjective) need to have confidence and a sense of assurance that their portfolio is accurate and being well managed?
Departmental and Executive administrators. These stakeholders are not the project managers of IRSO awards but they are the ones that often initiate actions relating to staff and other costs that impact these awards.
As of April 2023, the reporting frequency is monthly and current monthly reports are being produced from ROBI and iO (with excel and adobe acrobat).
While necessary, the report making process is too long. Takes more than an hour to simply build each report, a further hour or often more to analyze the content and to provide meaningful analytical review of the report.
We believe that reporting can certainly be improved.
Questions arising from the 4/17/23 kick off meeting
Reporting should add value for the stakeholders? What aspects add value, and to which stakeholders in particular?
Can we systematize or automate compliance checks for ineligible costs?
What is the best way to show projected cost? Burn rate? Can we show scenarios? Would that be useful?
What is the best way to present labor information to the various stakeholders? How can we improve on what we currently have?
Presentation of budget, actuals, commitment, and balance in a single horizontal stacked bar chart seems like a good way to show a snapshot of a financial status.
How best to represent impending award end dates? How best to represent award time and spending progression?
Following the kick off meeting, a series of further meetings took place. These meetings involved Research Administration staff from other departments.
Various content and design questions were considered. Examples of different reporting processes that other departments had generated were shown.
Following the suggestion of Brad Nagar of OIT, all development focused on offline data modeling tools. It was determined that (at this point in time)Tableau offered the best utility for us in meeting this goal, although excel tools were also considered and development of those would also be pursued.
Correspondingly, no further attempts were made to develop ROBI in the direction of monthly reports. All focus turned to potential uses of iO data exports within tableau and excel.
Ongoing Matters
A recurring theme in conversations surrounding the potential utility of this reporting process was that of ‘ongoing matters’.
‘Ongoing Matters’ had been a section that IRSO had included in its pre-existing monthly reporting product. Essentially this section was a text list of actionable items or otherwise important information that IRSO evaluated as being relevant to the PI’s attention.
We looked into methods to include such information in the new report format. Options within Tableau were considered but did not offer the same functionality as options that were discovered within google sheets.
Google Sheets offered the ability to upgrade the ‘Ongoing Matters’ concept into a persistent event log of actionable or important items of information relating to the award. Completed actions could be marked as such and would remain viewable after the fact. Maintaining a centralized operations log such as this generates the basis for a hugely beneficial management tool. Applications of such a tool are easy to imagine in scenarios where new or different staff become involved over an extended period of time with such an operations log providing a referenceable narrative of prior concerns, events, and actions.
As security for the google sheets could also be controlled via Rice netID, the presentation of the google sheet was able to be embedded within the tableau report structure.
With various concepts proven within Tableau, the focus shifted to the fork in the development road which was the decision over which build format to pursue (i.e. production of a static pdf export or a dynamic web-based representation). It was decided that the web-based representation offered the most, and allowed for the greatest degree of security and targeted access.
Once the basic build format was determined, considerations of security, scalability, presentation layout and integration with google sheets were major additional factors addressed throughout the build period.
IRSO worked with the Tableau User Group (TUG) at Rice to establish a secure data repository folder via which Tableau report instances would automatically update.
The data pipeline begins with monthly downloads of data from iO, which then go through certain cleaning and transformative operations (carried out by IRSO) prior to being uploaded to the dataviz server, from which netID-secured instances of customer-specific report instances automatically synchronize.
Any reporting product must have configurable security access. Each netID-secured report instance requires on-campus presence (or VPN), and for an additional layer of security also requires a numeric PI passkey to view it. Passkey access is currently maintained by IRSO.
No tableau license is required to view the report and there is no pathway back through the report for users to access underlying data tables.
Throughout August and September 2023, further testing feedback was sought from a select group of staff and faculty stakeholders.
In summary, the main one of these was to expand the offering into a cell-phone compatible design. The next iteration of monthly reports have now been redeveloped to enable cell phone access although VPN is required.
Feedback was received regarding the overall layout. Some found things too busy, at least on the summary pages within the report. Changes have been made to simplify and concentrate the presentation of information in the first three sections to the report (timeline balances, labor scaffold, ongoing matters).
Disallowed costs (presentation of data). Some questions have been posed about the viability of confirming corrective action. It may not be possible to do so… so it might be confusing to include multi-month information which could imply a disallowed cost remains when it does not. Further development and consideration of this feature is ongoing.
First release of this report as a monthly product given to supported PIs, centers, and institutes happened in October 2023.
Further supporting materials, including tooltips and user guides have been produced or are in development to support the November report distribution.
IRSO met with representatives of OIT and the Controllers Office in October 2023 to share insight on the professional Research Administration challenges that make a monitoring tool necessary, and the technical and logical challenges encountered when building such a tool based on existing iO data exports.
Matters of data security were discussed and considerations in that regard were found to have been adequately applied in the Tableau tool.
Various visualizations within the Tableau data tool were agreed as having significant potential to improve award portfolio management. Most notably among these being the award timeline visualization that in one chart provides analytical feedback relative to a broad variety of foundational award management questions.
The Labor Scaffold visualization that creates a continuous timeline of past labor allocation and future labor allocation was also considered to be a very intuitive labor management tool that could support a range of compliance and management workflows surrounding labor and effort. Representatives from the controllers office speculated that it might be duplicable (by them) within iO.
IRSO committed to share feedback on the tableau tool and in particular to identify what was well received and what the pain points were.
IRSO has transitioned to the use of the tableau report (including the 'Ongoing Matters' interface, re-named 'Organizational Log' in May 2024) as a core organizational interface between itself, departmental staff, and faculty for all of its supported awards and award portfolios.
User feedback data on the tool has been collected and tool development has iterated in response to this feedback.
Further incremental refinement of the report in tableau by IRSO is anticipated but the primary focus in the near term will be on simplifying and codifying the production method. The ETL process is extensive at present, with many procedural steps required. This makes the tool something that (likely) only IRSO will reasonably have the tool specialism/knowledge to produce. Correspondingly this the limits potential utility of considering Tableau as a tool that users could build their own reports with at a broader university level.
The Tableau tool will remain the primary mechanism by which IRSO will manage its award portfolio. Excel tools that can add value to Research Administration for the broader university will also be produced.