Data Warehouse (DWEF): Enhancements and Updates Archive

October 2012

The following tables have been added to DWEF.

FS_PS_PO_CHNG_SHIP – Purchase Order Shipping Change
FS_PS_GM_PROP_VNDR – Grants Proposal Vendors List
FS_PS_GM_PROP_VNDR_DE – Grants Proposal Vendor Deliver
FS_PS_GM_PROP_VNDR_TE – Grants Proposal Vendor Terms
FS_PS_GM_VNDR_BUD – Grants Proposal Vendor Budget Item
FS_PS_GM_AWD_PRJ_VNDR – Award Subrecipients
FS_PS_FM_AWD_VNDR_BUD – Award Subrecipients Budget
FS_PS_TEAM_MEMBERS – Sales Team Members
FS_PS_MEMBER_PERSON – Support Team Member Table

Summer/ Fall 2011

Below are summary and transaction data views added to DWEF in the data warehouse during the summer/fall of 2011. What is a data warehouse? The University's data warehouse is a collection of data that can be used to support the University's operational and decision making processes. The data is organized by subject area. Financial data is pulled from the enterprise financial system (EFS, a.k.a., PeopleSoft Financials) and placed in the repository known as "DWEF". DWEF is simply label used for data in the data warehouse from enterprise financials.

Quick Links

Financial Reporting Home
About
Initiatives
Reporting Tools
Job Aids and Training Materials


Have an idea or suggestion for financial reporting?

Tell us by filling out this form to provide your ideas, needs, or suggestions. Business process owners review submissions regularly. This link is only for feedback related to Financial Reporting within the University of Minnesota.

Table Name View Description Process that populates view

UMR_TRAN_BD_VW

Transaction Detail – Budget
Sponsored and non-sponsored budget data by year, period, date.

UMFGLAE012 (App Engine)

UMR_TRAN_ACT_VW

Transaction Detail Table – Actual
Sponsored and non-sponsored transaction detail. Shows all transactions from sub modules that have successfully posted to the ledger.

UMFGLAE020 (App Engine)

UMR_TRAN_ENC_VW

Transaction Detail – Encumbrance
Sponsored and non-sponsored encumbrance and pre-encumbrance detail from the commitment control (KK) activity log.

UMFGLAE011 (App Engine)

UMR_SUMACCTPD

(9/14/11 updated from
incorrect
"UMR_SUMACCTPDVW".)

Sponsored/Non-Sponsored Summary Table by Accounting Period
Sponsored and non-sponsored detail summarized at ChartField string by accounting period. This view will have totals for budget data, encumbrance, and pre encumbrance at the same level.

UMFGLAE021 (App Engine)

UMR_SUMACCTDT

(9/14/11 updated from
incorrect
"UMR_SUMACCTDTVW".)

Sponsored/Non-Sponsored Summary Table by Journal Date
Sponsored and non-sponsored detail summarized at ChartField string by journal date and/or KK_TRAN_DT. This view will have totals for actuals, encumbrances, and pre-encumbrances at the same level.

UMFGLAE021 (App Engine)

UMR_FYAP_PROMPT

Fiscal Year
Contains all fiscal year accounting periods that exist on the UMR_SUMACCTPDVW. Used for the Fiscal Year and Accounting Period prompts for reports that use these prompts. Distinct fiscal year and accounting periods for accounting periods where activity exists on the Summary by Accounting Period table. Will be used by the UM reports as the Fiscal Year/Accounting Period prompt.

UMFGLAE021 App Engine)

UMR_FUND_ALL_VW
UMR_FUND_CUR_VW
UMR_FUND_FY_VW

Fund View, flattened ChartField table
Fund ChartField details including tree and attribute values. The UM_FUND_CATEGORY tree will be flattened out in this table. Attributes shown include: Federal, GASB39, State of MN, LOC, CFDA

FSUMPRT Clone Process

UMR_DEPT_ALL_VW
UMR_DEPT_CUR_VW
UMR_DEPT_FY_VW

DeptID View, flattened ChartField table
Flattened table view for department. This will aid when reporting/querying for approvals and hierarchy rollups. Department ID ChartField details including tree and attribute values. Attributes shown include: 

  • UM_AP_DEPTALTAPPR1, alternate voucher approver less than $10,000
  • UM_AP_DEPTALTAPPR2, alternate voucher approver greater than $10,000
  • UM_AP_DEPTAPPR1, primary voucher approver less than $10,000
  • UM_AP_DEPTAPPR2, primary voucher approver greater than $10,000
  • UM_CERT_ALT_APPR, alternate certified approver (should match UM_CERT_APP_ALT, two alternate CA roles were necessary to meet system needs)
  • UM_CERT_APPROVER, primary certified approver
  • UM_CERT_APP_ALT, alternate certified approver (should match UM_CERT_ALT_APPR, two alternate CA roles were necessary to meet system needs)
  • UM_EX_DEPT_ALTAPP, alternate travel & expenses approver
  • UM_EX_DEPT_APP, primary travel & expenses approver
  • UM_FGL_DEPT_SFR_AC, this role is currently not used
  • UM_GL_DEPTALTAPPR1, alternate journal entry approver
  • UM_GL_DEPTAPPR1, primary journal entry approver
  • UM_PV_DEPT1_ALTAPP, alternate requisition approver less than $10,000
  • UM_PV_DEPT1_APP, primary requisition approver greater than $10,000
  • UM_PV_DEPT2_ALTAPP, alternate requisition approver greater than $10,000
  • UM_PV_DEPT2_APP, primary requisition approver greater than $10,000
  • UM_SA_DEPTID

Tree details include: UM_DEPTID_BUDGET, UM_DEPTID_FISCAL (Campus node only).

FSUMPRT Clone Process

UMR_PGM_ALL_VW
UMR_PGM_CUR_VW
UMR_PGM_FY_VW

Program View, flattened ChartField table
Flattened table view for Program. Program ChartField details including related Function.

FSUMPRT Clone Process

UMR_PROJ_ALL_VW

Project View, flattened ChartField table
Flattened table view for Project. Project ChartField details including related Function.

FSUMPRT Clone Process

UMR_PROJECT_VW

Project View
Contains high level Project and Contract information taken from the Project Header (PROJECT), Project Activities (PROJ_ACTIVITY) and view of Projects by Award (GM_AWD_PROJT_VW) tables. This view is keyed off Unit, Project ID, Activity ID, and Contract Number. In addition, the following data will be shown: Project Status, Project Description, Project Primary Flag, Start Date, End Date, Project PI EmplID, Project PI Name, Facilities Admin Rate, FA Base ID, and FA Funded Rate %.

This view will only show project activity with an activity_id equal to 1. All other activity IDs are invalid. 

FSUMPRT Clone Process

UMR_ACCT_ALL_VW
UMR_ ACCT_CUR_VW
UMR_ACCT_FY_VW

Account View, flattened ChartField table
Flattened table view for Account. Account ChartField details including tree details for CONTROL_BD_ACCTS, UM_ACCOUNT_CYFWD and UM_ACCOUNT_SFR trees.

FSUMPRT Clone Process

UMR_CF1_ALL_VW
UMR_CF1_CUR_VW
UMR_CF1_FY_VW

ChartField 1 View, flattened ChartField table
ChartField1 detail values.

FSUMPRT Clone Process

UMR_CF2_ALL_VW
UMR_CF2_CUR_VW
UMR_CF2_FY_VW

ChartField 2 View, flattened ChartField table
ChartField2 detail values.

FSUMPRT Clone Process

UMR_CF3_ALL_VW
UMR_CF3_CUR_VW
UMR_CF3_FY_VW

ChartField 3 (FinEmplID) View, flattened ChartField table
ChartField3 detail values. Also called the "FinEmplID".

FSUMPRT Clone Process

UMR_PROPOSAL_VW

Proposal View
Contains high level proposal information from the Grants Proposal (GM_PROPOSAL), Proposal Project (GM_PROP_PROJ), and Proposal Professionals (GM_PROP_PROF) tables. Data keys: Business Unit, Proposal ID, Version ID, Sub Proposal Number, and Row Number.

This view shows high level of proposal information taken from the Grants Proposal (GM_PROPOSAL) and Proposal Project (GM_PROP_PROJ), and Proposal Professionals (GM_PROP_PROF) tables. This view is keyed off BUSINESS_UNIT, PROPOSAL_ID, VERSION_ID, SUB_PROP_NBR, and ROW_NUMBER. 

In addition the following data will be shown: EMPLID, UM_EMPLID_NAME, CUST_ID, UM_SPONS_NA1, PROPOSAL_TYPE, PROPOSAL_STATUS_GR, CREATION_DT, BEGIN_DT, END_DT, UM_PROPOSAL_DESCR, DIRECT_PERS_COST, DIRECT_NONPER_COST, TOTAL_FA_COST, TOTAL_BUDGET_AMT, PPSL_TO_CNTRCT_FLG, UM_PPSL_DESCR, PROJECT_ID, PRIMARY_FLAG, DEPTID, UM_PROF_EMPLID, UM_PROF_NAME, and PROF_ROLE_TYPE.

Due to the relationship between the three tables in this view multiple rows will exist for each proposal. The view will have an outer join on GM_PROP_PROF as not all proposals have values on this table. Also there is an outer join on Personal Data for the name look up on GM_PROP_PROF as not all EmplIDs are valid on this record. 

FSUMPRT Clone Process

UMR_CON_AWD_VW

Contract View
This view shows high level of contract to award information taken from the Award Parent (GM_AWARD) and Contract Header (CA_CONTR_HDR) tables.  This view is keyed off Business Unit and Contract Number. In addition, the following data will be shown: Reference Award Number, Begin Date, End Date, Award Status, EMPLID and Name, Sponsor Customer ID and Name, Contract Description, CFDA, Contract Status, and Contract Type.

FSUMPRT Clone Process

UMR_AWD_FDBP_VW

Award View
Contains all contracts and projects and their associated funding periods for both active and inactive projects. This view shows the following data types: Contract Number, Business Unit, Project ID, Budget Period GM, Reference Award Number, Begin Date, End Date, and Effective Status.

FSUMPRT Clone Process

UMR_PI_COPI_VW

PI Project View
This view shows a list of all projects that an EmplID is associated to as a PI or Co PI. This view shows EmplID, OprID, Name, Project Role (PI or Co PI), Project ID and Project Status.

FSUMPRT Clone Process

UMR_ACCT_DT_VW

This table will be keyed by accounting date and will assign a corresponding fiscal year, accounting period, and fiscal quarter to every day. 

FSUMPRT Clone Process

UMR_HRACTGLN_VW

(9/14/11 updated from
incorrect
"UM_HRACTGLN_EXT".)

HR Accounting Line Table
Sibling to the PS_HR_ACCTG_LINE table. This table holds the combo code, sequence and line number, and commitment control amounts related to an employee. This extension table will allow the reports to map employees to journal ids on the HR_ACCTG_LINE table.
 
PS_UM_CF_GROUP_FLD
PS_UM_CF_GROUP_REL
PS_UM_CF_GROUP_TBL
PS_UM_CF_INTER_ID
ChartField Mapping Tables
These tables store the user entered ChartField to Internet ID associations. These tables are the driving tables for the new PI Reports. The information stored in these tables reduces the amount of entry required for executing the PI reports.
 

Data models for the new views


UMR_TRAN_ACT_VW

UMR_TRAN_ACT_VW


UMR_TRAN_ENC_VW

UMR_TRAN_ENC_VW


UMR_TRAN_BD_VW and UMR_SUMACCTDT

UMR_TRAN_BD_VW and UMR_SUMACCTDTVW


UMR_SUMACCTPD and UMR_FYAP_PROMPT

UMR_SUMACCTDTVW


ChartField Attribute and Tree Views


UMACCT_ALL_VW and UMR_FUND_ALL_VW

UMACCT_ALL_VW and UMR_FUND_ALL_VW


UMR_DEPT_ALL_VW

UMR_DEPT_ALL_VW


UMR_CF1_ALL_VW, UMR_CF2_ALL_VW, UMR_CF3_ALL_VW, UMR_PROJ_ALL_VW, UMR_PROG_ALL_VW

UMR_CF1_ALL_VW, UMR_CF2_ALL_VW, UMR_CF3_ALL_VW, UMR_PROJ_ALL_VW, UMR_PROG_ALL_VW


Proposal Contract and Project Views


UMR_PROPOSAL_VW, UMR_PI_COPI_VW, and UMR_CON_AWD_VW

UMR_PROPOSAL_VW, UMR_PI_COPI_VW, and UMR_CON_AWD_VW


UMR_PROJECT_VW and UMR_AWD_FDBP_VW

UMR_PROJECT_VW and UMR_AWD_FDBP_VW


Links

Go to the data warehouse to access DWEF. Requires two-factor login. Request access using this form (pdf). Contact the University Financial Helpline 612-624-1617 or controller@umn.edu for assistance with form.

Go to UM Reports. Requires Internet ID/password login; all employees have access though student workers may need a sponsored ID in order to access the site. U departments can request a sponsored ID here. There is a fee involved for sponsored Internet accounts. Contact 1-HELP for assistance or information on sponsored Internet accounts.

Go to the main reporting information page on Finsys.