ssas 分层维度_如何在SSAS多维中使用分区来优化维度安全性能

ssas 分层维度

介绍 (Introduction)

In the articles How to partition an SSAS Cube in Analysis Services Multidimensional and Benefits of Partitioning an SSAS Multidimensional Cube, the concept of measure group partitioning is introduced and the advantages are clearly illustrated. One of the biggest advantages of partitioning is partition elimination, where only the partitions necessary to satisfy the query are read instead of all the data.

在如何在Analysis Services多维 中对SSAS多维数据集进行分区以及对SSAS多维多维数据集进行分区的好处一文中 ，介绍了度量值组分区的概念，并清楚地说明了其优点。 分区的最大优势之一是分区消除 ，其中仅读取满足查询所需的分区，而不是所有数据。

In this article, we’ll investigate how we can couple partition elimination to dimension security. With dimension security, we restrict the members a certain user can see for a specific dimension. For example, we could create a role where members of the role only can view data of a specific region. If this region is contained in one single partition, only this partition needs to be read.

在本文中，我们将研究如何将分区消除与维度安全性结合起来。 借助维度安全性，我们限制了特定用户可以看到的特定维度的成员。 例如，我们可以创建一个角色，其中角色成员只能查看特定区域的数据。 如果该区域包含在一个分区中，则只需读取该分区。

设置维度安全 (Set-up Dimension Security)

In this article, we’ll build further upon the set-up used in the previous articles about partitioning. To recap, we have 7 partitions on top of the sales regions in the WideWorldImporters database:

在本文中，我们将进一步建立在先前有关分区的文章中使用的设置上。 回顾一下，我们在WideWorldImporters数据库的销售区域之上有7个分区：

Let us now create a new role. Members of this role will only be able to view sales for the Far West region.

现在让我们创建一个新角色。 该角色的成员将只能查看Far West地区的销售。

In the Membership tab, we need to add a domain user. We will use this user to test the role security.

在“ 成员资格”选项卡中，我们需要添加一个域用户。 我们将使用该用户来测试角色的安全性。

We can leave the Data Sources tab as is. In the Cubes tab, we need to give the members of the role read permission on the cube, otherwise they won’t be able to browse the data at all.

我们可以按原样保留“ 数据源”选项卡。 在“ 多维数据集”选项卡中，我们需要授予角色成员对多维数据集的读取权限，否则他们将根本无法浏览数据。

Cell Data is used to configure cell security. Since it has quite a performance impact, dimension security is used in most scenarios. In the Dimensions tab itself, there is not much to configure. You can either give Read permission, or Read/Write permission (for dimension write-back scenarios).

单元数据用于配置单元安全性。 由于它对性能有很大影响，因此在大多数情况下都使用维度安全性。 在“ 尺寸”标签本身中，没有太多要配置的内容。 您可以授予“ 读取”权限，也可以授予“ 读取 /写入”权限（用于维回写方案）。

Remark there isn’t an option to restrict access to an entire dimension. You can only restrict access to specific members of a dimension, which you can configure in the Dimension Data tab. The first step is to select the dimension (a regular dimension or a cube dimension) on which we want to apply security.

请注意，没有选项可以限制对整个维度的访问。 您只能限制对维的特定成员的访问，可以在“ 维数据”选项卡中对其进行配置。 第一步是选择要对其应用安全性的维度（常规维度或多维数据集维度）。

Next we need to select the desired attribute:

接下来，我们需要选择所需的属性：

There are two options for securing members of a dimension attribute:

有两个选项可保护维度属性的成员：

• Select all members. By default, the role has access to all members. You need to deselect members for which you want to deny access. New members will be allowed.
  选择所有成员。 默认情况下，该角色有权访问所有成员。 您需要取消选择要拒绝其访问的成员。 新成员将被允许。
• Deselect all members. The opposite of the previous option. Not a single member is allowed, unless explicitly selected. New members are denied by default.
  取消选择所有成员。 与上一个选项相反。 除非明确选择，否则不允许单个成员。 默认情况下，拒绝新成员。

In this example, we will explicitly deny all members and give only access to the Far West region:

在此示例中，我们将明确拒绝所有成员，并仅允许访问Far West地区：

The role is now configured and can be deployed to the server.

角色现已配置，可以部署到服务器。

维度安全性对分区的影响 (Impact of Dimension Security on Partitioning)

Let’s create a quick report using Power BI with the credentials of UserA.

让我们使用Power BI和UserA的凭据创建一个快速报告。

We can see the user can only see the “Far West” sales territory. However, the grand total still shows the sales count for all territories. What is happening? Well, this is behavior is the default because the cube shows the result for the All member. The cube reads all the data and then filters out the territories on the axis. However, the totals still show the result for all the territories. To avoid this, you can select the Enable Visual Totals checkbox in the Advanced tab. In this tab, you can also edit the MDX statements for the allowed or denied member set.

我们可以看到用户只能看到“远西”销售区域。 但是，总计仍显示所有地区的销售数量。 怎么了？ 嗯，这是默认行为，因为多维数据集显示了All成员的结果。 多维数据集读取所有数据，然后过滤出轴上的区域。 但是，总计仍显示所有地区的结果。 为避免这种情况，您可以在“ 高级”选项卡中选中“ 启用视觉总计”复选框。 在此选项卡中，您还可以编辑允许或拒绝的成员集的MDX语句。

The downsides of not enabling visual totals are clear:

不启用视觉总计的缺点很明显：

• You see the totals for all values. This might impose a data breach. For example, suppose there are only two territories. You could deduct the values of the other territory easily by just subtracting your values from the totals. In most cases, you want to avoid such scenarios and display totals for the allowed members only.
  您将看到所有值的总计。 这可能会导致数据泄露。 例如，假设只有两个地区。 您只需从总数中减去您的值即可轻松推算其他地区的值。 在大多数情况下，您希望避免此类情况，仅显示允许成员的总数。
• Also, because you need to have data for the totals, the cube needs to either rely on aggregations, but possibly also needs to read all partitions.
  另外，由于您需要总计的数据，因此多维数据集需要依赖于聚合，但可能还需要读取所有分区。

Remark: the table in Power BI Desktop shows a correct total. However, this depends on the client tool we are using. If we would create the same table in Excel, we can see that the grand total doesn’t match the actual total of the rows.

备注：Power BI Desktop中的表显示正确的总数。 但是，这取决于我们使用的客户端工具。 如果我们将在Excel中创建相同的表，则可以看到总计与行的实际总计不匹配。

When we look at Profiler, we can also see that all partitions are read:

当我们查看Profiler时，我们还可以看到所有分区均已读取：

When Visual Totals are enabled, we get the result we expect in Excel:

启用“ 视觉总计”后 ，我们将在Excel中获得预期的结果：

And in Power BI as well:

在Power BI中：

Now the totals are calculated only for the Far West territory, even if it is not present on one of the axis. When we look at profiler, we can see only one partition has been read (after clearing the cache):

现在，即使不在其中一个轴上，也仅针对远西部地区计算总计。 当我们查看事件探查器时，我们可以看到仅一个分区被读取（清除缓存后）：

There is one big cave-at though: it is possible all partitions are read when the partitioning attribute (Sales Territory in this example) is included in the axis. For example, let’s add the attribute to the table in Power BI Desktop:

不过，有一个很大的警告 ：在轴中包含partitioning属性 （在此示例中为Sales Territory）时，可能会读取所有分区。 例如，让我们将属性添加到Power BI Desktop中的表中：

When we look at Profiler, we can see all partitions are read:

当我们查看Profiler时，我们可以看到所有分区均已读取：

Let’s add a slicer on Sales Territory (which doesn’t make sense in the security context, but it’s for demonstration purposes):

让我们在Sales Territory上添加一个切片器（在安全性上下文中没有意义，但这只是出于演示目的）：

Now only one partition is read:

现在只读取一个分区：

This behavior is not well documented and there is not a clear explanation for now. It seems if you include the partition attribute on the axis, all partitions are read. If you explicitly filter on it, partition elimination does happen. Also, if you don’t reference the partition attribute at all – as we did earlier – then partitions are eliminated as well.

此行为没有得到充分的记录，目前还没有明确的解释。 似乎如果在轴上包括partition属性，则将读取所有分区。 如果您明确对其进行过滤，则确实会消除分区。 此外，如果您根本不引用partition属性（就像我们之前所做的那样），那么分区也会被消除。

At a customer, I have a similar set-up where security is dynamically assigned depending on the user that logs in. Security is assigned to the employee name. However, when creating a report with the employee name included, only the relevant partition is read, as expected. So, it’s not clear what causes this behavior.

在客户那里，我有一个类似的设置，其中安全性是根据登录的用户动态分配的。安全性分配给员工的姓名。 但是，在创建包含员工姓名的报告时，按预期方式仅读取相关分区。 因此，尚不清楚是什么导致了此行为。

结论 (Conclusion)

With the set-up described in this article, we can eliminate reading several partitions when the attribute we’re partitioning on is also the same attribute used in dimension security. However, in some cases, it’s possible all partitions are read if the partitioning attribute is included on the axis. Make sure to test your solution to see if it is applicable to your case.

通过本文描述的设置，当我们要进行分区的属性也与维安全中使用的属性相同时，我们可以消除读取多个分区的麻烦。 但是，在某些情况下，如果在轴上包含了partitioning属性，则可能会读取所有分区。 确保测试您的解决方案，以查看它是否适用于您的案例。

The previous articles in this series:

本系列中的前几篇文章：

• How to partition an SSAS Cube in Analysis Services Multidimensional如何在Analysis Services多维中对SSAS多维数据集进行分区
• Benefits of Partitioning an SSAS Multidimensional Cube分区SSAS多维数据集的好处

参考链接 (Reference Links)

• How to partition an SSAS Cube in Analysis Services Multidimensional 如何在Analysis Services多维中对SSAS多维数据集进行分区
• Benefits of Partitioning an SSAS Multidimensional Cube 分区SSAS多维数据集的好处
• Show correct totals when using SSAS role security 使用SSAS角色安全性时显示正确的总计
• Get most out of partition slices 充分利用分区片
• Grant custom access to dimension data (Analysis Services) 授予对维度数据的自定义访问权限（Analysis Services）

翻译自: https://www.sqlshack.com/how-to-optimize-the-dimension-security-performance-using-partitioning-in-ssas-multidimensional/

ssas 分层维度