Unique nonclustered Index (more than 16 columns) 1

[mSolver]

Greetings,
I have a fact table in a ROLAP style db which references several other tables (Dimensions). I have 17 columns in this fact table that I need to create a UNIQUE NONCLUSTERED Constraint on, but the maximum index size is 16 columns.

My first thought it to create a concatenated column for several or all of the unique columns and just use that single column as the unique index. Is this a good idea? Can anyone comment on best practices or other ideas I should consider to accommodate this requirement?

Regards,
Mike

 

[markros]

Yes, I think this is the common practice.

Take a look at this great presentation

http://cs.jjbresearch.org/files/3/default.aspx

PluralSight Learning Library

 

[RiverGuy]

What are these 17 columns? Are they the business key from the source system? If so, then yes I would concatenate them into one column not for the unique constraint but simply to make maintenance easier for your ETL. And then you can forgo your 17 individual columns.

You really don't need a unique constraint in most cases though. Your ETL should ensure the uniqueness, just as it ensures your foreign key references (you wouldn't need to declare foreign key relationships either).

 

[mSolver]

Thanks, the columns are dimensions related to the fact data and are required to be separate fields. Dimensions, such as time, Account, Entity, Department, currency, etc, all related to ERP's (Accounting Systems). In addition to ETL from many data sources, there will also be input coming from Excel users, so the unique constraints are critical to ensure clean data. I believe this follows the ROLAP STAR methodologies, I just have not needed this many dimensions in the past.

So my additional question to all is per Markus's response and reference document; can I just create a trigger to update the concatenated field or do I need to do the concatenation as part of the insert/update SQL. This is important because data may be inserted, changed and deleted from multiple interfaces.

Thanks again,
Mike