Data Model
This app runs from HRS data copied to the SMPH data warehouse and from there, is loaded into QlikSense to use for data views. Data is re-loaded from the sources every 24 hours. The source tables are listed below:
VIEW | SOURCE | DESCRIPTION |
|---|---|---|
| Regent Summary Report | WISDM/REGENT_SUMMARY_REPORT | Represents Award Funding Actions that must be reported to the Board of Regents. YEAR(ISSUE_DATE) >= 2017 AND YEAR(ISSUE_DATE) <= Year(Today()) AND BUSINESS_UNIT = 'UWMSN' |
| Faculty | HR/CURRENT_JOB | Contains the row currently in effect for each active and terminated job from HRS. Future jobs and future changes are NOT included. BUSINESS_UNIT = 'UWMSN' AND wildmatch(DEPTID, 'A53*') = 1; |
| Awards | WISDM/AWARDS.QVD | Joins to Regent Summary Report view on AWARDS_KEY. Fields include AWARD_START_DATE, AWARD_END_DATE, and AWARD_TYPE |
Data Model Constraints
- Business unit = UWMSN (UW-Madison awards only)
- ISSUE_DATE >= 2017 (includes awards granted 2017 or later)
- UWMSN employees with employee department A53****
Sheet Overview - Award Summary Dashboard
This sheet contains summaries of awards by division, department and award PIs. Award totals are calculated using a sum of FA_AMOUNT for each award ID. Project data is not included on this sheet to prevent duplication. To filter data, use the side filter panes, select any value in a visualization or click on a table column heading.
Sheet Contents
FEATURE | TYPE | DESCRIPTION |
|---|---|---|
Award Summary by Division < Department | Bar Chart (DD) | This chart displays the total funding amount for awards by department. Select a department to view a breakdown of awards by division. |
All Awards | Table | This table displays rows of basic award information in a spreadsheet format. Project data is not included in this visualization to prevent duplication of records. |
FA Total by Department & PI | Pivot table | Use this table to view FA_Amount totals by department. Expand each row using the '+' sign to view FA_Amount total broken down by PIs. |
Report by Award Dept. | Button | Filters data in this sheet to show 133/144 awards issued to Dept. of Medicine department. FUND=133/144 AWARD_DEPT_ID = 53** |
Report by PI Dept. | Button | Filters data in this sheet to show 133/144 awards from PIs with (HR) appointments in medicine divisions. FUND=133/144 PI_DEPT_ID = 53** |
Clear Selection | Button | Clears all current selections applied to this sheet. |
| # of Awards | KPI | A count of the number of unique award IDs. Changes along with filters. COUNT(unique AWARD_ID) |
| FA Amount | KPI | A sum of FA_AMOUNT for all awards. Changes along with filters. SUM(FA_AMOUNT) |
| Issue Date | Filter Pane (DD) | Allows for filtering awards by ISSUE_DATE from the RSR database. Formatted to be based on Fiscal Year. First select year that you wish to filter by and then (optionally) by a range of months in that fiscal year. Subfield(YearName(ISSUE_DATE, 0, 7), '-', -1) MonthName(ISSUE_DATE) |
Sheet Overview - Award Explorer
This sheet displays rows of basic award information in a spreadsheet format for all divisions and PIs. Project data is not included in this visualization to prevent duplication of records.
Sheet Contents
FEATURE | TYPE | DESCRIPTION |
|---|---|---|
| All Awards | Table | This table displays rows of basic award information in a spreadsheet format. Project data is not included in this visualization to prevent duplication of records. |
| Issue Date | Filter Pane (DD) | Allows for filtering awards by ISSUE_DATE from the RSR database. Formatted to be based on Fiscal Year. Select year that you wish to filter by and then (optionally) by a range of months in that fiscal year. Subfield(YearName(ISSUE_DATE, 0, 7), '-', -1) MonthName(ISSUE_DATE) |
| Award Action Date | Filter Pane (DD) | Allows for filtering awards by AWARD_ACTION_BEGIN_DATE from the RSR database. Formatted to be based on Fiscal Year. Select year that you wish to filter by and then (optionally) by a range of months in that fiscal year. Subfield(YearName(AWARD_ACTION_BEGIN_DATE, 0, 7), '-', -1) MonthName(AWARD_ACTION_BEGIN_DATE) |
| Reporting Period | Filter Pane (DD) | Allows for filtering awards by REPORTING_PERIOD_FY and REPORTING_PERIOD fields from the RSR database. Note that these fields are manually input by RSP. Select reporting period year that you wish to filter by and then (optionally) by a range of months in that fiscal year. Subfield(YearName(AWARD_ACTION_BEGIN_DATE, 0, 7), '-', -1) MonthName(AWARD_ACTION_BEGIN_DATE) |
| Department < Divisions < Award PI | Filter Pane (DD) | Allows for filtering awards by award department, award division and award PI. |
| Sponsor Type < Sponsor | Filter Pane (DD) | Allows for filtering awards by sponsor type and, optionally, further drill down the selection to individual award sponsors. Award Sponsor Type = SPONSOR_TYPE_DESCRIPTION Award Sponsor = SPONSOR_NAME |
| Other filters | Filter Pane | Fund - filter by fund code. Typically 133/144 |
| Clear Selection | Button | Removes all current selections from the sheet. |
Common Use Cases & Workflows
Example: How much funding did a specific Department have in FYXX
- Navigate to Award Summary Dashboard Sheet. Be sure to clear any existing selections
- Use the Issue Date filter pane to select the desired fiscal year. Optional: Select range of months.
- Use Award Department filter pane to select the desired department
Example: How much funding did a specific Division have in FYXX?
- Navigate to Award Summary Dashboard Sheet. Be sure to clear any existing selections
- Use the Issue Date filter pane to select the desired fiscal year. Optional: Select range of months.
- Use Award Division filter pane to select the desired division.
Example: Who are the top 5 researchers in a Dept/Division?
- Navigate to Award Summary Dashboard Sheet. Be sure to clear any existing selections
- Use the Issue Date filter pane to select the desired fiscal year. Optional: Select range of months.
- Select desired division or department from the Award Division or Award Department filter panes.
- View the Funding Action Total by Department & PI pivot table. Use the (+) symbol to expand the desired department and view funding totals by PI for that department.
Example: How many awards is a Dept/Division managing?
- Navigate to Award Summary Dashboard Sheet. Be sure to clear any existing selections
- Use the Issue Date filter pane to select the desired fiscal year. Optional: Select range of months.
- In the Award Summary by Division < Department bar chart, toggle Award Funding Action Amount to # of Awards. This will display the number of awards currently being handled by a division. Optionally, you can drill down the bar chart further to view # of awards by department.