Normalize task keeps failing at Analyze Step or Normalize performance slow down significantly

[#1]

Symptoms:

1. Normalize task timeout at Analyze ETL step
2. Normalize task takes much longer than before

Diagnosis:

1. Please make sure “Keep History” is disabled
2. Run below SQL query in BDNA_PUBLISH database, it will return a huge number -
   

   select count(*) from (select distinct KEY_SW_INSTANCE from DIM_N_SW_INSTANCE) DIM;
   
   -- 10293847260​

3. Per BDNA.log, ETL step will consume the most time of the whole access, like-
   

   2021-03-03 01:45:38,793 0 INFO Call remoting service=tcp://xx.xx.xx.xx:8084/NormalizeBIUpdateServer/DataService, method=EtlInventory...
   2021-03-03 22:18:00,260 0 INFO Return message from Analyze: Success.​

4. Check logs in BDNA_A_Logger table in BDNA_PUBLISH database, and you will see all of those SQL statements which slow down the ETL process badly are all the “DIM_N_SW_INSTANCE$” related, like-
   

   3/3/2021 2:40:42 INFO : [NBI.NBI_NM_IT_CLEANUP_DIMS_SP].[Executing stmt_str = exec NBI_CREATE_TAB_CONS_SP @p_table_name = 'DIM_N_SW_INSTANCE$'; ]
   3/3/2021 4:34:00 INFO : [NBI.NBI_NM_IT_CLEANUP_DIMS_SP].[Executing stmt_str = exec NBI_CREATE_TAB_INDEXES_SP @p_table_name = 'DIM_N_SW_INSTANCE$'; ]
   3/3/2021 8:48:13 INFO : [NBI.NBI_NM_IT_CLEANUP_DIMS_SP].[Executing stmt_str ...
   
   
   3/3/2021 9:09:47 INFO : [NBI.DM_ENABLE_TAB_INDEXES_SP].[Executing stmt_str = ALTER INDEX DIM_N_SWINS_KEY_SW_IDX ON [dbo].[DIM_N_SW_INSTANCE$] REBUILD WITH (MAXDOP = 15, FILLFACTOR = 100)]
   3/3/2021 10:27:09 INFO : [NBI.DM_ENABLE_TAB_INDEXES_SP].[Executing stmt_str = ALTER INDEX DIM_N_SWINS_SWLTUSRDMN_IDX ON [dbo].[DIM_N_SW_INSTANCE$] REBUILD WITH (MAXDOP = 15, FILLFACTOR = 100)]
   3/3/2021 11:23:37 INFO : [NBI.DM_ENABLE_TAB_INDEXES_SP].[Executing stmt_str = ALTER INDEX DIM_N_SWINS_SWLTUSRLGN_IDX ON [dbo].[DIM_N_SW_INSTANCE$] REBUILD WITH (MAXDOP = 15, FILLFACTOR = 100)]
   3/3/2021 12:21:18 INFO : [NBI.DM_ENABLE_TAB_INDEXES_SP].[Executing stmt_str = ALTER INDEX DIM_N_SWINS_SWLTUSRNAME_IDX ON [dbo].[DIM_N_SW_INSTANCE$] REBUILD WITH (MAXDOP = 15, FILLFACTOR = 100)]
   3/3/2021 13:19:31 INFO : [NBI.NBI_UTIL_UPDATE_STATISTICS].[Running update statistics for DIM_N_SW_INSTANCE$ table]
   
   
   3/3/2021 13:46:33 INFO : [NBI.NBI_NM_IT_CLEANUP_DIMS_SP].[Executing stmt_str = SELECT * INTO DIM_N_SW_INSTANCE$_K FROM DIM_N_SW_INSTANCE$ D WHERE EXISTS ( SELECT 1 FROM FACT_N_IT_ASSETS F WHERE F.KEY_SW = D.KEY_SW AND D.KEY_SW > 0 ); INSERT INTO DIM_N_SW_INSTANCE$_K SELECT * FROM DIM_N_SW_INSTANCE$ WHERE KEY_SW=0; DROP TABLE DIM_N_SW_INSTANCE$; EXEC SP_RENAME DIM_N_SW_INSTANCE$_K, DIM_N_SW_INSTANCE$;]
   3/3/2021 15:35:44 INFO : [NBI.NBI_NM_IT_CLEANUP_DIMS_SP].[Executing stmt_str = exec NBI_CREATE_TAB_CONS_SP @p_table_name = 'DIM_N_SW_INSTANCE$'; ]
   3/3/2021 17:44:27 INFO : [NBI.NBI_NM_IT_CLEANUP_DIMS_SP].[Executing stmt_str = exec NBI_CREATE_TAB_INDEXES_SP @p_table_name = 'DIM_N_SW_INSTANCE$'; ]
   3/3/2021 21:57:48 INFO : [NBI.NBI_NM_IT_CLEANUP_DIMS_SP].[Executing stmt_...​

Root Cause:

The current normalize procedure will not clean the table - “DIM_N_SW_INSTANCE$”, which makes the table get bigger and bigger. However, the index of the table “DIM_N_SW_INSTANCE$” will be dropped and re-built for each normalize run, and the date set size has a significantly impact on the index creation. Thus, a huge “DIM_N_SW_INSTANCE$” table will slow down the performance badly.

Note:

The unclean data in “DIM_N_SW_INSTANCE$” will have not any impact on Analyze Reports.

Solution:

1. The temporary solution is to run below SQL statement to truncate the "DIM_N_SW_INSTANCE$" tabel :
   

   truncate table DIM_N_SW_INSTANCE$;​

2. The permanent fix will be included in 5.5.39 release.