Udostępnij za pośrednictwem

Performance problems on high cardinality column in tabular model

  • Artykuł

Marco Russo has a great article regarding optimization on high cardinality column in tabular model at Optimizing High Cardinality Columns in VertiPaq

The basic idea is to avoid having high cardinality columns in an SSAS tabular model database. The reasons are

1. It takes a long time to process a high cardinality column. If you partition the table into multiple partitions, and you only process the data in a much smaller partition, you cannot query the data right away after processing the partition. You are required to do a ProcessRecalc on the database. When you run the ProcessRecalc command on the database, all the table columns/attribute hierarchies needs to be sorted again. Sorting that attribute hierarchy is going to be super expensive. There is cost of recalculating the attribute hierarchies and sorting their contents.

 

2. It takes a long time to filter on high cardinality column to find a particular value using MDX query based tool. Excel PivotTable and SSRS query designer uses the Filter(…,CurrentMember.MemberValue…) expression. It is slow to apply this Filter MDX function on a high cardinality column for an SSAS server admin user. For a user in a database security role, the performance problem is much worse. By the way, DAX based report tools, such as Power View, do not have such performance problem.

 

3. It requires more memory to store a high cardinality column.

 

4. It requires more disk space to store a high cardinality column in the data folder.

 

 

Marco's article suggests a common workaround to split the column into multiple columns, so the cardinality of each column is not too large. The cost of this workaround is the convenience of creating a report, and the users of the database need to be educated on such design change.

The #2 problem cannot be easily addressed by splitting the column. Even if the new columns have number of unique values in 100,000 range, it still takes a long time (a few dozen seconds) to complete the Filter function on either the MemberValue and MemberCaption properties for a user in a database role compare to an SSAS admin role user. The good news is, there will be a performance improvement release in SQL 2012 SP2 CU8 in September 2015. The KB number is 3087094. This improvement will bring much needed performance boost on filtering of a table column based on the member value or the member caption in MDX.

Comments

  • Anonymous
    August 25, 2015
    Great news!

  • Anonymous
    August 23, 2017
    The comment has been removed