Proactive Caching - SQL Server Notification Permissions

I did a presentation at the Melbourne SQL Server User Group this week on the proactive caching feature in Analysis Services 2005. One of the topics that I covered was the different notification methods that Analysis Services 2005 can make use of in order to discover that something has changed in the underlying relational source.

The first notification method on the list is SQL Server notifications, but you will notice that as soon as you select this option a warning message appears at the bottom of the screen.

What the message at the bottom of the screen says is:

And I heard this repeated on 2 different web casts, the US TechEd video and a live talk at TechEd 2005 in Australia, so I did not really have a reason to doubt it. It is even repeated again at the bottom of the partition storage settings after you have said OK to the proactive caching settings.

How ever this did seem strange to me. The SQL Server notifications work by watching the trace events raised by SQL Server, these are the same events that you can see using SQL Profiler. In SQL Server 7.0 and 2000 it is true that you had to be an system administrator in order to receive these trace notifications. However in SQL Server 2005 the ability to access these trace notifications can be assigned as a security privilege.

The connection for one of my proactive caching demos was set up with sysadmin rights (as I believed at the time that these were necessary) - so I took the user out of the sysadmin role and promptly got the following message:

error: OBJIDUPDATE permission denied in database 'ProactiveCaching'.; 42000. Trace: The SQL Server notification read operation failed. SQL Trace status code: 0.

And one thing to note was that this also killed my Analysis Services service. So be VERY careful of altering permissions on a production server. This may be just related to trace notifications when using proactive caching, but I did not expect it to bring the whole service down.

I then setup the connection with just db_datareader rights on the database and added the ALTER TRACE right on the server, which with the default CONNECT SQL and VIEW ANY DATABASE rights that the user got by default, left the login with the following permissions.

All that remained was to setup the proactive caching demo again. All I did was to redeploy the OLAP Database to the server and fire up my little sample application that injects random records into the cubes fact table.

Firing up SQL Profiler and having a look at the trace for Analysis Services proved conclusively that the database was in fact receiving notifications in spite of the connection not being configured as a sysadmin. And removing the ALTER TRACE permission from the login crashed the Analysis Services service in the same way that removing the sysadmin role did orginally.

I have logged this issue in ladybug here http://lab.msdn.microsoft.com/ProductFeedback/viewfeedback.aspx?feedbackid=478e4011-7f31-4794-996d-4c5886132227

The issue is mainly to do with misleading information, but as it stands SQL Server notifications would be hard to recommend in a production environment. Apart from the fact that the delivery of the notifications is not guaranteed (a network issue could prevent delivery) setting up the connection to the data source with sysadmin privileges is not something that a lot of DBAs would like to do.

Update 14 May 2006: I just noticed that Microsoft has responded to the bug I posted, saying that the text in the UI will be replaced and that they will look into updating the UI and fixing the documentation for the next major release.

Print | posted on Sunday, March 26, 2006 7:18 PM

Comments on this post

# re: Proactive Caching - SQL Server Notification Permissions

Requesting Gravatar...
Hi
Do you knwo how to use "Client Initiated" notification in Analysis Server?
Left by Rakesh Mishra on Apr 29, 2006 9:39 AM

# re: Proactive Caching - SQL Server Notification Permissions

Left by Rakesh Mishra on May 01, 2006 1:34 PM

# re: Proactive Caching - SQL Server Notification Permissions

Requesting Gravatar...
Hi,

I am using Proactive caching feature in Analysis Services 2005 using SQL server notification.

MDX queries get stuck while partitions are processed.
i.e. The MDX query which was earlier taking 5-6 seconds, gets stuck or takes 4 min. or 8 min.

AS Database Settings are as below:

Source Database snapshot isolation is ON
Isolation property in Data source defined in AS project is Snapshot.

Enable Proactive caching = Checked
SQL Server notification
Update the cache when data changes = Checked
Silence interval = 0 sec.
Silence override interval = 0 sec.
Drop outdated cache = Checked
Latency = 0 sec.
Bring online immediately = Checked.

Detail description:

One Job that executes SSIS Package loads data into the Cube source database
This package executes in transaction.

So after completion of the job, a notification (sql server) is sent to all the partitions of measure groups and Dimensions.

Now If we execute MDX query, when the processing of a partition is running;
Conceptually it should hit to relational database (as we are using Proactive caching).
It does the same thing but the MDX queries get stuck. It builds sql query and execute at source relational database.

Please suggest what should I do?
Please let me know if more details are required.

Thanks & Regards,
Amit Sharma
Left by Amit Sharma on Jun 06, 2006 2:19 PM

# re: Proactive Caching - SQL Server Notification Permissions

Requesting Gravatar...

Setting latency and selecting the option to drop the outdated cache will result in inconsistent performance like this. The severity of the performance degradation depends on the complexity of your UDM and MDX. A contributing factor is that often the indexing on your star schemas might not be optimal for ad-hoc style queries.

It is possible that the 4-8 minute queries are being directed to the relational database - you could confirm this by running SQL Profiler on either AS or SQL.

The question I would ask is this - Do you really need 0 latency? I suspect, if you are loading the fact table from an SSIS package then you may not need latency this low.

I think the "best" configurations for Proactive caching are ones where the outdated cache is never dropped. This will result in variable latency, but consistent query performance.

If you really want the data visible in the cube fast there are two options you could explore:

1) SSIS can use an SSAS partition as a destination. Doing it this way, SSIS "pushes" the data directly into the partition.

2) If you use polling queries instead of SQL Notifications, you can do incremental processing; only processing the new records in the fact table.

If your data does not lend itself to incremental processing, it becomes more difficult. You really want to explore trying to get the business to accept a longer latency or maybe try reducing the number and size of the partitions that need to be processed.
Left by Darren Gosbell on Jun 06, 2006 8:33 PM

# re: Proactive Caching - SQL Server Notification Permissions

Requesting Gravatar...
Hi Darren
Actually we really need 0 latency as we have two pages in our application. The 1st pages fetches data from OLAP where are the 2nd page fetches data directly from OLTP (SQl Server Database). To make these tow pages in sync we have to use the "0" latency.
It it possible to have some notification from AS2005 saying the proactive cache is built successfully?
Left by Rakesh Mishra on Sep 05, 2006 1:01 PM

# re: Proactive Caching - SQL Server Notification Permissions

Requesting Gravatar...
If you really need 0 latency, then proactive caching is probably not the answer. You can configure it to drop the MOLAP cache and effectively switch to ROLAP mode when the cache is out of date, but that would give you very inconsistant performance.

You could just use a ROLAP cube, the performance but it would never be as quick as a MOLAP cube, but the performance should be relatively consistant.

If your app is web based and the two pages are hitting different datasources I think will be extremely hard to have the pages 100% in sync all the time. In the time between the user viewing the first page and then navigating to the second page, extra records may have been inserted into the SQL database.

In regard to your second question, It probably would be possible to find out when the proactive cache has rebuild as there are trace events that are fired off, but I can't think why this would be useful as the whole point of proactive caching is that you do not need to know about the cache re-builds.
Left by Darren Gosbell on Sep 05, 2006 8:14 PM

# re: Proactive Caching - SQL Server Notification Permissions

Requesting Gravatar...
hi Darren Gosbell

this is very very urgent need, please do help me as soon as possible.

i have already defined Employee dimension with Hiearhchies.
LEVEL 1
LEVEL 2
LEVEL 3
LEVEL 4
LEVEL 5

fact table structure.
employeeid date Lunchtime

where lunchtime is defined as avg measure in aggreatefunction.

if employee x lunch time records for month of jan as follows

jan1 60 mints
jan2 40 mints
jan3 50 mints

we consider he is last level employee, doen't have children employees.
so the avg should be 150/3. i am getting this.

but when i calculate his boss lunch time avg,
1. if boss having lunch time records then boss avg must be calcualted as done for employee x.
2 . then boss avg = (boss avg + sum(sub-ordinate avg values)) / count(sub-ordinate) + 1 (boss).

but analsis service is doing normal avg.

for example

x jan1 60 mints
x jan2 40 mints
x jan3 50 mints
y jan1 60 mints
y jan2 40 mints
boss jan1 10 mints
boss jan2 20 mints

analysis service returns 60 + 40 + 50 + 60 + 40 + 10 + 20 / 7

but i expect value of boss must be
1. xx = (60 + 40 + 50) / 3
2. yy = (60 + 40) / 2
3. bb = 10+ 20 / 2
4. result = xx + yy + bb /3 (we need to consider avg result of childrens, not actuals).

NOTE: x, y are in 5th level, boss @ 4 level.


i think this could not be done with simple avg aggreate function.

help me

thanks in advance.
anand
Left by Calculating avg by hierarchiey on Jul 13, 2007 3:14 PM

# can we connect cubes (or KPI's) using notificstion service?

Requesting Gravatar...
Hi all,
Can u just say tat is it posssible to connect cubes(or KPI's) using .net coding via notification service in Sql server 2005.
if it is possible can u jus say the steps, How to work on the notification service(sending mails) in Sql server 2005.
Thank's to all in advance
Left by sindhu on May 14, 2008 4:03 PM

# WEbservice deployment

Requesting Gravatar...
Hi Friends,
I have created a web service in ASP.net ,
I want tat url of the web service to be accessed by "Action tab in SSAS"(which will appear after deploying).

Can u jus provide a idea,tat is there any other processs required to do with web service,so tat the url works in tat action tab os SSAS in SQL server 2005
Left by Sindhu on May 15, 2008 10:32 PM

# re: Proactive Caching - SQL Server Notification Permissions

Requesting Gravatar...
I do not believe that there is anyway of calling a web service directly from a URL action. There is no way to do the post with the parameters for the web service from the URL alone.

You would need to create a RESTful API (ie. create an ASPX page that calls your web service which accepts the parameters in the query string)
Left by Darren Gosbell on May 15, 2008 10:45 PM

# re: Proactive Caching - SQL Server Notification Permissions

Requesting Gravatar...
hi,
In SSAS is it possible for lookup table,for instance: if the purchase goes above 500 then rating is 5,if 400 then rating 4...the purchase values like 500,400 are in one dimension table.hence the rating are in another dimension table.


Is it possible to make a lookup with these dimension table by providing rating?


If it's possible,then how to implement???
Left by Sindhu on May 26, 2008 8:40 PM

# re: Proactive Caching - SQL Server Notification Permissions

Requesting Gravatar...
You can't really do range based joins in SSAS directly.

What you can do is to do these sort of joins during the ETL process and write the rating (or a surrogate key for it) directly into the fact table.

If your dimension table looked like the following:

Rating, Low, High
5, 401, 500
4, 301, 400
...

Then you could do a join where the price is between the Low and High values and insert the matching Rating into the fact table.
Left by Darren Gosbell on May 26, 2008 10:27 PM

# re: Proactive Caching - SQL Server Notification Permissions

Requesting Gravatar...
Humm... interesting,

thanks for the help on proactice caching.. this is a great blog, answered all my questions

Thanks for writing, most people don't bother.
Left by software developers on Oct 13, 2009 6:04 PM

# re: Proactive Caching - SQL Server Notification Permissions

Requesting Gravatar...
Thanks Darren ... Found this really helpful... I have written one post to explore more proactive caching in my blog... here is the link if someone interested
http://learnmicrosoftbi.blogspot.com/2010/10/proactive-caching-sql-server-2008.html
Left by Amit Gupta on Oct 13, 2010 7:10 PM

# re: Proactive Caching - SQL Server Notification Permissions

Requesting Gravatar...
i am trying to setup proactive caching for one of the partition of my cube using fol. properties:

1. silence interval: 10 secs
2. silence override interval: 05 mins
3. bring online immediately: checked
4. Notificatgion: sql server tracking table

however, after setting up all above things, when i go back to my OLTP database and fire any DML command on tracking table, Proactive does not seem to be receiving any notification. hence no processing of partition takes place.

can someone guide me on debugging this issue or steps i m missing
Left by jignesh on Dec 07, 2012 11:01 PM

# re: Proactive Caching - SQL Server Notification Permissions

Requesting Gravatar...
Running SQL Profiler against both SQL Server and SSAS is probably the best way to debug this. I would probably even do this while configuring pro-active caching to see if there are any warning or errors thrown.

However the bigger question is should you be doing pro-active caching at all. If your partition is relatively small and you have external processes other than a scheduled ETL process writing directly into your fact table then maybe it makes sense. But if you are loading your fact table via an ETL process you should not be using pro-active caching.
Left by Darren Gosbell on Dec 10, 2012 11:48 AM

# re: Proactive Caching - SQL Server Notification Permissions

Requesting Gravatar...
thanks Darren for replying back.

there aren't any warning(s) while setting up the proactive caching. however, i saw one thing missing. after inserting a new row into my tracking table, i see only insert statement under profiler. i do not see any analysis service running or picking any kind of notification under application tab of profiler .. which happens when i simulate it on different machine.

even profiler set on olap cube do not show me any proactive related event or any DML fired on tracking table notification. there are couple of subclasss events like 05- proactivce caching started and so on..

Darren can u please share your thoughts on this.

Thanks!!
Left by jignesh on Dec 27, 2012 7:16 AM

# re: Proactive Caching - SQL Server Notification Permissions

Requesting Gravatar...
i came acros this site - http://support.microsoft.com/kb/941153 which says that proactive wont start until unless metdata of the parition are acessed. havent tried this... i will have to check this.
Left by jignesh on Dec 27, 2012 7:28 AM

# re: Proactive Caching - SQL Server Notification Permissions

Requesting Gravatar...
Hi can some tell me why proactive caching not working in live and working fine in deb and uat.
Left by Dinesh on May 16, 2013 11:15 PM

# re: Proactive Caching - SQL Server Notification Permissions

Requesting Gravatar...
#Dinesh - most likely because of the permissions issue discussed in this blog post. However most of the time the simplest way to fix this is not to use ProActive caching at all. It's only really need in a very small corner of edge cases where your have built your cubes over live transactional tables *and* the users don't want the data to be more than a few minutes old.
Left by Darren Gosbell on May 17, 2013 6:48 AM

# re: Proactive Caching - SQL Server Notification Permissions

Requesting Gravatar...
Hello,
I have implemented Proactive cache in one of my project it was working fine for first two month , After that it stops automatically , What can be the reason what should I do Where should I do
My setting are as below
Storage Mode : MOLAP
Cache Setting
General :-
Cache Setting
Update the Cache when data changes
Silence Interval : 10 Sec
Silence Override Interval : 10 Min.
Notification
SQL Server:
Tracking Tables : Respective tables
Left by Sanjeewan on Feb 18, 2014 5:12 PM

# re: Proactive Caching - SQL Server Notification Permissions

Requesting Gravatar...
Hi @Sanjeewan - Have you made any changes to the cube design recently? You can try running a profiler trace to see if there are any errors being thrown by the proactive caching processing. Sometimes doing a full process will reset the proactive caching. However if you really need a near realtime cube you would probably be better to looking into manually implementing incremental processing using processAdd as tracing tables always trigger a full process which will get slower over time as you accumulate more data.
Left by Darren Gosbell on Feb 19, 2014 6:36 AM

Your comment:

 (will show your gravatar)