CODEWITHSUNDEEP
ssisetlscd
tlum
tlum

Reputation: 933

Issue with inferred member using SSIS Dimension Merge SCD Component

I'm using the SSIS Dimension Merge SCD Component (http://dimensionmergescd.codeplex.com/) and have a situation where I have a configuration with both SCD1 and SCD 2 columns. I have rows where the InferredMember flag is set however the component inserted new rows and did not reset the current flag on the existing inferred rows.

Does anyone else use this component and have you seen it work correctly? Am I misunderstanding? My understanding is the SCD2 columns become SCD1 where InferredMember is true, is this wrong?

Sorting is done in the database on the business key and the sort columns are set to match. The output of the DMSCD component are hooked directly to the OLE DB Command/OLE DB Destination components. This is in production and has otherwise been working correctly every day for months.

This is the Audit output from a run:

ExistingDimensionInputRowCount = 719941
SpecialMemberInputRowCount = 1
SourceSystemInputRowCount = 720516
UnchangedOutputRowCount = 719941
NewOutputRowCount = 720517
DeletedOutputRowCount = 0
SCD2ExpiredOutputRowCount = 0
SCD2NewOutputRowCount = 0
SCD1UpdatedOutputRowCount = 0
InvalidInputOutputRowCount = 0

Upvotes: 1

Views: 1314

Answers (2)

tlum
tlum

Reputation: 933

So, to answer my own question, yes, I am mistaken. The InferredMember flag alone does not trigger Inferred Member behavior.

Inferred members are skeletal records inserted in the dimension tables - often by a stored proc. - when a surrogate key look-up fails during fact table maintenance. The InferredMember flag typically triggers the dimension load process to fill in the remaining fields on the skeletal Inferred Member records. And in the case of SCD2 fields on Inferred Member records they are handled as SCD1 and new records should not be generated.

Through experimentation I was able to determine that the DMSCD component requires the Inferred Member skeleton to include at least the Business Key, Inferred Member flag, and an Active Date that is in the past - I was using the current date so the records were not treated as Inferred Member records, they were being treated as New records and duplicates were being generated.

Regrettably, I have never been able to find documentation that calls out the specification for inferred member records that are compatible with DMSCD so that Inferred Member records can be properly formed by the stored proc that is developed to create them. I'd also question the inability to tag inferred member records with an active date that is the current date.

Upvotes: 0

Todd McDermid
Todd McDermid

Reputation: 1680

You've only got issues with inferred members? And you're using the most recent version of the component as released on CodePlex?

It sounds like your issue is not solely related to inferred members, but that it may be occurring because SCD2 housekeeping columns (current member and date columns) are not set properly. The most common reasons outputs don't get delivered as you expect from the DMSCD is due to one of the following:

  1. You are altering dates using Derived Column components after the DMSCD, and/or are not updating/inserting the date information the DMSCD supplies. Instead, you're using hardcoded or variable values in a Derived Column, defaults in the table definition, or not mapping destination columns appropriately.

  2. The sort order of the inputs to the DMSCD is incorrect. You may be assuming that marking the IsSorted property on the output of your OLE DB Source to true, and setting various SortKeyPosition properties of columns is sufficient - it is not. Either remove the advanced edits you've made, or use a Sort component in the flow (for testing purposes - we can fix the OLE DB Source later).

Upvotes: 2

Related Questions