SPONSORED PROJECTS BRIO TRAINING bri·o n. - vigor, vivacity (source: dictionary. com) 1
Workshop Agenda 1. Introductions & Logistics 2. Review of Workshop Manual Contents 3. Data Overview (Power. Point) a) b) c) d) User Support & Communications Data Warehouse Overview Award/Proposal Data Mart Overview Subcontract Data Mart Overview Break 4. Hands-on intro to Brio, Sponsored standard reports and simple queries 2
User Support & Communications 3
Brio Support – Web & Listserv General Brio n n Send email to: Business-Intelligence@cornell. edu Brio-L – For CIT to communicate technical concerns Sponsor Data Warehouse n http: //www. research. cornell. edu/RAIS/Spons. DW/ 4
Brio Support - Spons. DW Training (this class) E-mail consulting n Send questions to support staff n Use for reporting Brio problems or questions about usage, training, access n osp_whs_feedback@cornell. edu User Group meetings or presentations at Research Admin Round Table will be held as needed 5
Sponsored Data Warehouse Overview 6
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) 7
Data Warehouse Building Blocks 1) Data are collected from various sources Principal Investigator = P. COOK OSP Number = 42947 Award Function = ORGANIZED RESEARCH Sponsor = NATIONAL SCIENCE FOUNDATION Sponsor ID = 01 -34562 R Award Amount = $300, 000 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 8
Data Warehouse Building Blocks 2) DATA are stored in COLUMNS OSP # Sponsor PI Dept Amount 37623 NYS J. SMITH ANIMAL SCI $200, 000 38241 NIH P. COOK CHEMISTRY $1, 500, 000 44782 NSF T. JONES MECH ENG $10, 750 3) Related COLUMNS are grouped into TABLES Award/Proposal GCO Investigator Sponsor GCO 9
Data Warehouse Building Blocks 4) TABLES are joined into one or more DATA MODELS using unique identifiers (keys) Award/Proposal GCO Sponsor ID Investigator OSP Number GCO Dept Code Department 10
Sponsored Data Warehouse - Proposals and Awards Sponsored Projects Portal Brio Sponsored Data Warehouse Investigator Department GCO Sponsor Program F & A Rates Award Proposal Distribution Status Group History Deliverable 11
Sponsored Data Warehouse - With Accounts and Subrecipients Sponsored Projects Portal Brio Sponsored Data Warehouse KFS Accounts Investigator GCO Sponsor Program ADW Department F & A Rates Award Proposal Distribution Status Group History Deliverable Pre FY 11 Subcontracts 12
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 7 PM and 11 PM) into Sponsored Data Warehouse 13
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/ 14
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: n n n Every Saturday 10 pm-6 am: Cold backups Sunday 6 am–Noon: Planned system maintenance as needed One or two weekdays/month 5 am-6 am for OS patches Reference: https: //brio. cit. cornell. edu/UPTIME. html 15
Sponsored Portal vs. Brio Sponsored Portal: Brio: A web-based view into Brio is a general purpose Sponsored Projects data, query tool. Brio allows you including some to organize data in a Accounting Data variety of formats Warehouse (ADW) data Data is represented as Data presented in a joined tables predefined, fixed format Pre-formatted "Standard https: //portal. research. cornell. edu or Reports" of data commonly https: //my. research. cornell. edu/ used are also available 16
Sponsored Portal vs. Brio Why Use the Sponsored Why Use Brio: Portal: Look up groups of awards and proposals based on Look up individual awards and proposals based on criteria you define in pre-defined criteria queries Submit new account Present results in a variety request forms, subcontract of formats including charts requests, change requests or pivot tables Save the queries and reports as templates so you can process them as needed 17
Sponsored Award/Proposal Data Mart Overview 18
Metadata: the data dictionary Metadata provides individual field definitions Two views available: n n 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 19
Award/Proposal Model Investigator Department GCO Sponsor Program Name Award Proposal F & A Rates Distribution Status Group History Deliverable 20
Award/Proposal Model Core Table Investigator Department GCO Sponsor Program Name Award Proposal F & A Rates Distribution Status Group History Deliverable 21
Award Proposal Table Award Proposal specific data (eg): n n n 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. 22
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 P 001 and is incremented to P 002, P 003, etc as award is amended Award ID normally A 001, but sometimes incremented Usually none or one “pending” proposal or “active award” per OSP number. 23
Award/Proposal Model Lifecycle Tracking Tables Investigator Department GCO Sponsor Program Name Award Proposal F & A Rates Distribution Status Group History Deliverable 24
Project Timeline (from pending proposal or active award perspective) Past History Table Present Status Group Table Future Deliverables Table 25
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 26
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 Spons. DW. PA-PROPOSAL AWARDED: The proposal was funded. A proposal record will be 27 maintained in Spons. DW, but a new award record has been created.
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 28
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. 29
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 CLOSED BY 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. 30
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, 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. 31
Status Groups All Proposals & Awards PUDD PURO Pending Proposal & Active Award Pending & Active Award PFUD ARO PA AIN ANA ANF AAF APFF AURO Pending Award AS APC Pending Proposal PAD PPA PNF PW PG APA PPURS AM… PURS Active Award ASAP AAC ATIP ACOSP AC 32
Status Group Table 33
Status Qualifiers Certain statuses have a qualifing value: n Proposal Under Development w Form 10 , Notice of Submission, Other Related Material, Scope of Work, Solicitation n Award Awaiting Feedback From, Award on Hold w Sponsor, PI, Other Research Admin Office n Award Pending Compliance w Biological Agents & Toxin , Conflict of Interest, Delinquent Final Technical Report, Export Control, Foreign Activity, Form 10, GMO, Hazardous Material, IACUC, IRB , MTA Statement, Radiation, r. DNA , Stem Cell 34
Project Timeline (from pending proposal or active award perspective) Past History Table Present Status Group Table Future Deliverables Table 35
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 36
History Table (cont. ) Types of events recorded n n n 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 37
Example History Records 38
Project Timeline (from pending proposal or active award perspective) Past History Table Present Status Group Table Future Deliverables Table 39
Deliverable Table Identifies what deliverables are required by sponsor n n Technical Fiscal Patent Equipment Final report due and sent date are always entered Interim dates may be entered 40
Example Deliverable Records 41
Award/Proposal Model Primary Detail Tables Investigator Department GCO Sponsor Program Name Award Proposal F & A Rates Distribution Status Group History Deliverable 42
Investigator Table Includes PI, Co. PIs, Key Persons and Research Fellow Only one PI per proposal/award Use Investigator Role to limit results to PI n Values are PI, CO, KP and FE Without PI limit, may return multiple records per project Data elements include name, netid, home department, college 43
Example Investigator Records 44
OSP Department Table Department administering the award. May be different than PI’s home department Use KFS Org Codes for department designation 45
KFS Org Codes in Dept Table Org in Sponsored DW maps to control account Org in KFS Three level rollup structure: n n n College (KFS Org Type C) Department (KFS Org Type D) Assigned Org (KFS Org Type D or S). w If Type D then Department and Assigned Org will be same value. Org usage rules are defined by colleges 46
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 47
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) - 01 AC - Academic (G) # 0127 - CALS Animal Science (D) - 01 B 7 - CALS AS Department Operations (S) ^ 01 B 8 - CALS AS Morrison/General Operations (S) * College # Department ^ Assigned Org on Sponsored Awards 48
Example Org Data Field Name A&EP Animal Science College Name College of Eng. CALS – Ag & Life Sci College Code 2300 0100 Department Name Applied & Eng. Physics CALS Animal Science Department Code 2371 0127 Org Name Applied & Eng. Physics CALS AS Morrison/Gen Ops Org Code 2371 01 B 8 Org Type D S 49
Sponsor Table Identifies primary sponsor of project Sponsors are stored in 3 level hierarchy n n DHHS/NIH/NIAID NYS/DEC Sponsor type (Fed Govt, Non-profit, Corporate) General contact information is sometimes available for small sponsors 50
Sponsor Table – Usage In reports display sponsor name, sponsor abbreviation or all levels field. In limit use Sponsor_Name_and_all_Levels with contains to maximize chance of match on either full name or abbreviation In limit use 3 -tier hierarchy levels for exact match on specific subsponsor (e. g. , Sponsor_Level_2 = “NIH” returns all NIH regardless of institute) Sponsor name/abbreviations may be different from what you use. Use the sponsor code lookup table to find match for setting limits. 51
Example Sponsor Data 52
Program Name Table Individual sponsor programs that have been designated for special tracking by OSP n n n Major Research Instrumentation (NSF) National Research Service Award (NIH) National Research Initiative (USDA) OSP tracks programs for multiple purposes including Limited Submissions and ARRA reporting 53
Award/Proposal Model Additional Detail Tables Investigator Department GCO Sponsor Program Name Award Proposal F & A Rates Distribution Status Group History Deliverable 54
GCO Table GCO = Grant and Contract Officer The official OSP contact for the project Provides full name, netid and phone for OSP contact See Sponsored Portal or staff responsibilities on OSP web site for Administrative Assistant contacts by department (http: //www. osp. cornell. edu/Contacts/) 55
Facilities Admin Cost Rate Table Includes one or more F&A rate per award Rate (as percentage) Budget year (1, 2, 3, etc) Function Location (on campus, off campus, Geneva) Type (endowed, contract college, NIAC) 56
Example F&A Records 57
Distribution Table List of who needs to be notified of any award changes Some distribution listings are automatic (e. g. PI and department contact(s)) Some distribution listings are added for specific awards as needed OSP can add to distribution list on an award or department level on request 58
Sponsored Subcontract Data Mart Overview 59
Subcontracts “When the portion of effort being performed by a third party constitutes a substantive component of the sponsored program, the third party is required to provide the resources and personnel necessary to conduct that portion of the work as an independent contractor. ” Office of Sponsored Programs Subcontracting Procedures (http: //www. osp. cornell. edu/Policies/sub-proc. html) 60
Subcontracts A subcontract is always associated with a single prime award Some prime awards have many subcontracts Subcontractors are usually other colleges, universities or research institutions Other subcontractors include government agencies and individuals Subcontract management is guided by OMB Circular A-21 61
Subcontract Model Prime Project Summary Subcontracto r Profile Subcontract History Status Group 62
Subcontract Table Subcontracts do not have proposals, but … Subcontracts may have one or more amendments. n n n Never more than one amendment with status “signed by all parties” Possible multiple pending amendments Superceded amendments are stored in an archive table (not yet available via published Brio models) Data elements include those entered in subcontract request plus others added by GCO 63
Active and Pending Subcontract Status Codes SIPC-In Process by Cornell SIN-In Negotiation SPC-Pending Compliances SS-Signed by Cornell SSAP-Signed by All Parties SPA-Pending Amendment 64
Closed Subcontract Status Codes SAC-Awaiting Closeout SC-Closed SNA-Not Accepted SNF-Not Funded 65
Subcontract Status Groups Because of the complexity and number of codes, OSP uses a number of special terms to refer to groups of statuses: “Pending Subcontract": (status of SIPC, SIN, SPC, SS or SPA) The department has requested a subcontract. The subcontract (or follow-on amendment) is in process. "Active Subcontract": (status of SSAP) The subcontract documents have been signed. The project work is underway. There may or may not be amendments under consideration. "Pending & Active Subcontract": (status of SIPC, SIN, SPC, SS, SPA or SSAP) The sponsors has indicated that they will fund the award or amendment. The award documents may or may not have been signed. 66
Subcontract Status Groups 67
Subcontract Status Groups All Subcontracts SNA Pending & Active Subcontracts SIN SNF SIPC Pending Subcontracts SS SPC SPA Active Subcontracts SSAP SAC SC 68
Subcontractor Profile Table Includes: Subcontractor name (2 levels) Contact information FDP indicator Source (Fed Govt, NYS, Corp, Non-profit) Various classifications (Small business, HBCU, veteran, individual) 69
Prime Project Summary Table Provides linkage to prime award information Contains most common prime award data elements that might be required for a query n n n Principal Investigator Department Sponsor source Prime amount and time period Prime status There is a single prime project record per subcontract. 70
Subcontract History Table Event code and description Date Comment Other event specific fields related to budget and time period changes There will usually be many events per subcontract 71
Summary Comments 72
Current Brio Models: Award/Proposal Subcontracts Key Performance Indicators 73
Summary Sponsored Data Warehouse contains basic proposal, award and subrecipient information Common source for both Sponsored Portal and Brio allows sophisticated (and simple) reports to be generated 74
Workshop Agenda 1. Introductions & Logistics 2. Review of Workshop Manual Contents 3. Data Overview (Power. Point) a) b) c) d) User Support & Communications Data Warehouse Overview Award/Proposal Data Mart Overview Subcontract Data Mart Overview Break 4. Hands-on intro to Brio, Sponsored standard reports and simple queries 75
Скачать презентацию SPONSORED PROJECTS BRIO TRAINING bri o n — vigor
278cdb489e56ccb2281e54850e263360.ppt