/
Logging AccountRightLive ETL (30-april-21)

Logging AccountRightLive ETL (30-april-21)

Created by Fox
Jun 11, 2021
2 min read

Hi Charles,

 

I have modified the CloudARLExtract.ps1 script to use my new extract program so if you want to do some testing over the next bit you can. It is the same as current production, but I have updated the main program to write to the table (so the extract data should be the same).

 

Jason

 

From: JasonFrom Hilsdon <jason@bi4cloud.com> 
Sent: Friday, 30 April 2021 3:36 PM
To: 'Charles A Fox' <fox@bi4cloud.com>
Subject: Recording of additional sync information for Cloud ARL files

 

Hi Charles,

 

I have done some work on your request in storing some additional information for cloud ARL extracts so that your extract template can try and make better decisions on the type of extract to do. 

 

To implement this, I have created a new table [BICloudProd].[ETL_CloudStats] which the extract program writes to for each extract, and my intention is for this table (filtered for that company file only) to also be put into the IRDB table.

 

 

The table contains the following fields:

 

ID

Stat ID (Primary key)

OrganisationID

IR Organisation ID

CompanyID

IR Company ID

ScheduleID

Key back to table [BICloudProd].[ETL_Schedule]

OperationID

Key back to table [BICloudProd].[ETL_Cloud]

SyncType

Type of sync. Can be one of the following values:

  • F (Full Sync)

  • I (Incremental Sync)

  • D (Date Sync)

StartDate

DateTime of when sync operation started

DataSyncEndDate

DateTime of when all calls to MYOB’s servers have completed

EndDate

DateTime of when extract has completed (including IRDB template)

Duration

Number of minutes for extract (EndDate – StartDate)

LastTrxDate

Date of last transaction in current company IRDB file. Determined by following query:

SELECT MAX(dateposted) FROM JournalTransaction

DateSyncBaseDate

Date used for date-based sync. Taken from -VerifyErr IRDB file. Value is determined by the following query:

SELECT MIN(TransactionDate2) FROM UnBalancedPeriods

Return_Status

Return status flag of sync operation. Value is True (1) / False (0)

Return_DateSync

Flag to determine if date-sync is required. Value is True (1) / False (0)

Return_FullSync

Flag to determine if full sync is required. Value is True (1) / False (0)

Return_Message

Basic notes usually indicating if sync failed. Similar to text seen in table ETL_Cloud

 

It was my intention that this table is to be injected into the IRDB file. An example of this would be:

 

 

If you order by the ID field, you can see that this sync above started as an incremental sync, with a last transaction date of 29/04, it then switches to a date-based sync with a base date of 01/03/2021 .

 

(Note it is valid that the last row doesn’t have an end date as this isn’t known until the IRDB script has run).

 

In my test area, the injection of this table is as follows:

 

IMPORT ETL_CloudStats = a1.ETL_CloudStats

 

I am running some tests in production now. If it is OK, I will modify your debug script (C:\Bin\CloudARLExtract.ps1) to call this extract so you can do some testing / provide feedback. I hope this will give you what you need to accomplish what you want to occur.

 

Thanks,

 

Jason

 

Add label