January 22, 2015 at 2:35 am
Hi everyone,
I read everywhere that it's best to fix the fact tables' foreign keys that can't be found in the dimension tables in the data warehouse by setting it to -1 for instance and by creating an "undefined" record in the dimension table.
That makes perfect sense and I like this idea, this way we can create foreign keys constraints, we can always use inner joins, it's cleaner and we can see right away that the link is missing.
However, it means you have to find "undefined" values for all your columns for this particular dimension row. If it's a text column that's fine, but if it's a date or a bit, then you have to make arbitrary choices that could be problematic. For instance, ok, 1900-01-01 could be used for the undefined date, but for a bit you'll have to choose 0 or 1 which already has a specific meaning.
In SSAS I like the UnkownMember because it's very clear, you can use it on any data type and you can change the label to whatever you want. So I think from an end user perspective, using this is way better than using your "undefined" values. But it seems that you can use this only if the key has not been found, which, again, is considered bad practice.
So my question is, is there a way to use the UnknownMember for a particular key (-1 for example) even if it has been found, and then override that row's values?
Thank you.
January 22, 2015 at 4:20 am
You can achieve what you ask but you'd be better off using your abstraction layer (DSV or Views) to change the values/datatypes in your dimensional unknowns, preserving the integrity of your data warehouse.
So an example of how to do exactly what you ask for is: In your abstraction layer (view or DSV) put a CASE against the foreign key in the fact table so that it renders all -1 as NULL.
In your dimension properties set the UnknownMember property as "Visible" and give it an appropriate name.
In you measure group properties set the following ErrorConfiguration values:
KeyErrorAction - "ConvertToUnknown"
KeyErrorLimit - "-1"
NullKeyNotAllowed - "IgnoreError"
Bear in mind that in this scenario if you have KeyDuplicate set to stop on error then you may encounter issues.
After that just process and you'll see that your -1 values are converted to the specified unknown member value.
I would go with my first suggestion though as the second one is really more of a hack and will probably cause more trouble than it's worth, but you did ask 😀
January 22, 2015 at 4:55 am
I did exactly the same as PB_BI suggested in his reply.
I use 0 (unknown) and -1 (N/A) as special surrogate keys in the DWH. This enforces referential integrity. If you use NULL in your surrogate keys, this can become problematic if that surrogate key is part of the business key of the fact table (for example with early arriving facts).
If you want to use the UnknownMember in SSAS, you can convert those special SKs to NULL in a view (I try to avoid the DSV as much as possible).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 22, 2015 at 6:33 am
Thank you guys for this tip!
I didn't think about this, that's a pretty good idea.
I'm surprised there's not an option in SSAS allowing to do that without having to add a layer or adding logic in the DSV.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply