緩やかに変化するディメンションを読み込む
ほとんどのリレーショナル データ ウェアハウスでは、ディメンション データに対する更新を処理し、一般に "緩やかに変化するディメンション" (SCD) と呼ばれるものをサポートする必要があります。
緩やかに変化するディメンションのタイプ
緩やかに変化するディメンションには複数の種類があり、そのうちの 3 つが一般的に実装されています。
タイプ 0
"タイプ 0" のディメンション データは変更できません。 変更しようとすると常に失敗します。
| DateKey | DateAltKey | 日付 | 月 | Year |
|---|---|---|---|---|
| 20230101 | 01-01-2023 | 土曜日 | January | 2023 |
型 1
"タイプ 1" のディメンションでは、ディメンション レコードはその場で更新されます。 既存のディメンション行に対して行われた変更は、それまでに読み込まれた、そのディメンションに関連するすべてのファクトに適用されます。
| StoreKey | StoreAltKey | StoreName |
|---|---|---|
| 123 | EH199J |
型 2
"タイプ 2" のディメンションでは、ディメンションを変更すると、新しいディメンション行が作成されます。 ディメンションの以前のバージョンの既存の行は履歴ファクト分析のために保持され、新しい行は将来のファクト テーブル エントリに適用されます。
| CustomerKey | CustomerAltKey | 名前 | Address | City | DateFrom | DateTo | IsCurrent |
|---|---|---|---|---|---|---|---|
| 1211 | jo@contoso.com | Jo Smith | 999 Main St | Seattle | 20190101 | 20230105 | False |
| 2996 | jo@contoso.com | Jo Smith | 1234 9th Ave | ボストン | 20230106 | True |
注意
タイプ 2 のディメンションには、多くの場合、エンティティの各バージョンの有効期間を追跡する列と、エンティティの現在のバージョンを表す行を示すフラグの、どちらか一方または両方が含まれます。 増分代理キーを使っていて、最後に追加されたバージョンのエンティティのみを追跡する必要がある場合は、これらの列は必要ない場合があります。ただし、その決定を行う前に、新しいファクトが入力されたときに、ファクトが関連するイベントが発生した日時に基づいて、エンティティの適切なバージョンを検索する方法を検討してください。
INSERT ステートメントと UPDATE ステートメントを組み合わせる
タイプ 1 とタイプ 2 の更新を実装するロジックは複雑な場合があり、さまざまな手法を使用できます。 たとえば、UPDATE ステートメントと INSERT ステートメントの組み合わせを使用できます。
-- New Customers
INSERT INTO dbo.DimCustomer
SELECT stg.*
FROM dbo.StageCustomers AS stg
WHERE NOT EXISTS
(SELECT * FROM dbo.DimCustomer AS dim
WHERE dim.CustomerAltKey = stg.CustNo)
-- Type 1 updates (name)
UPDATE dbo.DimCustomer
SET CustomerName = stg.CustomerName
FROM dbo.StageCustomers AS stg
WHERE dbo.DimCustomer.CustomerAltKey = stg.CustomerNo;
-- Type 2 updates (StreetAddress)
INSERT INTO dbo.DimCustomer
SELECT stg.*
FROM dbo.StageCustomers AS stg
JOIN dbo.DimCustomer AS dim
ON stg.CustNo = dim.CustomerAltKey
AND stg.StreetAddress <> dim.StreetAddress;
前の例では、IDENTITY 列に基づく増分代理キーによって各行が識別され、特定の代替キーに対する代理キーのうち最も大きい値のものが、その代替キーに関連付けられているディメンション エンティティの最新または "現在" のインスタンスを示すものと、想定しています。 実際には、多くのデータ ウェアハウス デザイナーは、変化するディメンションの現在アクティブなインスタンスを示すために Boolean 列を追加したり、ディメンション インスタンスの各バージョンのアクティブな期間を示すために DateTime フィールドを使ったりしています。 これらの方法では、タイプ 2 の変更のロジックに、新しいディメンション行の INSERT と、現在の行を非アクティブとしてマークするための UPDATE の "両方" を、含める必要があります。
MERGE ステートメントを使用する
複数の INSERT および UPDATE ステートメントを使う代わりに、1 つの MERGE ステートメントを使って "アップサート" 操作を実行し、新しいレコードの挿入と既存のレコードの更新を行うことができます。
MERGE dbo.DimProduct AS tgt
USING (SELECT * FROM dbo.StageProducts) AS src
ON src.ProductID = tgt.ProductBusinessKey
WHEN MATCHED THEN
-- Type 1 updates
UPDATE SET
tgt.ProductName = src.ProductName,
tgt.ProductCategory = src.ProductCategory,
tgt.Color = src.Color,
tgt.Size = src.Size,
tgt.ListPrice = src.ListPrice,
tgt.Discontinued = src.Discontinued
WHEN NOT MATCHED THEN
-- New products
INSERT VALUES
(src.ProductID,
src.ProductName,
src.ProductCategory,
src.Color,
src.Size,
src.ListPrice,
src.Discontinued);
Note
MERGE ステートメントについて詳しくは、Azure Synapse Analytics の MERGE に関するドキュメントをご覧ください。