SSAS 和 SSRS 中维度的缓慢变化
我有一个项目，其中每 6 个月一次到每 3 年一次对企业进行检查，检查记分卡的结果记录在类型 2 缓慢变化的维度表 [tblInspections] 中，使用 StartDate 和 EndDate 涵盖此记分卡有效的检查之间的时间段。检查表链接到 [tblEstablishments]，其中包含有关其他固定维度（例如位置和业务类型）的其他详细信息。
因此，目前，我们提供当前情况的汇总报告（其中 EndDate 为空）以及任何一家机构历史记录的审核报告（在 EstablishmentID 上）
我的下一个任务是提供更详细的记分卡结果趋势分析报告，并且我需要提供每个月最后一天情况的历史汇总结果。
我的问题是，尽管我确切地知道我想要什么，但我现在不确定如何到达那里。
1) 我是否首先编写 ETL 过程来构建一个基于所有历史结果的多维数据集，计算出每个月月底的所有聚合结果？
2）我是否能够在每个月末处理当前记录，有效地将新切片添加到现有多维数据集的末尾，而无需从头开始重新处理？ （如果是的话怎么办？）
3）还有其他方法吗？通过从具有开始日期和结束日期的多个记录中选择正确的记录来确定任意时间点的历史状态时，Analysis Services 是否有更好的方法自动处理 SCD？
任何与此相关的建议和教程的指示将不胜感激。
I have a project where establishments are inspected anything from once every 6 months to once every 3 years and the results of the inspection scorecard are recorded as a record in a type 2 slowly changing dimension table [tblInspections], using StartDate and EndDate to cover the period between inspections for which this scorecard is valid. The inspections table is linked to [tblEstablishments] which contains other details about other fixed dimensions such as location and business type.
So currently, we are providing aggregated reports of current situation (where EndDate is null) and also audit reports for the history of any one establishment (On EstablishmentID)
My next task is to provide more detailed analysis reports of trends of the scorecard results and I need to provide historical aggregated results of the situation on the last day of each month.
My problem is that despite knowing exactly what I want, I am now unsure how to get there.
1) Do I start by writing ETL process to build a cube based on all the historical results working out what all the aggregates would have been at the end of each month?
2) Am I then able to just process the current records at the end of each month effectively add a new slice onto the end of an existing cube without reprocessing from scratch? (if so how?)
3) Is there another way of doing this? Does Analysis Services have better ways of dealing with SCDs automatically when determining historical status at any point in time by selecting the correct record from multiple records with start and end date?
Any advice and pointers to tutorials related to this would be much appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
首先，如果您尝试分析跨机构（以及其他维度，例如时间/日期）的检查结果，我认为您将需要构建一个新的定期（每月）快照事实表。然后，您可以构建 ETL 流程来填充这个新的事实表。最后，您可以将事实表建模为新的或现有多维数据集中的新度量值组...请务必注意此新度量值组中度量值的聚合属性...通常您不想求和定期快照措施（想想如果您在每个月末将银行帐户余额相加并按年份查看会发生什么）。
是的，您将在每个月末运行 ETL，这将在您的定期（每月）快照事实表中包含更多行。然后您只需处理多维数据集即可。
Analysis Services 可以很好地处理 SCD2 维度（假设您正在使用代理键...您是不是？）。我认为您尝试建模的业务流程（检查）...导致了一些混乱，因为它不再是这个新分析中的一个维度，它已经成为一个事实（定期快照）事实上）
First I think you are going to want to build a new periodic (monthly) snapshot fact table if you are trying to analyze the inspection results across establishments (and other dimensions, like time/date). Then you can build the ETL process to populate this new fact table. Finally, you can model the fact table as a new measure group in a new or existing cube...be sure to pay attention to the aggregation property of the measures in this new measure group...typically you don't want to sum periodic snapshot measures (think about what happens if you sum your bank account balance at the end of each month and look at it by year).
Yes, you will run your ETL at the end of each month which will had more rows to your periodic (monthly) snapshot fact table. Then you can just process the cube and you are all set.
Analysis Services handles SCD2 Dimensions quite well (assuming you are using Surrogate Keys...you are aren't you?). I think the business process that you are trying to model (Inspections)...is what is causing some confusion because it's no longer a dimension in this new analysis, it has become a fact (a periodic snapshot fact)