Amazon Redshift での次元モデリング |アマゾンウェブサービス

プラトン再発行

フォロワー： 0

Amazonレッドシフトは、フルマネージドのペタバイト規模のクラウドデータウェアハウスであり、何万もの顧客が毎日エクサバイトのデータを処理して分析ワークロードを強化するために使用しています。次元モデルを使用すると、データを構造化し、ビジネスプロセスを測定し、貴重な洞察を迅速に得ることができます。 Amazon Redshift は、次元モデルからのモデリング、オーケストレーション、レポートのプロセスを高速化するための組み込み機能を提供します。

この投稿では、ディメンションモデル、特にキンボールの方法論。 Amazon Redshift 内でのディメンションと事実の実装について説明します。データレイクから生データをステージングレイヤーに取得してモデリングを実行することに重点を置いた統合プロセスである抽出、変換、読み込み (ELT) を実行する方法を示します。全体として、この投稿では、Amazon Redshift でのディメンションモデリングの使用方法を明確に理解できます。

ソリューションの概要

次の図は、ソリューションのアーキテクチャを示しています。

次のセクションでは、まず次元モデルの重要な側面について説明し、実証します。その後、Amazon Redshift を使用して、ディメンションテーブルとファクトテーブルを含むディメンションデータモデルを使用してデータマートを作成します。データは、 COPY コマンドを使用すると、ディメンション内のデータがロードされます。 MERGE ステートメントを作成すると、事実が洞察が得られる次元に結合されます。を使用してディメンションとファクトの読み込みをスケジュールします。 AmazonRedshiftクエリエディターV2。最後に、私たちが使用するのは、アマゾンクイックサイト QuickSight ダッシュボードの形式でモデル化されたデータに関する洞察を得ることができます。

このソリューションでは、Amazon Redshift がイベントチケット販売用に提供するサンプルデータセット (正規化された) を使用します。この投稿では、簡素化とデモンストレーションの目的でデータセットを絞り込みました。次の表は、チケット販売と会場のデータの例を示しています。

によるキンボール次元モデリング手法、次元モデルの設計には XNUMX つの重要な手順があります。

ビジネスプロセスを特定します。
データの粒度を宣言します。
ディメンションを特定して実装します。
事実を特定して実行します。

さらに、デモンストレーションの目的で、ビジネスイベントのレポートと分析という XNUMX 番目のステップを追加します。

前提条件

このチュートリアルでは、次の前提条件を満たしている必要があります。

ビジネスプロセスを特定する

簡単に言えば、ビジネスプロセスを特定することは、組織内でデータを生成する測定可能なイベントを特定することです。通常、企業はデータを生の形式で生成する何らかの運用ソースシステムを持っています。これは、ビジネスプロセスのさまざまなソースを特定するための良い出発点となります。

ビジネスプロセスは、データマート次元と事実の形で。前述のサンプルデータセットを見ると、ビジネスプロセスが特定のイベントで行われた売上であることが明確にわかります。

よくある間違いは、会社の部門をビジネスプロセスとして使用することです。データ (ビジネスプロセス) はさまざまな部門間で統合される必要があります。この場合、マーケティング部門は販売データにアクセスできます。正しいビジネスプロセスを特定することが重要です。この手順を誤ると、データマート全体に影響を与える可能性があります (最終レポートで粒度が重複し、不正確なメトリクスが表示される可能性があります)。

データの粒度を宣言する

粒度の宣言は、データソース内のレコードを一意に識別する行為です。ファクトテーブルでは粒度を使用してデータを正確に測定し、さらにロールアップできるようにします。この例では、これは販売ビジネスプロセスの品目である可能性があります。

私たちのユースケースでは、販売が行われたトランザクション時間を調べることで、販売を一意に識別できます。これは最も原子的なレベルになります。

ディメンションを特定して実装する

ディメンションテーブルには、ファクトテーブルとその属性が記述されます。ビジネスプロセスの説明的なコンテキストを特定するときは、ファクトテーブルの粒度を念頭に置いて、テキストを別のテーブルに保存します。ディメンションテーブルをファクトテーブルに結合する場合、ファクトテーブルに関連付けられる行は XNUMX つだけである必要があります。この例では、次のテーブルを使用してディメンションテーブルに分割します。これらのフィールドは、測定する事実を記述します。

次元モデル (スキーマ) の構造を設計するときは、次のいずれかを作成できます。星 or スノーフレークスキーマ。構造はビジネスプロセスと密接に一致している必要があります。したがって、スタースキーマがこの例に最適です。次の図は、エンティティ関係図 (ERD) を示しています。

次のセクションでは、ディメンションを実装する手順について詳しく説明します。

ソースデータをステージングする

ディメンションテーブルを作成してロードする前に、ソースデータが必要です。したがって、ソースデータをステージングテーブルまたは一時テーブルにステージングします。これはよく次のように呼ばれます。 ステージング層、これはソースデータの生のコピーです。 Amazon Redshift でこれを行うには、 COPYコマンドにある Dimension-modeling-in-amazon-redshift パブリック S3 バケットからデータをロードします。 us-east-1 領域。 COPY コマンドでは AWS IDおよびアクセス管理 (IAM) ロール Amazon S3 へのアクセス。役割は次のとおりである必要がありますクラスターに関連付けられている。ソースデータをステージングするには、次の手順を実行します。

作ります venue ソーステーブル:

CREATE TABLE public.venue ( venueid bigint, venuename character varying(100), venuecity character varying(30), venuestate character(2), venueseats bigint
) DISTSTYLE AUTO SORTKEY (venueid);

会場データをロードします。

COPY public.venue
FROM 's3://redshift-blogs/dimensional-modeling-in-amazon-redshift/venue.csv'
IAM_ROLE '<Your IAM role arn>'
DELIMITER ','
REGION 'us-east-1'
IGNOREHEADER 1

作ります sales ソーステーブル:

CREATE TABLE public.sales (
    salesid integer,
    venueid character varying(256),
    saletime timestamp without time zone,
    qtysold BIGINT,
    commission numeric(18,2),
    pricepaid numeric(18,2)
) DISTSTYLE AUTO;

販売ソースデータをロードします。

COPY public.sales
FROM 's3://redshift-blogs/dimensional-modeling-in-amazon-redshift/sales.csv'
IAM_ROLE '<Your IAM role arn>'
DELIMITER ','
REGION 'us-east-1'
IGNOREHEADER 1

作ります calendar テーブル：

CREATE TABLE public.DimCalendar(
    dateid smallint,
        caldate date,
        day varchar(20),
        week smallint,
        month varchar(20),
        qtr varchar(20),
        year smallint,
        holiday boolean
) DISTSTYLE AUTO
SORTKEY
    (dateid);

カレンダーデータをロードします。

COPY public.DimCalendar
FROM 's3://redshift-blogs/dimensional-modeling-in-amazon-redshift/date.csv'
IAM_ROLE '<Your IAM role arn>'
DELIMITER ',' 
REGION 'us-east-1'
IGNOREHEADER 1

寸法表を作成する

ディメンションテーブルの設計は、ビジネス要件に応じて異なります。たとえば、長期にわたるデータの変更を追跡する必要があるかどうかなどです。がある XNUMX つの異なる寸法タイプ。この例では、次を使用します。 1を入力歴史的な変化を追跡する必要がないからです。タイプ 2 の詳細については、を参照してください。 Amazon Redshift のタイプ 2 のゆっくりと変化するディメンションへのデータのロードを簡素化する。ディメンションテーブルは、主キー、代理キー、およびテーブルへの変更を示すいくつかの追加フィールドを使用して非正規化されます。次のコードを参照してください。

create schema SalesMart;

CREATE TABLE SalesMart.DimVenue( 
    "VenueSkey" int IDENTITY(1,1) primary key
    ,"VenueId" VARCHAR NOT NULL
    ,"VenueName" VARCHAR NULL
    ,"VenueCity" VARCHAR NULL
    ,"VenueState" VARCHAR NULL
    ,"VenueSeats" INT NULL
    ,"InsertedDate" DATETIME NOT NULL
    ,"UpdatedDate" DATETIME NOT NULL
) 
diststyle AUTO;

ディメンションテーブルの作成に関するいくつかの注意事項:

フィールド名はビジネス向けの名前に変換されます
私たちの主キーは VenueID、販売が行われた会場を一意に識別するために使用されます。
レコードがいつ挿入および更新されたかを示す XNUMX つの行が追加されます (変更を追跡するため)。
私たちが使用しているのは、 AUTO配信スタイル Amazon Redshift に配布スタイルの選択と調整の責任を与える

次元モデリングで考慮すべきもう XNUMX つの重要な要素は、 代理キー。サロゲートキーは、ディメンションモデリングでディメンションテーブル内の各レコードを一意に識別するために使用される人工キーです。これらは通常、連続する整数として生成され、ビジネスドメインでは何の意味も持ちません。これらは通常、自然キーよりも小さく、代理キーとして時間が経っても変化しないため、一意性の確保や結合のパフォーマンスの向上など、いくつかの利点があります。これにより、一貫性が保たれ、ファクトとディメンションをより簡単に結合できるようになります。

Amazon Redshift では、通常、IDENTITY キーワードを使用して代理キーが作成されます。たとえば、前述の CREATE ステートメントは、次のディメンションテーブルを作成します。 VenueSkey 代理キー。の VenueSkey 新しい行がテーブルに追加されると、列には一意の値が自動的に入力されます。この列を使用して、会場テーブルを FactSaleTransactions 列で番号の横にあるXをクリックします。

代理キーを設計するためのいくつかのヒント:

代理キーには小さい固定幅のデータ型を使用します。これにより、パフォーマンスが向上し、ストレージ容量が削減されます。
IDENTITY キーワードを使用するか、連続値または GUID 値を使用して代理キーを生成します。これにより、代理キーが一意になり、変更できないことが保証されます。

MERGEを使用してディムテーブルをロードします。

ディムテーブルをロードするにはさまざまな方法があります。パフォーマンス、データ量、場合によっては SLA 読み込み時間など、特定の要素を考慮する必要があります。とともに MERGE ステートメントを使用すると、複数の挿入および更新コマンドを指定する必要なく、更新/挿入を実行できます。設定できるのは、 MERGE のステートメントストアドプロシージャデータを入力します。次に、クエリエディターを使用してプログラムで実行するストアドプロシージャをスケジュールします。これについては、この記事で後ほど説明します。次のコードは、というストアドプロシージャを作成します。 SalesMart.DimVenueLoad:

CREATE OR REPLACE PROCEDURE SalesMart.DimVenueLoad()
AS $$
BEGIN
MERGE INTO SalesMart.DimVenue USING public.venue as MergeSource
ON SalesMart.DimVenue.VenueId = MergeSource.VenueId
WHEN MATCHED
THEN
UPDATE
SET VenueName = ISNULL(MergeSource.VenueName, 'Unknown')
, VenueCity = ISNULL(MergeSource.VenueCity, 'Unknown')
, VenueState = ISNULL(MergeSource.VenueState, 'Unknown')
, VenueSeats = ISNULL(MergeSource.VenueSeats, -1)
, UpdatedDate = GETDATE()
WHEN NOT MATCHED
THEN
INSERT (
VenueId
, VenueName
, VenueCity
, VenueState
, VenueSeats
, UpdatedDate
, InsertedDate
)
VALUES (
ISNULL(MergeSource.VenueId, -1)
, ISNULL(MergeSource.VenueName, 'Unknown')
, ISNULL(MergeSource.VenueCity, 'Unknown')
, ISNULL(MergeSource.VenueState, 'Unknown')
, ISNULL(MergeSource.VenueSeats, -1)
, ISNULL(GETDATE() , '1900-01-01')
, ISNULL(GETDATE() , '1900-01-01')
);
END;
$$
LANGUAGE plpgsql;

ディメンションのロードに関するいくつかの注意事項:

レコードが初めて挿入されると、挿入日と更新日が設定されます。値が変更されるとデータが更新され、更新された日付には変更された日付が反映されます。挿入された日付は残ります。
データはビジネスユーザーによって使用されるため、NULL 値がある場合は、よりビジネスに適した値に置き換える必要があります。

事実を特定して実行する

穀物が特定の時間に発生した販売イベントであると宣言したので、ファクトテーブルにはビジネスプロセスの数値ファクトが保存されます。

測定すべき次の数値的事実を特定しました。

販売ごとのチケット販売枚数
販売手数料

事実を実践する

全 XNUMX 種類のファクトテーブル (トランザクションファクトテーブル、定期スナップショットファクトテーブル、蓄積スナップショットファクトテーブル)。それぞれがビジネスプロセスの異なるビューを提供します。この例では、トランザクションファクトテーブルを使用します。次の手順を実行します。

ファクトテーブルを作成する

CREATE TABLE SalesMart.FactSaleTransactions( 
    CalendarDate date NOT NULL
    ,SaleTransactionTime DATETIME NOT NULL
    ,VenueSkey INT NOT NULL
    ,QuantitySold BIGINT NOT NULL
    ,SaleComission NUMERIC NOT NULL
    ,InsertedDate DATETIME DEFAULT GETDATE()
) diststyle AUTO;

挿入された日付とデフォルト値が追加され、レコードがロードされたかどうか、およびいつロードされたかを示します。ファクトテーブルを再ロードするときにこれを使用して、重複を避けるためにすでにロードされているデータを削除できます。

ファクトテーブルのロードは、関連するディメンションを結合する単純な挿入ステートメントで構成されます。から参加します DimVenue 事実を説明する作成された表。ベストプラクティスですが、必須ではありません暦日ディメンション。エンドユーザーがファクトテーブル内を移動できるようになります。データは、新しいセールが発生したときにロードすることも、毎日ロードすることもできます。ここで、挿入された日付またはロードされた日付が役に立ちます。

ストアドプロシージャを使用してファクトテーブルをロードし、日付パラメーターを使用します。

次のコードを使用してストアドプロシージャを作成します。ディメンションのロードで適用したのと同じデータの整合性を維持するために、NULL 値がある場合は、それをよりビジネスに適した値に置き換えます。

create or replace procedure SalesMart.FactSaleTransactionsLoad(loadate datetime)
language plpgsql
as
    $$
begin
--------------------------------------------------------------------
/*** Delete records loaded for the day, should there be any ***/
--------------------------------------------------------------------
Delete from SalesMart.FactSaleTransactions
where cast(InsertedDate as date) = CAST(loadate as date);
RAISE INFO 'Deleted rows for load date: %', loadate;
--------------------------------------------------------------------
/*** Insert records ***/
--------------------------------------------------------------------
INSERT INTO SalesMart.FactSaleTransactions (
CalendarDate    
,SaleTransactionTime    
,VenueSkey  
,QuantitySold  
,Salecomission
)
SELECT DISTINCT
    ISNULL(c.caldate, '1900-01-01') as CalendarDate
    ,ISNULL(a.saletime, '1900-01-01') as SaleTransactionTime
    ,ISNULL(b.VenueSkey, -1) as VenueSkey
    ,ISNULL(a.qtysold, 0) as QuantitySold
    ,ISNULL(a.commission, 0) as SaleComission
FROM
    public.sales as a
 
LEFT JOIN SalesMart.DimVenue as b
on a.venueid = b.venueid
 
LEFT JOIN public.DimCalendar as c
on to_char(a.saletime,'YYYYMMDD') = to_char(c.caldate,'YYYYMMDD');
--Optional filter, should you want to load only the latest data from source
--where cast(a.saletime as date) = cast(loadate as date);
  
end;
$$;

次のコマンドでプロシージャを呼び出してデータをロードします。

call SalesMart.FactSaleTransactionsLoad(getdate())

データロードのスケジュールを設定する

Amazon Redshift Query Editor V2 でストアドプロシージャをスケジュールすることで、モデリングプロセスを自動化できるようになりました。次の手順を実行します。

最初にディメンションロードを呼び出し、ディメンションロードが正常に実行された後、ファクトロードが開始されます。

BEGIN;
----Insert Dim Loads
call SalesMart.DimVenueLoad(); ----Insert Fact Loads. They will only run if the DimLoad is successful
call SalesMart.FactSaleTransactionsLoad(getdate());
END;

ディメンションのロードが失敗すると、ファクトのロードは実行されません。古いディメンションを含むファクトテーブルをロードしたくないため、これによりデータの一貫性が確保されます。

ロードをスケジュールするには、次を選択します。 スケジュール クエリエディター V2 で。

クエリを毎日午前 5:00 に実行するようにスケジュールします。
オプションで、有効にすることで失敗通知を追加できます。 Amazon シンプル通知サービス (Amazon SNS) 通知。

Amazon Quicksight でデータをレポートおよび分析する

QuickSight は、洞察を簡単に提供できるビジネスインテリジェンスサービスです。フルマネージドサービスである QuickSight を使用すると、任意のデバイスからアクセスしてアプリケーション、ポータル、Web サイトに埋め込むことができるインタラクティブなダッシュボードを簡単に作成して公開できます。

データマートを使用して、ダッシュボードの形式で事実を視覚的に表示します。 QuickSight を開始してセットアップするには、次を参照してください。自動検出されないデータベースを使用したデータセットの作成.

QuickSight でデータソースを作成した後、サロゲートキーに基づいてモデル化されたデータ (データマート) が結合されます。 skey。このデータセットを使用してデータマートを視覚化します。

当社のエンドダッシュボードにはデータマートの洞察が含まれており、会場ごとの合計コミッションや最も売上が高かった日などの重要なビジネス上の質問に答えます。次のスクリーンショットは、データマートの最終製品を示しています。

クリーンアップ

今後料金が発生しないようにするには、この投稿の一部として作成したリソースをすべて削除してください。

まとめ

これで、 DimVenue, DimCalendar, FactSaleTransactions テーブル。私たちの倉庫は完成していません。より多くのファクトを使用してデータマートを拡張し、より多くのマートを実装できるようになり、ビジネスプロセスと要件が時間の経過とともに増大するにつれて、データウェアハウスも増大します。この投稿では、Amazon Redshift でのディメンションモデリングの理解と実装についてエンドツーエンドの視点を示しました。

始めましょう Amazonレッドシフト今日は立体模型。

著者について

バーナード・ヴァースター は、スケーラブルで効率的なデータモデルの作成、データ統合戦略の定義、データガバナンスとセキュリティの確保に長年携わってきた経験豊富なクラウドエンジニアです。彼は、ビジネス要件と目標に合わせながら、データを使用して洞察を引き出すことに情熱を持っています。

アビシェーク・パン は、AWS インドの公共部門の顧客と協力する WWSO スペシャリスト SA-Analytics です。彼は顧客と連携してデータ主導の戦略を定義し、分析のユースケースに関する詳細なセッションを提供し、スケーラブルでパフォーマンスの高い分析アプリケーションを設計します。彼は 12 年の経験があり、データベース、分析、AI/ML に情熱を持っています。彼は熱心な旅行者であり、カメラのレンズを通して世界を捉えようとしています。

SEO を活用したコンテンツと PR 配信。今日増幅されます。
PlatoData.Network 垂直生成 Ai。自分自身に力を与えましょう。こちらからアクセスしてください。
プラトアイストリーム。 Web3 インテリジェンス。知識増幅。こちらからアクセスしてください。
プラトンESG。自動車/EV、カーボン、クリーンテック、エネルギー、環境、太陽、廃棄物管理。こちらからアクセスしてください。
ブロックオフセット。環境オフセット所有権の近代化。こちらからアクセスしてください。
情報源： https://aws.amazon.com/blogs/big-data/dimensional-modeling-in-amazon-redshift/

タイムスタンプ：２０２２年７月１１日

タイムスタンプ： 2023 年 2 月 8 日

Amazon Redshift での次元モデリング | アマゾンウェブサービス

プラトン再発行

ソリューションの概要

前提条件

ビジネスプロセスを特定する

データの粒度を宣言する

ディメンションを特定して実装する

ソースデータをステージングする

寸法表を作成する

MERGEを使用してディムテーブルをロードします。

事実を特定して実行する

事実を実践する

データロードのスケジュールを設定する

Amazon Quicksight でデータをレポートおよび分析する

クリーンアップ

まとめ

著者について

より多くの AWSビッグデータ

オープンソース JDBC コネクタを使用した Amazon MSK Connect でデータをストリーミングする | アマゾンウェブサービス

AWS Glue クローラーによる Apache Hudi サポートの導入 | アマゾンウェブサービス

AWS Glue を使用して価格透明性データを処理する

Amazon QuickSight アクセスをオープンソース ID プロバイダー Keycloak と連携 | アマゾンウェブサービス

私たちに関しては

垂直検索とAi

プラットフォーム

フォロー