关于BI尺寸度量的思考 [英] Thoughts on dimension measures for BI

问题描述

我正在与一位顾问合作，他建议创建一个度量维度，然后将度量维度键添加到我们的事实表中。

I am working with a consultant who recommends creating a measure dimension and then adding the measure dimension key to our fact table.

我可以看到如何通过仅添加行而不是在事实表中实际创建列来使添加新度量更加容易。我还可以看到它如何为ETL流程添加工作，如何向星型架构添加另一个联接，实际上是一个通用列以容纳所有度量数据等。

I can see how this can make adding new measures easier by just adding rows instead of physically creating columns in the fact table. I can also see how this can add work to the ETL process, adds another join to the star schema, one generic column in fact table to hold all measure data etc.

我对其他人如何处理这种情况很感兴趣。目前，我们有将近二十种度量。

I'm interested in how others have dealt with this situation. We currently have close to twenty measures.

推荐答案

本能地，我不喜欢它：它是EAV模型，不是非常流行（您可以在Google上找到原因）。

Instinctively, I don't like it: it's the EAV model, which is not very popular (you can Google the reasons why).

• 通常认为EAV模型是查询和维护的头疼事


• 不同的度量具有不同的维度；这种方法很容易变成所有事物都有一个庞大的事实表，而不是特定报告区域的多个较小事实表。


• 我怀疑您最终将创建视图以显示多个事实仍然使用表


• 您将事实表中的行数乘以度量数量，从而得到更大的物理表


• 即使一个好的索引/分区方案，包含多个度量的查询将必须读取更多行才能获取数据


• 具有不同数据类型的度量怎么样？


• 您的报告工具中是否容易支持此功能？

• The EAV model is generally considered to be a headache to query and maintain
• Different measures go together with different dimensions; this approach could easily turn into "one giant fact table for everything" instead of multiple smaller fact tables for specific reporting areas
• I suspect you would end up creating views to give the appearance of multiple fact tables anyway
• You will multiply the number of rows in your fact table by the number of measures, resulting in a much bigger physical table
• Even with a good indexing/partitioning scheme, queries that include more than one measure will have to read a lot more rows to get the data
• What about measures with different data types?
• Is this easily supported in your reporting tool?

我确定还有其他问题，但这些都是立即想到的那些。根据经验，如果有人建议在任何情况下实施EAV，您都应该非常警惕，并询问他们确切的优点是什么，以及随着数据和复杂性的增加如何对其进行管理。但是我认为您已经确定了一些关键的关注领域。

I'm sure there are other issues, but those are the ones that come to mind immediately. As a rule of thumb, if someone suggests an EAV implementation in any context, you should be very wary and ask them exactly what advantages it offers and how it will be managed as the data and complexity increase. But I think you've already identified some key areas of concern.

这篇关于关于BI尺寸度量的思考的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！