OIT - Data Warehouse and UMReports EFS Conversion News

Beginning the afternoon of Monday, June 30th, several tables will be unavailable while they undergo updates related to the EFS implementation. Backup versions of some of these tables will be available either in a permanent historical format or a temporary conversion backup table. New versions of tables will be released after they have been tested.

In addition to the tables being updated, some tables will become static between June 27th and July 1st. Many of these tables are fiscal year based, and their new counterparts will be created over the same time period. Others will be updated in Phase II, and a few do not have updated versions.

DWFS, DWIN, DWRA and DWUS will not be updated with new EFS data, and will become static on a staggered schedule over the next four months as CUFS updates come to an end.

Finally, some tables won’t change structurally, but will have new EFS data appear over the coming week.

Tables Being Updated

*Data in the updated PS_DWFA_ST_ASSISTANCE and PS_DWFA_FDN_SUMMARY may not be available until the end of July. The backups of these tables will be available after 7/3, and will contain previous fiscal year data through 2008.

Tables Becoming Static

New Tables

Tables with Changing Values

The following data groups will contain data through fiscal year 2008. Included below are expected last updates.

Data Groups that will Not be Updated with New PeopleSoft Data

Posted by iaco0018 at 9:48 AM | Permalink | Comments (0)

Data Groups that will Not be Updated with New PeopleSoft Data

Posted by iaco0018 at 8:19 AM | Permalink | Comments (0)

Chart of Accounts (COA)
A COA conversion table (CUFS to EFS) can be found in the Data Warehouse and is accessible to all users who previously had access to financial tables in the Data Warehouse. These tables are under the datagroup of DWFS. The table name is FS_C_COA_MAP_TBL and is available for immediate use. This table does not require a functional ID and is open to people with current access to DWFS tables.
If a financial user who does not currently have access to this table would like to request access to it, they need to fill out an access request form (ARF) through OIT Data Security, which can be found at http://www1.umn.edu/datasec/security/ARF_pdf.htm. The ARF along with the following attachment needs to be filled out, signed by the appropriate authorities, and submitted to Data Security: Financial DW Tables - DWFS (found under the Financial Systems & Data heading).
The COA conversion table will also be available in July within the EFS reporting instance.

Document Types

Below is a list of common FFN/CUFS document types, showing where these transactions will be handled in the new financial system.

To run a query of this table:
select * from FS_C_COA_MAP_TBL
where C_FUND = 'xxxx'
--AND C_AREA = 'xxx'
--AND C_ORG = 'XXXX'

Posted by iaco0018 at 9:03 AM | Permalink | Comments (4)

Updates to the tables scheduled to convert over the weekend of June 14th and 15th within the Data Warehouse are complete.

Updates to the following tables have been completed:
PS_DWPY_VAC_SICK
PS_DWHR_COMP_PROP
PS_DWHR_DEMO_ADDR
PS_DWHR_JOB
PS_DWHR_JOB_HIST
PS_DWHR_POSITION_DATA
PS_DWHR_EMP_REVIEW
PS_DWHR_TRAINEE
DWPA_POSTING
PS_DWPY_DIST - this table contains rows starting with fiscal year 2009

Tables refreshed either for the last time or until fall:
PS_DWPY_FRNG_DETAIL_08 - new table for fiscal year 09 will be created on 6/30
PS_DWEO_PERSON_PRIM_JOB - Next refresh with updates scheduled for August
PS_DWEO_PERSON_SEC_JOB - Next refresh with updates scheduled for August
PS_DWEO_PERSON_PRIM_JOB_HS - Next refresh with updates scheduled for October
PS_DWEO_PERSON_SEC_JOB_HS - Next refresh with updates scheduled for October

New tables:
PS_DWPY_DIST_HS - contains rows for fiscal years through 2008
PS_DW_DEPTID_FISCAL_TREE - Financial roll-up information
PS_DW_DEPTID_REPORT_TREE - This table creates two levels (college_admin_unit, vp_admin_unit) for reporting across campuses
PS_UM_DEPT_TREE_HIST - Contains historical versions of the PS_UM_DEPT_TREE
PS_VALID_COMBO_TBL - PeopleSoft table

New Lookup Tables:
FS_PS_GL_ACCOUNT_TBL
FS_PS_FUND_TBL
FS_PS_DEPT_TBL
FS_PS_PROJECT
FS_PS_PROGRAM_TBL
FS_PS_CHARTFIELD1_TBL
FS_PS_CHARTFIELD2_TBL
FS_PS_CF_ATTRIB_TBL

Note on new lookup tables: Because tables with the same name exist in both the new financial system and the hr/payroll/student system, for example PS_DEPT_TBL, tables from the financial system have been given an extra prefix of 'FS_'. These lookup tables are available within the Data Warehouse for use as lookup tables.

Posted by iaco0018 at 10:05 AM | Permalink | Comments (0)

On June 14, 2008, HRMS Job and Position tables converted to include the new 5-digit Department ID. Some Job data did not convert to the new 5-digit Department ID and will continue to only contain the Entity in the Department ID field.

Listed below are the exceptions that did not convert:

• Historic job and position rows did not convert
• Job data for inactive employee records did not convert (e.g. Terminations)
• Employees where the status as of 6/9/2008 is 'short work break' or 'suspend' did not convert. The people on 'short work break' will be assigned a new 5-digit Department ID at the time their 'return from break' status change is entered.
• Inactive positions as of 6/9/2008 did not convert.

For all job and position data that did not convert, the Entity value will be carried in the Entity and Department ID fields.

Because some data did not convert, we recommend that your queries be analyzed to determine if it is best to select based on Entity instead of Department ID, or a combination of Entity and Department ID, particularly if you need Short Work Break or Suspend job rows in your results.

Posted by iaco0018 at 7:00 AM | Permalink | Comments (0)

After 10pm on Sunday, several tables will be unavailable for up to 48 hours, while they undergo conversion to EFS format. During the outage, backup version of these tables will be available. We anticipate that the maximum amount of time a table may be down is 48 hours. The tables will be released after they have been tested.

Another set of tables will become static over the weekend, and will continue to be available in the Data Warehouse. Some of these will be updated during Phase II.

Finally, two new tables will be available starting on Monday following the conversion.

Posted by iaco0018 at 8:45 AM | Permalink | Comments (0)

A recap of the questions from yesterday's meeting on the upcoming EFS Conversion:

• Q: Is UM_ENTITY the same as CUFS_AREA or DEPTID? If so, why the duplicate?

  A: During conversion for the new financial system, only entries for active employees will be converted. For these individuals, UM_ENTITY will contain the 4-character data values that were previously carried in the DEPTID column. DEPTID will contain a 5-digit value matching the financial system. CUFS_AREA will be blank. Entries for non-active employees, e.g. terminated employees, will not be converted. For them, UM_ENTITY will be blank, DEPTID will be the old 4 character data value and CUFS_AREA will contain the old CUFS area value that corresponds to the 4 character DEPTID value.

• Q: Will AREA CLASS be replaced?

  A: Yes, by RRC. But, for FY 2008 and older, tables will still have AREA_CLASS. FY 2009 and newer will have RRC.

• Q: Is access to DWTEST confined to Functional IDs?

  A: No. You can request access to DWTEST via the ARF. You will be given the same access to data that you have in DWPROD. You will NOT be given access to data from the new financial system.

• Q: For people without Functional IDs: How can we combine payroll and financial info? Is it correct that the new reports won't detail people's expenses (i.e. HR and Financial data together)?

  A: Individuals without Functional IDS who want to combine data from the financial and payroll systems will have to download the information separately from the Financial Reporting Instance and the Data Warehouse and combine it in a local application such as Excel. For information that can be found in system provided reports, contact the EFS project.

  Q: How will those without Functional IDs get them?

  A: The process for securing a Functional ID is documented on the Data Security web page. Once the ID is secured, requests for access to the financial gateway views can be submitted to the EFS project. However, access to these views is restricted and requires significant justification. For most individuals, access to financial data will be through the reporting instance.

• Q: : Is there a DEPTID map table created?

  A: Yes, and you don't need a Functional ID to access. It will be available as one of the setup tables in the data warehouse.

• Q: Will a Functional ID guarantee direct access to financial tables?

  A: No. While a Functional ID is a requirement for access financial data via the gateway, it is not all that’s needed to get access. Requests for direct access will be evaluated on a case by case basis. They must demonstrate that local requirements cannot be met through the standard reporting instance access.

• Q: Can you get FY 2009 financial data without a Functional ID?

  A: Yes. You can access it through the reporting instance, just not through the data warehouse.

• Q: Can you clarify what data can be seen in DWPROD/DWTEST and the Reporting Instance as well as what type of ID you need to see it?

  A: In DWTEST you will be able to access historical and post financial implementation HR, Payroll and student data as we make modifications, as well as historical financial data from CUFS. In DWPROD you will be able to access historical and post financial implementation HR, Payroll and student data, as well as historical financial data from CUFS. Information from the new financial systems is and will be available from the Financial Reporting Instance. You use your normal data warehouse user ID for accessing these data.

  In DWTEST now and in DWPROD after 7/1, you will be able to access some financial data from the new system IF you request it and are approved. You will need a Functional ID to get this access. Your access will only be approved if you have a local application requiring financial data that must be loaded via an automatic process. This type of access is not to be used for ad hoc querying. Ad hoc querying of financial data must be done in the Financial Reporting Instance.

• Q: What's corollary to PS_UMN_DEPT_TREE?
  A: DEPT_ID_FISCAL_TREE

• Q: Are these posted dates (on the blog) the dates when the updates will actually happen in DWPROD?

  A: Yes.

• Q: So, how long will jobs be down?

  A: No more than two days (hopefully).
  

• Q: What will happen if our (an end user's) job runs before the table is released from testing (after conversion)?
  A: It will be unavailable until we're finished testing the table. Access to tables will be removed until they’ve been completely tested. You will get a table/view does not exist error if you try to use a table that has not had its access restored yet.

• Q: Where is the best place to find out the most current deployment/change dates?

  A: The blog.

• Q: How do you get access to other PPLSoft departments?

  A: Go to the access forms page.

• Q: How do you get new views, or customized views added to those available via the gateway?

  A: No formal process in place--contact Bill Decker.

• Q: Will the Academic Health Center's customized (user specific) views be updated as well?

  A: Yes.

• Q: Will the Sponsored Financials Reports on the web get updated after CUFS goes away?

  A: No. Go to EFS Reporting Instance for FY 2009 and use the reports that have been developed there.

• Q: Who's going to own UM Reporting? Who can I talk to about additional financial reporting after implementation?

  A: We don’t know for sure at this time. No owner or process is specified that we know of. Contact the project?

• Q: Will Lookup tables be available for the various CHARTFIELD values?

  A: Yes, these tables will be available as setup tables. It will also be possible to see current values for these CHARTFIELDs via data dictionary queries.

• Q: Many tables have been mapped--is a big Data Dictionary project in PPLSoft in the works?

  A: We don't know. Perhaps in the future. In the meantime, PSQUERY in financial reporting instance has some data dictionary capability, provided by PPLSoft.

• Q: Question about specific columns in Tuition.

  A: Look at DWTA specs on the blog.

  Posted by iaco0018 at 8:45 AM | Permalink | Comments (0)

Thursday, June 5th, 2008 Meeting Agenda

Following is the agenda for the June Analyst Group meeting that focuses on the upcoming EFS Conversion:

Agenda

1. Recap of impacted tables in the Data Warehouse and reports in UMReports
   1. Background
      1. Changing from CUFS to PS Financials
         1. Link to EFS: http://www.finsys.umn.edu/Overview.html
   2. Description of changes
      1. DW Financial Tables and Financial Reports will become static
         1. DW and UMReports are not going away.
            1. CUFS is going away – Some of the financial reporting in DW and UMReports
               1. http://www.finsys.umn.edu/Linked%20files/EFSTrifoldFactSheet.pdf
         2. DWFS, DWRA, DWUS, DWIN
            1. Will become static sometime around July 1st and after
         3. Financial Reports – Under Finances Category
            1. Will be marked as “Report data available through FY 2008 only�
            2. Example on dev site:
               1. https://www.umreports-dev.umn.edu/
      2. Changes coming through to DW are based on changes to HCM
         1. Link to list of all impacted DW tables:
            1. http://blog.lib.umn.edu/joh02855/dwconversionnews/2008/04/
               update_phases_of_conversion.html
         2. Payroll, HR, Student
            1. HR Changes
               1. Link to DWHN:
                  1. http://blog.lib.umn.edu/joh02855/dwconversionnews/dw_table_specs/
                     phase_i/dwhn/
            2. Payroll changes
               1. Link to DWPY:
                  1. http://blog.lib.umn.edu/joh02855/dwconversionnews/dw_table_specs/
                     phase_i/dwpy/
            3. Student Changes
               1. Link to DWSF:
                  1. http://blog.lib.umn.edu/joh02855/dwconversionnews/dw_table_specs/
                     phase_i/dwsf/
               2. Link to DWFA
                  1. http://blog.lib.umn.edu/joh02855/dwconversionnews/dw_table_specs/
                     phase_i/dwfa/
         3. DWTEST
            1. Same access as DWPROD
            2. Web Query tool
               1. https://dw-test.ims.umn.edu
               2. Requires M-Key
            3. Development Server - do not assume what you are looking at is always correct. Best to confirm by checking the specs.
      3. Reports
         1. Report Site has information on message board
            1. Link to UMReports:
               1. https://www.umreports.umn.edu
            2. Also on blog
               1. Link to blog section:
                  1. http://blog.lib.umn.edu/joh02855/dwconversionnews/reports/
2. Where to find Financial Information after conversion
   1. EFS Reporting Instance and how to get access
      1. Development instance
         1. Contact Bill Decker
         2. Not sure they are creating new users at this time
         3. Link to test instance:
            1. http://www1.umn.edu/cco/
      2. Production
         1. Data Security website:
            1. http://www1.umn.edu/datasec/security/ARF_word.htm
         2. Access Form:
            1. http://www1.umn.edu/datasec/security/documents
               /2A-EFSReportingAccess.doc
         3. Available July 1st
   2. Access to EFS table views
      1. Gateway through the DW:
         1. https://dw.umn.edu/efsupdates/updates/docs/DBI_solution.doc
      2. Functional ID’s only
         1. Two roles
            1. ORAFSQ – Standard access
            2. ORAFSQHIP – HIPAA access
      3. Access forms available on Data Security Website
         1. Link to Data Security Site
            1. http://www1.umn.edu/datasec/security/Requestingaccess.html
         2. Link to Access Form
            1. http://www1.umn.edu/datasec/security/documents
               /2A-EFSGatewayService.doc
      4. Reference Document
         1. Commonly used tables document:
            1. https://dw.umn.edu/efsupdates/updates/docs
               /PPLsoft_Commonly_Used_Tables.xls
   3. Financial Reports that used to be in FSS and UMReports
      1. Link to Reporting on EFS site with information on new system:
         1. http://www.finsys.umn.edu/Reporting.html
3. Deployment Schedule
   1. Summary list of Major dates changes will occur in the HR/Payroll/Student and EFS PS systems (Not Comprehensive of all changes)
      1. June 14/15 - HR
      2. June 28/29 – Payroll
      3. June 30 – Student Financials
      4. July 1st – EFS goes live
      5. July 18 – Acad_Org updates
   2. How this impacts the deployment of Data Warehouse tables and UMReports
      1. Tables
         1. Staggered approach has presented challenges
            1. The Data Warehouse tables have a lot of interdependencies
            2. Two options
               1. Freeze DW for 5 weeks
               2. Do our best to release changes as they occur
                  1. We chose the second option.
         2. Link to blog posting:
            1. http://blog.lib.umn.edu/joh02855/dwconversionnews/deployment/
         3. Link to Calendar:
            1. https://dw.umn.edu/efsupdates/updates/dw/ConversionRollout.doc
      2. UMReports
         1. Same interdependencies
         2. Link to blog posting on Report Migration Dates
            1. http://blog.lib.umn.edu/joh02855/dwconversionnews/reports/
4. Where to find information going forward
   1. Using blog to post specs and updates
   2. Use the various listserv
   3. E-mail me, Benjamin P. Johnson Cashen at joh02855@umn.edu
   4. Remember, all dates are subject to change
5. Q&A Session

Posted by iaco0018 at 1:04 PM | Permalink | Comments (0)

Beginning June 16th, 2008, IMS will update UM Reports with the new values needed from the Enterprise Financial System (EFS). Reports will be updated on the following schedule. The schedule is tentative and may be subject to change based on EFS conversion.

Updates:
- HR – conversion complete
- Payroll - beginning June 30th (with the exception of the Pre-Payroll Distributions and Employees with Change of Status in Pay Period reports, which were converted the week of June 16th and the Current Month YTD Encumbrance Payroll Summary scheduled to be converted the beginning of August)
- Student Finance - beginning July 2nd
- Financial Aid and Work Study – beginning July 7th
- Student and Instruction - beginning July 17th
- Tuition Allocation – beginning August 18th

Individual reports will be listed on the UM Reports homepage as they are converted, and will be unavailable while the updates occur.
Once conversion is complete, we will post a message when reports are available for use.

Posted by iaco0018 at 8:51 AM | Permalink | Comments (1)

Please use DWTEST to access updated tables.

DWTEST can be accessed by using the following link:
http://dw-test.ims.umn.edu
Please note this is a development environment and all functionality of the web query tool is not guaranteed. For the best access to DWTEST please consider using a tool like SQL Developer or Toad.

Posted by iaco0018 at 7:58 AM | Permalink | Comments (0)