locked
using a hierarchy in tabular mode for security RRS feed

  • Question

  • Hi we run 2012 enterprise. I'm so new to tabular mode that I will probably use the wrong words to ask this question.

    We have a fact table with a variety of related dims.  One dim is an employee id.  We have a hierarchical relational table that describes who reports to whom etc. 

    We would like to know if ssas can be trained to use these hierarchical relationships to ensure that I can only see my facts and those of the folks in my organization?  For example, when I go into ssms, browse the cube and start pivoting, can something be set up in ssas to limit what I can see based on the organizational structure?

    I don't know if it matters but the org relational table is based on sql hierarchy ids.  And every subordinate has only one boss.

    Wednesday, July 5, 2017 7:03 PM

Answers

  • Hi db042190,

    Thanks for your response.

    You can also find Dynamic security at page 14 in your first link(The whitepaper "Securing the Tabular BI Semantic Model" ). Actually all of links are talking the same thing "Dynamic security" with different sample. As SSAS are running queries in memory, performance should not be a problem.


    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

    • Proposed as answer by alexander fun Wednesday, July 12, 2017 10:54 AM
    • Marked as answer by db042190 Wednesday, July 12, 2017 10:57 AM
    Monday, July 10, 2017 9:47 AM

All replies

  • this article seems to address it but I don't know the difference between tabular mode and tabular bi semantic model so I'm not sure if its addressing something else.  And I don't see any discussion there on the performance hit.

    https://msdn.microsoft.com/en-us/library/jj127437.aspx

    Thursday, July 6, 2017 12:05 PM
  • this article seems to suggest it could be something as simple as a lookup (to something like empid x subordinate) in order to filter...

    https://docs.microsoft.com/en-us/sql/analysis-services/supplemental-lesson-implement-dynamic-security-by-using-row-filters

    Thursday, July 6, 2017 12:29 PM
  • Hi db042190,

    Thanks for your question.

    According to your description, you can achieve this through implementing dynamic security using USERNAME or LOOKUPVALUE function.

    Following are blogs about how to implement dynamic security using USERNAME or LOOKUPVALUE function:
    https://blogs.msdn.microsoft.com/himanshu1/2013/03/01/implementing-dynamic-security-in-tabular-models/
    https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-tutorial-row-level-security-onprem-ssas-tabular/

    https://bipassion.wordpress.com/2012/10/01/ssas-tabular-dynamic-security/


    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


    Friday, July 7, 2017 2:18 AM
  • thx, and what about the path alternative in my first link?  And what about the performance hit?  Your links look a lot like my 2nd link. 
    Friday, July 7, 2017 10:54 AM
  • Hi db042190,

    Thanks for your response.

    You can also find Dynamic security at page 14 in your first link(The whitepaper "Securing the Tabular BI Semantic Model" ). Actually all of links are talking the same thing "Dynamic security" with different sample. As SSAS are running queries in memory, performance should not be a problem.


    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

    • Proposed as answer by alexander fun Wednesday, July 12, 2017 10:54 AM
    • Marked as answer by db042190 Wednesday, July 12, 2017 10:57 AM
    Monday, July 10, 2017 9:47 AM
  • thx, and what's better the path approach or usename/lookup?
    Monday, July 10, 2017 10:08 AM
  • Hi db042190,

    Thanks for your response.

    I would suggest you to use the approach with both username and lookupvalue, as bipassion stated in below blog:
    https://bipassion.wordpress.com/2012/10/01/ssas-tabular-dynamic-security/


    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

    Tuesday, July 11, 2017 3:09 AM
  • can u explain why you have that opinion?
    Tuesday, July 11, 2017 10:38 AM
  • Hi db042190,

    Thanks for your response.

    As we know that we can use CUSTOMDATA and USERNAME to implement dynamic security for SSAS Tabular Model.

    CUSTOMDATA is not suitable for dynamic security when end user connects to tabular model directly. Because in that case, an end user might be able to change this property in whatever client tool he or she is using. While using both USERNAME and LOOKUPVALUE, end users can not do so like CUSTOMDATA.


    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

    Wednesday, July 12, 2017 9:06 AM