Scope:
There are four tasks within the scope of the v1.2 update of the Research Dashboard; three of these items are behind-the-scenes data storage and aggregation matters. The one change that will be visible to the users, and which involves a change in the Dashboard itself, is a minor change to the display of detail rows on the Monthly Detail page, to make clear that some values are for total activity and some are for activity of those departments and individuals as recognized by participation.
Project Tasks and Requests (as received Thursday, Oct. 16, 2008, and approved Nov. 22, 2008):
1. Implement Change to Activity Details Exhibit on the Monthly Detail Page: December 31, 2009
Currently, the Activity Details Exhibit on the Monthly Details Page of the Research Dashboard includes fields for the Number of Colleges “Cols”, Departments or Centers “Depts” and Investigators “Invs” involved on a project, as well as a field for the dollar value of the itemized research activities “Value”.
When that the users have not applied any filters, these values are reported for the entire activity as in Figure 1.
When however, users have applied a filter(s), these fields are reported based on recognized values limited by the filters as in Figure 2.
Because users often mistake the filtered field values as total activity values, the following design modifications to the Activity Details Exhibit are needed:
- Report the total activity values for the “Cols”, “Depts”, “Invs” and “Value” fields regardless of any user selections made in the drop-down list boxes.
- Change the “Value” column heading to “Total $”.
- Remove the “PI” field
- Add a new field “RI” that will report the number of recognized investigators consistent with filtering based on any user selections made in the drop-down list boxes.
- Add a new field “Recognized $” that will report the recognized value of the activity consistent with filtering based on any user selections made in the drop-down list boxes.
If these design modification are successfully completed, the Activity Detail Exhibit feature in previously in Figure 1, would be revised to be consistent with Figure 3.
In the case that users have applied a filter(s), the “RI” and “Recognized $” fields would be reported based on recognized values limited by the filters as in Figure 4.
These changes to the Activity Details Exhibit do not require modifications to the activity overview pop-up returned if the user clicks an Item’s hyperlinked “Number”.
2. Add/Modify Data Fields in “RA_ALL” Tables in the Sponsored Data Warehouse: December 12, 2008
1. Add recognition based “Direct Cost”, “Indirect Cost” and “Modified Total Direct Cost” to the “RA_ALL_EXPENDITURE_EOM_RECOG” table in the Sponsored Data Warehouse.
2. Add an “Account #” field to the “RA_ALL_AWARD_EOM_RECOG” and “RA_ALL_EXPENDITURE_EOM_RECOG” tables in the Sponsored Data Warehouse.
3. Add "Award Title", "Activity Type", and "Award Status" to tables.
3. New Processes and Procedures for TRIF Data: (deferred)
Note: This item is now removed from the project, and is part of the version 2.0 request.
OVPREA Accounting Staff has indicated that changes should be made the ETL Queries used to populate the TRIF Data in the “RA_TRIF_EXPENDITURE_EOM” and “RA_TRIF_EXPENDITURE_EOM” tables in the Sponsored Data Warehouse which are ultimately reported on the Research Dashboard. UTO, PRIDE and OVPREA Accounting will need to coordinate and specify the required changes.
Currently, there are considerable inconsistencies in many of the data fields for TRIF data. UTO, PRIDE and OVPREA Accounting will need to coordinate and specify the standards for data formats and potentially alternative data storage, management, entry and update procedures.
4. Develop a Multidimensional Database to Support the Research Dashboard: (deferred)
Note: This item is now removed from the project, and will be a stand-alone project, prioritized separately.
To decrease response times on the Research Dashboard, a multidimensional database should be developed to support the Research Dashboard as well as other OVPREA | PRIDE Research Analytics tools. This multidimensional database must be accessible both by direct query and from data “connections” within MS Excel Workbooks, by users authorized to access the Research Dashboard. User authentication should be based on asurite IDs.
5. Implement Facilities & Administrative Costs Grid, Trends, and Details :: January 20, 2009
Modify database objects to be able to return indirect costs (F&A) in addition to proposals, awards, and expenditures. These should include Total Value, Count, and Investigator Count like the other activity types. Grids, trends, and details shall all be modified to display these items, including appropriate selections and options in the drop-down listboxes (DDLs) for each screen. In addition, the row and columns of "Average Size" shall be removed everywhere (except in the trends charts, where the yellow line should remain). Also, the range of dates (month and year DDLs, generally) shall be modified to allow ten years of data to be charted/viewed. This should be a dynamic process, and the ETL routines might need to be changed to accommodate this as well.
