Monday, September 28, 2015

How to prevent first time cube access performance issue, after a cube refresh with Dynamic Security...

Recently we faced an issue that, after a cube refresh there is a performance issue while accessing the cube. This happens since the dynamic security was implemented.
This performance issue can be prevented by building cache for each user.

SSIS package was implemented to build the cache. In the package the approach used was to execute the create cache mdx command using each user id.
You can connect to the SSAS cube with different users by using the EffectiveUserName property in the Connection String.

The structure of the package is as shown below



Steps:

  • Create four variables as shown below. 





  • OLAP_ConnString is the value used as the connection string for SSAS cube. It includes the user name by using the EffectiveUserName property.
  • UserNames variable is used to get the list of users. 
  • Each user is assigned to UserName variable in the Foreach Loop Container.




  • Execute SQL Task us used with in the Foreach Loop Container use to create the cache using the MDX command. Since Effective User Name property is set using the UserName variable, the MDX command is executed for the particular user. That means the cache was created for that user.


After a cube refresh, we need to execute this package and it will prevent the first time access performance issue.