James Snape - Uniqueidentifiers and Parent Child Dimensions

Tuesday, August 10, 2004

« Don't let this happen to you... | Main | Trees and Common Table Expressions »

Uniqueidentifiers and Parent Child Dimensions

It seems that you can't use columns of type uniqueidentifier as parent-child keys because the dialogs (with and without intellicube) only list integer candidates. I hope this gets fixed as its much easier to use uniqueidentifiers as primary keys (no need to mess with scope_identity).

Of course, the reason might be that no one would use a key so large in a fact table and that's why they are not listed...

SQL Server

by James Snape This posting is provided "AS IS" with no warranties, and confers no rights.
posted on Tuesday, August 10, 2004 4:31:05 PM (GMT Daylight Time, UTC+01:00)

Comments [2] Trackback
Related posts:
Do you have great business intelligence skills?
Integration Services Design Principals
Physical Data Warehouse Design
Analysis Services Essential Reading
When should you do an incremental extract?
Post TechReady and Popfly Invites

Wednesday, August 11, 2004 8:39:47 AM (GMT Daylight Time, UTC+01:00)

One approach is to convert the uniqueidentifier to CHAR (in which case, stripping out the hyphens is a good idea too). On the plus, you get the functionality of a uniqueidentifier, in a way that is acceptable in parent-child dimensions; on the minus, the size of the key cast to CHAR is even bigger. Which is more valuable? "*You* decide!"

Koan

Wednesday, August 11, 2004 11:22:33 AM (GMT Daylight Time, UTC+01:00)

Hi Koan,

If you try using a cast as char then the dimension dialog allows you to select the new char column as the parent key but when you come to process it you get the following error:

"Errors in the metadata manager. The skipped levels column of the Parent Child attribute is not valid because either the attribute is not a parent attribute or the data type is not integer."

The message is misleading though as I wasn't using skip levels. I think the key think is the part about "the data type is not integer".

Regards,
James.