power bi角色管理_Power bi中的角色扮演维度

power bi角色管理

After explaining how you can reduce your Power BI data model size by 90%, by simply adhering to some simple rules, and understanding how your data model is being optimized by the VertiPaq engine in the background, it’s the right moment to learn one of the core concepts of dimensional modeling.

在解释了如何通过遵循一些简单规则将Power BI数据模型的大小减少90％之后，并了解了VertiPaq引擎在后台如何优化数据模型，现在正是学习以下方法之一的正确时机尺寸建模的核心概念。

The role-playing dimension is not a concept exclusively related to Power BI. It’s a universal data modeling technique that comes from Kimball’s methodology.

角色扮演维度并不是一个专门与Power BI相关的概念。 这是一种由Kimball的方法学得出的通用数据建模技术。

Simply said, it’s the situation when you use one same dimension for creating multiple relationships to your fact table. Typical usage of the role-playing dimension concept is with the Date dimension since in many cases, your fact table will contain multiple date fields. For example, in the betting industry, there are fields DateBetPlaced and DateBetProcessed, which don’t need to be identical (and in most cases are not). So, let’s say that the business request is to analyze data both on DateBetPlaced and DateBetProcessed.

简而言之，当您使用一个相同的维度为事实表创建多个关系时就是这种情况。 角色扮演维度概念的典型用法是与日期维度配合使用，因为在许多情况下，事实表将包含多个日期字段。 例如，在博彩业中，存在字段DateBetPlaced和DateBetProcessed，它们不必相同(在大多数情况下不是相同)。 因此，假设业务请求是分析DateBetPlaced和DateBetProcessed上的数据。

一维具有多个引用的解决方案 (Solution with multiple references of one dimension)

The first solution will be to create two copies of exactly the same Date dimension and relate DateKey to DateBetPlaced in the first case, and to DateBetProcessed in the second case. Something like this:

第一种解决方案是创建两个具有完全相同的Date维度的副本，并将DateKey与第一种情况的DateBetPlaced和第二种情况的DateBetProcessed关联。 像这样：

As you see, each date field from the fact table relates to its own Date dimension. And we say that each of these references “play” its role in the model.

如您所见，事实表中的每个日期字段都与它自己的日期维度相关。 我们说这些参考文献中的每一个都在模型中“扮演”其角色。

Of course, this model is not optimal, because we are basically making data redundant without a valid reason. Additionally, we would need separate filters for each reference of the Date dimension, in order to get valid results.

当然，该模型不是最佳模型，因为我们基本上是在没有正当理由的情况下使数据冗余。 另外，我们需要为Date维度的每个引用使用单独的过滤器，以获取有效的结果。

优化模型 (Optimizing the model)

Instead of keeping multiple references of one same dimension, we can relate one single dimension multiple times to a fact table. This concept behaves differently in different tools (in SSAS multidimensional for example, you can define multiple active relationships between dimension and fact table, in case your data source view has proper foreign keys in place. But, that’s out of the scope of this post), so I will focus on Power BI.

代替保留一个相同维度的多个引用，我们可以将一个单一维度多次关联到事实表。 此概念在不同的工具中的行为有所不同(例如，在SSAS多维中，如果数据源视图具有适当的外键，则可以在维度和事实表之间定义多个活动关系。但是，这不在本文讨论范围之内) ，所以我将专注于Power BI。

Therefore, I will just delete the redundant Date dimension from my data model and simply connect DateKey from the Date dimension to the DateBetProcessed field in my fact table.

因此，我将仅从数据模型中删除冗余的Date维度，然后将Date维度中的DateKey连接到事实表中的DateBetProcessed字段。

What happened here? Power BI created a relationship, but as you notice, this relationship is marked with a dotted line. That’s because Power BI allows only one active relationship between two tables and in my case, it’s between DateKey and DateBetPlaced. So, when I put that on the report canvas, I get something like this:

这里发生了什么？ Power BI创建了一个关系，但是您要注意，该关系用虚线标记。 这是因为Power BI仅允许两个表之间存在一种活动关系，在我的情况下，它是在DateKey和DateBetPlaced之间。 因此，当我将其放在报表画布上时，我得到的是这样的：

I can see the total number of bets per month, but since my active relationship is between DateKey and DateBetPlaced, I am seeing totals based on dates when the bet was placed!

我可以看到每月的下注总数，但是由于我的活跃关系是在DateKey和DateBetPlaced之间，所以我看到的是根据下注日期得出的总数！

What if I want to see, instead of how many bets were placed, how many bets were processed per month. Here comes in hand DAX function USERELATIONSHIP. This function enables us to define which relationship should be active for a specific calculation.

如果我想查看，而不是每月要处理多少个赌注，而不是下注多少个赌注。 这是DAX函数USERELATIONSHIP的功能。 此功能使我们能够定义对于特定计算应激活的关系。

So, when I write the following measure:

因此，当我编写以下措施时：

Bets Processed = CALCULATE(
                    COUNT('Fact Bets'[BetID]),
                    USERELATIONSHIP(DimDate[DateKey],'Fact Bets'[DateBetProcessed])
)

I’m explicitly saying to Power BI: here, I don’t want you to use default active relationship (DateKey — DateBetPlaced). Instead, I want you to switch to using another relationship (DateKey — DateBetProcessed) and make this relationship active for this calculation only!

我对Power BI明确地说：在这里，我不希望您使用默认的活动关系(DateKey — DateBetPlaced)。 相反，我希望您切换到使用其他关系(DateKey — DateBetProcessed)，并使该关系仅对于此计算有效！

Here are the results:

结果如下：

As you may notice, lines are different, based on the relationship used in calculations.

您可能会注意到，根据计算中使用的关系，行是不同的。

结论 (Conclusion)

Using this technique, we enabled our users to slice and dice data from different perspectives and gave them the flexibility to analyze figures based on multiple scenarios, keeping our data model tidy and not redundant.

使用此技术，我们使用户能够从不同的角度对数据进行切片和切块，并赋予他们灵活性，可以基于多种场景分析图形，从而使数据模型保持整洁而不冗余。

Subscribe here to get more insightful data articles!

在此处订阅以获得更多有见地的数据文章！

翻译自: https://towardsdatascience.com/role-playing-dimensions-in-power-bi-185dc58f90f1

power bi角色管理