How to Avoid Security Filters for Prompt Element Retrieval in MicroStrategy

Element prompts need special attention while using security filters. MicroStrategy’s SQL engine by default tries to find a way to apply a security filter everywhere it’s possible to relate the objects. Let’s consider an example where we have a security filter restricting access to location data. A user can access data for only those locations he has access to.

 

Since the security filter is on the Location attribute, we would expect a location prompt to obey the security filter, and it will. The screenshot below shows a prompt that only allows for the (6) locations a user has access to.

And the corresponding SQL for the prompt would be as follows:

Select distinct(a11.LOCATION_KEY), a11.LOCATION_DESC

from D_LOCATIONS a11

where a11.LOCATION_KEY in (0002,0520,0521,1050,1060,1061); 

An unwanted side effect of using a security filter is that the MicroStrategy SQL engine tries to apply it to other prompts that are not related to the Location attribute. For example, we have a prompt on our Date-Time dimension that prompts for 60_Min_Intervals which is not directly related to Location.




While querying for the 60_Min_Intervals’ elements, the MicroStrategy SQL engine tries to relate it to Location and apply the security filter.
SQL for the prompt would be similar to this:

Select distinct a24.MIN60_TIME_KEY

from D_TIME a24

join F_MENU_MIX a25

on (a24.TIME_KEY = a25.TIME_KEY)

where a25.LOCATION_KEY in (0002,0520,0521,1050,1060,1061);

The MicroStrategy SQL engine uses a fact table to establish relationship between the attributes, which is unnecessary and very inefficient in case of performance. Depending on the size of the fact table, it would slowdown the element retrieval or even time-out. To by-pass this issue, we need to make sure the security filter is not applied to those attributes which do not relate directly to the security filter attribute. We do this by un-checking the “Apply security filters to element browsing” setting on the Display tab of any attribute.

We can also make this a global setting at project level in Project Configuration → Project Definition → Advanced → Attribute element browsing settings.

Hope that helps those who are using the security filter and get proper result efficiently.