Uniqueness with summary fact table with too many dimensions

Question

Hi,
We have summary fact tables that have > 15 dimensions. We need to put unique indexes but SQL server only supports indexes upto 14 dimensions.
Is there a way around this problem?
Thanks,

Answer 1

Hi,

Create a table of associated dimensions & generate a unique key for the combination of those dimensions. And in summary fact just have that key.

This will also reduce row size of the fact. Proper indexing will ensure the query performance.

Let me know if this helps !!!!

Thanks,

-------------

Sandeep Kulkarni.

DW/BI Analyst,

Microsoft Corporation, Redmond, WA, 98052.

 

(3 row(s) affected)

Answer 2

Where is it documented that the support is for 14?

---

Phil Brammer | http://www.ssistalk.com

Answer 3

Sorry, the limit is 16. SQL server gives an alert when you try to build the index with more than 16 columns..

Answer 4

Thanks Sandeep for the proposal.
My concern is that, this will increase the complexity of reporting queries, which fundementally conflicts with the main purpose of DWs, i.e. easy reporting.

Does anyone else have practical experience with this problem?

Answer 5

Vertical partitioning may help.

Vertical partitioning usually divides a big table (with large number of columns with huge row size) in "n" number of tables with one common key across all the vertically partitioned tables. Vertical partitioning should be done very carefully; since it might affect your ETL (if any) and/or reporting performance. Basic guideline would be to group most commonly reported attributes in a single group; so they can be reported querying only one vertical partition of the table increasing report performance. For uniform view of the table a SQL view can help.

Thanks,

-------------

Sandeep Kulkarni.

DW/BI Analyst,

Microsoft Corporation, Redmond, WA, 98052.

 

(3 row(s) affected)