PowerPIvot Optimization, which column to sort?

Question

I've been told that the best way to compress/optmize powerpivot is to sort on the source table. while it's easy to make that statement. I'm having a hard time determining which is the best columns to sort. As an example, I have a table that includes 50+ columns (i.e. fiscal month, seller, customer info, pipeline staging, lead information, etc) some of these fields could be blank but some cannot be.

Is there any easy way to find what are the best sorting order? Is the trial and error the best approach?

score 1 · Accepted Answer · answered Jan 19 '16 at 00:34

1

Sort by the columns that have the most repetitive data. That way the compression will be optimized.

Here is good discussion of the topic. Note the second comment by the author Alberto Ferrari:

I got the best results sorting by the least variant datum first. If the same value for a column appears many times in a chunk, it will be compressed better. On the other hand, if the value changes very often, then compression will be worse.

answered Jan 19 '16 at 00:34

teylyn

34,374
4
53
73

Thanks for the info, is there anyway /query to find the count of unique entry for each column? – user2669043 Jan 19 '16 at 02:29
create a pivot table :-)) – teylyn Jan 19 '16 at 03:10
I've landed on using SQLQuery to get the distinct count of each columns, just thinking of having to do all that in a pivot table gave me a headache – user2669043 Jan 19 '16 at 06:28

PowerPIvot Optimization, which column to sort?

1 Answers1