September 16, 2014 at 12:11 pm
I'm working on the ETL from a regular OLTP environment into an OLAP-ified data warehouse. I remember from a previous job that my co-worker always had a -1 record for an unknown members in his dimension tables. (For late-arriving facts?) What I cannot remember is whether that -1 goes into the SurrogateKey or the BusinessKey column. Can anyone help?
September 16, 2014 at 12:33 pm
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.
September 19, 2014 at 1:27 pm
Thanks, Steve! I talked about the options with my boss & he thinks it will be extremely rare. He said to go with the -1 for now, but I really like your idea about adding a - of the business key instead. That's a great way to handle the problem of having more than 1 unidentifiable dim. I hope my boss is right and this won't happen. As long as he's happy, I'm happy. Thanks so much for your response!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply