代码之家 › 专栏 › 技术社区 › Chris Adragna

如何在SQL Server数据仓库中提供YTD、12M和年度度量?

ssas-tabular data-warehouse powerbi sql-server sql

Chris Adragna · 技术社区 · 8 年前

Project需要SQL Server表中的可用数据仓库(DW)。他们不喜欢分析服务,SQL Server DW提供了他们所需要的一切。

他们开始使用powerbi,并表示希望在SQL Server表中提供所有事实和度量,而不是多维多维多维数据集。客户端还使用了SSR(在很大程度上),以及一些Excel(由用户使用)。

起初他们只要求 收入事实 对于周期x产品x位置。这将使用定期快照类型的事实,而不是事务性粒度事实。

因此,为了为所有期间提供YTD度量,我的第一个挑战是填写“空白”事实,因为没有收入,但之前(和/或之后)期间有收入。空事实数据表包含YTD度量值的列。

我通过为无收益期创建空事实(无收益)解决了这一问题,例如:

Period 1, Loc1, Widget1, $1 revenue, $1 YTD
Period 2, Loc1, Widget1, $0 revenue, $1 YTD (this $0 "fact" created for YTD)
Period 3, Loc1, Widget1, $1 revenue, $2 YTD

我只是以本年迄今为例,但这些要求包括过去12个月的指标,以及除本年迄今之外的按年计算的指标。

我发现计算YTD的最佳方法实际上是创建记录来保存度量(YTD),其中没有来自事务数据的事实(也就是说,维度组合没有收入)。

现在,需求需要更多两个维度的收入事实,即市场细分和客户。这意味着我需要重构现有的存储过程来执行相同的过程,但现在为了更详细的事实:

Period x Widget x Location x Market x Customer

这将导致创建更多记录来保存YTD(和其他)度量。这些措施的记录将比实际情况多得多。

以下是我认为可能的解决方案:

只需在SQL DW表中执行即可。这使得在任何需要的地方都可以很容易地使用。(就像现在一样)
在power bi中这样做吗——假设在pbix中使用dax表达式?
SSAS表格——表格是计算YTD等度量的合适地方,还是应该在报告层处理?

就其价值而言,客户机不愿意使用SSAS表格,因为他们希望将层的数量保持在最小。

后续问题:

有没有像我这样的SQL Server体系结构来提供这种解决方案,也许可以减少必要的记录数量?
如果他们将powerbi用于YTD、1200万、按年计算的度量,那么在SQL DW中,除了事实之外,还需要提供什么?
这是SSAS表格本身解决的问题吗?

2 回复 | 直到 8 年前

Nick.Mc 8 年前

这是我的经验:

我一直认为dw应该是所有数据所在的位置。然后任何客户机工具可以使用该dw并得到相同的答案。

我在最近的一个项目中遇到了同样的问题:生成“去年同一天”类型的计算(以及YTD、FIN YTD等)。SQL Server似乎是放置这些“稀疏”事实的显而易见的地方,但正如我发现的(正如您所发现的),稀疏度随着维度的增加而变得越来越大和越来越复杂,最终会使大小膨胀。和不断地回来追查那些遗漏的稀奇古怪的事实,最糟糕的是,必须想出奇怪的“分配”规则,将措施降低到所需的详细程度。

IMHO DAX是这样做的地方,但是在学习语言时有很多痛苦,特别是你来自传统的关系背景。但是我真的认为这是自SQL以来最好的事情,如果你能越过学习曲线的话。

使用DAX而不是DW的一个最明显的优点是DAX在运行时识别出客户机工具(在Power BI、Excel或其他工具中)中当前的过滤器是什么,并且可以自动调整它的计算。很明显,你不能用DW中的数字来实现这一点。例如,您可以识别特定日期筛选的人员、图表或行,因此您当前的年度/以前的计算将根据日期自动计算正确的本年迄今。

DAX有许多“日历”类型的功能(称为“时间智能”),但它们只适用于特定类型的日历,并且有很多 constraints ,所以通常您最终需要创建自己的日历表并围绕该日历表构建函数。

我的建议是从这里开始: https://www.daxpatterns.com/ 尝试在DAX中生成一些YTD计算

就其价值而言,客户不愿意使用SSAS表格因为他们想把层数保持在最小。

PowerBi已经有了一个(必需的)建模层,可以在内部有效地使用SSAS表格,因此已经有一个额外的逻辑层。它与报告层使用相同的工具。区别在于,目前仅在PowerBI中进行建模并不是“企业”方法。Power BI不支持诸如模型版本控制、分区负载、高级行级安全性等功能(尽管谁知道下个月会带来什么)

只要你控制层,它们就不是坏事。否则,我们应该回到单块COBOL程序。

当然有可能开始只在Power BI中进行建模,然后在后期需要特性、控制和可伸缩性时,迁移到SSAS表格。

需要考虑的一点是,在Azure中,SSAS表格式PaaS提供的服务可能会非常昂贵,但是如果您需要分区加载(即,仅在本周将数据加载到具有大量历史记录的非常大的多维数据集中),则需要使用它。

有没有像我这样的SQL Server体系结构来提供这种解决方案,也许可以减少必要的记录数量?

我想体系结构将在视图中定义记录。这有很多明显的下降。有一个“稀疏”指示符,但它只是为具有大量空值的字段优化存储,这种情况甚至可能不是这样。

如果他们将powerbi用于YTD、1200万、按年计算的度量,那么在SQL DW中,除了事实之外,还需要提供什么?

您肯定需要一个定义会计年度的综合日历表

这是SSAS表格本身解决的问题吗?

如果您只想按日历期间(1月1日至12月31日)报告,那么内置时间智能是“固有的”,但是如果您想按会计期间报告,则不能使用时间智能。不管怎样,您仍然需要定义DAX计算。他们可以得到 真的? 大的

David Browne - Microsoft 8 年前

首先,SSAS表格和POWER BI使用相同的引擎。所以它们同样适用。

使用大量分类属性中的任何一个,定义可以跨数据片计算的度量值的能力,是您希望在SQL Server前面使用SSAS表格或Power BI之类的东西的主要原因之一。(其他功能包括缓存、简化最终用户报告、跨源混合数据的能力以及自定义安全性。)

理想情况下,SQL Server应该提供事实以及到任何维度表(包括日期维度表)的单列联接。然后,power-bi/ssas表格将DAX度量定义、过滤器流行为以及可能的行级安全性分层。