Process Number: 190	Revision Date: 7/15/2008 9:33:00 PM
Revision #: 14	Back | Access Password:

EIS/320 Report Year End

Office Responsible:

DCS - District - Computing Services

Process Type:

internal

Position Title:	Sr. Programmer Analyst		Author:	jmoody
Cross Trained Staff:		Manager:	rkeith	Vice Pres.:

Timeline (Deadlines or Time-Constraints) :

fiscal year end

What must be done before this process is started:

Auditor reviews EIS 320 workbook for P3 submission (see ProcessNo 206) - notifies DCS to freeze updates.

Which processes are waiting for this process:

Source documents or communication with information needed for this process:

What is the end-result of this process, or the hand-off

New ReportYear activated - updates to closed report year frozen. Read-only copy of EIS/320 data for closed fiscal year with audit information on CD to auditor.

What steps must be taken independent of the computer system (Manual Process)

Which steps must be taken on a computer (Electronic Process):

Process Steps: PART 1 - Run final EIS extracts >>>On Datatel Step 1: Run S02.EIS.BLD to create full EIS file suite, including cum GPA in student file. Note: This needs to be run at the terminal screen, as it is not envisioned yet. (Written in UniBasic) >>>On SQL Server (SHMI) Note: All executions are done on the SHMI server itself. Use DameWare to access the desktop of the SHMI server. Step 2.1: Check to see if files have successfully transferred and are in the D:\FTP\EIS and have correct file date and time stamps for the process run in Part 1, Step 1. Step 2.2: Open up MS SQL Enterprise Manager. Step 2.3: Detail down to “SQL Server Agent\Jobs” Step 2.3: Execute LoadEisAllxxxxfiles one final time Note: xxxx = RptYr being closed. >>>Expected Results: • You should see the job status as “Executing Job…” if the job has started correctly and is running. • You should receive an e-mail that the process has started. • When the process has finished successfully, you should receive an e-mail stating so. If no e-mail, then process has ended un-successfully. See Database Administrator if errors. PART 2 - Update SQL Import Processes for new fiscal year Note: All executions are best done on the SHMI server itself. Use DameWare to access the desktop of the SHMI server. Step 1: Open Local Packages Step 2: Create LoadEisAllyyyyfiles and delete in all steps to new RptYR. Note: yyyy = new RptYr being opened. Note: After creating the transform process for the new Report year (yyyy), do NOT use closed Report Year. Step 2.1: Open up previous year’s process in design mode. Step 2.2: For each “Delete” process update fiscal year (total of 6 delete step processes) There are several local transforms on SQL server. Be sure all "DELETE" statements on ALL transforms are updated to the new Report Year, to avoid unintentional deletes of closed Report Year. Step 2.3: Save package as new fiscal year (LoadEisAllyyyyfiles). Step 3: Update other local packages to new Report year. Note: These should be saved, and not saved as a separate copy. Packages to change: - LoadEisSecFiles - LoadEisStuFiles - LoadEisRegData - LoadFacLoadFile - LoadFacultyFile Step 4: Delete prior scheduled task for LoadEisAllxxxxFiles. Note: Write down scheduled information for later use. Step 5: Schedule LoadEisAllyyyyfiles to run. Note: Use the same settings as the prior task. PART 3: Run final 320 Audit Extracts >>>On Datatel Step 1: Open datatel terminal, run: ED JANICE S02.EIS.BLD Step 2: Change AUDIT.320 to “Y” 0035: AUDIT.320 = `N` Step 3: Compile S02.EIS.BLD Step 4: Run S02.EIS.BLD to create audit files with student names and course titles. Step 5: Change AUDIT.320 to “N” 0035: AUDIT.320 = `Y` Step 6: Compile S02.EIS.BLD >>>On SQL Server Step 1: Execute LoadStuAudFile. Step 2: Execute LoadTitlesFile. Step 3: Modify SQL view vwAuditSection for Report Year being closed. Step 4: Modify SQL view vwAuditStudent (SectionTitles) for Report Year being closed. PART 4 - Update Datatel Extract Processes for new fiscal year >>>On Datatel Step 1: Set new Report year (in S02.EIS.BLD or parameter file it reads). Step 1.1: Add history note “* - to fiscal year.” Step 1.2: Change Report Year 0077: BASE.RPT.YR = "2008" Step 1.3: Compile S02.EIS.BLD PART 5 - Creation of the Microsoft Access Database Files >>>In Microsoft Access (2000 or 2003) Note: Do not open database in ACCESS 2007. Step 1: Copy prior year file to desktop and rename (e.g. 320Audit2001.mdb). Note: Located on the file server \\csb-fs-01\Source\Archives\320 Audit\ Step 2: Open Access data base on desktop. Step 3: Replace tables for prior year with report year being closed Step 3.1: Delete prior year Step 3.2: Add back same files (vwAuditSection, vwAuditStudent, SectionTitles) with newer year Step 3.2.1: Select New Step 3.2.2: Click Import Table Step 3.2.3: Select file type ODBC Database using EIS.dsn Note: (Relationships between vwAuditSection, vwAuditStudent, SectionTitles was in Relationships, now in query). Step 4: Test Queries (vwAuditSectionQry, vwAuditStudentQry) Step 5: Test Report (prompt for Synonym/Term) Step 6: Save Access data base for Report Year being closed (e.g., 320Audit2002.mdb) PART 6 - Create/Burn CD copies and provide to auditors Step 1: Copy Access data base (e.g., 320Aud2002.mdb) to shared drive (\\CSB-FS-01\Source\Archives\320 Audit). Step 2: Copy to CD burning software Step 3: Burn 2 CD copies to be saved in DCS and sent to Auditor`s office.

Related Documents to Process (e.g. Datatel documentation, Government code books, etc.)

Copy with pictures is available at: \\csb-web-02\doclib\District\District_Computing_Services\Documentation-User\320 Reporting\SBCCD_EIS_320_Report_Year_End_071408.doc

Notes (Cautions, suggestions for improvement, etc.)

There are several local transforms on sql server. Be sure all "DELETE" statements on ALL transforms are updated to the new Report Year, to avoid unintentional deletes of closed Report Year. LoadEISAllyyyFiles - after creating transform for new Report year (yyyy), do NOT use closed Report Year. (or only have LoadEISAllFiles for current year?) Load(Eis)SectionFile - run automatically each night (sends email) Load(Eis)StudentFile - run automatically each night (sends email)