none
Cache Warming Strategy and Best Practice RRS feed

  • Question

  • Hi guys,

    I am just starting to implement cache warming on my cube, it's quite large so it should really benefit. But I have some general questions:

    1. How should I identify what MDX queries to run? Should I put the result of a profiler into a table then create the scripts off that? Maybe leave the profiler running for 6 weeks to collect all of the results?
    2. Do I have to run a query for each member? So if for example I did cache warming on sales and month, would I have to also do one for YTD? And for each individual member 1 at a time?
    3. I have row level security implemented would I have to do a cache warm for each user? Or would a warm on sales by month be shared? If i do need to run a query for each user would just including cross join for each user by the sales and month be used?

    I know a lot of this info I can get from profiler but I thought I may as well ask people who have already implemented cache warming to make sure I am on the right path.

    Thanks as always.

    Joe

    Monday, November 12, 2018 1:43 AM

Answers

  • Hi JoeyStyles,

    1. Yes, you need to use SQL profiler. /File/Export/Extract SSAS events /Extract All queries to a text file. Then add create cache for every MDX query.

    You can also use SSIS to warm cache.

    SSAS Cache Warming Using SSIS

    2. If there are calculations in your query that are referencing cells outside of those directly referenced in your query. i.e. if your cache only asks for in Feb 2010, but you have a YTD calculation in your query, you would need to expand the create cache call to include Jan 2010.

    Reference: Create Cache

    3. Yes, you may need to create cache for each user. You can use SSIS script task to loop create connection for each user and send MDX to create cache.

    See: Warm up cube for each user with difference roles

    Hope this helps.

    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 JoeyStyles Wednesday, November 14, 2018 7:33 AM
    Tuesday, November 13, 2018 8:35 AM

All replies

  • Hi JoeyStyles,

    1. Yes, you need to use SQL profiler. /File/Export/Extract SSAS events /Extract All queries to a text file. Then add create cache for every MDX query.

    You can also use SSIS to warm cache.

    SSAS Cache Warming Using SSIS

    2. If there are calculations in your query that are referencing cells outside of those directly referenced in your query. i.e. if your cache only asks for in Feb 2010, but you have a YTD calculation in your query, you would need to expand the create cache call to include Jan 2010.

    Reference: Create Cache

    3. Yes, you may need to create cache for each user. You can use SSIS script task to loop create connection for each user and send MDX to create cache.

    See: Warm up cube for each user with difference roles

    Hope this helps.

    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 JoeyStyles Wednesday, November 14, 2018 7:33 AM
    Tuesday, November 13, 2018 8:35 AM
  • Thanks Pirlo that's much appreciated I will have a look through all of your links.
    Wednesday, November 14, 2018 7:33 AM