SSAS 2019 FE Cache dynamic security

Question

Hello there,

after migrating from SSAS 2016 to SSAS 2019 (and to Active Directory Authentication) our multidimensional cube has suffered severe performance problems.
After analyzing a slow query (Excel generated, nothing but a measure on an axis, no dimensions) it seems to me as if the server is not caching the dynamic security mdx results.
In SSAS 2016 it can be seen that the allowed dimension data is loaded once at connection start and after that no calls to the storage engine regarding the allowed data are made.
In SSAS 2019 every (!) query causes many "Query Subcube Verbose" "Cached Data" events, which if I understand correctly means that the result for the allowed dimension data was not cached in SE cache. T

I compared all available settings for the two SSAS-Databases, but could not find any meaningful differences.

The mdx to determine the allowed items uses a measure group filtered by the username and a predefined category.

Example "Query Subcube Verbose" output from the 2019 profiler:

Dimension 0 [CustomDataSecurityType] (11)  [CustomDataSecurityType]: A category
Dimension 1 [UserSecurityIsCatchAll] (2)  [ID]: 0
Dimension 2 [UserSecurityOlapUser] (33)  [User]: <Username>
Dimension 3 [UserSecurityDomainItems] (*)  [ID]:*

CacheEntries: 31
CacheHits: 36738
CacheSearches: 36849
CacheEvictions: 0

Example MDX that secures dimension data (allowed members in a role):

Extract(
[A secured dimension].Children *
Generate(
    NonEmpty(
      [UserSecurityDomainItems].[ID].Children,
        (
            StrToMember ("[UserSecurityOlapUser].[User].[" + Username + "]"),
            [CustomDataSecurityType].[CustomDataSecurityType].&[ a category id],
            [UserSecurityIsCatchAll].[ID].&[0],
            [Measures].[another measure group Count]
        )
    ),
    STRTOSET("{([a related dimension].&["+  [UserSecurityDomainItems].[ID].CurrentMember.Properties("key")  +"])}")
), [A secured dimension].[a level] )

Is there anything that has changed in regards to how the SE engine is caching from SSAS 2016 to SSAS 2019?

When accessing the cube with Excel for every action that is done (i.e. opening a dimension combobox, opening the All hierarchy member) the whole Cube script (Mdx after calculate) is executed again (Execute Mdx Script Begin/End).

Addendum:
I have since rebuilt the dynamic security restrictions to use a dotnet assembly with internal caching and database backing. This seems reduces the storage engine requests but still leaves me with the Execute Mdx Script for every query. The profiler states that these have a duration of 0 but still a simply opening a dimension (which is not restricted by dynamic security, and has only two elements) takes more than 2 seconds. And when profiling on SSAS 2016 the "Execute Mdx" are only present once when connecting, every other request does not cause them.

Thank you for your time,
regards,
Kris

Answer 1

Thank you everyone for your time and sorry for my late reply.

After removing every single dimension data security mdx declaration and adding it back bit by bit I found the cause. It is the definition for the default member mdx of a dimension:

IIF(
IsEmpty(CustomData()),
[A Dimension].[A Dimension].&[54],
StrToMember("[A Dimension].[A Dimension].&[" + CustomData() + "]")
)

It seems to me that there is a difference in SSAS 2016 and SSAS 2019 how this part is handled. If I fix this to a literal value, in SSAS 2019 all the troubles go away (only one time Query Dimensions) . In 2016 the problem is not present with the same mdx definition.

Is there a change in if the CustomData-Function is regarded as deterministic?

Regards,
Kristoffer

Answer 2

Hi,
What storage mode are you using?

As far as I know there was no announcement about the change of the caching mode in SSAS. Probably
something went wrong during the migration.

Answer 3

Hi anonymous userWitt-7957 ,

the security and also the MDX script should be cached after the first connection of a user. So every following connection should be able to use this cache and it should not need to be evaluated again
I am also not aware of any change of this behavior from 2016 to 2019

If this caching does not happen, here are some possible reasons:

• they got evicted from memory due to memory pressure -> make sure your memory settings on the SSAS server level are correct
• the use of any non-deterministic functions in the Allowed-/Denied-Set MDX and/or in the MDX script could cause this issues

do you see a lot of "Query Dimension" events when re-establishing the connection? This would mean the security is indeed evaluated again.

you can also have a look at this slide-deck which shows and explains some of the details on the later pages
https://files.gbrueckl.at/blog/Sessions/SQLBits_XII_Gerhard_Brueckl_DeepDive_into_Analysis_Services_Security.pdf

regards,
-gerhard