Skip to main content

Hi all, 

We ran into an issue with history settings in combination with the DW_SourceCode. We need the DW_SourceCode in our primary key as there is overlap in the sources and this ensures uniqueness. 

 



In the history settings we cannot choose the DW_SourceCode to be the natural key: 



Without this, history does seem to work, but when we enable Create new record when a deleted record reappears in the source

 

We obtain the error 

Error during Data Processing for Table: Maintenance.Verkoopinfo at: Maintenance

The column name 'DW_SourceCode' is specified more than once in the SET clause or column list of an INSERT. A column cannot be assigned more than one value in the same clause. Modify the clause to make sure that a column is updated only once. If this statement updates or inserts columns into a view, column aliasing can conceal the duplication in your code.

I've looked into the stored procedure that produces this error and this snippet produces the error: 

-- History: handle deleted records

		INSERT INTO RDMA].OERP_TABLE]
		(
			b_pk_ProduktgroepId]
			,  _pk_Produktnr]
			,  _pk_Produktsoort]
			,  _pk_Produktsubgroep]
			,  DW_SourceCode] -----------HERE-----------------------------------
			,  SCD From DateTime]
			,  SCD Is TombStone]
			,  DW_Batch]
			,  DW_SourceCode] -----------HERE---------------------
			,  DW_TimeStamp]
		)
		SELECT
			b_pk_ProduktgroepId]
			,  _pk_Produktnr]
			,  _pk_Produktsoort]
			,  _pk_Produktsubgroep]
			,  DW_SourceCode] ------------------HERE----------------------
			, @LoadDate AS DSCD From DateTime]
			, 1 AS 	SCD Is TombStone]
			, @version AS sDW_Batch]
			, V.>DW_SourceCode] ------------------HERE--------------------
			, @LoadDate AS DDW_TimeStamp]
		FROM #HistoryMatch H
		INNER JOIN EDMA].NERP_TABLE] V ON
			H. DW_Id] = V.IDW_Id]
		WHERE
			H. Delete] = 1
			AND V.	SCD Is TombStone] = 0

DW_SourceCode seems to be inserted twice, which is of course not possible. However it is i.e. the same value so I believe one insert would suffice. 

 

We cannot just clone the DW_SourceCode as then we have an issue with mismatching keys for incremental loading.

 

So, is it at all possible to use the DW_SourceCode in combination with History?

We just updated the KB Article on how to use History to implement a Slowly Changing Dimension table. The link to the new article is: 

The new “Surrogate Key” section at the end of the article clarifies how to create the surrogate key for the slowly changing dimension table, which uses the DW_Id field. Please review and advise if this approach will be sufficient to resolve your issue.

Hi,

 

@Greg Lennox : I think the problem exists inside the history table, not with respects to linking to other tables due to PK/NK no longer being unique.

 

@sierd.zuiderveld : are you on the new release? In that case you can either use a mapping set or add a Source Table field *under advanced settings on the table) and use that in the PK. It seems to work for me...

Hey @Greg Lennox, thanks for linking the article. As @rory.smith points out, I do not have an issue with linking. My issue is that here it says the default natural key is the PK: 
 

however, I cannot use my primary key which includes DW_SourceCode. 

 

@rory.smith yes we are on the new release. I haven't used the new Source Table field yet, would that work if we map directly from the ODX? 

Hi,

seems to work for me: I have a number of fields set to type 2 and am also creating new records on reappearance after deletion. As this is a code structure issue and not a runtime issue, I think my Deploy & Execute succeeding is a good indication of success.

 

 

Thx @rory.smith . So this does indeed seem to work, it is also possible to add it as natural key. However, our tables have the same names: 

So the key wouldn't be correct. 

Hi,

 

true - I guess you would then at the moment need to either use an intermediate table (extra staging layer for instance) to add the DW_SourceCode's content in an additional field and map that into a history table, or use a Table Query to add the field in the ODX. 

 

In my opinion the syntax you showed should be fixed, so that you can add the DW_SourceCode  to the primary key (and/or natural key).

Hi @sierd.zuiderveld 

It is likely a feature change we could have. I know originally you could not even set the field as PK, so it is just an expansion on that.

What about using a supernatural key to merge the various options?

Like I did here.

As you can see I could choose all the fields of my table. I set the field as PK and it ran with no issues.
I did not have potential issues as you do as my names are a little bit different.

Hi @sierd.zuiderveld 

Were you able to use the intermediate table workaround or the ODX Query table workaround Rory mentions above? Or the supernatural key field suggested by Thomas?

Hey @Christian Hauggaard, I will test the suggestions tomorrow. I will update you on the outcomes as soon as possible.

@Thomas Lind , thanks your solution seems to work nicely!

Reply


Terms and ConditionsCookie settings