Issue mapping special members columns to existing dimension columns

Topics: Issues 2: Using the Component (Design-Time)
Nov 16, 2010 at 2:24 PM

I am experiencing an issue in only some of my SSIS packages where the SCD component continues to generate two warning messages which relate to the 'Column Mapping' tab.  The first message is 'Special Members columns are not completely mapped to Existing Dimension columns' and the second is 'Source System columns are not completely mapped to Existing Dimension columns'.

The first 'Special Members' message is causing me more trouble at the moment because whenever I ensure that all the Special Members columns are mapped correctly and finish the SCD dialog screens, the component fails to save the changes I have made, and the component remains in a state of error.

The second 'Source System' message is a little more confusing in that I am planning to build a hierarchy in an SSAS OLAP cube from the tables I'm populating via SSIS, so I need to include the foreign key of the related source table.  I then use this to look-up its surrogate key to include as a foreign key in the dimension table.  So, the source system contains the business key of the related column, and the existing dimension contains the surrogate key of the related column, which obviously don't map to one another.  How do I get around this issue of having columns in both the source system and existing dimension tables that do not map?

Many thanks for any help.

Coordinator
Nov 23, 2010 at 3:39 PM

You're going to have to do the lookup to translate your business keys to a surrogate key before you process them with the component.

As for your mapping warnings that don't have to do with that issue (or maybe they all do?) - the component should allow you to save it "as-is" even when it's in an error state.  Which version are you using?  v1.4's interface for mapping columns was much harder (IMO) to interpret...

Nov 24, 2010 at 8:48 AM

Hi Todd,

Thanks for your response.  You're absolutely correct in saying the component will let me save 'as-is' even when in error, but unfortunately this does not help me address the underlying issue of why it fails to save one of the special members mappings I make every time.  I have checked what I think could be the obvious causes, such as mismatched data types between the mapped columns, but nothing looks particularly suspicious. I am using version 1.5 of the component.

I have also noticed that in relation to the 'Source System' mapping error I reported above, an error message is still displayed even when I successfully map and save all columns from the source system onto the existing dimension.

Many thanks.

Coordinator
Nov 25, 2010 at 6:04 PM

That's interesting, I haven't seen that behaviour before.

Can you post a lot more details?  Please give details as to all of the incoming metadata from all inputs, how you're configuring the columns (are they BKs, SCD1, etc), and how you're mapping them to each other.

Dec 1, 2010 at 12:06 PM

Hello Todd,

I'm able to reproduce the issue with a very simple example.

How can I deliver you the package?

Regards,

Tjomme Vergauwen

Coordinator
Dec 10, 2010 at 7:24 PM

I think I understand now - I wasn't understanding what either of you were getting at earlier.

Short answer - You can't do what you're expecting to be able to do, you need to change your architecture.

Long answer:

Your dimension table has several "types" of columns in it:

  • The surrogate key - a single column, (usually) integer
  • The business key - could be several columns, associates dim rows with a single source row, usually the source system's PK
  • SCD Housekeeping columns - "current" dates, possibly audit columns, inferred indicators, etc...
  • Type 1 columns - columns that should be updated to the "most recent" value in the source system, regardless of how "old" of a version this row is
  • Type 2 columns - columns that should be tracked historically, triggering a new row version to be made when the source system values change

The columns you two are describing don't fit any of those categories.  Jason, if I (now) understand your situation correctly, I'll paraphrase as this:

You have a product dimension table.  This product dimension table has an SK for each row: ProductID.  It also has a BK that links its rows to source system rows: ProductSKU.  You have a "CategoryID" column in the dimension table that is linked to a Category dimension (lookup) table.  You intend for the CategoryID to be a Type 1 attribute - because any change to a product's category is assumed to be a "fix" to the data, and should be retroactive to all prior "versions" of that product in the data warehouse.  The Category dimension table has CategoryID as the SK, and "Category" and "SubCategory" as attributes.  You're trying to load the product dimension from your source system.  Your source system table has the following columns: ProductSKU, Category, SubCategory.

You can not load your dimension table directly from that source system.  You can not pass those "category" and "subcategory" columns from the source system "through" the SCD component without touching them.  The complaint you're getting from the SCD component is that you've left CategoryID "unlinked" to anything.  Why does it care?  Because you've identified that column as a Type 1.  That means you want the SCD component to compare that column to something from the source system, and if the dimension table has different information in the CategoryID column than what the source system does, you want to update the dimension table.  Your problem - there's nothing to compare it to - you're trying to compare apples to oranges.  You can't relate CategoryID to Category and SubCategory... without referencing the Category dimension table that decodes what "orange" a particular "apple" is.  If this paragraph isn't clear, please let me know.  Because if it isn't clear, then the following paragraph won't help you - it will only confuse you further.

What you need to do in this fictitious example - and this may not apply exactly to your specific circumstances - is to do a Lookup BEFORE you push the source rows into the SCD.  You need to look up the CategoryID in the Category dimension via the Category and SubCategory columns.  This implies (requires) that you load up your Category dimension table BEFORE you load up your Product dimension table... because the Product dimension table depends on the Category dimension table.  If you do this lookup before you push the rowset into the SCD, the SCD will then have "apples" to compare to "apples".

Dec 14, 2010 at 11:40 AM

Hello Todd,

 

This doesn't seem to be an answer to the issue in the test package I've send you.

Or am I missing something?

 

Tjomme

Coordinator
Dec 30, 2010 at 10:47 PM

It certainly does apply to the sample package you sent (IMO).

There is absolutely no way for the SCD to know how to map a "foreign business key" to a "foreign surrogate key".  It doesn't have a lookup table to do this decoding.  That's up to you to do in the flow before you send rows to the SCD.

Feb 25, 2011 at 11:56 AM

Hello Todd,

Indeed, I found the issue. It is mandatory to link an input to every Business Key and Type 1/2 field. Quite logic of course...

Sorry for this.