Outdated: Transfer from old extraction process to Stage I

In the old extraction process, the warehouse tables were placed in the Org_Analytics database; this warehoused data must be preserved in the move to the new system.

 

  1. On the CRM server which hosts the Org_MSCRM database, create a new database named Org_Warehouse.
    1. The collation for this database must be Latin1_General_CI_AI.
    2. The recovery mode can be Simple.
    3. [See Sync documentation for change tracking]
  2. Create the schema for Org_Warehouse.
    1. Script to create the scehma: create org_warehouse tables and views.sql
  3.  [Alternatively, a backup of an empty Org_Warehouse made at this point can be used as the basis for all clients’ Org_Warehouse databases.]
  4. Move data from the Org_Analytics database into the new Org_Warehouse database,
    1. Each data transformation script must have the following string replacements:
      1. The string Org_Analytics is replaced with the actual Org_Analytics database name.
    2. Script to move the data: copy analytics data.sql
    3. This script is run from the new Org_Warehouse database.
    4. This script is expected to take 5 to 15 minutes per client and should not be run while the overnight process is running.
    5. Note that the structure of the old Org_Analytics and the new Org_Warehouse differ slightly;  the script which moves the data also transforms it. A plain table copy would not work between these databases.
  5. The DTSX package “OrgXport.dtsx” for the client should be moved.
    1. The package must be opened and repointed to the new Org_Warehouse database.
    2. The physical file must be moved from the report server to the CRM server
  6. The Extract 1 and Extract 2 jobs must be created on the CRM server which hosts the Org_MSCRM database.
    1. Each creation script must have the string OrgNameHere replaced with the organization name abbreviation (the “Org” string in Org_MSCRM et al.).
    2. Script to create Extract 1: create extract 1.sql
    3. Script to create Extract 2: create extract 2.sql
  7. The Extract 1 and Extract 2 jobs must be scheduled.
    1. Extract 1 runs close to midnight.
    2. Extract 2 runs after Extract 1 is expected to finish.
    3. Stagger Extract 2 for various clients;  the subrecord retrieval, export file creation, and analytics application execution all depend on this time.
  8. The Extract 3 job must be created on the report server.
    1. The creation script must have the string OrgNameHere replaced with the organization name abbreviation (the “Org” string in Org_MSCRM et al.).
    2. Script to create Extract 3: create extract 3.sql
    3. The command line for each client should be the command line previously used: 
      update newstep  
      set command = case when oldstep.command is not null 
        then oldstep.command 
        else 'NO SPSS JOB DEFINED YET' end  
      from sysjobs as new  
      left join sysjobs as old  
        on REPLACE(new.name, 'extract 3 - analytics - ', '') 
        = REPLACE(old.name, 'analyticsextract_', '') 
      inner join sysjobsteps as newstep  
        on newstep.job_id = new.job_id  
      left join sysjobsteps as oldstep  
        on oldstep.job_id = old.job_id  
        and oldstep.step_name like '%spss%'  
      where new.name like 'extract 3%' 
      and newstep.step_name = 'Analytics - Run the SPSS application'
  9.  The view vw_DescAnalytics_Complete in the Org_Analytics database needs to be altered to point to the Org_Warehouse database, and a view vw_DescAnalytics_Old in the Org_Analytics database should recreate the old version of this view if comparison is desired.
    1. Script to alter the view: create org_analytics views.sql
  10. Any per-client export customization must be applied.
  11. All three jobs have email steps;  these steps should be edited to specify the interested parties who should be emailed on success and/or failure of the process.
Comments