locked
Refreshing problem in SSAS 2008 while creating role using dynamic security RRS feed

  • Question

  • I have created a assembly which returns a string in MDX format according to values in accessinfo table in the database.
    This function is used in Cell data expression of the role so that the values are dynamically restricted in the browser.But the role is not automatically getting refreshed once the values are changed in accessinfo table.To reflect this change in browser,the role has to be opened again explicitly and click OK.Is there any way to refresh role(to reflect the change in the accessinfo table)automatically rather than doing explicitly in role definition
    Tuesday, September 8, 2009 4:53 AM

Answers

  • You can send a ClearCache XMLA command to the AS server every time the table changes. Somehow you have to force AS to drop the security definition caches that were built up for the users and roles -- and ClearCache does that for you (besides dropping other caches).


    -- This posting is provided "AS IS" with no warranties, and confers no rights
    • Marked as answer by Raymond-Lee Friday, September 18, 2009 9:26 AM
    Tuesday, September 8, 2009 5:49 AM
  • Yes, this is caused by the cache.

     

    ClearCache XMLA statement demo

     

    <ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

      <Object>

        <DatabaseID>Adventure Works DW</DatabaseID>

      </Object>

    </ClearCache>

     

    And another similar thread:

    http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/fbcbe64a-22f9-4c3a-854d-4599f0501f1a

     

    Hope this helps,

    Raymond

    • Marked as answer by Raymond-Lee Friday, September 18, 2009 9:26 AM
    Wednesday, September 9, 2009 7:43 AM

All replies

  • You can send a ClearCache XMLA command to the AS server every time the table changes. Somehow you have to force AS to drop the security definition caches that were built up for the users and roles -- and ClearCache does that for you (besides dropping other caches).


    -- This posting is provided "AS IS" with no warranties, and confers no rights
    • Marked as answer by Raymond-Lee Friday, September 18, 2009 9:26 AM
    Tuesday, September 8, 2009 5:49 AM
  • Yes, this is caused by the cache.

     

    ClearCache XMLA statement demo

     

    <ClearCache xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

      <Object>

        <DatabaseID>Adventure Works DW</DatabaseID>

      </Object>

    </ClearCache>

     

    And another similar thread:

    http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/fbcbe64a-22f9-4c3a-854d-4599f0501f1a

     

    Hope this helps,

    Raymond

    • Marked as answer by Raymond-Lee Friday, September 18, 2009 9:26 AM
    Wednesday, September 9, 2009 7:43 AM
  • Jagadeesh,

    As Raymond mentioned Clear Cache XMLA seems to be the perfect solution. But I had encountered similar issue with SSAS 2000 (Dynamic security with DLL) and the solution that I applied was to process my Time dimension (Incremental process). Then it resolved my issue. Not sure if this helps you in this case as well.

    Thanks,
    Ashok
    Wednesday, September 9, 2009 1:05 PM
  • Jagadeesh,

    As Raymond mentioned Clear Cache XMLA seems to be the perfect solution. But I had encountered similar issue with SSAS 2000 (Dynamic security with DLL) and the solution that I applied was to process my Time dimension (Incremental process). Then it resolved my issue. Not sure if this helps you in this case as well.

    Thanks,
    Ashok

    Yes if you process a dimension or a partition it will clear the cache but that is not the correct way to go about. Reason is , what you are seeing is because the data is coming from cache and the best approach is to clear the cache .

    Another example is when you add a row in fact table which has ROLAP partition on it. If you refresh , you wont see the update. Now like you said you can process a dimension of that measure group or you can just clear cache . 


    Ashwani Roy

    My BI Blog

    My Entity Framework Blog

    Please click the Mark as Answer button if a post solves your problem!

    Monday, June 7, 2010 9:03 PM
    Answerer