Encumbrance

Encumbrance is a project that began in the mid 2010s and is a way for Seattle University departments to effectively gauge their spending throughout the fiscal year, which starts on July 1st of every year. At Seattle U, we use a third party system for much of our daily accounting and budget needs, ProcureSU. ProcureSU’s underlying architecture runs on a platform provided by a third party, Jaggaer. In order for us to see what kinds of encumbrance totals changed day by day, we extract data from ProcureSU to calculate what remaining encumbrance values are left for the year for a given GL account. These changes then get pushed back into Colleague so we have an internal record of encumbrance values each day.

There are three main components to this load:

1. Daily extract from ProcureSU (11:30AM): This is a process owned by the data integration team. As of the date this article was written, Chelsea Lien is the main developer and point of contact to get these files on our dropshare located at \oitfs2\oit$\DROPSHARE\ProcureSU\DataFilesFromJaggaer_POs\Archive\Prod. This process is typically complete at around 11:30AM each day. If the exact number of files are not present as of 12PM, the job will fail and an email will be generated for the integration team to fix the issue (see step 2 below for more information about this process).

2. SSIS package runs (12:00PM): This is a deployed package that sits on ITS-EDW-SSRS1 and is scheduled to run at 12PM daily. The name of the job is encumbrance_load_v5. It is the main driver that processes the raw files placed in the dropshare above to determine what encumbrance values have changed.

   1. The first component, and arguably the most important one, is the extract script (\oitfs2\oit$\DROPSHARE\ProcureSU\DataFilesFromJaggaer_POs\script\po_data_move.ps1). This script combines the po_data and po_line_customdata from each yearly zip file to be consumed by the rest of the job. If all files are not present, which DOES happens on occasion, the job will not run and action will be required by the data integration team to get all files to be present. Once this is done, you will need to manually run encumbrance_load_v5 again on ITS-EDW-SSRS1.

   2. All of the subsequent processes are stored procedures on ITS-TST-DW1 in the EncumbranceProject database.

3. GLIM file is created (usually around 12:10PM): The output of the SSIS package generates a flat file used by the controller’s office who then uploads the file back to Colleague.

Opening the project:

*** if the ssis job is failing before running the powershell script OR hanging on the powershell script, ensure the calling server (in this case ITS-EDW-SSRS1) has the proper execution policy set. Run this in powershell as administrator: Set-ExecutionPolicy -ExecutionPolicy Bypass ***

In order to access the code base for Encumbrance, you’ll need:

1. Visual Studio 2019 with SQL Server Data Tools and SQL Server Integration Services

2. Priv access to ITS-EDW-SSRS1 to deploy and manage the SSIS package above. Note: the main file preparation script can only run from this server if you need to manually execute it!

3. Priv access to ITS-TST-DW1 to modify any stored procedures that are executed in the EncumbranceProject database via the SSIS package.

The SSIS package can be found in devops in the following repository:
https://seattleu-its.visualstudio.com/InformSU_TFS/_git/Encumbrance

The expectation here is that you are familiar with SSIS, git repositories, and know how to clone the project down to your local machine. If you do not know how to do this, please contact David Abney, Nick Porter, or Mark Young.

Required Maintenance:

There are two manual maintenance tasks that need to be done annually:

1. During winter break, it’s common for Mark Schoen or someone else in the controller’s office to request that the job be disabled as nobody is in the office to process the output of each daily run. Once SU employees return at the beginning of the year, we re-enable the schedule in SSIS. Once this is re-enabled, you will need to update the powershell script that combines the raw files from step 1 above to include another expected year (So, in 2022, we get 6 years of data extracted from ProcureSU. In 2023, this will need to be updated to 7).
   
   

   Open

   

2. Around the end of June, a decision is made about when we want to close the current fiscal year. When that decision is made, usually by Mark Schoen, update the table EncumbranceProject.dbo.fyr_close the day of that close date after the midday run.