緩時變維度轉換Slowly Changing Dimension Transformation

「緩時變維度」轉換可在資料倉儲維度資料表中協調記錄的更新與插入。The Slowly Changing Dimension transformation coordinates the updating and inserting of records in data warehouse dimension tables. 例如,您可利用此轉換來設定轉換輸出,該轉換輸出會使用 AdventureWorks OLTP 資料庫中 Production.Products 資料表的資料,在 AdventureWorksDW2012AdventureWorksDW2012 資料庫的 DimProduct 資料表內插入和更新記錄。For example, you can use this transformation to configure the transformation outputs that insert and update records in the DimProduct table of the AdventureWorksDW2012AdventureWorksDW2012 database with data from the Production.Products table in the AdventureWorks OLTP database.

重要

「緩時變維度精靈」只支援與 SQL ServerSQL Server的連接。The Slowly Changing Dimension Wizard only supports connections to SQL ServerSQL Server.

「緩時變維度」轉換會提供下列功能,以管理緩時變維度:The Slowly Changing Dimension transformation provides the following functionality for managing slowly changing dimensions:

  • 將傳入資料列與查閱資料表中的資料列比對,以識別新的和現有的資料列。Matching incoming rows with rows in the lookup table to identify new and existing rows.

  • 在不允許變更時識別包含變更的傳入資料列。Identifying incoming rows that contain changes when changes are not permitted.

  • 識別需要更新之推斷的成員記錄。Identifying inferred member records that require updating.

  • 識別包含需要插入新記錄和更新過期記錄之記錄變更的傳入資料列。Identifying incoming rows that contain historical changes that require insertion of new records and the updating of expired records.

  • 偵測包含需要更新現有記錄 (包括過期記錄) 變更的傳入資料列。Detecting incoming rows that contain changes that require the updating of existing records, including expired ones.

    「緩時變維度」轉換支援四個類型的變更:變更屬性、記錄屬性、固定屬性及推斷的成員。The Slowly Changing Dimension transformation supports four types of changes: changing attribute, historical attribute, fixed attribute, and inferred member.

  • 變更屬性變更會覆寫現有記錄。Changing attribute changes overwrite existing records. 此種變更相當於「類型 1」變更。This kind of change is equivalent to a Type 1 change. 「緩時變維度」轉換會將這些資料列導向稱為 [變更屬性更新輸出] 的輸出。The Slowly Changing Dimension transformation directs these rows to an output named Changing Attributes Updates Output.

  • 記錄屬性變更會新建記錄,而不是更新現有記錄。Historical attribute changes create new records instead of updating existing ones. 現有記錄中唯一允許的變更,是更新指示記錄為目前記錄還是過期記錄的資料行。The only change that is permitted in an existing record is an update to a column that indicates whether the record is current or expired. 此種變更相當於「類型 2」變更。This kind of change is equivalent to a Type 2 change. 「緩時變維度」轉換會將這些資料列導向至兩個輸出:[記錄屬性插入輸出] 及 [新輸出]。The Slowly Changing Dimension transformation directs these rows to two outputs: Historical Attribute Inserts Output and New Output.

  • 固定屬性變更指示資料行的值不得變更。Fixed attribute changes indicate the column value must not change. 「緩時變維度」轉換會偵測變更,並可將具有變更的資料列導向稱為 [固定屬性輸出] 的輸出。The Slowly Changing Dimension transformation detects changes and can direct the rows with changes to an output named Fixed Attribute Output.

  • 推斷的成員指示資料列在維度資料表中為推斷的成員記錄。Inferred member indicates that the row is an inferred member record in the dimension table. 當事實資料表參考尚未載入的維度成員時就會有推斷的成員。An inferred member exists when a fact table references a dimension member that is not yet loaded. 會建立最低推斷的成員記錄以預期相關的維度資料,該相關維度資料會在維度資料的後續載入中提供。A minimal inferred-member record is created in anticipation of relevant dimension data, which is provided in a subsequent loading of the dimension data. 「緩時變維度」轉換會將這些資料列導向稱為 [推斷的成員更新] 的輸出。The Slowly Changing Dimension transformation directs these rows to an output named Inferred Member Updates. 當載入推斷成員的資料時,可以更新現有的記錄而不是建立新記錄。When data for the inferred member is loaded, you can update the existing record rather than create a new one.

注意

「緩時變維度」轉換不支援需要變更維度資料表的「類型 3」變更。The Slowly Changing Dimension transformation does not support Type 3 changes, which require changes to the dimension table. 藉由識別具有固定屬性更新類型的資料行,您可以擷取適用「類型 3」變更的資料值。By identifying columns with the fixed attribute update type, you can capture the data values that are candidates for Type 3 changes.

在執行階段,「緩時變維度」轉換會首先嘗試將傳入資料列與查閱資料表中的記錄比對。At run time, the Slowly Changing Dimension transformation first tries to match the incoming row to a record in the lookup table. 如果找不到相符部分,則傳入資料列會成為新記錄;因此,「緩時變維度」轉換不會執行其他工作,並會將資料列導向 [新輸出]。If no match is found, the incoming row is a new record; therefore, the Slowly Changing Dimension transformation performs no additional work, and directs the row to New Output.

如果找到相符部分,「緩時變維度」轉換會偵測資料列是否包含變更。If a match is found, the Slowly Changing Dimension transformation detects whether the row contains changes. 如果資料列包含變更,則「緩時變維度」轉換會識別每個資料行的更新類型,並將資料列導向 [變更屬性更新輸出]、[固定屬性輸出]、[記錄屬性插入輸出] 或 [推斷的成員更新輸出]。If the row contains changes, the Slowly Changing Dimension transformation identifies the update type for each column and directs the row to the Changing Attributes Updates Output, Fixed Attribute Output, Historical Attributes Inserts Output, or Inferred Member Updates Output. 如果資料列未變更,則「緩時變維度」轉換會將資料列導向 [不變更輸出]。If the row is unchanged, the Slowly Changing Dimension transformation directs the row to the Unchanged Output.

緩時變維度轉換輸出Slowly Changing Dimension Transformation Outputs

「緩時變維度」轉換擁有一個輸入和最多六個輸出。The Slowly Changing Dimension transformation has one input and up to six outputs. 輸出會將資料列導向至對應到此資料列的更新與插入需求之資料流程子集。An output directs a row to the subset of the data flow that corresponds to the update and the insert requirements of the row. 此轉換不支援錯誤輸出。This transformation does not support an error output.

下表描述轉換輸出及其後續資料流程的需求。The following table describes the transformation outputs and the requirements of their subsequent data flows. 該需求會描述「緩時變維度精靈」建立的資料流程。The requirements describe the data flow that the Slowly Changing Dimension Wizard creates.

輸出Output 描述Description 資料流程需求Data flow requirements
[變更屬性更新輸出]Changing Attributes Updates Output 會更新查閱資料表中的記錄。The record in the lookup table is updated. 此輸出用於變更屬性資料列。This output is used for changing attribute rows. 「OLE DB 命令」轉換會使用 UPDATE 陳述式更新記錄。An OLE DB Command transformation updates the record using an UPDATE statement.
[固定屬性輸出]Fixed Attribute Output 不得變更之資料列中的值與查閱資料表中的值不相符。The values in rows that must not change do not match values in the lookup table. 此輸出用於固定屬性資料列。This output is used for fixed attribute rows. 不會建立任何預設資料流程。No default data flow is created. 如果轉換已設定為遇到固定屬性資料行的變更之後繼續,則應該建立擷取這些資料列的資料流程。If the transformation is configured to continue after it encounters changes to fixed attribute columns, you should create a data flow that captures these rows.
[記錄屬性插入輸出]Historical Attributes Inserts Output 查閱資料表至少包含一個相符的資料列。The lookup table contains at least one matching row. 標示為「目前的」的資料列現在必須標示為「已過期」。The row marked as “current” must now be marked as "expired". 此輸出用於記錄屬性資料列。This output is used for historical attribute rows. 「衍生的資料行」轉換會為過期資料列和目前資料列指標建立資料行。Derived Column transformations create columns for the expired row and the current row indicators. 「OLE DB 命令」轉換會更新現在必須標示為「已過期」的記錄。An OLE DB Command transformation updates the record that must now be marked as "expired". 具有新資料行值的資料列會導向至「新輸出」,在此會插入該資料列並將其標示為「目前」。The row with the new column values is directed to the New Output, where the row is inserted and marked as "current".
[推斷的成員更新輸出]Inferred Member Updates Output 會插入推斷之維度成員的資料列。Rows for inferred dimension members are inserted. 此輸出用於推斷的成員資料列。This output is used for inferred member rows. 「OLE DB 命令」轉換會使用 SQL UPDATE 陳述式更新記錄。An OLE DB Command transformation updates the record using an SQL UPDATE statement.
[新輸出]New Output 查閱資料表不包含相符的資料列。The lookup table contains no matching rows. 會將資料列加入維度資料表。The row is added to the dimension table. 此輸出用於新資料列和記錄屬性資料列的變更。This output is used for new rows and changes to historical attributes rows. 「衍生的資料行」轉換會設定目前資料列指標,而 OLE DB 目的地則會插入該資料列。A Derived Column transformation sets the current row indicator, and an OLE DB destination inserts the row.
[不變更輸出]Unchanged Output 查閱資料表中的值與資料列值相符。The values in the lookup table match the row values. 此輸出用於不變更的資料列。This output is used for unchanged rows. 因為「緩時變維度」轉換不執行任何工作,所以不會建立任何預設資料流程。No default data flow is created because the Slowly Changing Dimension transformation performs no work. 如果您要擷取這些資料列,則應該為此輸出建立資料流程。If you want to capture these rows, you should create a data flow for this output.

商務索引鍵Business Keys

「緩時變維度」轉換至少需要一個商務索引鍵資料行。The Slowly Changing Dimension transformation requires at least one business key column.

「緩時變維度」轉換不支援 Null 商務索引鍵。The Slowly Changing Dimension transformation does not support null business keys. 如果資料包含商務索引鍵資料行為 Null 的資料列,則應該從資料流程移除那些資料列。If the data include rows in which the business key column is null, those rows should be removed from the data flow. 您可利用「條件式分割」轉換,來篩選商務索引鍵資料行包含 Null 值的資料列。You can use the Conditional Split transformation to filter rows whose business key columns contain null values. 如需詳細資訊,請參閱 Conditional Split TransformationFor more information, see Conditional Split Transformation.

最佳化緩時變維度轉換的效能Optimizing the Performance of the Slowly Changing Dimension Transformation

如需如何改善緩時變維度轉換效能的建議,請參閱 資料流程效能功能For suggestions on how to improve the performance of the Slowly Changing Dimension Transformation, see Data Flow Performance Features.

疑難排解緩時變維度轉換Troubleshooting the Slowly Changing Dimension Transformation

您可以記錄緩時變維度轉換對外部資料提供者執行的呼叫。You can log the calls that the Slowly Changing Dimension transformation makes to external data providers. 您可以使用這項記錄功能,疑難排解緩時變維度轉換對外部資料來源執行的連接、命令和查詢。You can use this logging capability to troubleshoot the connections, commands, and queries to external data sources that the Slowly Changing Dimension transformation performs. 若要記錄緩時變維度轉換對外部資料提供者執行的呼叫,請啟用封裝記錄,然後在封裝層級選取 [診斷] 事件。To log the calls that the Slowly Changing Dimension transformation makes to external data providers, enable package logging and select the Diagnostic event at the package level. 如需詳細資訊,請參閱 封裝執行的疑難排解工具For more information, see Troubleshooting Tools for Package Execution.

設定緩時變維度轉換Configuring the Slowly Changing Dimension Transformation

您可以透過 SSISSSIS 設計師或以程式設計方式設定屬性。You can set properties through SSISSSIS Designer or programmatically.

如需有關可以在 [進階編輯器] 對話方塊中或以程式設計方式設定之屬性的詳細資訊,請按下列其中一個主題:For more information about the properties that you can set in the Advanced Editor dialog box or programmatically, click one of the following topics:

設定緩時變維度轉換輸出Configuring the Slowly Changing Dimension Transformation Outputs

在維度資料表中協調記錄的更新與插入是一項複雜的工作,特別是如果同時使用「類型 1」和「類型 2」變更。Coordinating the update and insertion of records in dimension tables can be a complex task, especially if both Type 1 and Type 2 changes are used. SSISSSIS 設計師提供兩種方法,以設定緩時變維度的支援: Designer provides two ways to configure support for slowly changing dimensions:

  • [進階編輯器] 對話方塊,在其中您可以選取連接、設定一般與自訂元件屬性、選擇輸入資料行,以及設定六個輸出上的資料行屬性。The Advanced Editor dialog box, in which you to select a connection, set common and custom component properties, choose input columns, and set column properties on the six outputs. 若要完成緩時變維度支援的設定工作,您必須手動建立「緩時變維度」轉換所使用之輸出的資料流程。To complete the task of configuring support for a slowly changing dimension, you must manually create the data flow for the outputs that the Slowly Changing Dimension transformation uses. 如需詳細資訊,請參閱 資料流程For more information, see Data Flow.

  • 「載入維度精靈」,其會引導您執行設定「緩時變維度」轉換和建立轉換輸出的資料流程等步驟。The Load Dimension Wizard, which guides you though the steps to configure the Slowly Changing Dimension transformation and build the data flow for transformation outputs. 若要變更緩時變維度的組態,請重新執行「載入維度精靈」。To change the configuration for slowly change dimensions, rerun the Load Dimension Wizard. 如需詳細資訊,請參閱 使用緩時變維度精靈來設定輸出For more information, see Configure Outputs Using the Slowly Changing Dimension Wizard.

設定資料流程元件的屬性Set the Properties of a Data Flow Component