html5-img
1 / 75

SPONSORED PROJECTS BRIO TRAINING

SPONSORED PROJECTS BRIO TRAINING. bri·o n. - vigor, vivacity (source: dictionary.com). Workshop Agenda. Introductions & Logistics Review of Workshop Manual Contents Data Overview ( PowerPoint ) User Support & Communications Data Warehouse Overview Award/Proposal Data Mart Overview

adair
Télécharger la présentation

SPONSORED PROJECTS BRIO TRAINING

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SPONSORED PROJECTS BRIO TRAINING bri·on. - vigor, vivacity (source: dictionary.com)

  2. Workshop Agenda • Introductions & Logistics • Review of Workshop Manual Contents • Data Overview (PowerPoint) • User Support & Communications • Data Warehouse Overview • Award/Proposal Data Mart Overview • Subcontract Data Mart Overview Break • Hands-on intro to Brio, Sponsored standard reports and simple queries

  3. User Support & Communications

  4. Brio Support – Web & Listserv • General Brio • Send email to: Business-Intelligence@cornell.edu • Brio-L – For CIT to communicate technical concerns • Sponsor Data Warehouse • http://www.research.cornell.edu/RAIS/SponsDW/

  5. Brio Support - SponsDW • Training (this class) • E-mail consulting • Send questions to support staff • Use for reporting Brio problems or questions about usage, training, access • osp_whs_feedback@cornell.edu • User Group meetings or presentations at Research Admin Round Table will be held as needed

  6. Sponsored Data Warehouse Overview

  7. What is a Data Warehouse? • Data from one or more sources organized in a format optimized for generating reports. • Data is stored in a database (e.g., Oracle) • Data can be retrieved using a variety of tools (e.g., Brio or a web portal)

  8. Data Warehouse Building Blocks 1) Data are collected from various sources OSP Number = 42947 Principal Investigator = P. COOK Award Function = ORGANIZED RESEARCH Sponsor = NATIONAL SCIENCE FOUNDATION Award Amount = $300,000 Sponsor ID = 01-34562R Department = NUTRITIONAL SCIENCE CFDA Number = 32.400 Sponsor Type = FEDERAL GOVERNMENT Proposal Sent Date = 10/17/2002 Period of Performance = 6/1/2003 – 5/31/2006

  9. Data Warehouse Building Blocks 2) DATA are stored in COLUMNS 3) Related COLUMNS are grouped into TABLES Award/Proposal Investigator Sponsor GCO GCO

  10. Award/Proposal GCO Sponsor Investigator GCO GCO Sponsor ID OSP Number Dept Code Department Data Warehouse Building Blocks 4) TABLES are joined into one or more DATA MODELS using unique identifiers (keys)

  11. Investigator Department GCO Sponsor Program Award Proposal Status Group History F & A Rates Distribution Deliverable Sponsored Data Warehouse- Proposals and Awards Sponsored Projects Portal Brio Sponsored Data Warehouse

  12. Investigator Department GCO Sponsor Program Award Proposal Status Group History F & A Rates Distribution Deliverable Sponsored Data Warehouse- With Accounts and Subrecipients Sponsored Projects Portal Brio KFS Sponsored Data Warehouse Accounts ADW Pre FY11 Subcontracts

  13. Where does the data come from? • Extracted from proposal or award documents (e.g. Form 10) • Entered by OSP staff into OSP operational data system • Loaded nightly (between 7PM and 11PM) into Sponsored Data Warehouse

  14. Data Access Policy • Can be provided at college, department, PI or project • Proposal records access follows data access limits • Award records are open to all. Some components (e.g. attached documents) follow data access limits in the Sponsored Portal. • Data Access Request Form: http://www.research.cornell.edu/rais/sponsdw/Access/

  15. Data Warehouse Availability • Targeted for 99% availability excluding known database maintenance times • Actual 6 month average has been at or above this target • Maintenance times: • Every Saturday 10pm-6am: Cold backups • Sunday 6am–Noon: Planned system maintenance as needed • One or two weekdays/month 5am-6am for OS patches • Reference:https://brio.cit.cornell.edu/UPTIME.html

  16. Sponsored Portal: A web-based view into Sponsored Projects data, including some Accounting Data Warehouse (ADW) data Data presented in a predefined, fixed format https://portal.research.cornell.edu or https://my.research.cornell.edu/ Brio: Brio is a general purpose query tool. Brio allows you to organize data in a variety of formats Data is represented as joined tables Pre-formatted "Standard Reports" of data commonly used are also available Sponsored Portal vs. Brio

  17. Why Use the Sponsored Portal: Look up individual awards and proposals based on pre-defined criteria Submit new account request forms, subcontract requests, change requests Why Use Brio: Look up groups of awards and proposals based on criteria you define in queries Present results in a variety of formats including charts or pivot tables Save the queries and reports as templates so you can process them as needed Sponsored Portal vs. Brio

  18. Sponsored Award/Proposal Data Mart Overview

  19. Metadata: the data dictionary • Metadata provides individual field definitions • Two views available: • All fields are listed by table in the Metadata Report named “Columns by Table” • All fields are listed in alphabetical order, with table indicated, in the Metadata Report named “Column Alpha Index” • Colored page section in Training Manual • Generate updated report with Brio

  20. Investigator Department GCO Sponsor Program Name Award Proposal Status Group F & A Rates History Distribution Deliverable Award/Proposal Model

  21. Investigator Department GCO Sponsor Program Name Award Proposal Status Group F & A Rates History Distribution Deliverable Award/Proposal Model Core Table

  22. Award Proposal Table • Award Proposal specific data (eg): • OSP Number • Project period • Project amount • Proposal purpose • Award type (grant, contract, FDP, MTA, NDA) • Summary financial data • Use Award_or_Proposal field to select only “AWARD” or only “PROPOSAL” • Database includes current and archived records. Use status codes to limit records included in results.

  23. Award Proposal Table – Project ID coding • OSP number is 5 digit unique identifier that tracks the project from proposal to award • Proposal ID starts at P001 and is incremented to P002, P003, etc as award is amended • Award ID normally A001, but sometimes incremented • Usually none or one “pending” proposal or “active award” per OSP number.

  24. Investigator Department GCO Sponsor Program Name Award Proposal Status Group F & A Rates History Distribution Deliverable Award/Proposal Model Lifecycle Tracking Tables

  25. Past Present Future History Table Status Group Table Deliverables Table Project Timeline (from pending proposal or active award perspective)

  26. Status Codes • Defines the current state of an award or proposal • Codes designed to be meaningful to users across campus • Codes identify many stages in life of project

  27. Proposal Status Codes • PUDD-PROPOSAL UNDER DEVELOPMENT BY DEPARTMENT: Department has working on proposal, not yet submitted to OSP • PAD-PROPOSAL ABANDONED BY DEPARTMENT: Department decided not to submit this proposal • PURO-PROPOSAL UNDER REVIEW BY OSP: The proposal is being reviewed. It has not yet been submitted to the sponsor. This is the default status for all new proposal records. • PURS-PROPOSAL UNDER REVIEW BY SPONSOR: The proposal has been submitted and is under consideration by the sponsor. This is considered an "pending" proposal. • PPURS-PRE-PROPOSAL UNDER REVIEW BY SPONSOR: A pre-proposal has been submitted and is under consideration by the sponsor. This is considered an "pending" proposal. • PPA-PRE-PROPOSAL ACCEPTED a pre-proposal was submitted to sponsor and accepted. The full proposal will be track in new record. • PNF-PROPOSAL NOT FUNDED BY SPONSOR: The sponsor has notified Cornell that the proposal will not be funded. • PW-PROPOSAL WITHDRAWN BY CORNELL: The proposal was withdrawn by Cornell before a funding decision was made. • PFUD-PROPOSAL FUNDED UNDER DIFFERENT OSP NUMBER: The project was funded, but different OSP number used. • PG-PROPOSAL FUNDED AS GIFT: It was determined that the proposal will be treated as a gift and tracked by Foundation Relations. No award record is created in SponsDW. • PA-PROPOSAL AWARDED: The proposal was funded. A proposal record will be maintained in SponsDW, but a new award record has been created.

  28. Award Status Codes • ANA-AWARD NOT ACCEPTED: The proposal was accepted by the sponsor, but Cornell has declined to accept it. • ANF-AWARD NOT FUNDED: After having initially indicated that the proposal would be funded the sponsor has decided not to fund the project. • AURO-AWARD UNDER REVIEW BY OSP: The sponsor has indicated that they plan to fund the proposal. The award documents are being reviewed by OSP staff. • AIPC-AWARD IN PROCESS BY CORNELL: The sponsor has indicated that they plan to fund the proposal. The award documents are being reviewed by OSP staff other than the assigned GCO or by a Cornell staff member outside OSP. • APC-AWARD PENDING COMPLIANCES: The award acceptance is on hold waiting for all compliance requirements to be completed. • AS-AWARD SIGNED BY CORNELL: The award documents have been signed by Cornell, but not by the sponsor. • ASAP-AWARD SIGNED BY ALL PARTIES: The award documents have been signed by all parties. Notification of the award acceptance has been provided to SFA, the department, the PI and others on campus who need to know. The project work is underway. • APA-AWARD PENDING POST-AWARD ACTIVITY: A post-award request has been submitted by OSP and is pending approval from the sponsor (e.g. no-cost extension). --(note that this one replaces the old AWARD PENDING AMENDMENT with same status code). • ATIP-AWARD TRANSFER IN PROGRESS: The award is being transferred to another university

  29. Award Status Codes (cont.) • AOH-AWARD ON HOLD: There has been no activity from the sponsor or PI in the last 60 days. • AAF-AWARD AWAITING FEEDBACK: Additional information is required from the Sponsor, PI or other office before additional action can be taken. university • APFF-AWARD PENDING FLOW-THRU FUNDING: Funding is expected but our sponsor has not received funding from their prime sponsor. • ARO-AWARD RECEIVED BY OSP: OSP has received the amendment but has not yet reviewed it. • AMNAC-AMENDMENT NOT ACCEPTED BY CORNELL: The amendment was approved by the sponsor but was declined by Cornell. • AMPC-AMENDMENT PENDING COMPLIANCE: The amendment acceptance is on hold until all compliance requirements are completed. • AMPFF-AMENDMENT PENDING FLOW-THRU FUNDING: Additional funding is expected but our sponsor has not yet received funding from their prime sponsor. • AMRO-AMENDMENT RECEIVED BY OSP: OSP has received the amendment but has not yet reviewed it. • AMSC-AMENDMENT SIGNED BY CORNELL: The amendment has been signed by Cornell and returned to the sponsor. Cornell is waiting for the sponsor to return the fully executed amendment. • AMURO-AMENDMENT UNDER REVIEW BY OSP: OSP has received the amendment and is reviewing it. • AMOH-AMENDMENT ON HOLD: There has been no activity from the sponsor or the PI in the past 60 days. • AMAF-AMENDMENT AWAITING FEEDBACK: Additional information is required from the Sponsor, PI or other office before additional action can be taken.

  30. Closed Award Status Codes AAC-AWARD AWAITING CLOSEOUT: The award period has ended, no amendments are pending. OSP is waiting for notification that final deliverables have been provided to the sponsor. ACOSP-AWARD CLOSEDBY OSP: The award period has ended and all deliverables have been submitted except the technical report. The hardcopy award file is archived for a period of time specified in the retention period after which time it is destroyed. AC-AWARD CLOSED: The award period has ended and all deliverables have been submitted. The hardcopy award file is archived for a period of time specified in the retention period after which time it is destroyed.

  31. Status Groups Because of the complexity and number of codes, OSP uses a number of special terms to refer to groups of statuses: • "Pending Proposal": (status of PURS or PPRUS) The proposal has been submitted and is under consideration by the sponsor. • "Pending Award": (status of ARO, AURO, AAF, AS, APFF, APC or APA) The sponsor has indicated that they wish to fund the proposal or amendment. The award documents have not yet been signed. • "Active Award": (status of ASAP, ATIP, APA, AMRO, AMURO, AMAD, AMPC, AMPFF or AMSC) The award documents have been signed. The project work is underway. There may or may not be amendments under consideration. • "Pending & Active Award": (status ARO, AURO, AAF, AS, APFF, APC, APA, ASAP, ATIP, APA, AMRO, AMURO, AMAD, AMPC, AMPFF or AMSC) The sponsors has indicated that they will fund the award or amendment. The award documents may or may not have been signed. • "Pending Proposal or Active Award": (status of PPURS, PURS, ARO, AURO, AAF, AS, APFF, APC, APA, ASAP, ATIP, APA, AMRO, AMURO, AMAD, AMPC, AMPFF or AMSC) The project is in some stage of activity by the sponsor, OSP office and/or researcher. • “Post-Award Activity”:(status APA, AMRO, AMURO, AMAF, AMPC, AMPFF, AMSC, AMOH) The award has been accepted and currently has an amendment action in progress.

  32. Status Groups All Proposals & Awards ANA Pending & Active Award PUDD ANF Pending Proposal & Active Award PURO Pending Award PFUD ARO AAF Pending Proposal PA AIN APFF AURO AS APC Active Award APA PAD AM… PPURS PPA PURS AAC PNF ASAP ATIP ACOSP PW AC PG

  33. Status Group Table

  34. Status Qualifiers • Certain statuses have a qualifing value: • Proposal Under Development • Form 10 , Notice of Submission, Other Related Material, Scope of Work, Solicitation • Award Awaiting Feedback From, Award on Hold • Sponsor, PI, Other Research Admin Office • Award Pending Compliance • Biological Agents & Toxin ,Conflict of Interest, Delinquent Final Technical Report, Export Control,Foreign Activity, Form 10, GMO, Hazardous Material, IACUC, IRB , MTA Statement, Radiation, rDNA , Stem Cell

  35. Past Present Future History Table Status Group Table Deliverables Table Project Timeline (from pending proposal or active award perspective)

  36. History Table • Event code and description • Date • Comment • Other event specific fields. Usually those that changed as a result of the event (e.g., mod number, amount, sponsor, PI) • There will usually be many events per project

  37. History Table (cont.) • Types of events recorded • All status changes • Award documents: received/reviewed/ approved/distributed • Award change: budget revision/key personnel/no cost extension/scope • Award terminated early • Compliance use rescinded/reinstated

  38. Example History Records

  39. Past Present Future History Table Status Group Table Deliverables Table Project Timeline (from pending proposal or active award perspective)

  40. Deliverable Table • Identifies what deliverables are required by sponsor • Technical • Fiscal • Patent • Equipment • Final report due and sent date are always entered • Interim dates may be entered

  41. Example Deliverable Records

  42. Investigator Department GCO Sponsor Program Name Award Proposal Status Group F & A Rates History Distribution Deliverable Award/Proposal Model Primary Detail Tables

  43. Investigator Table • Includes PI, CoPIs, Key Persons and Research Fellow • Only one PI per proposal/award • Use Investigator Role to limit results to PI • Values are PI, CO, KP and FE • Without PI limit, may return multiple records per project • Data elements include name, netid, home department, college

  44. Example Investigator Records

  45. OSP Department Table • Department administering the award. May be different than PI’s home department • Use KFS Org Codes for department designation

  46. KFS Org Codes in Dept Table • Org in Sponsored DW maps to control account Org in KFS • Three level rollup structure: • College (KFS Org Type C) • Department (KFS Org Type D) • Assigned Org (KFS Org Type D or S). • If Type D then Department and Assigned Org will be same value. • Org usage rules are defined by colleges

  47. KFS Org Example – A&EP Engineering assigns sponsored accounts to D-type Orgs * 2300 - College of Engineering(C) - 2301 - Academic (G) - 2303 - Academic Departments (G) #,^ 2371 - Applied & Engineering Physics(D) * College # Department ^ Assigned Org on Sponsored Awards

  48. KFS Org Example – Animal Sci CALS assigns sponsored accounts to S-type Orgs * 0100 - CALS - College of Ag. & Life Sciences (C) - 0101 - CALS Ithaca Campus (G) - 01AC - Academic (G) # 0127 - CALS Animal Science (D) - 01B7 - CALS AS Department Operations (S) ^ 01B8 - CALS AS Morrison/General Operations (S) * College # Department ^ Assigned Org on Sponsored Awards

  49. Example Org Data

  50. Sponsor Table • Identifies primary sponsor of project • Sponsors are stored in 3 level hierarchy • DHHS/NIH/NIAID • NYS/DEC • Sponsor type (Fed Govt, Non-profit, Corporate) • General contact information is sometimes available for small sponsors

More Related