Solved

DW_SourceCode as natural key

  • 21 November 2023
  • 10 replies
  • 172 views

Userlevel 3
Badge +1

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 [DMA].[ERP_TABLE]
(
[_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
[_pk_ProduktgroepId]
, [_pk_Produktnr]
, [_pk_Produktsoort]
, [_pk_Produktsubgroep]
, [DW_SourceCode] ------------------HERE----------------------
, @LoadDate AS [SCD From DateTime]
, 1 AS [SCD Is TombStone]
, @version AS [DW_Batch]
, V.[DW_SourceCode] ------------------HERE--------------------
, @LoadDate AS [DW_TimeStamp]
FROM #HistoryMatch H
INNER JOIN [DMA].[ERP_TABLE] V ON
H.[DW_Id] = V.[DW_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?

icon

Best answer by Thomas Lind 22 November 2023, 15:53

View original

10 replies

Userlevel 3
Badge

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.

Userlevel 6
Badge +7

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...

Userlevel 3
Badge +1

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? 

Userlevel 6
Badge +7

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.

 

 

Userlevel 3
Badge +1

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. 

Userlevel 6
Badge +7

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).

Userlevel 6
Badge +5

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.

Userlevel 6
Badge +5

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?

Userlevel 3
Badge +1

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

Userlevel 3
Badge +1

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

Reply