如何在事实表“之后”添加列它已经部署并填充了吗？
我有一个 SQL Server 2005 数据集市星型模式，其中包含通常的事实表和维度表。这是通过基于 SSIS 的 ETL 包进行部署和填充的。到目前为止一切都很好。
客户刚刚向我提出了新的要求。从来没有，我听到你说！这一要求意味着我需要向数据集市添加一个新的维度表，以衡量传入事实的新方面（恰好是财务方面）。
为了能够通过这个新维度“切片”事实，我需要在链接到新维度的事实表中添加一个新的外键列。
我不清楚做到这一点的最佳方法。我应该如何处理已经捕获的数据？只是让新列可以为空并接受旧事实将具有 NULL 吗？事实上，当我打字时，我突然意识到我也可以更新旧的事实。或者也许我应该创建一个单独的（子？）事实表，其中仅包含指向每个新（父）事实的链接和指向新维度的链接。
我无法找到有关此类更改的最佳实践的任何信息。
任何帮助将不胜感激。
顺便一提。尚未使用分析服务。
谢谢， 马丁
I have a SQL Server 2005 data-mart star schema with the usual fact and dimensions tables. This is deployed and being populated via an SSIS based ETL package. All fine so far.
I have just been approached by the customer with a new requirement. Never, I hear you say! This requirement will mean I need to add a new dimension table to the data-mart to measure a new aspect of the incoming facts which happen to be financial.
To be able to 'slice' the facts by this new dimension I need to add a new foreign key column in the fact table linking to the new dimension.
I am unclear on the best way to do this. What should I do with the data that has already been captured? Just make the new column null-able and accept that old fact will have a NULL? Actually, as I am typing, its dawned on me that I could update old facts as well. Or maybe I should create a separate (child?) fact table which would just contain a link to each new (parent) fact and a link to the new dimension.
I've not been able to find any information on a best practice for this type of change.
Any help would be much appreciated.
By the way. No Analysis Services used yet.
Thanks,
Martin
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
添加新的维度表。
填充它。
在现有事实上添加可为 null 的引用。
并非所有事实都可以连接到新维度。当您获得新信息时，这种情况很常见。如果您拥有所有原始文件，您可能拥有更新所有事实所需的所有信息。
如果无法将所有事实映射到新维度，请向该维度添加“N/A”行。有时，根据您对事实的了解，有充分的理由有多个 N/A 行。
更新您的事实，以便它们全部引用新维度 - 正确的维度值或特殊的 N/A 行。
更新您的事实，
理想情况下，您将修改事实表以使该列不可为空。有时这会花费非常长的时间，导出数据、重新定义表和重新加载数据会更容易。
Add the new dimension table.
Populate it.
Add the nullable reference on your existing facts.
Not all facts may be joinable to the new dimension. This is common when you have new information. If you have all of your original files, you may have all the information required to update all facts.
If all facts cannot be mapped to the new dimension, add a "N/A" row to the dimension. Sometimes there's a good reason to have several N/A rows depending on what you know about your facts.
Update your facts so they all reference the new dimension -- either the proper dimension value or the special N/A row.
Ideally, you'll modify your fact table to make the column not-nullable. Sometimes this takes a painfully long time, and it's easier to export the data, redefine the table, and reload the data.
好吧，S.Lot 回答了大部分问题，我只想补充一点，如何处理旧事实是一个商业决策。请务必询问他们并以书面形式获得。
Well S.Lot answered most of it, I would just add that what to do with the old facts is a business decision. Make sure you ask them and get it in writing.
S.Lott 有一个很好的答案，如果您将其与“子”事实表的建议结合起来，您可以将另一个事实表作为具有新维度和所有其他相同维度的“桥接表”，但请考虑一下- 如果你遇到这个麻烦，你可以制作一个与旧的相同的新事实表设计，但具有不可为空新维度（桥接表的唯一费用是空间无论如何）。将历史记录适当地填充到新表中 - 按照 S.Lott 的建议链接到历史记录的有效维度或 N/A 维度。然后开始填充新数据。您永远不必返回并将可空值更改为不可空值。然后，您可以弃用旧的事实表 - 或交换您的表示层视图（如果您有这些视图，这将为您的事实和维度提供很大的自由度）。
S.Lott has a great answer, if you combine that with your suggestion of a "child" fact table, you could have another fact table as a "bridge table" with the new dimension and all the other same dimensions, but think about it - if you are going to that trouble, you could just make a new fact table design identical to the old, but with a non-nullable new dimension (the only expense over your bridge table would be the space of the facts anyway). Populate the history appropriately into the new table - linking either to a valid dimension or the N/A dimension for history as S.Lott suggested. Then start populating new data. You never have to go back and change the nullable to non-nullable. Then you can just deprecate the old fact table - or swap your presentation layer views (if you have those, this gives you a lot mofr freedom underneath with your facts and dimensions).