Switching data mart from full load to incremental ... - Qlik Community

RonFusionHSLLC · ‎2024-01-22

First time for this one...for me at least.

Compose version:May 2022 (2022.5.0.555) on Sql Server 2022

I have a number of data marts, some of the downstream marts share (reference) dims from other marts...this situation is one of those.

This mart shares dims from 2 other upstream marts. The mart builds perfectly fine using a full load option. Using the incremental load option the first dim fails with a duplicate data error.

The dim that fails has a type2 column, and is not referenced and only used by this particular mart. Looking at the log, it's clear that the instructions are to incrementally load the dim.

The dim in question has 2 indexes on it, which is a bit different from some other Type2 dims I have...they only have a unique index on the VID column. This dim also has a unique index on the OID, FD and Obsolete columns. No other dims in my marts have this type of index.

I did a drop an re-create tables, I get the same results each time...these 2 indexes are created on the dim table.

TDMA_1Dim_CBank.IDX_DMA_UNQ_7301_7313 -- this is the name of the weird index.

/****** Object: Index [dbo].[TDMA_1Dim_CBank].[IDX_DMA_UNQ_7301_7313] Script Date: 1/22/2024 9:01:56 AM ******/
USE [COGITO_DW];
GO

CREATE UNIQUE NONCLUSTERED INDEX [IDX_DMA_UNQ_7301_7313]
ON [dbo].[TDMA_1Dim_CBank]
([CBank_OID] , [CBank_FD] , [OBSOLETE__INDICATION])
WITH
(
PAD_INDEX = OFF,
FILLFACTOR = 100,
IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF,
DATA_COMPRESSION = NONE
)
ON [PRIMARY];
GO

TDMA_1Dim_CBank.PK_7301_7313 -- typical index

CREATE UNIQUE NONCLUSTERED INDEX [IDX_DMA_UNQ_7301_7313]
ON [dbo].[TDMA_1Dim_CBank]
([CBank_OID] , [CBank_FD] , [OBSOLETE__INDICATION])
WITH
(
PAD_INDEX = OFF,
FILLFACTOR = 100,
IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF,
DATA_COMPRESSION = NONE
)
ON [PRIMARY];
GO

pool-2-thread-5 2024-01-20 12:48:55.400 [engine ] [INFO ] [] PI# 170 RUN : Populating temp table TTMP_7317_DM_MT_INS for incremental loading. pool-2-thread-2 2024-01-20 12:48:55.400 [engine ] [INFO ] [] PI# 122 RUN : Populating temp table TTMP_7315_DM_MT_INS for incremental loading. pool-2-thread-8 2024-01-20 12:48:55.400 [engine ] [INFO ] [] PI# 241 RUN : Creating temp table TTMP_7320_DM_MT_INS. pool-2-thread-9 2024-01-20 12:48:55.400 [engine ] [INFO ] [] PI# 96 RUN : Creating temp table TTMP_7314_DIM_MUT. pool-2-thread-10 2024-01-20 12:48:55.400 [engine ] [INFO ] [] PI# 67 RUN : Creating temp table TTMP_7313_DIM_MUT. pool-2-thread-3 2024-01-20 12:48:55.400 [engine ] [INFO ] [] PI# 145 RUN : Creating temp table TTMP_7316_DM_MT_INS. pool-2-thread-4 2024-01-20 12:48:55.400 [engine ] [INFO ] [] PI# 218 RUN : Populating temp table TTMP_7319_DM_MT_INS for incremental loading. pool-2-thread-8 2024-01-20 12:48:55.405 [engine ] [INFO ] [] PI# 241 FINISHED - ROWS AFFECTED: 0 pool-2-thread-8 2024-01-20 12:48:55.405 [engine ] [INFO ] [] PI# 242 STANDBY. pool-2-thread-9 2024-01-20 12:48:55.405 [engine ] [INFO ] [] PI# 96 FINISHED - ROWS AFFECTED: 0 pool-2-thread-9 2024-01-20 12:48:55.405 [engine ] [INFO ] [] PI# 97 STANDBY. pool-2-thread-3 2024-01-20 12:48:55.405 [engine ] [INFO ] [] PI# 145 FINISHED - ROWS AFFECTED: 0 pool-2-thread-10 2024-01-20 12:48:55.405 [engine ] [INFO ] [] PI# 67 FINISHED - ROWS AFFECTED: 0 pool-2-thread-3 2024-01-20 12:48:55.405 [engine ] [INFO ] [] PI# 146 STANDBY. pool-2-thread-10 2024-01-20 12:48:55.405 [engine ] [INFO ] [] PI# 68 STANDBY. pool-2-thread-1 2024-01-20 12:48:55.440 [engine ] [INFO ] [] PI# 68 RUN : Populating temp table TTMP_7313_DIM_MUT for incremental loading. pool-2-thread-8 2024-01-20 12:48:55.440 [engine ] [INFO ] [] PI# 97 RUN : Creating temp table TTMP_7314_DM_MT_INS. pool-2-thread-9 2024-01-20 12:48:55.440 [engine ] [INFO ] [] PI# 146 RUN : Populating temp table TTMP_7316_DM_MT_INS for incremental loading. pool-2-thread-3 2024-01-20 12:48:55.440 [engine ] [INFO ] [] PI# 242 RUN : Populating temp table TTMP_7320_DM_MT_INS for incremental loading. pool-2-thread-8 2024-01-20 12:48:55.450 [engine ] [INFO ] [] PI# 97 FINISHED - ROWS AFFECTED: 0 pool-2-thread-8 2024-01-20 12:48:55.450 [engine ] [INFO ] [] PI# 98 STANDBY. pool-2-thread-1 2024-01-20 12:48:55.480 [engine ] [INFO ] [] PI# 68 FINISHED - ROWS AFFECTED: 62 pool-2-thread-1 2024-01-20 12:48:55.480 [engine ] [INFO ] [] PI# 69 STANDBY. pool-2-thread-10 2024-01-20 12:48:55.485 [engine ] [INFO ] [] PI# 69 RUN : Adding index 'IDX_DIM_MUT_7313' to TTMP_7313_DIM_MUT. pool-2-thread-8 2024-01-20 12:48:55.485 [engine ] [INFO ] [] PI# 98 RUN : Populating temp table TTMP_7314_DM_MT_INS for incremental loading. pool-2-thread-6 2024-01-20 12:48:55.490 [engine ] [INFO ] [] PI# 266 FINISHED - ROWS AFFECTED: 96231 pool-2-thread-6 2024-01-20 12:48:55.490 [engine ] [INFO ] [] PI# 267 STANDBY. pool-2-thread-10 2024-01-20 12:48:55.510 [engine ] [INFO ] [] PI# 69 FINISHED - ROWS AFFECTED: 0 pool-2-thread-10 2024-01-20 12:48:55.515 [engine ] [INFO ] [] PI# 70 STANDBY. pool-2-thread-1 2024-01-20 12:48:55.530 [engine ] [INFO ] [] PI# 70 RUN : Counting number of updateable objects. pool-2-thread-6 2024-01-20 12:48:55.530 [engine ] [INFO ] [] PI# 267 RUN : Adding index 'IDX_DIM_MUT_7321' to TTMP_7321_DIM_MUT. pool-2-thread-1 2024-01-20 12:48:55.545 [engine ] [INFO ] [] PI# 70 FINISHED - ROWS AFFECTED: 0 pool-2-thread-1 2024-01-20 12:48:55.550 [engine ] [INFO ] [] PI# 71 STANDBY. pool-2-thread-10 2024-01-20 12:48:55.575 [engine ] [INFO ] [] PI# 71 RUN : Adding new objects to Type2 dimension 'TDMA_1Dim_CBank'. pool-2-thread-10 2024-01-20 12:48:55.600 [engine ] [WARN ] [] PI# 71 run failed, retry not supported. ( sqlstate '23000', errorcode '2601', message 'Cannot insert duplicate key row in object 'dbo.TDMA_1Dim_CBank' with unique index 'IDX_DMA_UNQ_7301_7313'. The duplicate key value is (1, 1780-01-01 00:00:00.000000, 0).' ) pool-2-thread-10 2024-01-20 12:48:55.600 [engine ] [ERROR ] [] PI# 71 Process Step# 1030 ERROR: Adding new objects to Type2 dimension 'TDMA_1Dim_CBank'. pool-2-thread-10 2024-01-20 12:48:55.600 [engine ] [ERROR ] [] sqlstate '23000', errorcode '2601', message 'Cannot insert duplicate key row in object 'dbo.TDMA_1Dim_CBank' with unique index 'IDX_DMA_UNQ_7301_7313'. The duplicate key value is (1, 1780-01-01 00:00:00.000000, 0).' java.sql.SQLException: sqlstate '23000', errorcode '2601', message 'Cannot insert duplicate key row in object 'dbo.TDMA_1Dim_CBank' with unique index 'IDX_DMA_UNQ_7301_7313'. The duplicate key value is (1, 1780-01-01 00:00:00.000000, 0).' at com.attunity.compose.instruction.SqlInstruction.run(SqlInstruction.java:325) [composeEng-1.1.0-SNAPSHOT.jar:1.1.0-SNAPSHOT] at com.attunity.compose.instruction.BaseInstruction.call(BaseInstruction.java:101) [composeEng-1.1.0-SNAPSHOT.jar:1.1.0-SNAPSHOT] at com.attunity.compose.instruction.FutureInstruction.run(FutureInstruction.java:140) [composeEng-1.1.0-SNAPSHOT.jar:1.1.0-SNAPSHOT] at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [?:1.8.0_202] at java.util.concurrent.FutureTask.run(FutureTask.java:266) [?:1.8.0_202] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_202] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_202] at java.lang.Thread.run(Thread.java:748) [?:1.8.0_202] pool-2-thread-10 2024-01-20 12:48:55.610 [engine ] [ERROR ] [] INSERT INTO [COGITO_DW].[dbo].[TDMA_1Dim_CBank] ( [CBank_OID] , [CBank_FD] , [CBank_TD] , [ID_Bank] , [Alias] , [Name] , [Address1] , [Address2] , [City] , [State] , [Zip] , [Zip4] , [Contact] , [Phone] , [Extension] , [Fax] , [Email] , [AccountNumber] , [RoutingNumber] , [Notes] , [EntryDt] , [EntryBy] , [LastModDt] , [LastModBy] , [RECORD_STATUS] , [OBSOLETE__INDICATION] , [CBank_RUNNO_INSERT] , [CBank_RUNNO_UPDATE] ) SELECT [ID] , [FD] , [TD] , [ID_Bank] , [Alias] , [Name] , [Address1] , [Address2] , [City] , [State] , [Zip] , [Zip4] , [Contact] , [Phone] , [Extension] , [Fax] , [Email] , [AccountNumber] , [RoutingNumber] , [Notes] , [EntryDt] , [EntryBy] , [LastModDt] , [LastModBy] , [RECORD_STATUS] , 0 , 2529 , 2529 FROM [COGITO_DW].[dbo].[TTMP_7313_DIM_MUT] WHERE [qlk__NewObj] = 1 pool-2-thread-10 2024-01-20 12:48:55.640 [engine ] [INFO ] [] Logging session info to database pool-2-thread-10 2024-01-20 12:48:55.650 [engine ] [INFO ] [] Logging instruction info to database pool-2-thread-10 2024-01-20 12:48:55.655 [engine ] [INFO ] [] Finished logging to database pool-2-thread-10 2024-01-20 12:48:55.655 [engine ] [ERROR ] [] 2024-01-20 12:48:55.655: FATAL (CODE 1): sqlstate '23000', errorcode '2601', message 'Cannot insert duplicate key row in object 'dbo.TDMA_1Dim_CBank' with unique index 'IDX_DMA_UNQ_7301_7313'. The duplicate key value is (1, 1780-01-01 00:00:00.000000, 0).' Cause: pool-2-thread-10 2024-01-20 12:48:55.655 [engine ] [INFO ] [] * Memory usage: Used memory = 9 MB Available memory = 2 GB (0.00%) pool-2-thread-10 2024-01-20 12:48:55.655 [engine ] [ERROR ] [] Terminated

sureshkumar · ‎2024-01-24

Hello @RonFusionHSLLC

Please open a support case and share the necessary details for further investigation.

Regards,

Suresh

deepaksahirwar · ‎2024-01-28

Dear @RonFusionHSLLC ,

From your query, i can understand that you are having trouble with switching data mart from full load to incremental load.

Based on your description, it seems that the problem is caused by the unique index on the OID, FD and Obsolete columns of the dimension table. This index prevents the insertion of duplicate rows based on these columns, but it does not take into account the VID column, which is the surrogate key for type 2 dimensions.
Therefore, when you try to incrementally load the dimension table, you may encounter duplicate key errors if the source data has changed in any of the other columns.

Solution:
One possible solution is to drop the unique index on the OID, FD and Obsolete columns, and create a new one that includes the VID column as well. This way, the index will allow multiple versions of the same object to exist in the dimension table, as long as they have different VID values. Alternatively, you can modify the logic of the incremental load to handle the updates and inserts of the type 2 dimension table more carefully, and avoid inserting duplicate rows.

I hope this helps you resolve the issue.Have a nice day! 😊

If our response has been helpful, please consider clicking "Accept as Solution". This will assist other users in easily finding the answer

Best Regards,
Deepak

RonFusionHSLLC · ‎2024-01-29

Turns out we are running an interim build, upgrading today to see where things end up. Even if I drop the index, the generated code would try to introduce dups in the dim in an undesirable way. I'll see what happens after the update

RonFusionHSLLC · ‎2024-01-29

First test, incremental with no data - success. Waiting to see what happens in the morning with an incremental run....looks good so far - newest release seems to be the fix

Switching data mart from full load to incremental load fails with duplicate data error

Errors - Unexpected Behavior