How do I complete the CCH Axcess technical DAU setup for Aiwyn?

The following items must be completed as part of your Integration with Aiwyn:

• Enable Update Cadence
• Enable Full Refresh
• Enable Change Tracking

Enable Update Cadence

Enable and Set 4-HR Update Cadence for Axcess to update the DAU, which requires your DAU to have been updated by CCH Axcess on or after September 2023.

In the Windows Task Scheduler, and after ensuring the Axcess update has been processed to allow a 4-HR cadence, you must manually set the 4-HR update cadence so that Axcess can update the DAU every 4 hours. Previously, Axcess was only able to update the DAU every 24 hours.

• Setup a Windows Batch File to store the code that will trigger an Axcess update
  • Data Axcess Utility - Default set to run every 4 hrs
• Navigate to the Windows Task Scheduler
• Edit the existing schedule ("Data Axcess Utility - Default") to run on 6 "daily" triggers to run on 4-hour increments

• Below are the prescribed times to set the DAU updates in UTC. ** Please note, Task Scheduler relays times in the time zone your server is in, so they must be set accordingly. CST and EST time conversions are added below for your convenience.

Reference: What is the Data Availability window for the DAU?

• Take a screenshot of your screen and submit this in the IT form for your Aiwyn IT requirements.
• Save this screenshot. Any time Axcess runs a software update (not often), this may have to be reset in the Task Scheduler

Enable Full Refresh

Setup a Daily DAU full table rerun for the CLIENTCRS, CLIENTBILLINGEMAIL, CLIENTCONTACT, CLIENTCONTACTBILLINGEMAIL, CLIENTEMAIL tables, using the documentation below. We must do this to account for deleted records which occur in the aforementioned tables. Because the DAU tables do not function like normal SQL data tables, Change Tracking does not always account for deletions.

In lieu of this, Aiwyn has rerun flows on our side that take the newly updated table, run it against our database, and handle deletions for the firm by inactivating any record in Aiwyn's system that does not exist in the newly updated table.

We have provided attachments for your convenience to make this process easier to execute, but we have also outline the manual instructions below

Expedited Setup

• Download the SQL file flow and store on server ("Update_DWRAWCTL_CTLCDC.sql")
• Download this batch file that gets executed by the Windows Scheduled Task and it executes the contents of the sql file. The firm must update the contents of this file with the server, username and password ("DAU_SetFullRefreshFlags.bat")
• Run this PowerShell script to create the Windows Schedule Task. The firm will need to set the "Run whether user is logged on or not". ("CreateWindowsScheduledTask_DAU_SetFullRefreshFlags.ps1")

Manual Setup

• Copy this SQL script and place into a Batch file (#1) on the server. This is the full-refresh script.

UPDATE [DW_RAW_CTL].[dbo].CTL_CDC
SET TRUNCATE_ON_NEXT_LOAD ='Y', NEXT_LOAD_IS_FULL_LOAD_FLAG = 'Y'
WHERE TARGET_TABLE in('CLIENTBILLINGEMAIL','CLIENTCRS','CLIENTCONTACT','CLIENTCONTACTBILLINGEMAIL','CLIENTCLIENTREFBRIDGE','CLIENTGROUP') AND PERMITS_FULL_LOAD_FLAG = 'Y'
GO

• Create another Batch file (#2) which the task scheduler will use to call upon the full refresh script in the step above (#1)
  • The firm must update the contents of this file with the server, username and password
• Navigate to the Windows Task Scheduler
• Create a schedule to call upon the Windows Batch File you setup (#2)

Below is the schedule for this full refresh operation. Please note that this is 5 minutes prior to the Axcess update in the previous step.

These 4 tables must be fully refreshed first so that the update from Axcess to the DAU can pass the full refresh for these 4 tables in addition to passing the normal data update for other tables that do not need a full refresh.

• Take a screenshot of your screen and submit this in the IT form for your Aiwyn IT requirements.
• Reference: How to setup DAU to automatically do a Full Download / Update

Enable Change Tracking and Grant Permissions

Enable Change Tracking and View Permissions on the tables outlined below using the SQL scripts provided below the tables list. 

Enable both Change Tracking (CT) and View Permission on the following tables:

To enable Change Tracking and a 14-day retention period for the above tables, you can use the below script:

The [aiwyn] user placeholder will need to be updated with the login/user created for our connection before executing these statements.

-- ENABLE CHANGE TRACKING FOR DB
-- This enables Change Tracking for the database and sets the retention period.
-- If Change Tracking is already enabled but the retention period does not match, it will be updated.

DECLARE @retentionDays nvarchar(10) = '14'

IF NOT EXISTS (
    SELECT *
    FROM sys.change_tracking_databases
    WHERE database_id = DB_ID('CCH_ENT')
    AND retention_period_units_desc = 'DAYS'
    AND retention_period = @retentionDays
)
BEGIN
    ALTER DATABASE CCH_ENT
    SET CHANGE_TRACKING = ON
    (CHANGE_RETENTION = 14 DAYS, AUTO_CLEANUP = ON);

    PRINT 'Change Tracking has been enabled with a retention period of ' + @retentionDays + ' days.';
END
ELSE
BEGIN
    PRINT 'Change Tracking has already been enabled and the retention period is properly set.';
END

-- ENABLE CHANGE TRACKING FOR TABLES
-- This enables Change Tracking for the tables declared in the INSERT statement below.
DECLARE @tableName NVARCHAR(128)
DECLARE @schemaName NVARCHAR(128) = 'dbo'
DECLARE @dbName NVARCHAR(128) = 'CCH_ENT'
DECLARE @sqlCommand NVARCHAR(MAX)

-- List of tables that require Change Tracking
DECLARE @tables TABLE (TableName NVARCHAR(128))
INSERT INTO @tables (TableName) VALUES ('INVOICE'), ('ARDISTRIBUTION'), ('ARCHARGES'), ('ARTRANSACTION'), ('CLIENTCRS'), ('CLIENTCONTACT'),('CLIENTCONTACT'),('CLIENTCONTACTBILLINGEMAIL'), ('CLIENTBILLINGEMAIL'), ('CLIENT'), ('CLIENTPHONE'), ('CLIENTADDRESS'), ('CLIENTEMAIL'), ('INTERIMWORKINGPROJECT'), ('FIRM'), ('SERVICECODE'), ('STAFF'), ('STAFFEMAIL')

-- Cursor to iterate through the tables
DECLARE tableCursor CURSOR FOR
SELECT TableName FROM @tables

OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @tableName

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Build the dynamic SQL command
    SET @sqlCommand = '
        USE [' + @dbName + '];

        IF NOT EXISTS (
            SELECT *
            FROM sys.change_tracking_tables
            WHERE object_id = OBJECT_ID(''' + @schemaName + '.' + @tableName + ''')
        )
        BEGIN
            ALTER TABLE [' + @schemaName + '].[' + @tableName + ']
            ENABLE CHANGE_TRACKING
            WITH (TRACK_COLUMNS_UPDATED = ON);
            PRINT ''' + @tableName + ' Change Tracking Enabled''
        END
        ELSE
        BEGIN
            PRINT ''' + @tableName + ' already has Change Tracking Enabled''
        END
    '

    -- Execute the dynamic SQL command
    EXEC sp_executesql @sqlCommand

    FETCH NEXT FROM tableCursor INTO @tableName
END

CLOSE tableCursor
DEALLOCATE tableCursor

-- Grant permission to the Aiwyn SQL user.
-- Make sure to update the "[aiwyn]" placeholder if not named aiwyn.
USE CCH_ENT;
GRANT VIEW CHANGE TRACKING ON INVOICE TO [aiwyn];
GRANT VIEW CHANGE TRACKING ON ARDISTRIBUTION TO [aiwyn];
GRANT VIEW CHANGE TRACKING ON ARCHARGES TO [aiwyn];
GRANT VIEW CHANGE TRACKING ON ARTRANSACTION TO [aiwyn];
GRANT VIEW CHANGE TRACKING ON CLIENTCRS TO [aiwyn];
GRANT VIEW CHANGE TRACKING ON CLIENTCONTACT TO [aiwyn];
GRANT VIEW CHANGE TRACKING ON CLIENTBILLINGEMAIL TO [aiwyn];
GRANT VIEW CHANGE TRACKING ON CLIENTCONTACTBILLINGEMAIL TO [aiwyn];
GRANT VIEW CHANGE TRACKING ON CLIENT TO [aiwyn];
GRANT VIEW CHANGE TRACKING ON CLIENTPHONE TO [aiwyn];
GRANT VIEW CHANGE TRACKING ON CLIENTADDRESS TO [aiwyn];
GRANT VIEW CHANGE TRACKING ON CLIENTEMAIL TO [aiwyn];
GRANT VIEW CHANGE TRACKING ON INTERIMWORKINGPROJECT TO [aiwyn];
GRANT VIEW CHANGE TRACKING ON FIRM TO [aiwyn];
GRANT VIEW CHANGE TRACKING ON SERVICECODE TO [aiwyn];
GRANT VIEW CHANGE TRACKING ON STAFF TO [aiwyn];
GRANT VIEW CHANGE TRACKING ON STAFFEMAIL TO [aiwyn];