Outdated: 3.1 jobs

Overnight Execution of Extract Process and other features
Edit section

Last Updated 10/1/2010

The extract process for copying data from the transactional system into the warehouse runs on a once-daily schedule, and three related features, the Standard Export, the Advanced Export, and the Descriptive Analytics, run with it on the same schedule.

This version-specific document describes the extract from the CRM 4.0 transactional system into the Schema 3.1 warehouse and associated features.

Versioning note
Edit section

This page documents the 3.1 jobs which run against the Schema 3.1 warehouse. The Stage III jobs were substantially similar: the primary differences are in the filtering based on record state and in the movement of the ordinal assignment to a separate step. 

 

  1. 1. Overnight Execution of Extract Process and other features
  2. 2. Versioning note
  3. 3. Related Documents
  4. 4. Architecture
  5. 5. Transactional system
  6. 6. Warehouse
    1. 6.1. Warehouse tables
    2. 6.2. Other Extract Process objects
  7. 7. Jobs
    1. 7.1. Job "Extract 1 – Base Records – Org"
      1. 7.1.1. 1. Extract - Get entry term data
      2. 7.1.2. 2. Extract - Stage opportunity records by entry term 
      3. 7.1.3. 3. Extract - Get opportunity-based data
        1. 7.1.3.1. Set the date 
        2. 7.1.3.2. AnalyticsMaster
        3. 7.1.3.3. OpportunityPerson_Fast 
        4. 7.1.3.4. StaffAssignment
        5. 7.1.3.5. FinAidProfile
      4. 7.1.4. 4. Fail - Send email
    2. 7.2. Job "Extract 2 – Subrecords and Processing – Org"
      1. 7.2.1. 1. Verify - Check that Extract 1 has succeeded
      2. 7.2.2. 2. Extract - Get subrecord data as of midnight
        1. 7.2.2.1. Activities
        2. 7.2.2.2. Addresses
        3. 7.2.2.3. Awards 
        4. 7.2.2.4. Ids
        5. 7.2.2.5. Interests
        6. 7.2.2.6. Notes
        7. 7.2.2.7. OpportunityRequirements 
        8. 7.2.2.8. PersonOrgs
        9. 7.2.2.9. Races
        10. 7.2.2.10. Relationships
        11. 7.2.2.11. Scores
        12. 7.2.2.12. StatusChange
        13. 7.2.2.13. CampaignResponses
      3. 7.2.3. 3.  Transform - Set export ordinals
        1. 7.2.3.1. Activities
        2. 7.2.3.2. Addresses
        3. 7.2.3.3. Awards 
        4. 7.2.3.4. Ids
        5. 7.2.3.5. Interests
        6. 7.2.3.6. Notes
        7. 7.2.3.7. OpportunityRequirements 
        8. 7.2.3.8. PersonOrgs
        9. 7.2.3.9. Races
        10. 7.2.3.10. Relationships
        11. 7.2.3.11. Scores
        12. 7.2.3.12. StatusChange
        13. 7.2.3.13. CampaignResponses
      4. 7.2.4. 4. Transform - Handle deleted opportunity and person records
      5. 7.2.5. 5. Transform – Move data into local sync tables
      6. 7.2.6. 6. Export - Move data into standard export table
      7. 7.2.7. 7. Export - Create CSV file for export
      8. 7.2.8. 8. Export - Create Zip file for export
      9. 7.2.9. 9. Export - Delete csv
      10. 7.2.10. 10. Analyze - Start SPSS job on the report server
      11. 7.2.11. 11. Succeed - Send email
      12. 7.2.12. 12. Fail - Send email
      13. 7.2.13. 13. Cancel - Send email
    3. 7.3. Job "Extract 3 – Analytics – Org"
 

Related Documents
Edit section

The extract process design is defined in "Extract to the Warehouse".

The structures of the Advanced Export, of the Standard Export, and of the Descriptive Analytics are described in "Warehouse column dictionary".

Architecture
Edit section

The transactional system is hosted in one SQL Server database per client; the structure of this database is defined by the CRM 4.0 software and by the customizations done to this software by Admissions Lab which should be identical for all clients. Multiple clients with their databases are hosted together on a single SQL Server instance. The naming convention for each client’s transactional system database is [OrgName]_MSCRM.

The warehouse is hosted in one SQL Server database per client; the structure of this database is defined entirely by Admissions Lab and has an identical base for all clients with optional client-specific additional objects. The warehouse database is hosted on the same SQL Server instance as the client’s transactional system database, and therefore multiple client warehouses are hosted together on a single SQL Server instance. The naming convention for each client’s warehouse database is [OrgName]_Warehouse.

The extract process runs on a schedule by use of SQL Server Agent jobs hosted on the same SQL Server instance as the client’s transactional system database. These jobs run in security contexts which allow them to log directly into the transactional system database and the warehouse database and manipulate data.

Transactional system
Edit section

In order to retrieve data from the transactional system database, the extraction steps must run in the context of a login which is mapped to a user defined within the transactional system. A database login which is not mapped to a user in the transactional system will not have sufficient permissions to retrieve data.

  • The login used for this is 422X\C422System. This login is set explicitly in each step which extracts data from the transactional system.

A data object is a conceptual unit in the transactional system which may be a record, a linked set of records, or a partial record in the transactional system’s database; some data objects are updated in place in the transactional system while others have multiple instances created to reflect multiple updates. The following data objects are selected out of these entities in the transactional system:

    Data ObjectTransactional system Entity
    ActivityActivityPointer
    ActivityAppointment
    ActivityCampaignActivity
    ActivityEmail
    ActivityFax
    ActivityLetter
    ActivityPhoneCall
    ActivityServiceAppointment
    ActivityTask
    AddressCustomerAddress
    AwardA422_Award
    CampaignResponseActivityParty
    CampaignResponseCampaignResponse
    CampaignResponseContact
    CampaignResponseEt_ExactTargetJob
    CampaignResponseStringMap
    FinAidProfileOpportunity
    IdA422_ids
    InterestA422_Interest
    NoteAnnotation
    OpportunityPersonContact
    OpportunityPersonOpportunity
    OpportunityPerson:FirstActivityActivityPointer
    OpportunityPerson:FirstActivityOpportunity
    OpportunityRequirementA422_OpportunityRequirement
    PersonOrgA422_PersonOrg
    PersonOrgAccount
    RaceA422_Ethnicity
    RelationshipContact
    RelationshipCustomerAddress
    RelationshipCustomerRelationship
    RelationshipA422_PersonOrg
    ScoreA422_dom_scoresource
    ScoreA422_PersonScore
    StaffAssignmentOpportunity
    StatusChangeA422 _dom_oppstatuscategory
    StatusChangeA422_OpportunityStatusChange
    -all data objects-FilteredStatusMap


Additionally, metadata for defining the active entry term is selected out of the following entities in the transactional system:

  • A422_Settings_Custom: the client must have the a422_analyticsactivetermid field populated
  • A422_dom_entryterm: the client must have the a422_year and a422_termordinal fields populated for every valid entry term

Warehouse
Edit section

Warehouse tables
Edit section

The warehouse database has warehouse tables which correspond to the types of data object from the transactional system. These tables hold multiple data objects of each type and multiple instances of each data object which track the historical state of the data object; these instances are either effective-date-stamped (created by the extract process and date-stamped with the date of the extract) or creation-date-stamped (created by the transactional system and copied intact by the extract process) so that the warehouse table can be queried by date to retrieve a historical slice of data.

Although the warehouse tables do not contain formal foreign key references to each other, they all contain an effective reference directly to the warehouse table which corresponds to the OpportunityPerson data object from the transactional system. (This is the AnalyticsMaster table.) Each record in a warehouse table carries an effective or creation date-stamp which is independent of any reference to an OpportunityPerson object, so any date-slicing of any type of data in the warehouse must be done individually on each table.

Other Extract Process objects
Edit section

The warehouse database has extraction views which also correspond to the types of data object from the transactional system. These views are pre-compiled pieces of the extract process; because these views can contain references to the transactional system database (which is specific to each client), the scheduled extract process job only needs to reference the view (which is identical for all clients) and provide a security context (which is identical for all clients). The extraction views allow the scheduled extract process jobs to be more generic between clients.

For convenience, the extract process uses a staging table for OpportunityPerson objects which is also the final base table for the Standard Export feature. Additionally, there are several temporary export tables in the warehouse in order to process the Standard Export data which is not placed into warehouse tables.

The extract process has a staging table in the warehouse for the entry term metadata.

Other objects in the warehouse are not used by the extract process.

Jobs Your browser may not support display of this image.
Edit section

A scheduled process currently performs several functions in one sequence:

  1. It performs the extract process to copy data from the transactional system into the warehouse.
  2. It moves deleted data into audit tables from warehouse tables.
  3. It moves current data into sync tables from warehouse tables for use by Advanced Export.
  4. It places data in the export table for use by Standard Export.
  5. It produces an export file from the export table for the Standard Export.
  6. It launches the analytics application on the report server for Descriptive Analytics.

This is done using three SQL Server Agent jobs per client. If successful, this sequence of jobs will send two "success" emails, one for the Standard Export and one for the Descriptive Analytics. Failure at any point will cause a "failure" email to be sent.

Job "Extract 1 – Base Records – Org"
Edit section

This scheduled job is hosted on the SQL Server which hosts the transactional system. It is scheduled to run as close to local midnight as possible in order to accurately provide effective-date-stamping.

This job extracts entry term metadata and uses this to extract staging opportunity and person data.

It extracts updateable data objects from the transactional system, currently opportunity, person, and financial aid data, so that a date-stamp of midnight can be placed on any new records created. It also extracts additional Standard Export data for opportunities, persons, and addresses at this time.

This job is expected to take between 2 and 6 minutes per client.

1. Extract - Get entry term data
Edit section

The retrieval from the transactional system database is an extraction step and runs in the explicit context of login 422X\C422System.

The staging entry term table is truncated when this step begins; prior to this step, the staging entry term table is populated with the previous run's data. The list of entry term names and guids is placed in the staging entry term table with a flag to indicate if the entry term is "active". An "active" entry term is defined in relation to the "analytics active entry term" custom setting and is one whose year and term are equal to or later than the year and term of the active entry term.

2. Extract - Stage opportunity records by entry term 
Edit section

The retrieval from the transactional system database is an extraction step and runs in the explicit context of login 422X\C422System.

The staging table is truncated when this step begins; prior to this step, the staging table is populated with the previous run's data.

The guids of opportunity records which have active entry terms and which are associated with active students are placed in the staging table with their corresponding person guids and a flag indicating that they are active.

Additionally, the guids of opportunity records which do not have active entry terms in the CRM database but which do have active entry terms in the warehouse (and which are still associated with active students) are placed in the staging table with their corresponding person guids and a flag indicating that they are not active. (This is done so that these records can be updated a final time in the warehouse.) 

3. Extract - Get opportunity-based data
Edit section

The retrieval from the transactional system database is an extraction step and runs in the explicit context of login 422X\C422System.

Set the date 
Edit section

This step defines the date "Yesterday" as a date with no time to be used as the effective date of new records. It treats the previous local day as yesterday only if the local time is prior to 10AM, after which the day shifts forward by one; this is to allow extractions run during the working day or in the evening to register the day’s data.

AnalyticsMaster
Edit section

The  organization name and department are set here.

If any data is already in the AnalyticsMaster warehouse table with an effective date of "Yesterday", that data is deleted so that this run's data can replace it. (Presumably, if an extract must be run twice in one day, the first run was in error.)  The IsCurrent flag is reset if data is deleted, since it would be possible for changes to be reverted such that new data would not be added to those tables to replace the deleted data as the most current.

The AnalyticsMaster warehouse table is updated from the CRM database with new and changed "slowly-changing" data. This data is not expected to be changed often, which keeps this table from growing too rapidly.

  • Records in the AnalyticsMaster table are never updated to reflect changes in the CRM database; instead, new AnalyticsMaster records are created so that old and new states can be tracked using the effective date.
    • The opportunity data is drawn from the live opportunity view. 
    • The person and address data is added, drawn from the live contact and customer address views. 
    • The first activity date is added, drawn from the opportunity and activitypointer views (joined with the first activity GUID stored as a string in the opportunity table). 
    • The hardcoded organization name and department are used.
    • The effective date of the new record is set to "Yesterday".
    • New records are created with the current flag set on.
  • Data is considered new or changed based on a comparison to the existing data in the AnalyticsMaster table:
    • The join is on the opportunity guid, which will never be changed for the existence of the opportunity.
    • Only current warehouse records are considered for comparison. This is done to be sure that records which are changed and then changed back to their previous state have both changes recorded.
    • High Score is compared separately, because it is not stored as a data type which can be included in a calculated checksum column.
    • The calculcated checksum column is a fast method of checking the other columns.
    • Because a checksum can give false equality on changed records in a small number of cases, the other columns are also checked individually.
  • Because the data is based on the opportunities listed in the staging table, it is limited to opportunities with active entry terms and associated person records, or to opportunities which had active entry terms and have been updated to have inactive entry terms.
    • Unless the definition on an active entry term changes, this will be the final update to opportunity records which now have inactive entry terms.

The current flag on AnalyticsMaster records is then updated for records which are no longer current because changed records have been added.

OpportunityPerson_Fast 
Edit section

Historical data is not kept in the OpportunityPerson_Fast table; this table holds the opportunity and person data which is expected to changed frequently, so it reflects only the current state of the CRM data.

First, data for new opportunity records is added to the table.

Next, data for existing opportunity records is updated in the table.  Records are marked for update by their modification dates only.

  • The [ContactType]Allow fields are stored in CRM as DoNot[Contact], so they are bitwise reversed for storage in the warehouse. This matches the import values of these fields in the standard import and is the format used by the standard export.
StaffAssignment
Edit section

If any data is already in the StaffAssignment warehouse table with an effective date of "Yesterday", that data is deleted so that this run's data can replace it. (Presumably, if an extract must be run twice in one day, the first run was in error.)  The IsCurrent flag is reset if data is deleted, since it would be possible for changes to be reverted such that new data would not be added to those tables to replace the deleted data as the most current.

Although the staff assignment is not a slowly-changing field for most clients, historical data is required for it; this is why it is tracked in a separate and narrow table.

The staff assignments for new opportunity records and any changed staff assignments for existing opportunity records are added to the StaffAssignment table with the IsCurrent flag set to true and an EffectiveDate of yesterday.

The current flag on StaffAssignment records is then updated for records which are no longer current because changed records have been added.

FinAidProfile
Edit section

If any data is already in the FinAidProfile warehouse table with an effective date of "Yesterday", that data is deleted so that this run's data can replace it. (Presumably, if an extract must be run twice in one day, the first run was in error.)  The IsCurrent flag is reset if data is deleted, since it would be possible for changes to be reverted such that new data would not be added to those tables to replace the deleted data as the most current.

Historical data for financial aid profiles is tracked separately from the rest of the opportunity data.

The financial aid profiles for new opportunity records and any changed financial aid profiles for existing opportunity records are added to the FinAidProfile table with the IsCurrent flag set to true and an EffectiveDate of yesterday.

  • Only data which is non-null in at least one data field is used.
  • This query uses a checksum backed up by a field comparison to determine if data is new or has been updated.

The current flag on FinAidProfile records is then updated for records which are no longer current because changed records have been added.

 

4. Fail - Send email
Edit section

This job sends email if it fails, but does not report a successful run.

Job "Extract 2 – Subrecords and Processing – Org"
Edit section

This scheduled job is hosted on the SQL Server which hosts the transactional system. It is scheduled to run between the expected ending of the Extract 1 job and the beginning of the school’s workday, staggered through the night to reduce the server load.

This job extracts all other data not already extracted by the previous job which are date-stamped no later than local midnight (this is the non-updateable data objects which are already date-stamped in the transaction system), updates the warehouse and export tables, and then performs the non-extract processing also needed overnight in the warehouse. When it is done, it calls a third job to run the descriptive analytics task.

This job is expected to take between 5 and 30 minutes per client.

1. Verify - Check that Extract 1 has succeeded
Edit section

This step checks that the Extract 1 job for this client has completed successfully; if it cannot find a successful run of that job in the last 24 hours, it waits an hour and retries to allow a slow Extract 1 to finish running. If at that time a successful Extract 1 cannot be found, the job cancels all further action and sends a cancellation email.

2. Extract - Get subrecord data as of midnight
Edit section

Next the job retrieves all subrecord information. This is an extraction step and runs in the explicit context of login 422X\C422System.

Each subrecord type is retrieved into a warehouse table. Records which will be flattened for a "top N records" export have a sort order created during the retrieval, but the number of subrecords retrieved is not limited to N. All subrecords draw from unfiltered views for improved performance. For certain record types, associated data or domain tables are also included in the join in order to retrieve fields not included in the standard record view. For records with statecode and statuscode fields, data is drawn from the FilteredStatusMap table to retrieve friendly state data otherwise only available from filtered views. Only new or updated records are retrieved; only records which correspond to opportunities with current entry terms (whether the correspondence is direct or via a person record corresponding to an opportunity) and which correspond to a student who is active (direct or via an opportunity) are affected; only records created before the "Today" cutoff point are affected. Affected records deleted from the CRM database are deleted from the warehouse database (except for primary addresses; see below).

This step defines the date "Today" as a datetime to be used as the cutoff date of selected records. It treats the current local day as today only if the local time is prior to 10AM, after which the day shifts forward by one; this is to allow extractions run during the working day or in the evening to select the day’s data. The time of "Today" is local midnight expressed as UTC time so that it can be compared to UTC times in the CRM database records. This step also defined "Yesterday" as the day before today, for reasons which are hopefully clear.

Activities
Edit section

Retrieve activity data along with activity contact and place it in the activity warehouse table, where the activity record is associated with a current opportunity and was created prior to the cutoff.

    1. Record are updated if their modification date, parent record, activity category name, or activity value name has changed.
    2. Activities are not limited by state code.
    3. Each type of activity is retrieved by attachment to opportunity and then by attachment to person.
      1. All activities retrieved have person join data, but only some also have opportunity join data; the opportunity join data always takes priority.
    4. All of the data retrieved is data common to all activities. The types of activities aren’t retrieved individually, but they are defined in the CRM database as:
      1. Appointment
      2. ServiceAppointment
      3. Email
      4. Letter
      5. Fax
      6. PhoneCall
      7. Task

 

Addresses
Edit section

Retrieve address data which is changed from the existing warehoused data and place it in the address warehouse table, where the address record is associated with a current opportunity and was created prior to the cutoff.

The addresses table tracks historical data only for primary addresses; current data is retrieved for all secondary addresses.  

 

  1.  Addresses are not limited by state code.
  2. If any data is already in the Addresses warehouse table with an effective date of "Yesterday", that data is deleted so that this run's data can replace it. (Presumably, if an extract must be run twice in one day, the first run was in error.) The IsCurrent flag is reset if data is deleted, since it would be possible for changes to be reverted such that new data would not be added to those tables to replace the deleted data as the most current.
  3. New primary addresses are added to the warehouse.
  4. If any records were secondary and are now primary, their secondary record is deleted from the warehouse.
  5. The current flag on primary address records is then updated for records which are no longer current because changed records have been added.
  6. New secondary addresses are added to the warehouse.
  7. Changed secondary addresses are updated in the warehouse. Record are updated if their modification date or parent record has changed.
  8. Removed secondary addresses are deleted from the warehouse.
  9. The row ordering is updated. 

 

Awards 
Edit section

Retrieve award data which is changed from the existing warehoused data and place it in the award warehouse table, where the award record is associated with a current opportunity and was created prior to the cutoff.

 

  1. Record are updated if their modification date, parent record, award category name, or award type name has changed.
  2. Records are limited to state code 0 (active). 

 

Ids
Edit section

Retrieve id data along with data on the id name from the id name domain table and place it in the id warehouse table, where the id record is associated with a current opportunity and was created prior to the cutoff.

  1. Record are updated if their modification date, parent record, award category name, or award type name has changed.
  2. Records are limited to state code 0 (active). 
  3. Id name domain information retrieved is the display order.
  4. The data is grouped to prevent duplicates when multiple opportunities for the linked person are present.
Interests
Edit section
Retrieve interest data which is changed from the existing warehoused data, and place it in the interests warehouse table, where the interest record is associated with a current opportunity and was created prior to the cutoff.
  1. Record are updated if their modification date, parent record, interest category name, or interest value name has changed.
  2. Interests are limited to records with a state code of 0 (active).

 

Notes
Edit section

Retrieve note data and place it in the note warehouse table, where the note record is associated with a current student, opportunity, or activity and was created prior to the cutoff.

  1. Record are updated if their modification date or parent record has changed.
  2. Records are not limited by state code. 
OpportunityRequirements 
Edit section

Retrieve opportunity requirement data which is changed from the existing warehoused data and place it in the opportunity requirements warehouse table, where the opportunity requirement record is associated with a current opportunity and was created prior to the cutoff.

  1. Record are updated if their modification date, parent record, activity category name, activity value name, or organization name has changed.
  2. Opportunity Requirements are limited to records with a state code of 0 (active).
PersonOrgs
Edit section

Retrieve person-organization data along with data on the organizations from the account table which is changed from the existing warehoused data, and place it in the person-organization warehouse table, where the person-organization record is associated with a current opportunity and was created prior to the cutoff.

  1. Record are updated if their modification date, parent record, organization name, degree diploma name, or organization type name has changed.
  2. PersonOrgs are limited to records with a personorg state code of 0 (active).
  3. The person-organization data is retrieved with a DISTINCT limitation to prevent duplicates.
  4. Note the non-standard double ordering.
  5. The person-organization table is joined to the staging table.
    1. This limits the person-organization data to persons who have opportunities; related persons' data is not warehoused unless those relations have their own opportunities.
    2. This excludes person-organization data for deleted opportunities which have not been marked for person-organization deletion.
    3. This limits it to current records.
Races
Edit section
Retrieve race data and place it in the race warehouse table, where the race record is associated with a current opportunity and was created prior to the cutoff.
  1. Duplicate race category + race name combinations are eliminated.
  2. Record are updated if their modification date, parent record, race category name, or race value name has changed.
  3. Races are limited to records with a state code of 0 (active).
Relationships
Edit section
Retrieve customer relationship data along with data on the related persons from the contact table, data on the related persons' addresses from the customer address table, and data on the related persons' schools from the person orgs table, and place it in the relationship warehouse table, where the customer relationship record is associated with a current opportunity and was created prior to the cutoff.
    1. Relationships are not limited by role.
    2. Record are updated if their modification date, parent record, related person modification date, student role name, relation role name, related person role name, related person org guid, related person organization name, or related person diploma name has changed.
    3. Records are not limited by state code. 
Scores
Edit section

Retrieve score data along with data on the status category from the score source domain table, and place it in the score warehouse table, where the score record is associated with a current opportunity and was created prior to the cutoff.

    1. Score source domain information retrieved is the official source flag.
    2. Record are updated if their modification date, parent record, source, source name, or test name has changed.
    3. Scores are limited to records with a state code of 0 (active).
StatusChange
Edit section

 

Retrieve status change data along with data on the status category from the status category domain table, and place it in the status change warehouse table, where the status change record is associated with a current opportunity and was created prior to the cutoff.
    1. Status category domain information retrieved is the display order.
    2. Record are updated if their modification date, parent record, current category name, current value name, original category name, or original value name has changed.
    3. Status changes are limited to records with a state code of 0 (active).
CampaignResponses
Edit section
Retrieve campaign response data along with data on the campaign jobs from the Exact Target job table and data on the email status from StringMap (functionally a domain table), and place it in the campaign response warehouse table, where the campaign response record is associated with a current opportunity and was created prior to the cutoff.
    1. Campaign information retrieved is campaign job name.
    2. Email status information retrieves is the text of the email status.
    3. This data is not currently being used, and may be removed and replaced with campaign data in a different format sometime soon.
    4. Record are updated if their modification date or parent record has changed.
    5. Records are not limited by state code.

 

3.  Transform - Set export ordinals
Edit section

This step updates the ordinal field on all relevant warehouse tables based on the newly extracted data.

Activities
Edit section

Ordered by modification date descending and activity guid (for records with actual end dates and subjects which link directly to an opportunity) or unordered (for the other records).

Addresses
Edit section

Ordered by modification date descending and address number (except for current primary addresses, which are ordered as 1).

Awards 
Edit section

Ordered by modification date descending and award guid.

 

Ids
Edit section

Ordered by id name’s display order ascending, modification date descending, and id guid.

Interests
Edit section
Ordered by modification date and interest guid.

 

Notes
Edit section

Not ordered.

OpportunityRequirements 
Edit section

Not ordered.

PersonOrgs
Edit section

Ordered by modification date descending and person org guid for each set of active person org records for each person and again for each set of inactive person org records for each person and organization.

    Races
    Edit section
    Ordered by race name (alphabetically descending).
    Relationships
    Edit section
    Ordered by modification date.
    Scores
    Edit section

    Ordered by test date descending, modification date descending, and score guid for score and ordered by subscore name ascending for subscore.

    StatusChange
    Edit section

    Not ordered.

    CampaignResponses
    Edit section
    Not ordered.

    4. Transform - Handle deleted opportunity and person records
    Edit section

    This step defines the date "Yesterday" as a date to be used as the deleted date. It treats the current local day as yesterday only if the local time is later than 10AM; otherwise yesterday is the day prior to the current day.

    The list of records in the staging table is taken as the definitive list of existing opportunity records.

     

    • If records are given in the staging table and also present in the deleted record audit table, they are removed from the audit table and restored to the AnalyticsMaster table.
    • If records are given in the AnalyticsMaster table with active entry terms but are not present in the staging table, they are removed from the AnalyticsMaster table and other opportunity tables and placed in the deleted record audit table. 
      • The audit tables uses Yesterday as the deletion date.
      • The audit table stores all of the slow-changing historical records (from the AnalyticsMaster table).
      • The records are deleted from the AnalyticsMaster, OpportunityPerson_Fast, FinAidProfile, and StaffAssignment tables.

     

    Once this step completes, the extract process is finished. All of the remaining steps are related to other warehouse-based features.

     

    5. Transform – Move data into local sync tables
    Edit section

    This step is executed for the Advanced Export feature.

    Next the export job incrementally updates the sync tables with any changes. The sync tables hold only the current records from their associated tables, so that this selection of data can be synchronized for Advanced Export.

    1. The OpportunityPerson_Local sync table is synchronized with the AnalyticsMaster, OpportunityPerson_Fast, and StaffAssignment warehouse tables.
      1. Only records where IsCurrent = 1 should exist in the sync table (all records from the OpportunityPerson_Fast table, which holds only current records, are used).
      2. This definition of “current” is not limited by entry term; all records are used.
      3. Any new opportunities which have been added to the warehouse table are inserted in the sync table.
      4. Any existing opportunities with new effective dates are updated in the sync table.
      5. Any deleted opportunities are removed from the sync table.
    2. The FinAidProfile_Local sync table is synchronized with the FinAidProfile warehouse table.
      1. Only records where IsCurrent = 1 should exist in the sync table.
      2. Only records associated with opportunities in the OpportunityPerson_Local sync table should exist in the FinAidProfile _Local sync table.
      3. If an opportunity in the OpportunityPerson_Local sync table does not have a fin aid profile, the fin aid profile is inserted in the sync table.
      4. If an opportunity in the OpportunityPerson_Local sync table has a fin aid profile with a different effective date, the fin aid profile is updated in the sync table.
      5. If a fin aid profile in the sync table is associated with an opportunity which is no longer in the OpportunityPerson_Local sync table, the fin aid profile is deleted from the sync table.
    3. The Addresses_Local sync table is synchronized with the Addresses warehouse table.
      1. Only records where IsCurrent = 1 should exist in the sync table.
      2. Addresses are not limited to primary only; secondary addresses are also listed. (All secondary addresses are current.)
      3. Any new addresses which have been added to the warehouse table are inserted in the sync table.
      4. Any existing addresses with new effective dates are updated in the sync table.
      5. Any deleted addresses are removed from the sync table.

    6. Export - Move data into standard export table
    Edit section

    Next the job copies the data from the warehouse tables into the export table using the “ordinal” field on the warehouse tables to select the "top N records".

    • The Xport table is truncated when this step begins. Until this step, the Xport table was populated with the results of the previous run.
    • The Xport table is populated with all active opportunity records.
    • Update with opportunity and person data from the AnalyticsMaster and OpportunityPerson_Fast tables.
      • Only current data from the AnalyticsMaster table is used.
    • Update with staff assignment data from the StaffAssignment table.
      • Only current data is used.
    • The top 8 activity records are used. Activity joins on the opportunity id.
      • From step 2, activities were ordered by actual end date descending and activity guid.
      • From step 2, activities without actual end dates or without subjects are not used for exports.
      • From step 2, only activities tied to opportunities (as opposed to persons) are used for exports.
      • Activity values are truncated at nvarchar(100).
      • Activity dates (without times) are transformed into strings.
    • The top 2 addresses are used. Address joins on the person guid.
      • From step 2, primary addresses were ordered by modification date descending and address guid.
      • Street1, Street2, and Street3 fields are truncated at nvarchar(50). 
    • The top 8 award records are used. Award joins on the opportunity id.
      • From step 2, awards were ordered by modification date descending and award guid.
      • Award modification dates (with times) are transformed into strings.
      • The first (most recent) award modification dates is also used as the financial aid modification date in the export table and is transformed (with time) into a string.
    • The top 5 id records are used. Id joins on the person id.
      • From step 2, ids were ordered by id name’s display order ascending, modification date descending, and id guid.
    • The top 8 interests are used. Interest joins on the opportunity id.
      • From step 2, interests were ordered modification date then interested guid.
    • The top 1 active person-org record is used. Person-Org joins on the person id.
      • From step 2, active person-orgs were ordered by modification date descending and person-org guid.
      • Person-Org modification dates (with times) are transformed into strings.
      • The transcript received and last attended dates (without times) are transformed into strings.
    • The top 8 race records are used. Race joins on the person id.
      • From step 2, races were ordered by race name (alphabetically descending).
      • Only the race value is used for the standard export.
    • The top 2 relationship records are used. Relationship joins on the person id.
      • From step 2, relationships were ordered by modification date.
    • The top 4 official scores are used. Score joins on the person id.
      • From step 3, official scores were ordered by test date then modification date.
      • Score test dates (without times) are transformed into strings.

    7. Export - Create CSV file for export
    Edit section

    This step is executed for the Standard Export feature.

    The job uses the "[Org]Xport.dtsx" package to create a CSV export file in the export folder. (See export documentation for more detail.)

    The package is executed by command line. This produces a CSV file on the ftp server.

    • The package location for all clients is \\422rpt001\SSISPackages\
    • The ftp location for each client is \\422biz001\ftp\[organization name]\

    Note: Some clients may have custom packages or nonstandard ftp locations.

    8. Export - Create Zip file for export
    Edit section

    This step is executed for the Standard Export feature.

    The job uses WinZip to zip the CSV export file. (See export documentation for more detail.)

    The WinZip application is called by command line. The CSV file is zipped into the same export folder.

    9. Export - Delete csv
    Edit section

    This step is executed for the Standard Export feature.

    Next the CSV file is deleted from the export folder. (See export documentation for more detail.)

    10. Analyze - Start SPSS job on the report server
    Edit section

    This step is executed for the Descriptive analytics feature.

    Next the job triggers the Extract 3 job (see the next section). The Extract 3 job is triggered asynchronously and this step does not wait to see if the Extract 3 job succeeds before proceeding. (See analytics documentation for more detail.)

    11. Succeed - Send email
    Edit section

    12. Fail - Send email
    Edit section

    13. Cancel - Send email
    Edit section

    If there have been no errors, the job sends a success email for the export. This email is sent to the client and bcced to the general support email.

    The job sends cancellation email only if the verification in the first step fails. Failure in any other step causes a failure email. This is to distinguish in the email between potentially unreported errors in the Extract 1 job (which would cause the verification to fail) and errors which occur in the Extract 2 job itself. These emails are send to the DBA.

    Job "Extract 3 – Analytics – Org"
    Edit section

    This job exists on the report server and it is not scheduled to run; it runs only when called by the Extract 2 job. It calls the analytics application with client-specific command-line parameters.

    This job is expected to take between 12 and 25 minutes per client.

    The first step of this job, which calls the analytics application, will be retried 4 times at 20 minute intervals if it does not succeed. The analytics application has been known to time out and to return an error when called during a heavy load time on the reporting server. In the old process, 4 retries at 20 minute intervals has been enough to run the application successfully after a timeout.

    If the analytics application returns no errors on its first attempt or on any subsequent retrials, the job sends a success email for the analytics to the general support email.

    This job sends email if it fails on all retrials to the DBA and the administrator of the SPSS application.

    See the analytics documentation for more information on this process.

    Comments