The PennERA Proposals Data Collection is a subset of the University Data Warehouse containing data from the Proposal Tracking module of PennERA, the University's Electronic Research Administration. The PennERA Proposals data collection contains current and historic information about sponsored projects (e.g., grants and contracts) at PENN.
For a brief overview of the collection, see PennERA Proposals Data Collection Quick Reference.
Tables are listed in alphabetical order. Select Table to obtain the explantion, common uses, primary keys, indexed data elements, and related tables for a table. Select Elements to obtain the definition, list of values, formats, and not null values for each data element in the table. A description of table and data element terminology is in the document What the Warehouse Contains.
Looking for a specific element or caution? Try the searching the collection documentation for particular keywords (search page will open in a new tab).
| PENNERA_FUND_ADJ Table Elements |
PENNERA_PROPOSAL_INVESTIGATOR Table Elements |
| PENNERA_INCREMENT Table Elements |
PENNERA_PROPOSAL_PARENT Table Elements |
| PENNERA_INCR_STAT_HIST Table Elements |
PENNERA_PROP_REGULATORY_APPR Table Elements |
| PENNERA_PEOPLE Table Elements |
PENNERA_PROP_STAT_HIST Table Elements |
| PENNERA_PERIOD Table Elements |
PENNERA_REQUEST Table Elements |
| PENNERA_PERIOD_STAT_HIST Table Elements |
PENNERA_REQ_STAT_HIST Table Elements |
| PENNERA_PROPOSAL Table Elements |
PENNERA_SECURITY Table Elements |
| PENNERA_PROPOSAL_ACTIVITY Table Elements |
PENNERA_SPONSOR Table Elements |
| PENNERA_PROPOSAL_INTL_LOCATION Table Elements |
|
| Snapshot versions of some tables are also available. | |
| General Ledger data included in one or more of the BusinessObjects Universes accessing the PennERA Proposals Collection: | |
| BALANCES Table Elements |
FUND_SFS_CATEGORY_CODES Table Elements |
| CENTER_REF_CODES Table Elements |
FUND_SPONSORS* Table Elements *Sponsor Code Conversion notes |
| CNAC_CODES Table Elements |
GL_PERIODS Table Elements |
| FUND_BILLING_FORMAT_CODES Table Elements |
OBJECT_CODES Table Elements |
| FUND_BILLING_FREQ_CODES Table Elements |
ORG_CODES Table Elements |
| FUND_CODES Table Elements |
PARENT_CNAC_CODES Table Elements |
| FUND_FINAL_RPT_CODES Table Elements |
PARENT_FUND_CODES Table Elements |
| FUND_INVESTMENT_CODES Table Elements |
PARENT_ORG_CODES Table Elements |
| FUND_LOC_RPTG_CODES Table Elements |
PARENT_PROGRAM_CODES Table Elements |
| FUND_RECLASS_CODES Table Elements |
PROGRAM_CODES Table Elements |
| FUND_RPT_FORMAT_CODES Table Elements |
PURPOSE_CODES Table Elements |
| FUND_RPT_FREQ_CODES Table Elements |
RCM_OBJECT_TREE Table Elements |
| Salary Management data included in the BusinessObjects Universes accessing the PennERA Proposals Collection: | |
| EMPLOYEE_GENERAL Table Elements |
PAYROLL_ACTION_CODE Table Elements |
| JOB_CLASS_GENERAL Table Elements |
|
| Human Subjects Protocols data included in one or more of the BusinessObjects Universes accessing the PennERA Proposals Collection: |
| HS_PROTOCOL_IRB_V Table Elements |
The PennERA Proposals data collection tables that store current data are refreshed nightly in the Data Warehouse, Monday through Friday, following the completion of PT4010J, the job that feeds BEN Financials data to PennERA. The "nightly" jobs that refresh the PennERA Proposals tables actually run in the early morning, Tuesday through Saturday, and are usually finished by the start of business. These jobs completely replace the contents of the PennERA Proposals tables that store current data. Note that the tables storing current data are not available for querying while the nightly jobs are in progress. To see whether the jobs are in progress, check the Research Data section of the Data Warehouse Update/Load Status page.
To support longitudinal studies, a snapshot of the tables in the PennERA Proposals data collection is taken annually. The snapshot also includes many of the reference tables for the Balances table (in the General Ledger data collection). A snapshot is identified by the snapshot fiscal year, which is the latest closed fiscal year as of the date when the snapshot job was run. The snapshot job does not change the data for earlier snapshots; it simply adds the data for the new snapshot. It refreshes the "snapshot" version of a given table by appending a complete copy of the "current data" version of the table. The snapshot job is not automatically scheduled, but is run upon Data Administration's request--usually in December or January. The snapshot tables are not available for querying while the snapshot job is in progress.
Source of the Data
The PennERA Proposals current data in the Data Warehouse (accessible via the BusinessObjects PennERA Proposals Universe) comes from the Proposal Tracking module of PennERA, the University’s electronic research administration system (in use since October 14, 2003). The Office of Research Services uses Proposal Tracking to track pre- and post-award information on sponsored projects (such as grants and contracts).
The PennERA Proposals snapshot data in the Data Warehouse (accessible via the BusinessObjects PennERA Proposals Snapshots Universe) comes from the tables in the Data Warehouse that store current data for the PennERA Proposals and General Ledger data collections.
Time Span Covered
The data accessible via the BusinessObjects PennERA Proposals Universe includes records for currently active sponsored projects, as well as historical data going as far back as approximately 1982. (Much of the historical data was converted from the legacy system (RSS). Historical data from before 1982 is spotty.) However, the data on administrative activities (stored in the PENNERA_PROPOSAL_ACTIVITY table) does not go back that far. Data is available only for activities whose effective date is Jan. 22, 2008 or later.
The data accessible via the BusinessObjects PennERA Proposals Snapshots Universe includes data for snapshot fiscal year 2004 and thereafter. A snapshot is a static copy of all of the records that were in the source tables when the snapshot job was run. A snapshot is identified by the snapshot fiscal year, which is the latest closed fiscal year as of the date when the snapshot job was run. (The University's fiscal year runs from July 1 through June 30.) For example, in December, 2005, the data for snapshot fiscal year 2005 was loaded in the Warehouse. (By December, 2005, for most of the proposals submitted during fiscal year 2005, the sponsors had let the University know whether they had awarded funding or not.)
The PennERA Proposals collection is described in the PennERA Proposals and PennERA Proposals Snapshots diagrams. Each diagram illustrates the architecture of a specific BusinessObjects Universe, displaying the tables that are included and the join criteria between tables. A table or BusinessObjects Class is shown in the diagram as a box; a join is shown as a line between the boxes for the joined tables. Click on the box for a table to see the data documentation for that table.
The PennERA Proposals diagram illustrates the architecture of the BusinessObjects Universe that is usually used to access the PennERA Proposals data collection, because it accesses the data that is refreshed nightly. Reference tables from the Salary Management data collection are included, to provide further information on PennERA people who are also University employees. Also included are reference tables from the General Ledger and Space@Penn data collections, to supply org. and school/center names, and to provide further information on the segments of the increment's award GL account and on the proposal's prime fund. See Notes on the PennERA Proposals diagram for an explanation of the blue labels next to the boxes and for other information about the diagram. If you choose to print the diagram, landscape orientation and letter-sized paper (8.5 by 11 inches) are recommended. Note: there is one PennERA Proposals table that is refreshed nightly, but is not included in the PennERA Proposals Universe: PENNERA_FUND_ADJ. It is available in the PennERA Balances Universe, which is a cross-collection Universe.
The PennERA Proposals Snapshots diagram illustrates the architecture of the BusinessObjects Universe that is designed to support longitudinal studies, because it accesses the annual snapshots of the PennERA Proposals data collection. This Universe also accesses Balances and GL Periods (in the General Ledger data collection), the annual snapshots of many of the reference tables for Balances, and the relevant monthly snapshots of Employee General and Job Class General (from the Salary Management data collection). The tables from the General Ledger and Salary Management data collections are shown in the diagram as boxes with grey backgrounds. See Notes on the PennERA Proposals Snapshots diagram for an explanation of the asterisks and for more information about the diagram. If you choose to print the diagram, specify landscape orientation, and legal-sized paper (8.5 by 14 inches).
Self-Serve, Online Training Options
Knowledge Link: Introduction to the PennERA Proposals Data Collection and Using the PennERA Proposals Universe (optional)
This course will orient you to PennERA Proposals data, its organization, and refresh cycle as well as help you to use SAP BusinessObjects (InfoView & Webi) to
- Find the available corporate (or "canned") reports
- Create your own queries using result objects and filters to control which data elements and records are retrieved
- Select the correct tables (classes) and objects (data elements) to include in your own specific queries
After taking this course, you will also know where to turn for answers and additional help with the PennERA Proposals Data Collection.
Website: PennERA Query Building Scenarios (optional)
These scenarios are excerpted from the Knowledge Link course. They present common business questions involving PennERA Proposal data and demonstrate how to create a query/report to answer the question. Decision points throughout the process are explained so that the viewer understands why certain data objects were chosen for the query.
See how to build a query from a simple business question in a short Flash presentation.Print the content from each attached PDF if you prefer hard copy.
- Which proposals submitted over a year ago are still awaiting a final decision from the sponsor? (Flash Video)
- To what accounts can costs for my projects be charged? (Flash Video)
- Which of my awarded projects involve cost sharing? (Flash Video)
- What is the status of the regulatory approvals for my outstanding proposals? (Flash Video)
- What is the expected funding from the NIH for Dr. "Marie Curie" this fiscal year? (Flash Video)
Classroom Activities/Office Hours
Occasionally classroom activities and 'office hours' concerning the PennERA Proposals Data Collection will be announced and offered. It is recommended to take advantage of the online training opportunities since they are always available at your convenience.
Please contact Enterprise Information & Analytics for assistance with specific training questions. You can also refer to the table and element documentation for information and cautions about specific data. The following resources may also be of help:
- Questions to ask before writing Proposals queries
- PennERA Upgrade to version B10 - Please refer to the this page for information on the effects to the Warehouse of the April 2005 PennERA B10 upgrade, and for pointers on revising queries that involve period status information.
- Information on how the PennERA Proposals Data Collection differs from its predecessor, the Sponsored Projects Data Collection. Note: The data in the Sponsored Projects Data Collection has not been refreshed since the fall of 2003.
Those who wish to access the PennERA Proposals data collection must submit the Data Warehouse Access Request eForm for PennERA Proposals data. (Note: Those who had access to the Sponsored Projects data collection were automatically given access to the PennERA Proposals data collection when it became available.) Those who have access to BEN Financials will be given similar access to PennERA Proposals. (For example, someone with access to Cancer Biology, Org. # 4130 in BEN Financials will be given access to PennERA Proposals data for Cancer Biology.) Those who do not have access to BEN Financials must specify the level of access they require.
Releasing Data Outside the University
Be cautious about releasing PennERA Proposals data outside the University. If you do not customarily release data outside Penn, and you receive a request for data to be sent outside the University, contact the Office of Research Services (898-7293) or Institutional Research (898-5897).
Releasing Data Within the University
Within the University, PennERA Proposals data may be disclosed only if it is needed to do the business of the University, and only to those who need to know the information in order to do their jobs. If you are not sure whether to fulfill a request for PennERA Proposals data, call the Office of Research Services (898-7293).
Query Results
If you save your query results in Excel, pdf, or any other local file type, you must see to it that any sensitive data stored on your peronal computer is safeguarded through physical security, access control software, or encryption. When a computer is left signed to an account, it is easy for someone to gain unauthorized access. Either sign off from your account before you leave your computer or restrict access by some other means (physical security or access control software).
For more information on security and privacy, contact the Office of Information Security.
Overview. PennERA Proposals data is complex, and a listserv is available to help you query the Warehouse and retrieve PennERA Proposals data efficiently and effectively.
Subscription to the listserv. When you are granted access to the PennERA Proposals data in the Warehouse, a subscription is automatically set up for you to participate in an E-mail listserv called SPONPROJ-WH@LISTS.UPENN.EDU. (Those with subscriptions to the list are called list members. If any member sends an E-mail note to the list, every member of the list receives a copy.)
When you have a question. If you have a question about the PennERA Proposals data, please send E-mail to the list -- and if you have the answer to a list member's question, please E-mail your reply to the list so all the members can benefit from the discussion.
The list is also the place to send E-mail if you notice that some documentation needs updating. For documentation published on the Web, please specify the URL for the documentation that needs to be updated. For BusinessObjects documentation (help for Objects), please specify the Universe, Class, and Object.