PTA Extract to Enterprise Data Warehouse

Extracting data from the Property Tax Admistration (PTA) database to the Enterprise Data Warehouse (EDW) consists of 2 Informatica workflows. The first workflow examines activity in the PTA database at regular intervals and determines whether or not if it OK to actaully start the second workflow that actually extracts the data. 

Purpose

The PTA_DWDirector workflow has been developed to collect statistics from the PTA Task Scheduler and the PTA Independent Municipal Import Utility, provide email notifications concerning PTA job conflicts and job warnings, and start the workflow PTA_EDWExtract, if no conflicts exist. The EDWDirector workflow examines PTA jobs that ran, are running, and are scheduled to be run in order to determine if it is all clear to begin pulling information from the PTA database. It provides warnings by verifying specific jobs ran out of an expected sequence. The EDWDirector workflow uses the view WC_JOBTRACKING_VW which helps standardize the differences between the two (2) PTA utilities and their separate sets of tables.

wf_PTA_EDWDirector

Job Conflicts -- will cause the EDWExtract workflow not to start

Jobs that need to be checked must be entered in the table ADM_PTA_JobsToCheck. The PTA job name, PTA status, check future start times, only check after tax bills mailed, and the hours from and to are all required.  For the initial set up, Failure statuses are reported every time the EDWDirector workflow executes, providing the parameters do not tell the workflow to skip a day or specific hour. All other statuses are checked the hour before the EDWExtract is to execute thru the hour where the EDWExtract workflow will end its attempt to start the EDWExtract workflow. This is parameter driven so the hours listed in the table ADM_PTA_JobsToCheck needs to coincide with the EDWDirector workflow parameters. Note: If any job conflicts are found, the EDWExtract workflow will not execute. 

ADM_PTA_JobsToCheck (sample)  

Job Warnings (Notifications only)

Currently there are three (3) cross checks to alert the PTA Administrator.  
1) Tax Bills were not mailed yet a Municipal Listing Roll Over occurred.
2) The Municipal Roll Over year is not consecutive.
3) Special Assessments were deleted after the Tax Bills were mailed via the stored procedure pta_delete_specialimports.

Email notifications will be sent to the PTA Administrator should any of these errors occur every time the EDWController workflow executes provided the parameters do not tell the workflow to skip a day or specific hour.  Typically, these warnings will cease after the EDWExtract workflow completes during a given cycle.

Workflow Parameters (example)

Sample EDWDirector workflow parameters. Note: the EDWDirector workflow has been initially set up to execute ever 15 minutes.
$$WF_WAITDEFAULTMI=0
$$WF_WAITEXTENDMI=59
$$WF_WAITEXTENDHH24=13,14,15,16,17,18,19,20,21,22,23,00
$$WF_STARTEXTRACTHH24=02,03,04,05,06
$$WF_RESETEXTRACTHH24=01
$$WF_SKIPDAY=Saturday,Sunday
$$WF_SKIPHH24=07,08,09,10,11,12

$$WF_WAITDEFAULTMI - > When current hour is not listed in $$WF_WAITEXTENDHH24, this parameter can be set to extend or shorten the time when Conflict and Warning cross checks are emailed to the PTA Administrator. Since the EDWDirector workflow is set up to execute over 15 minutes, the values 0-15 could generate an email notification every 15 minutes.  Values of 16-30 could generate an email notification every 30 minutes.  Values of 31-60 could generate an email notification every 60 minutes. So on and so forth…

$$WF_WAITEXTENDMI=59 - > This parameter can be set to extend or shorten the time when Conflict and Warning cross checks are emailed to the PTA Administrator when current hour is listed in $$WF_WAITEXTENDHH24. Since the EDWDirector workflow is set up to execute every 15 minutes, the values 0-15 could generate an email notification every 15 minutes.  Values of 16-30 could generate an email notification every 30 minutes.  Values of 31-60 could generate an email notification every 60 minutes. So on and so forth ….. Using this example, email notifications will be sent every 60 minutes during the hours of 1pm-12:59am.

$$WF_WAITEXTENDHH24=13,14,15,16,17,18,19,20,21,22,23,00-> List the hours that the Conflict and Warning cross checks are to be emailed to the PTA Administrator at a frequency other than the default.  This provides the ability to send email notifications with two (2) different frequencies. In this example, email notifications will be sent every 60 minutes from 1pm-12:59am.

$$WF_STARTEXTRACTHH24=02,03,04,05,06 - > These are the hours where the EDWDirector workflow will attempt to start the EDWExtract workflow. The EDWDirector workflow will attempt to start the EDWExtract workflow provided there are no conflicts and it is the first time for a given extract cycle. In this example, the EDWController will attempt to start the EDWExtract workflow every 15 minutes between 2-6:59am. If it was unsuccessful during that time period, it will begin to try again at 2am the following day (or Monday if it was a Friday morning).

$$WF_RESETEXTRACTHH24=01 -> this parameter is used to reset the extract cycle. Once the EDWDirector workflow starts the EDWExtract workflow, a flag is set indicating that the EDWExtract workflow has already been started. During the hour(s) listed, the flag is cleared indicating that the EDWExtract has not yet been started.  If the flag is not cleared, the EDWExtract workflow will only execute one time. In this example, the flag is cleared every time the EDWDirector workflow executes during 1-1:59am.

$$WF_SKIPDAY=Saturday,Sunday -> The EDWDirector workflow will not attempt any email notifications nor will it attempt to start the EDWExtract workflow during these days.

$$WF_SKIPHH24=07,08,09,10,11,12 -> The EDWController workflow will not attempt any email notifications nor will it attempt to start the EDWExtract workflow during these hours.

The above sample parameter settings result in the following:
Job Statistics are captured every 15 minutes.
No attempt to start the EDWExtract workflow will occur on Saturday or Sunday.
No attempt to send email notifications will occur on Saturday or Sunday (exception -session failure) 
No attempt to start the EDWExtract workflow will occur between 7am and 12:59pm Monday thru Friday.
No attempt to s send email notifications will occur between 7am and 12:59pm Monday thru Friday (exception - session failure).
The EDWDirector workflow will attempt to start the EDWExtract workflow every 15 minutes between 2am and 6:59am Monday thru Friday.
If email notifications become necessary, they will be sent every hour from 1pm thru 12:59am Monday - Friday.
If email notifications become necessary, they will be sent every 15 minutes from 1am until one of the following occurs: either the EDWExtract workflow completes or it's later than 6:59am.

 View -  WC_JOBTRACKING_VW 

JOBID - Contains ImportFile.FileId or SCHEDULEDJOBS2.ROWID

JOBNAME - Contains IMPORTFILE.IMPORTTYPE or JOBS.NAME. The view does transpose some of the PTA job names from the Independent Municipal Import Utility in order to be a bit more descriptive.
WC_ASMTS -> ListingImport
BILLING -> BillingImport
PAYMENTS-> PaymentImport

STARTTIME - Contains ImportFile.DateCreated, SCHEDULEDJOBS.SCHEDULEDSTART from the task scheduler when the task is expected to start in the future, or SCHEDULEDJOBS.STARTTIME for all other jobs in the Task Scheduler.

ENDTIME - This contains either the max ImportFile.Statusdate or SCHEDULEDJOBS.ENDTIME from the Task Scheduler

STATUS - Contains the status fields from ImportFile and SCHEDULEDJOBS tables. To achieve some consistency, the view transposes the status codes from the Independent Municipal Import Utility to correspond to the Task Scheduler. 
SUCCESS -> COMPLETED
PENDING -> RUNNING
FAIL -> FAILURE

TRA - > Contains the value from ImportFileParam.Value where ImportFileParam.ParamName = 'IMPORT.MUNI or SCHEDULEDJOBPARAMS.VALUE where SCHEDULEDJOBPARAMS.DESCRIPTION = 'TRA' or 'Municipality'

TAXYEAR - > Contains the value from ImportFileParam.Value where the ImportFileParam.ParamName = 'IMPORT.TAXYEAR' or SCHEDULEDJOBPARAMS.VALUE where CHEDULEDJOBPARAMS.DESCRIPTION = 'Target Year', Tax Year' or = 'taxYear'

DATECREATED - > Contains ImportFile.DateCreated, or SCHEDULEDJOBS.DATECREATED

CREATEDBY - > Contains ImportFile.CreatedBy or SCHEDULEDJOBS.CREATEDBY

DATEMODIFIED - > Contains ImportFile.DateCreated or SCHEDULEDJOBS.DATEMODIFIED

MODIFIED BY - > Contains ImportFile.CreatedBy or SCHEDULEDJOBS.MODIFIEDBY