Proactive Caching - List all Objects in your SSAS Server using it
Hello All,
Like every problem that follows with a solution, today I am going to present a SSAS problem in hand and resources for you to solve it :
Prerequisite information
What is Proactive Caching?
Refer : https://technet.microsoft.com/en-us/library/ms174769(v=sql.110).aspx
Taking an extract out of the above article : "Proactive caching provides automatic MOLAP cache creation and management for OLAP objects. The cubes immediately incorporate changes that are made to the data in the database, based upon notifications received from the database. The goal of proactive caching is to provide the performance of traditional MOLAP, while retaining the immediacy and ease of management offered by ROLAP." .
In a simpler note, it allows your SSAS database to reflect the changes made in relational database, without manual processing from SSAS Server. The SSAS gets to know the changes with the help of a notification trace created on the SQL Server side.
Problem
I came across a scenario where our SSAS end user Mr.OLAP approached me and wanted to know which databases are using Proactive Caching feature in their SQL Analysis Services instance. Thinking about it myself, It dint look to be really a big deal, you just need to right click on either Partition or dimension and looks for its properties, select the Proactive Caching page and review if its enabled or not. Simple isn't it?
Of course, This approach works just fine if you have about few countable number of databases with less objects. But then boom, Mr.OLAP challenges us, saying he has about 50-100 databases in his SSAS instance. Would you really want to spend your day, just making noises with the right clicks and reviewing each and every object in your SSAS server? If I were you, I sure would doze off half way through.
Solution
Like they say, Script is the answer to any automation! But in this case, Unfortunately, My treasure hunt search for a script to solve our problem, wasn't available readily.
So, I decided to spend some time exploring AMO and PowerShell, and here I am sharing a simple to use PowerShell script to do exactly what I needed as a solution for our Mr.OLAP.
Find the powershell script, added as File Attachment in this blog.
Syntax
.GetProactiveCachingObjects.ps1 -serverName server_name/instance_name
Output
The output of this script, will be generated in a file "ProactiveCachingObjects.txt" in the same directory, where you execute the script
Demo
SSAS Server used for demo, has below databases :
Below is a sample demo of how this script needs to be run, along with how the output looks like :
Sample Execution :
(Text format below, in case the image isn't clear)
Windows PowerShell
Copyright (C) 2009 Microsoft Corporation. All rights reserved.
PS C:Usersmuthar> .GetProactiveCachingObjects.ps1 -serverName muthar
Dumping Proactive Caching Objects of SSAS Server: muthar to ProactiveCachingObjects.txt in current directory
Currently Processing Database : AdventureWorks-Part I
Currently Processing Database : AdventureWorks-Part II
Processing Completed. Output Generated in File ProactiveCachingObjects.txt
Sample Output:
Now, with this ready to use script, any end user/administrator/developer who is using the cube, will be able to answer the below questions :
1) Is my SSAS server using proactive caching?
2) Which databases or dimensions or partition, has this feature enabled?
I hope you find this script useful. Please drop a feedback below, if you use this script or if you have any suggestions!
Thanks,
Muthukumaran, A
SQL Server BI Team (CSS)
Microsoft
All postings are provided AS IS with no warranties, and confer no rights. Additionally, views expressed herein are my own and not those of my employer, Microsoft.