Considering surrogate keys for Unknown members

re: Considering surrogate keys for Unknown members

Mladen — Wed, 14 Oct 2009 21:45:18 GMT

the only really great reason i was given for not using int min value for the unknown is that the data compresion in 2008 is useless on it.

if you start at -1, 0 you get at least great compression for first "few" rows.

re: Considering surrogate keys for Unknown members

SkullKiller — Wed, 14 Oct 2009 22:48:56 GMT

Hi.

You posted a very valid issue. With the "unknown member SK" we are really giving a meaning to something meaningless. But is it really true? It can mean so many things. It's like the null value: null != null, null = null, null = "nothing else", null != "nothing else". It means so many things and means nothing at the same time. We are always trying to give it a meaning (absence of value, no value exists, ...). The key to this is definition: it means what we define.

So I see nothing wrong with the "unknown member SK". On the other hand, when we use the "unknown member SK" in hierarquies sometimes we are identifying it as the "unknown member" of something, let's say, "unknown member of laptop category". This way we sometimes use "-[0,9]*" member.

To ease things a bit when I use this kind of SK I rather call it "unindentified @atributemember" instead of "unknown @atributemember", this way I don't even think of the meaning issue given that it has a meaning.

re: Considering surrogate keys for Unknown members

Ben E — Thu, 15 Oct 2009 09:39:47 GMT

Hear!Hear! There is a reasonable rationale for this in OLAP frameworks that don't natively support the concept of an unknown member, but SSAS doesn't have this limitation. I accept that in 10+ years of working with relational databases I have never once seen a negative integer surrogate key; but then neither have I seen SQL injection attempted against any websites I've developed, cross-site scripting attacks, et al.

It is about inferring meaning, which in modern era of not having business logic enforced in the database is even more important.

re: Considering surrogate keys for Unknown members

Eric Wisdahl — Thu, 15 Oct 2009 13:02:44 GMT

Great question! I have thought about the negative SKs before and must say that the main reason why I believe that people don't use them is that they don't expect to ever need a SK larger than the max int value.

I suspect as more an more data is generated and examined we will begin to see a trend towards using "the other half" of the int region for SKs as well.

As to the actual -1 for an unknown member? Well, I would say that there is little harm in the consistent use of this value. The main risk is from lazy programming...

re: Considering surrogate keys for Unknown members

Dave — Fri, 16 Oct 2009 13:52:30 GMT

I'll just be happy if I can get my team to stop hardcoding SK values generally in our production code. For example in a report, rather than joining to the lookup table and filtering on the business descriptive fields all too often the code will filter on an SK.

Where

Store = 5

Apparently it's fine, the SK will never change. Well we better hope it doesn't as if it did we'd have to recode half our system.

I agree that there is no need to use -1, but there is no great need to not use it either. Making use of -ve numbers seems to make sense though, especially as we recently hit the upper limit on a table with a few hundred million rows! Big Int ftw \o/

re: Considering surrogate keys for Unknown members

jamiet — Fri, 16 Oct 2009 14:29:22 GMT

Dave,

Ouch, hardcoding SK values in REPORTS? Jeez...that's a recipe for disaster. Does the report work in different environments? Surely the SKs aren't guaranteed?

-Jamie

re: Considering surrogate keys for Unknown members

Everest — Tue, 20 Oct 2009 01:48:01 GMT

I not only used negative SKs recently, but purposefully let the mainframe creating + surrogates so SQL Server could assign negative surrogates whenever the key was generated from the internet. In either case it's as easy to go forwards and backwards and avoided collision.

Good stuff, Jamie! Thanks.

Lee

re: Considering surrogate keys for Unknown members

Ralph Wilson — Fri, 23 Oct 2009 15:03:35 GMT

I haven't thought about using the -1 (or other negative values) for surrogate keys before reading this post. Now, though, I am wondering about using zero (0) instead of the -1. It seems to me that there could be a psychological and "reasonable" basis for using zero . . . since you have zero information about the Unkown. ;-)

re: Considering surrogate keys for Unknown members

Kristian Wedberg — Sat, 07 Nov 2009 15:53:32 GMT

Hey Jamie, on the meaninglessness:

IMHO a so called key decision in a data warehouse with SKs is whether Surrogate Keys are static, or if Business Keys are static, i.e. the warehouse should guarantee that one or the other won't change over time (without also changing its meaning.) This way the warehouse users (e.g. reports) know whether to hard code Surrogate Keys or Business Keys in filters etc. The decision doesn't have to be the same across the whole warehouse, but consistency is obviously preferable.

Examples:

* If an organisation (or country, or person, or...) changes its name (assuming this is the Business Key), and I still want to treat it as the same organisation, I should make the Surrogate Keys static and hard code them in reports, but allow Business Keys to vary. This way the reports will still filter on the correct entry, even though the displayed name has changed.

* If I want the Business Key change to imply this is a separate organisation, then reports should hard code business keys instead.

In the first example we're adding even more meaning to SKs (beyond the -1 meaning), and as long as it's a conscious and communicated decision I believe it's a very good one.

Tossing in even more meaning into the SK mix, instead of just using -1 = (Unknown), it's often good to use -1 = (Not Available) and -2 = (Not Applicable) to better show _why_ there is missing data.

Thoughts?

re: Considering surrogate keys for Unknown members

jamiet — Sun, 08 Nov 2009 10:19:13 GMT

Hi willshak,

Thanks for the comments.

Regarding static SKs or business keys...yes, one of them should definitely stay static/stable/be immutable. Generally I use business keys in my filter predicates as it reinforces the point that SKs are supposed to be meaningless. I would expect this to be the norm though readily accept that there may be other circumstances (possible unforeseen) where the business key changes.

Regarding breaking down the reason for something being Unknown - yes, I think this is a good idea. On my most recent engagement most dimensions had an 'Unknown' & a 'Not applicable' member.

Of course, if you're using the "Not Applicable" member then it suggests that the data you're storing against it is being stored at the wrong fact grain - however there may be good justifications for doing this.

Thanks again for the cvomments, good thought provoking stuff.

-Jamie

re: Considering surrogate keys for Unknown members

JamesH — Tue, 10 Nov 2009 18:18:16 GMT

I have been using 'Unknown' -1 : and 'Uncategorized' -2 for many years but only when the project owner (or profiling) states that it is necessary to balance or keep track of these types of data. Unknown has primarily been used in the past (even before SSAS) to facilitate auditing and correcting the master data, if it's ignored in the onset of a DW/DM project, you can bet that over time a manager that is concerned about his bonus will eventually want to know what the heck 'Unknown' means and how it's impacting his performance rating (sales and margins). Uncategorized is a bit different and is usually provided by the source system but can have the same effect (positive/negative) on a system. The navigational

aspect of these comes into play with balancing and without them can create the 'blackhole of death' for a BI project.

Concerning SK's, it is and always has been necessary for a warehouse to 'shield' itself from the inconsistencies and changes from an underlying system(s); moreover, carrying natural keys might be useful with a single source but when you start integrating multiple systems you will inevitably run into the same problems as a relational database unless you use a completely generic key. I could care less if it is a string or numeric as long as it can be controlled. A good ETL process is, IMHO, the proper place for assignment and control of SK's.

re: Considering surrogate keys for Unknown members

JustinS707 — Sun, 05 Sep 2010 19:12:49 GMT

Is it too much to ask SSIS to create an inferred member surrogate key in the dimensions when the fact arrives too early? Use of the "-1" value for the Unknown member is inefficient:

1. You must keep your natural keys in the fact table to be able to update the surrogate key at some point. Effectively, you defeat the purpose of using the surrogate key because you must keep the natural key column for every dimension that has an unknown member.

2. You must have a process to check the dimension for all Unknown member natural keys in the fact table and update the fact table with the correct surrogate key.

I believe this convention is antiquated and a lazy coding practice.

Data Warehouse modelling deliberations – foreign keys and unknown members

SSIS Junkie — Tue, 16 Apr 2013 21:36:39 GMT

Earlier today I posted the following question on Twitter: Foreign keys in a data warehouse. Yes or no?

re: Considering surrogate keys for Unknown members

James Snape — Wed, 17 Apr 2013 10:01:52 GMT

Hi Jamie,

I read this blog post when you originally posted it and I used to be with the -1 crowd but in my last two projects I've decided to try "letting null be null"... We do apply some rules but overall it was most liberating.

1. If Null or <empty string> have meaning in the source system then replace the null or empty string with the meaning (e.g. NULL > N/A, '' > Empty) BEFORE the surrogate key mapping stage. Then these values will be mapped using the same mapper.

2. We consider null to be unknown once it reaches the data warehouse. We configure SSAS Unknown member handling properly.

I was worried about doing this but in reality we have far fewer issues than we ever did with -1s and -2s. It even helps us maintain a ROLAP realtime partition because if rows get added but the dimension hasn't caught up then the cube still works - we just get some unknown data for a while which magically fixes itself when the dimension is processed.

I do start keys at 1 because of a) data compression and b) until sequences came about it was really useful to be able to create an identity in the opposite direction when your modelling needs change without worrying about clashes - particularly around inheritance hierarchies.

Oh yeah - FK's are a must in the DW too. Otherwise none of the tools work and new starters have no idea how your model fits together. I consider it unprofessional to not have them although if there is a proven performance hit I might keep them as metadata only FK's.

re: Considering surrogate keys for Unknown members

jamiet — Wed, 17 Apr 2013 10:06:38 GMT

Great comment, thank you James. Definitely some food for thought there.

The "data compression" argument for starting keys at 1 is something I've heard before. I'm wrestling with whether that's a good enough justification or not. Everything's a trade-off.

cheers

Jamie