SSAS SCDS：没有数据显示活跃的维度成员

最新推荐文章于 2024-07-03 11:01:02 发布

tianlianchao1982

最新推荐文章于 2024-07-03 11:01:02 发布

阅读量722

点赞数

文章标签： date attributes performance integer 2010 report

本文链接：https://blog.csdn.net/tianlianchao1982/article/details/6749155

版权

2型尺寸播放使用SSAS，因为当我们使用SSAS中决定哪些成员是基于我们在测量组数据的时段有关。 This is always the case – if we slice by a particular date, then all members from a SCD which do not link up to the data in the slice will yield empty values, which we can easily hide in most clients (or by using an MDX function like NON EMPTY).这是总是如此 - ，如果我们通过一个特定的日期切片，然后将产生的所有成员从SCD不切片中的数据联系起来，我们可以很容易地隐藏在多数客户机中的空值（或通过使用一个MDX如非空的函数）。 This works well in most cases.这在大多数情况下。 However, there is a small problem in a small subset of all usage scenarios – that is when we have no data against a member relevant for the same period.然而，在所有场景下使用的一小部分有一个小问题 - 那就是当我们有没有对一个会员的同期相关数据。 Because it still yields nothing when we go through the data in the cube, it gets hidden, too.因为它仍然产生什么时，我们经历了在多维数据集中的数据，它被隐藏，太。

Let's explore a simple case.让我们来探讨一个简单的例子。 If we have a SCD called Employee Type, which has two values: Internal and External for 2010, but three members Internal, Non-Internal and Contractor for 2008 and 2009; and then we have a measure called Employee Count, if we slice by Employee Type we get something like:如果我们有一个SCD名为Employee类型，它有两个值：2010年的内部和外部，但三名成员内部，非内部和2008年和2009年承包，然后我们有所谓的员工数的措施，如果我们按员工切片类型，我们得到的东西，如：

 Internal     150内部150
External      20外部20
Non-Internal   15非内部15
Contractor    10承包10

When we slice by a year we may get something like:当我们通过一年的切片，我们可能得到的东西，如：

          2008    2009    2010 2008 2009 2010
Internal     50    50   50内部50 50 50
External              20外部20
Non-Internal        15非内部15
Contractor      5    5承包5 5

Note that if we slice by 2008 and we hide empty cells we would get:注意：如果我们片，到2008年，我们隐藏空单元格，我们会得到：

          2008 2008
Internal      50内部50
Contractor      5承包5

However, what we may want to see on our report may be:然而，我们可能希望看到我们的报告可能是：

          2008 2008
Internal      50内部50
Non-Internal非内部
Contractor     5承包5

Un-Hiding empties would actually show:取消隐藏清空实际上显示：

          2008 2008
Internal      50内部50
External外部
Non-Internal非内部
Contractor      5承包5

Well, in SQL we would just use the EffectiveFrom and EffectiveTo dates in the dimension table to determine the correct results.那么，在SQL中，我们只想使用维度表中的EffectiveFrom和EffectiveTo日期，以确定正确的结果。 In SSAS we are not that lucky.在SSAS中，我们没那么幸运。 I would like to show a possible solution.我想表明一种可能的解决方案。

Firstly, for an SCD we would typically have the dates I just mentioned – EffectiveFrom and EffectiveTo for each row.首先，我们会为SCD通常有我刚才提到的日期 - EffectiveFrom每一行和EffectiveTo。 I would typically exclude them from the dimension in SSAS, but in our specific case we need them.我通常会排除在SSAS维度，但在我们的具体情况，我们需要他们。 Therefore, we can add them and just hide them instead of excluding them completely.因此，我们可以添加他们只是隐藏他们，而不是完全排除他们。 Once we have them set up in this way we could write a bit of MDX, using LinkMember:一旦我们有他们这样我们可以写一个位的MDX，使用LinkMember设置：

 WITH 与 
 MEMBER [Measures].[ActiveType] 会员[措施] [ActiveType] 
 AS AS 
   IIF({{LinkMember([Type].[EffectiveFrom].CurrentMember, IIF（{{LinkMember（[类型] [EffectiveFrom]。CurrentMember 
                    [Date].[Date]): [日期] [日期]）： 
      LinkMember([Type].[EffectiveTo].CurrentMember, LinkMember（[类型] [EffectiveTo]。CurrentMember 
  [Date].[Date])}* [日期] [日期]）} * 
  [Date].[Year].CurrentMember}.Count = 0, [日期] [年]。CurrentMember}计数= 0， 
       NULL, 为NULL， 
     1) 1） 
 SELECT 选择 
 { { 
   [Date].[Year].[Year].&[2008]* [日期] [年] [年]＆[2008] * 
  [ Measures].[Employee Count] } ON 0, [措施] [员工数] 0}， 
 { { 
   NONEMPTY([Type].[Type].[Type], 非空（[类型] [类型] [类型]， 
            [Measures].[ActiveType]) [措施] [ActiveType]） 
 } ON 1 } ON 1 
 FROM [MyCube] 从[MyCube]

Now we get exactly what we want (Non-Internal shown in 2008 but with no data):现在，我们得到正是我们想要的（非内部在2008年，但没有数据显示）：

          2008 2008
Internal      50内部50
Non-Internal非内部
Contractor     5承包5

Please note that for large dimensions this is a very bad approach from performance point of view and should be avoided.请注意，大尺寸，这是一个非常不好的做法，从性能的角度来看，应避免。 Surprisingly enough, users rarely consider performance in their top 10 priorities, while functionality somehow always makes it there, so someone might find this technique useful in extreme user cases.令人惊奇的是，用户很少考虑在他们的排名前10位重点性能，而功能不知何故总是使得它有，所以有人可能会发现这种技术，用户在极端情况下有用。

It would be also interesting if there is another approach to this scenario, which I may be unaware of – I am sure developers have hit (or have been hit) by this problem in the past and there must be other solutions, as well.它也将有趣的，如果这种情况下还有另一种方法，我可能不知道的 - 我相信开发商已经打（或受到的冲击），这在过去的问题，必须有其他的解决方案，以及。

Note: If you try to replicate this in Adventure Works as I did, you will find that the keys in the Date dimension(s) and the keys for the Start and End Date in the Type 2 SCDs are not the same.注：如果您尝试复制Adventure Works的，像我一样，你会发现，在日期维度（S）在2型纪常会的开始和结束日期的键的键是不一样的。 The Date dimension uses integer keys, while the Start and End Date attributes use datetime. “日期”维度使用整数键，而使用datetime的开始和结束日期属性。 Therefore, LinkMember will not be able to match these cross-dimensional attributes and the above approach will not work.因此，LinkMember将无法匹配这些交叉的三维属性和上面的方法将无法正常工作。 As a lesson from this exercise – Effective From and Effective To columns should be of the same data type as your date attribute key in the Date dimension table.作为这次演习的教训 - 起有效列应该是相同的数据类型作为你的日期在日期维度表中的属性键。