locked
Cube measure not accessable- SSAS N/A RRS feed

  • Question

  • Hi,

    My users under roles are not able to access any measures data, all they see is N/A.

    a) Under cube membership I have granted Process database and read definition. 

    b) Under cube-tab I have set access as read and Local cube/Drillthrough access as "Drillthrough and local cube"

    I'm using SQL 2008R2

    Tuesday, August 29, 2017 11:33 AM

Answers

  • Hi StSingh,

    Thanks for your question.

    If the users under roles are not able to access any measures data, all they see is N/A, then you may have implement cell security for the measures. You may need to check the Cell Data settings in this role.

    For more information, please refer to below document:
    https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/grant-custom-access-to-cell-data-analysis-services


    Best Regards
    Willson Yuan
    MSDN Community Support
    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 MSDNFSF@microsoft.com

    • Marked as answer by StSingh Thursday, August 31, 2017 7:49 AM
    Wednesday, August 30, 2017 3:00 AM
  • Hi,

    You should be able to add all the measures in the same statement.

    Something like this :

    To grant access:

    (Measures.CurrentMember IS [Measures].[Reseller Sales Amount]) OR (Measures.CurrentMember IS [Measures].[Reseller Total Product Cost])  
    

    To deny access:

    (NOT Measures.CurrentMember IS [Measures].[Discount Amount]) AND (NOT Measures.CurrentMember IS [Measures].[Discount Percentage])  
    

    More details here

    Hope this helps.

    N

    • Proposed as answer by Nimish Rao Thursday, August 31, 2017 4:34 AM
    • Marked as answer by StSingh Thursday, August 31, 2017 7:50 AM
    Wednesday, August 30, 2017 10:53 AM

All replies

  • Hi,

    N/A normally is used when u are using CellSecurity.

    Tuesday, August 29, 2017 12:51 PM
  • Hi StSingh,

    Thanks for your question.

    If the users under roles are not able to access any measures data, all they see is N/A, then you may have implement cell security for the measures. You may need to check the Cell Data settings in this role.

    For more information, please refer to below document:
    https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/grant-custom-access-to-cell-data-analysis-services


    Best Regards
    Willson Yuan
    MSDN Community Support
    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 MSDNFSF@microsoft.com

    • Marked as answer by StSingh Thursday, August 31, 2017 7:49 AM
    Wednesday, August 30, 2017 3:00 AM
  • Thanks Yuan for the response.

    Should i write the above query for all the measure individually?

    Also, one of users is getting a very wierd error "Error encountered in transport layer" (sorry I'm squeezing in one more question).

    Thanks for your help.

    Wednesday, August 30, 2017 9:42 AM
  • Hi there,

    The transport layer error occurs when the user is not properly authenticated to the SSAS Server.

    Run the profiler against the SSAS Server and check if the correct user name appears in the trace or you are getting an anonymous login?

    Also, try impersonating the user in SQL Server Management Studio and see the error. Details here.

    Hope this helps.

    N

    • Proposed as answer by Nimish Rao Thursday, August 31, 2017 4:34 AM
    Wednesday, August 30, 2017 10:36 AM
  • Hi,

    You should be able to add all the measures in the same statement.

    Something like this :

    To grant access:

    (Measures.CurrentMember IS [Measures].[Reseller Sales Amount]) OR (Measures.CurrentMember IS [Measures].[Reseller Total Product Cost])  
    

    To deny access:

    (NOT Measures.CurrentMember IS [Measures].[Discount Amount]) AND (NOT Measures.CurrentMember IS [Measures].[Discount Percentage])  
    

    More details here

    Hope this helps.

    N

    • Proposed as answer by Nimish Rao Thursday, August 31, 2017 4:34 AM
    • Marked as answer by StSingh Thursday, August 31, 2017 7:50 AM
    Wednesday, August 30, 2017 10:53 AM
  • Note that you should ONLY expand the cell security expression if you need to restrict access to some of your measures. If you are happy with all users seeing all measures you should delete the cell security expression entirely. Implementing cell security comes with a big performance overhead.


    http://darren.gosbell.com - please mark correct answers

    Wednesday, August 30, 2017 11:26 AM
  • Thanks Nimish. I've posted a separate question on transport layer issue.
    Thursday, August 31, 2017 7:54 AM