• Typically into the surrogate key. I would have thought this was for late arriving dimension values, not facts (this is usually used for early arriving facts with late, or never, arriving dims).

    A couple of points on using this approach (from someone who used to use it religiously):

    - look at the trend in your data for early arriving facts. Do you get an ID in that data, but just don't get it 'yet' in the dims? If so, another approach I've seen used is to use the true value you received in the Business Value field (e.g. 22) but use the negative of this in the surrogate key (e.g. -22). Obviously you then use -22 as the FK in the fact. The advantage here is that you can easily identify the records, individually, that are 'unknown' versus having a group/glut of unknowns. This approach allows you to 'unwind' the unknown-ness for each record, without too much effort (an update on the dimension key and an update on the FK).

    - if none of the above is possible, just realize that ALL of your unknowns will get -1, so (say for a dimension called customer), this could represent 1 unknown customer, or perhaps 1500, or 5000.... who knows, because once you modify the fact record to use -1 as the FK, there is only one dimension record with that key. It's quick and easy to implement, but has some downsides 🙂

    HTH,

    Steve.