Get Started. It's Free
or sign up with your email address
Rocket clouds
DI ETL by Mind Map: DI ETL

1. General Troubleshooting

1.1. Check process log tables to identify fail cause

1.1.1. SELECT * FROM NWMStaging .[NWM].[ProcessLogDetails] NOLOCK ORDER BY ProcessLogDetailsId DESC

1.1.2. SELECT * FROM NWMStaging .[NWM].[ProcessLog] NOLOCK ORDER BY ProcessLogId DESC

1.2. View NWM_Daily_ETL history to see which step failed

1.3. Check the Excel file of historic issues encountered to find similar scenarios

1.3.1. http://0105sps01/sitedirectory/EnterpriseSolutions/InformationManagement/Data Integrity Process/DI Troubleshoot Log.xlsx

1.4. Search in code for

1.4.1. Keyword in C:\Projects\DataWarehouse

1.4.2. _SP folder in VS Team Project

1.5. Before rerunning an ETL step

1.5.1. Write endDateTime to ProcessStatus

1.5.1.1. UPDATE [NWMStaging].[NWM].[ProcessStatus] SET EndDateTime = GETDATE() WHERE ProcessName = 'uspNWMProcess'

1.6. Rerun from failed step

1.6.1. Right click job NWM_DailyETL

1.6.1.1. Start from step

1.6.1.2. Select #

2. 18 DI ETL Jobs

2.1. ETL Process

2.1.1. Custodian to Source

2.1.1.1. 1. ETL - Check NWM_SSIS jobs

2.1.1.1.1. Check that custodian jobs completed successfully

2.1.1.1.2. SSIS Jobs

2.1.1.2. 2. ETL - Check for holiday

2.1.1.2.1. select @IsHoliday = dbo.[udfIsHoliday](convert(date,getdate())) if (@IsHoliday = 1) raiserror ('No ETL on holiday date', 16,1)

2.1.1.2.2. Long Weekend or STAT Holiday

2.1.1.3. 3. ETL - Wait for data

2.1.1.3.1. select @sdate = [nwmlanding].[dbo].[udfSourceDate]('ETL') if (@sdate is null) begin raiserror ('Already processed ETL for today.', 16,1) end

2.1.1.4. 4. ETL - Check for TD metadata change

2.1.1.4.1. Custodian metadata changed (e.g. new columns?) - if so, send alert

2.1.2. Source to DW

2.1.2.1. 5. ETL - Source to DW

2.1.2.1.1. This step will update NWMSource with current day data from various custodian/non-custodian sources and launch Source to DW ETL.

2.1.2.1.2. Troubleshoot

2.1.2.2. 6. ETL - Source to DW Reconciliation

2.1.2.2.1. This step will reconcile processed DW data against custodian sources. This steps has two parts.

2.1.2.2.2. Troubleshoot

2.1.2.3. 7. ETL - Wait for global account creation

2.1.2.3.1. This step will pause the job until global accounts are created in JNFG.

2.1.3. DW to JNFG

2.1.3.1. 8. ETL DW to JNFG

2.1.3.1.1. This step will publish current day data from DW to JNFG

2.2. 9. ETL - Investment success notification

2.2.1. This step notify Datawarehouse Group when the job completes successfully (Investment).

2.3. 10. ETL - Post process

2.3.1. Generates DI reports for backdated transactions

2.3.1.1. Troubleshoot: NWMStaging.NWM.uspDIReportBackdatedTransactions.

2.3.2. Generates DI reports for cancel/reversal transactions

2.3.2.1. Troubleshoot NWMStaging.uspDIReportCancelAndReversal

2.3.3. Generate QTrade product prices from TD prices

2.3.3.1. Troubleshoot 0105SQL01.JNFG.dbo.uspDICreateQtradeProductPricesFromTd

2.3.4. Uptdate total unit and market value

2.3.4.1. Troubleshoot 0105SQL01.JNFG.dbo.uspDIUpdateTotalUnitsAndMarketValue

2.4. DI Process

2.4.1. JNFG

2.4.1.1. 11. Sample Client

2.4.1.1.1. This checks Ian Hass base client JNFG records against Sample Client and updates JNFG to match. (InvestTrans, InvestHolding, InvestUserDef)

2.4.1.1.2. If there is a new client added or removed from the base client, the process will stop with an error. You have to go into CRM and create the missing client or remove

2.4.2. NWMODS Refresh NWMODS to DW JNFG to DW

2.4.2.1. 12. ETL - Client Process

2.4.2.1.1. Refresh NWMODS with the latest data from JNFG, FiscalReports, etc. etc.

2.4.2.1.2. Push data from NWMODSSource to DW

2.4.2.1.3. Push JNFG data to DW

2.4.2.1.4. Populates (if applicable) the ProcessLog table and executes the cooresponding stored procs as indicated in the ProcessOrder table.

2.4.2.1.5. Checks to see if previous updates were successful before continuing.

2.4.2.1.6. Restrict to the following tables only

2.4.2.1.7. DW tables that will be populated in NWMStaging.NWM.ProcessOrder:

2.4.2.1.8. DW tables that will be refreshed when missing accounts are added

2.4.2.2. 13. ETL - Client success notification

2.4.2.2.1. This sproc sends email notification to stakeholders

2.4.3. DW to DM

2.4.3.1. 14. ETL - DM Process

2.4.3.1.1. Replication is disabled prior to load to DM, then enabled after completion

2.4.3.1.2. Populates (if applicable) the sysProcessLog table and executes the cooresponding stored procs as indicated in the sysProcessOrder table.

2.4.3.1.3. Checks to see if previous updates were successful before continuing

2.4.3.1.4. Troubleshoot

2.4.3.2. 15. ETL - Post DM Process

2.4.3.2.1. Auto runs the checks after DM ETL is finished.

2.4.4. Client360 Data refresh

2.4.4.1. 16. Refresh Client 360 after DM ETL

2.4.4.1.1. Refreshes data in dwh03 Client360DM database from NWMReportingDM source.

2.4.4.1.2. Also triggers snapshot replication from dwh03 Client360DM to dwh04 Client360DM.

2.4.4.2. 17. Refesh Internal WebODS

2.4.4.2.1. Triggers snapshot replication from dwh03 Client360DM to 0005sql01 WebODS for Client 360 dashboard.

2.4.4.3. 18. Refresh External WebODS after DM ETL

2.4.4.3.1. Triggers snapshot replication from dwh03 Client360DM to 0105c3sql01 WebODS for Client Website.

3. Tracking

3.1. Automatic process

3.1.1. Starts 06:15

3.1.2. Takes about 1.5 hours

3.2. Notifications

3.2.1. Information Management Inbox

3.2.2. Normal status

3.2.3. Error status

4. Data files

4.1. Schedule

4.1.1. Download data Night before at 10pm

4.1.1.1. 9:30PM the earliest

4.1.1.2. Next morning if not

4.1.1.3. If weekend files may be on Sat. night

4.2. Where to find

4.2.1. NBIN files from https://filecabinet.nbcn.ca/

4.3. What to do

4.3.1. Save cabinet zipped file

4.3.2. Copy file to local computer

4.3.3. Run uffv3.bat from local computer

4.3.3.1. Extract files to C:\uffrvc2\Output

4.3.4. Copy the 4 output files

4.3.4.1. Files

4.3.4.1.1. OUTPUTA.TXT

4.3.4.1.2. OUTPUTB.TXT

4.3.4.1.3. OUTPUTC.TXT

4.3.4.1.4. OUTPUTD.TXT

4.3.4.2. If a file is missing make an empty file with that name

4.3.4.3. Destination here

4.3.5. Rename files in output folder

4.3.5.1. Prefix with YYYY-MM-DD

4.3.5.2. Sample: 2019-08-29 OUTPUTA.TXT

5. SSIS

5.1. Server: 0105DWH03

5.2. Steps

5.2.1. Logon to 0105DWJ03 server

5.2.1.1. User: nicolftp

5.2.1.2. E:/SSIS/SSC

5.2.2. Run WinSCP

5.2.3. Check for todays file

5.2.3.1. Outgoing/Prices_pre-aprproval

5.2.3.2. If mor than 1 file

5.2.3.2.1. Contact NWM Fund Admin

5.2.3.2.2. Delete wrong file

5.2.3.3. If file is missing

5.2.3.3.1. Notify NWM Fund Admin

5.2.4. Run step 1

5.2.4.1. NWM_SSIS_SSC job

5.3. General info

5.3.1. Server: 0105DWH03

5.3.2. File arrival ETA

5.3.2.1. Monday: 08:45

5.3.2.2. Any other day: 08:15

5.3.2.3. No file after stat holiday

5.3.3. NWM Fund Admin

5.3.3.1. Toby Lee

5.3.4. Completion time 15 mins

6. Missing Global Accounts

6.1. General info

6.2. When Global Account is missing

6.2.1. Steps

6.2.1.1. Wait for NAT team to create the account in FDP

6.2.1.1.1. Usually before 8am