You are currently offline, waiting for your internet to reconnect

Your browser is out-of-date

You need to update your browser to use the site.

Update to the latest version of Internet Explorer

Writeback performance issue when cell security is enabled in SQL Server Analysis Services

Symptoms
Assume that you are running Microsoft SQL Server Analysis Services (SSAS) under a role for which cell security is enabled. When you try to execute an UPDATE CUBE Multidimensional Expressions (MDX) statement, the statement execution may take longer to execute than for a role for which cell security is not enabled.
Cause
This behavior is by design. When cell security is enabled, the Analysis Services engine executes the queries in cell-by-cell mode. If the writeback operation performs allocation at a high level, the space of leaf level cells will be very large.

Note The space is not the number of rows in the fact table. The space is the full cross join space of all dimension granularity attributes. It takes a long time to enumerate those cells one-by-one in order to check the cell security.
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Workaround
To work around this issue, use one of the following methods.

Method 1

Put the measures that should be secured into a separate cube, and implement the cube level write security under your role.

Note The performance when you use this method is as fast as when the query runs under an admin role. However, your cube design becomes complex, and you have to create virtual cubes to use linked measure groups in order to return the different measures in a single MDX query. Additionally, when you perform the writeback operation, you have to create an MDX query that uses the correct cube name based on the writeback measure.

Method 2

Perform the writeback operation at the lowest granularity level of a certain member. You cannot allocate for many detailed granularity members.

Note You may have to create dummy members in dimension tables that are marked as adjustment members in each dimension, to support the writeback operation.
Properties

Article ID: 2747616 - Last Review: 02/19/2013 02:23:00 - Revision: 2.0

  • Microsoft SQL Server 2008 R2 Analysis Services
  • Microsoft SQL Server 2008 Analysis Services
  • Microsoft SQL Server 2005 Analysis Services
  • Microsoft SQL Server 2012 Analysis Services
  • kbsurveynew kbtshoot kbexpertiseadvanced KB2747616
Feedback
>ript' src='" + (window.location.protocol) + "//c.microsoft.com/ms.js'><\/script>");