使用SQL Server更新维度表（BID或数据工具） [英] Updating dimension tables using SQL Server (BIDs or Data Tools)

问题描述

对于应该如何将维度成员添加到数据仓库中，我感到很困惑。假设TOWN_NAME是将Town_Id链接到town_name的维度表。因此，现在，我有1000个客户名称，它们来自9个城镇。突然，在我的下一个ETL流程中，最终添加了一个客户，该客户的城镇不在我所称的9个城镇之中。因此，我需要向我的维度表添加一个成员。我将使用BIDS或DATA TOOLS（BIDS 2012）中的哪个步骤/过程？这应该怎么做？我对可以做什么感到很迷茫。

I'm quite confused as to how I'm supposed to be adding dimension members to my data warehouse. Let's say that TOWN_NAME is a dimension table that links town_Id to a town_name. So, now, I have 1000 customer names, and they are from 9 towns. Suddenly, in my next ETL process, a customer ends up being added whose town is not amongst that 9 towns i have in my dimension. So I need to add a member to my dimension table. Which step/process in BIDS or DATA TOOLS (BIDS 2012) would have I to use? How should this be one? I'm quite lost as to what could be done.

推荐答案

通常的模式-无论您使用什么工具填充数据仓库-填充维度在之前，您要填充事实，正是为了避免这个问题。

The usual pattern - regardless of what tools you're using to populate your data warehouse - is to populate your Dimension before you populate your Fact, precisely to avoid this problem.

通常的处理方式是拥有一个可从中提取Dimension数据的软件包您的源系统，然后将所有新行加载到Dimension表中。然后，当事实表加载在此过程中的稍后发生时，您将使用城镇名称从维度中查找ID列。然后，将您的Fact数据加载到Fact表中，并将相关城镇的ID作为其列值之一。

The usual way to do things is to have a package which pulls out your Dimension data from your source system(s), and then load any new rows into your Dimension table. Then, when your Fact table load happens later in the process you look-up the ID column from the Dimension using the town name. Your Fact data should then be loaded into the Fact table with the ID for the relevant town as one of its column values.

具体来说，在SSIS中，您可以通过以下方法进行管理创建一个可以加载Dimension表的包，并在同一项目中创建另一个可以加载Fact表的包。然后，您可以通过几种不同的方式控制这些发生的顺序：

Specifically, in SSIS, you can manage this by creating a package which does your Dimension table load, and another package in the same project which does your Fact table load. Then you can control the order these happen in a couple of different ways:

• 您可以创建第三个程序包，该程序包使用两个Execute Package任务来执行


• 您可以创建一个SQL Server代理作业，先调用Dimension程序包，然后调用Fact程序包。

如果您希望能够一次运行Visual Studio中的所有内容，请选择第一个选项。

If you want to be able to run everything from within Visual Studio in order in one go, then go with the first option.

这篇关于使用SQL Server更新维度表（BID或数据工具）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！