Partager via

Proactive caching with in-Memory tables as notification

  • Article

 

Recently we encountered an issue using a Multidimensional Model where ROLAP and proactive caching were enabled on one of the partition. The notification was set to SQL Server table to track the changes and refresh the cache.

 

The behavior we noticed was that if a SQL Server Table was set to a Memory Optimized Table, we don’t see a notification within Analysis Services for any changes within the SQL Server Table. But if the SQL Server Table was not a memory optimized table, the notifications were sent back to Analysis Services and the cache was refreshed.

 

  • We used AdventureWorks to understand this behavior, where the partition: Customers_2005  (Measure group: Internet Customer) with ROLAP and proactive caching enabled.

 

 

  • After Deploying the model to SSAS 2016 instance and making changes within SQL server ([FactSalesQuota]), we could see this notification from the SSAS profiler trace :” A notification was received from an instance of SQL Server for the '[dbo].[ FactSalesQuota]' table”
  • The data was changed on the SSAS as well:

 

Before the changes SSAS DB:

Sales Amount Quota : 154088000

After the changes SSAS DB (Sales Quota partition converted to ROLAP):

Sales Amount Quota : 154088004.7

 

  • We converted the FactSalesQuota table to an in-memory table and tested the behavior. This time, we didn’t see any notification triggered from the analysis services profiler trace
  • Took a profiler on the SQL and SSAS simultaneously and we see the below query getting triggered whenever there is a change in the table on SQL side profiler:

 

DECLARE @OlapEvent BIGINT;SELECT @OlapEvent = ObjIdUpdate(2);SELECT (@OlapEvent & convert(bigint, 0xffff000000000000)) / 0x0001000000000000 AS Status, (@OlapEvent & convert(bigint, 0x0000ffff00000000)) / 0x0000000100000000 AS DbId, @OlapEvent & convert(bigint, 0xffffffff) AS ObjId;

 

  • But when we convert the table to an in-memory table. The query was not getting triggered and we are not seeing any notification back on SSAS as well.
  • After more research it seems this query keeps running in suspended state all this while.
  • For normal [FactSalesQuota] table (not in-memory)  we see the below:

 

SQL Profiler : I could see the notification query is getting triggered:

 

SSAS Profiler: SSAS is receiving a notification:

 

 

  • Once we convert the FactSalesQuota tables as in-memory table, I still see the notification query running in suspended state.

 

 

 

 

  • But after we make the change to the table, the notification query is not triggered.

 

 

No notification seen in SSAS:

 

 

  • We verified this behavior with our PG Team and understood that we rely on the SQL server notification to know if and when any changes have been made to the SQL table and only then do we initiate a refresh cache.

 

 

 

 

Conclusion:

 

Proactive caching with SQL server set for notification doesn't will not work for in-memory tables in SQL server. This is a limitation from the SQL side itself.

 

 

 

Author:      Chandan Kumar –Support Engineer, SQL Server BI Developer team, Microsoft

Reviewer:   Kane Conway – Support Escalation Engineer, SQL Server BI Developer team, Microsoft