HelpdeskDocsTemplate/logo.png HelpdeskDocsTemplate/UTS.png

Mass Data Update Utility (MDUU)

The Mass Data Update Utility is a business tool that institutions can use to quickly and easily develop batch updating processes. That is, the Mass Data Update Utility allows institutions to create their own rules (based on SQL scripts) that can be used to update and insert data for circumstances that are not part of the current Banner functionality.

Warning! The Mass Data Update Utility allows you to easily create processes for modifying data. It is recommended, therefore, that you perform the same thorough testing in a non-production environment that would be needed for any other software development.

Users must understand the principles behind the Mass Data Update Utility, especially when using the auto-populate functionality. It is recommended that you thoroughly test functionality before committing to auto-populating a table.

References/Required Reading

Banner Mass Data Update Utility Handbook

8.2 Article 000042042 – build a popsel from an external file

1-YA8BD3: Mass Data update utility (MDUU) Quick Reference guide overview

MDUU Terminology:

Process – the actual ‘job’. The process is a collection of tasks that need to be run.

Task – a step of the process.

Activity – The processing unit that contains the business logic. Can be used to select data and insert or update data in a single target table.

The documentation uses a tree metaphor. The MDUU is comprised as:

  • A single root node (process)
  • One or more branches (tasks)
  • One or more leaf-nodes (activities) for each branch

The MDUU job will need all three elements (process, task, activity) in order to be considered valid.

To create a MDUU

1) Create process code – GKVSQPR

  • *Name should start with module/system code
    • Example: R_GLAEXTR_LOAD

2) Create task code(s) - GKVPTAS

  • *Name should end in _T
    • Example: R_GLAEXTR_LOAD_T Task codes need to be associated with the process code.

3) Create activity codes - GKVPACT

  • *Name should end in _A
    • Example: GLAEXTR_LOAD_A Activity codes are associated with the process code.

4) Optional – create any parameter codes –

  • a) Create parameter code on GKVSQPA
  • b) Associate parameter codes with process code – GKRSQPA
    • Enter process code, next block, and then enter parameters for the process.

5) Optional – for importing data from external files

  • a) Create CSV file - Optional
    • Identify needed columns Do not use column headers. Migrate file to the appropriate dataload directory.
      • Note: You need access to the specific dataload directory you are using.
    b) Define file on GKAPEXS
    • Enter process code, next block, click on Import tab, and then enter parameters for the process.
    • External Table Name: File will be loaded to this table, to be used in activities.
      • Naming standards is first letter of table needs to begin like this >>

      • 'A' then belongs to ALUMNI
      • 'F' then belongs to FINANCE
      • 'G' then belongs to UGRADADM
      • 'P' then belongs to PAYROLL
      • 'R' then belongs to FINAID
      • 'S' then belongs to STUDENT
      • 'T' then belongs to ARSYS
      • 'U' then belongs to UTS
      • Directory Name: Dataload directory
        • File name: name of file (case sensitive)
          • Record Delimiter: NEWLINE (Leave the default)
        • Fields: Define column names and lengths on the created table.
          • * Note: add one to the last column. The process is picking up a hidden character
      • Field Delimiter: ,

GKAPEXS.png

Click on Register File button.

  • Two files could be generated in the specific department's dataload directory you used in 5b.
    • .bad – shows rows in file that couldn’t be loaded. (My experience, the field length was too short)
    • .log – shows results of the load (and past loads).

The file contents will be loaded in a table indicated in the External Table Name field. In this case, R_GLBLOAD.

6) Create necessary synonyms on GKVPSYN for any needed table.

7) Build Activity - GKAPACT

  • The activities define the processes behavior. You will be creating the SQL for the data updates.
  • You need an understanding of Banner Table structure and relationships.
    • ***Identify and understand any and all Table constraints***
  • A) Source Tables
    • Enter table names that contain your data.
  • .B) Target Table
    • Name of table you want to change (insert/update/delete data).
    • Use the dropdown list. You will get errors if the name and comments do not match exactly.
  • C) Target Columns.
    • Use the select buttons to enter name of columns used in process.
    • Do not free-type. Any slight difference between what you entered and the validation will be rejected.
    • Any valid subset of columns from the target table can be populated
  • D) Select Parts
    • Use to define what will go into the target columns.
    • Populate Key – update record if match is found (otherwise insert)
      • Key fields should have the populate key checked.
    • Purge Key – deletes the matching record.
  • E) Join/Filter
    • Define any additional joins and/or other conditional criteria.
  • F) Generated SQL
    • Compile activity and verify you get no errors.
    • The activity will need to be recompiled after any changes.

8) Build the process tree – GKAPEXS

  • Expand the tree on the left pane. Select to enter a task.
  • Expand the task to enter an activity
  • Click on the top process code and click the Change Status.
  • Verify all activities are enabled.

GKAPEXE.png

9) Compile the tree – GKAPEXE

  • Verify the activities are enabled.
    • Go to execution tab. Choose execution mode (update/audit), exception mode (log and abort, continue), and run now. Click Yes on all popups.

GKAPEXE2.png

Errors can be seen on GKARLOG

Helpful Hints

GKAPEXS – Import Tab/Saving Changes

  • The import tab was not designed to permanently save the values into a database table. The values you entered will not be saved.
    • When you go back to time Import tab, all the values will be the default.

Data files:

  • Columns need to be in the same order, format, and length as specified in the import step.
  • Fields can be blank (if not required) but there must be some data in the first column. Excel will not save that column in the .csv
  • Use standard text characters. A long dash can result in a black diamond with a ? in it.
  • MDUU can handle CLOB datatype. However, the file has a max length of 4000 characters.
  • On import, a hidden character is picked up on the last field of each row. Adjust the field lengths to allow the import and then trim (substring) the field in the activity.
  • Data is imported as a CHAR data type. The fields will need to be cast to the correct data type in the activity.

GKAPEXS2.png

When designing MDUU processes in your area, create Banner-table centric process not source-data centric. This will promote process reuse.

  • ‘I need to update data in <<Table>>’ versus ‘I want to import data from <<system>>

Be mindful of regular business processes. If records are being manually entered at the same time a MDUU process is run, data integrity may be compromised. Example, a table with sequence numbers that link records in another table.

TEST! TEST! TEST!