OLAP Cube with member level security giving poor performance

New Contributor
Posts: 2

OLAP Cube with member level security giving poor performance

We have few web reports created over SAS OLAP cubes (OLAP cube -> Info map -> WRS). All the reports are almost similar with a cross-tab displaying few dimensions and measures

The reports started taking longer time to display than earlier. They used to open within 10 seconds but now they take 3 minutes.

There was no change to the cube or report structure, but values in the dimension (on which member level security has been applied) has increased. Below are the details:

For the few logins, we have applied specific MDX expression on a particular dimension of the cube.

The MDX expression is made of two basic  MDX functions Descendants() and Filter(); as shown below

{Descendents ( Filter (<Dimension_name>.<Hierarchy_name>.<Level_name>.Members,

<Dimension_name>.<Hierarchy_name>.Currentmember.Name ="SUB:Smiley FrustratedAS.PersonName").Item(0))}

Earlier, we had only few values(in hundreds) for this dimension, but now the values for this dimension has increased (to thousands).

The web report is displayed within 10 seconds when we have few values but once the cube is re-built (with thousands values) for this dimension, the time taken for execution and display of the report is impacted. It takes almost 3-4 minutes. (Note there was no other change done to the cube or the report).

Creating cube aggregations on this particular hierarchy also did not have any effect on the time taken by the report.

Any suggestion/help on how to optimize the time taken to fetch data from the cube (with this particular MDX expression applied) would be greatly appreciated.urit

Super User
Posts: 5,256

Re: OLAP Cube with member level security giving poor performance

One could suspect that the aggregation tables has expanded up to 10 times the original size, depending on your data structure...?

The first is try to monitor the cube to see which aggregation tables (or the NWAY!) gets queried.

Be sure that you have the aggregation tables indexed, try to monitor that as well.

Data never sleeps
Ask a Question
Discussion stats
  • 1 reply
  • 2 in conversation