One of the most common scenarios I see is Oracle as a Source for SSAS Cubes.
When I started in my actual company and I looked at processing jobs I saw a lot of Errors which been ignored and/or mapped to Unknown member. I was just shocked that these looks like no problem in Production environment. So I asked whats happening, no problems?.
Well one reason was of different collations between source and cube like Case sensitive in the source and CI on the cube.
Many of the problems occure due to dirty data like Names which are loaded: one time starting with a uppercase, second time without uppercase. This happening due to missing Data cleansing process. Well these doesn't seem to be a problem directly. But in my cube I like to have it loaded without problem.
So what to do:
1) I changed the collation in my cube to Binary. So no more errors due to this point.
The sorting from my point of view looks ugly like:
AAA
ABC
AaA
Also with this Sorting I get now two members instead of one-> increasing my Cubesize
But this is a point which has to be decided by the business. "If u want an effective cube, watch your data".
2) Introduce a sort field for that attribute where I lowercase the complete name.
This is also one of the positive things of building up cubes: All the small dirty things u do or don't do comes to your eys :)
