本文共 7919 字,大约阅读时间需要 26 分钟。
ssas 分层维度
In the articles and , 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.
在 以及 ,介绍了度量值组分区的概念,并清楚地说明了其优点。 分区的最大优势之一是分区消除 ,其中仅读取满足查询所需的分区,而不是所有数据。
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.
在本文中,我们将研究如何将分区消除与维度安全性结合起来。 借助维度安全性,我们限制了特定用户可以看到的特定维度的成员。 例如,我们可以创建一个角色,其中角色成员只能查看特定区域的数据。 如果该区域包含在一个分区中,则只需读取该分区。
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:
有两个选项可保护维度属性的成员:
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.
角色现已配置,可以部署到服务器。
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:
不启用视觉总计的缺点很明显:
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.
在客户那里,我有一个类似的设置,其中安全性是根据登录的用户动态分配的。安全性分配给员工的姓名。 但是,在创建包含员工姓名的报告时,按预期方式仅读取相关分区。 因此,尚不清楚是什么导致了此行为。
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:
本系列中的前几篇文章:
翻译自:
ssas 分层维度
转载地址:http://iznwd.baihongyu.com/