Working with my compatriots Ayad Shammout (@aashammout) and Peter Myers, we had found out that PowerPivot at times can be really insensitive…in terms of case that is. That is by default, the collation for PowerPivot is case-insensitive.
For example, if you import the table below:
Within the PowerPivot window, you’ll notice that the lower case “a” has now become an upper case “A”.
If “a” had come before “A”, then both rows would be “a” instead of “A” 😉
Why are you so in-sensitive?
Actually, it isn’t THAT in-sensitive. When importing, the PowerPivot client picks up the regional settings and sets the database language and engine defaults the collation based on that language (thanks to Deva Kaladipet Muthukumarasamy for clarifying this).
The technical reason behind this had to do with the great compression that you get with PowerPivot’s VertiPaq Column-based store. For example, if you a data source that has the rows:
And your regional settings are set to case insensitive, then you would certainly save more space and compress better if what was stored was just “PowerPivot” (the appropriate branding name) instead of “Powerpivot” which is wrong branding-wise anyways 🙂
Some additional thoughts
One way to work around this is to build a view that would map the different codes so that “a” is “something” and “A” is something else. You could also add modify it slightly such as “a.” so you keep the lower case. The PowerPivot development team is also considering allowing users to change this in the future.
This shouldn’t be a major hassle since there is a workaround and I’ve yet to see a lot of people run into this issue. Saying this, if it is a significant problem – I’d like to hear it in the comments section, eh?!