locked
Dimension Case sensitivity RRS feed

  • Question

  • Hi All,

    This is regarding case sensitivity of dimenion members:

    I understood from several blogs that Dimension  collation has to be mapped to the relational collation. I use oracle as relational source for my cube which is case sensitive. I can make Key columns as case -sensitive. But I am confused with name colums. Are name columns case sensitivity should be same as releational Source or key columns? 

    The problem I am facing here is

    1. If I make dimensions are case - insensitive, it failes with the duplicate key error due to relational source is case - sensitive.

    2. If I make key columns/Name columns case-sensitive, dimension data from 'Roles' do not filter it for users. For example 'Users' are part of dimension and if the dimension is case -  sensitive, is user 'MARK'  is stored in 'Users' and the if user log in as  'Mark' , are considered as different login than the 'User'. And the user cannot see the data.

    Can you give me an idea , how do I resolve this.

    With Warm Regards,

    Monday, October 29, 2018 3:03 PM

Answers

  • Hi PXPRXR,

    SQL Server uses a case sensitive collation while SSAS uses a case in-sensitive collation. This causes SQL Server to treat a and A as two different values, while SSAS treats them as the same value (giving the duplicate key error). The same thing happens with an accent sensitive collation.

    Solution: If you can, use the same collation type on both sides. If not, you could access the database table using a view that changes the collation or change the collation for the SSAS key columns.

    Reference:A duplicate attribute key has been found during processing (revisited)

    In this scenario, you may try to set both SQL Server and Key columns/Name columns to CASE IN-SENSITIVE so that the role is able to see the data.

    Regards,

    Pirlo Zhang 


    MSDN Community Support<br/> Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    • Marked as answer by PXPRXR Thursday, November 22, 2018 2:37 PM
    Tuesday, October 30, 2018 8:17 AM