Unmatched Members - assign a specific Sid.

[Robl]

Sometimes the users are able to enter an invalid customer code into the system.
This won’t match a customer in the customer Dim

Rather than reject the record I want to accept unmatched members but set the surrogate key of the unmatched members to a fixed value.
So any unmatched member gets a surrogate ID of 1.
Then I can set the dim to have SID of 1 to be ‘Missing Customer’.

Is there a way of doing this in the F build when I join a dim, or do I need to do it manually and do an outer join to the dim in the SQL ?

[UseCog]

Hi,

i think you can match the customer id with the one in dimension at Transformation model level and if it is not matching , then those fact entries will be rejected and kept to the rejection file.

One way you can do is, you can create another build to read from the rejected build and place it in the jobstream just after the actual fact build. hope this will solve your problem

UC

[rockytopmark]

Create a 0 SID row in the Customer Dimension. Set the value of the SID to 0, then use something like "Invalid" or "Unknown" in the Name/Description columns, 0s for the numerics, for example.

Then in the Delivery for the Fact, if there was no hit on the lookup to Customer Dimension, there will be no value (NULL) for the Customer SID... use the property in the delivery for value to use if NULL...  set it to 0.  Rows will invalid customers will get the 0 SID for the Customer Dimension.

Set Dimension lookup to allow unmatched members.

You will never have a reject, and the unmatched rows will not be lost.  You can then choose to sinmply report on them, or to possibly fix the process that invalid customer entry is allowed, using this data as a tool.