变更数据捕获和SQL Server Analysis Services

Question

我正在设计一个数据库应用程序，其中的数据将随着时间的推移而变化。我希望保留历史数据，并允许我的用户使用SQL Server Analysis Services对其进行分析，但我正在努力想出一种允许这样做的数据库架构。我已经想出了一些可以跟踪更改的模式(包括依赖于CDC)，但是我不知道如何在SSAS中将该模式转换为工作的BISM。我还能够创建一个可以很好地转换为BISM的模式，但是它不具备我所寻找的历史功能。做这类事情有什么既定的最佳实践吗？

下面是我想要做的一个例子：

我有一个名为Sales的事实表，其中包含每月的销售数据。我还有一个名为Customers的常规维度表，它允许用户查看按客户细分的销售数据。客户和销售代表之间存在多对多关系，因此我可以创建一个名为Responsibility的引用维度，该维度引用customer维度，并创建一个引用Responsibility维度的sales customer引用维度。现在，我有了通过引用维链Sales -> Customer -> Responsibility -> Sales链接到销售代表的销售事实，它允许我查看按销售代表细分的销售数字。问题是，随着时间的推移，销售数据并不是唯一会发生变化的东西。我还希望能够维护销售代表在特定销售事实发生时负责客户的历史记录。我还想知道销售代表的办公室在特定销售事实发生时的位置，这可能与他现在的位置不同。我可能还想知道特定销售事实发生时客户组织的规模，这也可能与目前的情况不同。我不知道如何以BISM友好的方式对此进行建模。

Answer 1

您提到您目前有一个事实表，其中包含每月的销售数字。所以每个客户每个月都有一条记录。因此，该事实数据表中的每条记录实际上都是当月发生的相应维度的单个销售“事务”的聚合。

因此，在给定的月份中，可能有5个单独的销售交易，每个10美元的customer 123...and每个单独的销售交易可以由不同的销售代表(A，B，C，D，E)处理。在您描述的事实数据表中，customer 123...but有一条$50的记录，我们如何对SalesReps (A-B-C-D-E)建模？

基于你的目标。

要维护特定销售时哪个销售代表负责客户的历史记录fact

• to知道销售代表的办公室在特定销售时位于何处

• 知道特定销售fact

时客户组织的规模

...I认为，以较低的granularity...specifcally对每个销售事务具有1条记录的销售-交易事实表进行建模会更容易。每笔销售交易都有一个客户和一个销售代表。

FactSales
    DateKey (date of the sale)
    CustomerKey (customer involved in the sale)
    SalesRepKey (sales rep involved in the sale)
    SalesAmount (amount of the sale)

现在，对于具有要跟踪其历史更改的属性的历史更改tracking...any维度，需要将其建模为“渐变维度”，因此将需要使用“代理键”。因此，例如，在您的客户维度中，客户ID将不是主key...instead，它将只是您将使用的任意整数作为主key...this任意键的业务key...and。

下面是我为你的维度建模数据的方法。

DimCustomer
    CustomerKey (surrogate key, probably generated via IDENTITY function)
    CustomerID (business key, what you will find in your source systems)
    CustomerName
    Location (attribute we wish to track historically)
    -- the following columns are necessary to keep track of history
    BeginDate
    EndDate
    CurrentRecord

DimSalesRep
    SalesRepKey (surrogate key)
    SalesRepID (business key)
    SalesRepName
    OfficeLocation (attribute we wish to track historically)
    -- the following columns are necessary to keep track of historical changes
    BeginDate
    EndDate
    CurrentRecord

FactSales
    DateKey (this is your link to a date dimension)
    CustomerKey (this is your link to DimCustomer)
    SalesRepKey (this is your link to DimSalesRep)
    SalesAmount

这样做的目的是允许您拥有同一客户的多个记录。例如。2012年3月5日，CustomerID 123从NC迁移到GA ...

CustomerKey | CustomerID | CustomerName | Location | BeginDate | EndDate | CurrentRecord
1 | 123 | Ted Stevens | North Carolina | 01-01-1900 | 03-05-2012 | 0
2 | 123 | Ted Stevens | Georgia        | 03-05-2012 | 01-01-2999 | 1

这同样适用于SalesReps或您想要跟踪某些属性的历史更改的任何其他维度。

因此，当您按CustomerID、CustomerName (或任何其他非历史跟踪属性)对sales事务事实表进行切片时，您应该会看到单个记录，其中汇总了客户的所有事务中的事实。如果您决定按CustomerName和位置分析销售事务(历史跟踪属性)，您将看到与客户在该位置时的销售额相对应的每个“版本”客户位置的单独记录。

顺便说一句，如果您有时间并且有兴趣学习更多，我强烈推荐金宝圣经"The Data Warehouse Toolkit"...which为维度建模场景提供坚实的基础。

Answer 2

建立的最佳实践方法是使用尺寸缓慢变化的维度模型来完成您想要的操作。销售代表经常用来描述SCD的有用性。例如，奖金与团队绩效挂钩的销售经理不希望在代表调到一个新的领域时，他们的总奖金会下降。SCD非常适合于跟踪这类事情(以及您所描述的情况)，并允许您查看历史上任何时候的情况。

花点时间在Ralph Kimball's website上开始吧。我推荐你阅读的前3篇文章是“缓慢改变维度”、“缓慢改变维度第2部分”和“维度建模的10条基本规则”。

为了取得成功，以下是一些需要重点关注的事情：

1. 您正在设计的不是3NF事务性数据库。熟悉denormalization.
2. Make，确保您理解粒度的含义并明确定义数据库的粒度。
3. 不使用自然键作为键，也不将任何智能添加到您的代理键中(时间键除外)。
4. 您的应用程序的目标应该是查询速度和易解性，以及类型1和类型2慢慢变化的维度，并知道在哪里使用它们。
5. 确保您在业务方面有一个有能力“打破束缚”的赞助商。你会发现组织中不同的人对同一件事有不同的定义，你需要一个有权做出决定的执行者。为了理解我的意思，让你的组织中的5个不同的人来定义“客户”或“毛利”。你会很幸运地得到两个人以同样的方式定义。
6. 不要尝试即兴发挥。阅读The Data Warehouse Lifecycle Toolkit并接受这些想法，即使它们一开始看起来很奇怪。
7. 联机分析处理功能强大，如果实施得当，可以改变人们的生活。如果不是，这绝对是一场噩梦。
8. 玩得开心！