大网数据广告活动
数据库与应用安全

Writeback performance issue

· 52CE 我爱测

This article provides workarounds for the writeback performance problem that occurs when cell security is enabled in SQL Server Analysis Services.

This article helps you work around the writeback performance problem that occurs when cell security is enabled in SQL Server Analysis Services.

Original product version:   SQL Server 2012 Analysis Services and later versions
Original KB number:   2747616

Symptoms

Assume that you're 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 isn't 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 large.

Note: The space isn't 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.

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 can't 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.

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 05/09/2025
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。