I have a fact table that has 17 keys. Normally I have been designating the primary key as all of my dimensional keys. MS SQL server 2008 has a limitation of 16 columns in a primary key or unique constraint. Are there any work arounds?
+1
A:
Build a calculated column as a concatenation and index on that?
You only need to concatenate the columns in excess of the 15th, and make that extra column the 16th.
Are you sure you need seventeen dimensions?
lavinio
2009-07-16 19:07:01
Yes I do need 17 dimensions. I am using ints for the keys, how would the concatenation work?
Rick
2009-07-16 19:11:24
If all you care about is uniqueness and not collation, you could just convert them to strings and combine them with some separator character.If you care about collation, I'd use a money type, and use a multiplier. So if the 17th value ranges from 0 to 1,000,000, then the calc value = 16th * 1000000 + 17th.There are other more effective ways to do it, depending on the structure of your data and how much work you're willing to put in.
lavinio
2009-07-16 19:36:05
An even more abstract way to combine keys would be with the binary(x) data type -- add four bytes per INT key. But it breaks the star schema paradigm, and would be as ugly as sin.
Philip Kelley
2009-07-24 14:50:54
+1
A:
I downloaded Microsoft's project real. They do not include all keys in the pk. There are 2 scenarios. If all the keys actually are unique per the business rules then that is the pk. 2- If there are more keys on the table than what makes it unique per the busines rules, then a clustered index is used on the unique keys and the table doesn't have a primary key.
Rick
2009-07-24 14:43:05
A:
Can you combine dimensions? I once had three dimensions with three-four values each, and lumped them together into a "junk" dimension (Kimball's name, not mine) with about 48 rows.
Philip Kelley
2009-07-24 14:52:23
I had considered a junk dimension. In this particular case it didn't make much sense.
Rick
2009-07-27 17:00:45
A:
I hope you do not have a lot of facts. With 17 dimensions you run into problems aggregating realy fast.
Stephan Eggermont
2010-06-16 08:31:05
For this particular fact, we do not. It's actually a factless fact table. The only measure is a count against the table.
Rick
2010-06-17 18:48:16