はじめに

このチュートリアルでは、MySQLのデータソースと、Snowflakeのデータウェアハウス(DWH)を対象に、Qlik ReplicateとQlik Compose for Data Warehousesを利用してリアルタイムにデータを分析するためのDWHを設計・構築の手順をご紹介します。

【検証環境】

  • Qlik Replicate 6.6.0 SR1 on Windows
  • Qlik Compose for Data Warehouse 6.6.0 SR2 on Windows
  • MySQL Community Edition 8.0.20 on Windows
  • Snowflake  on Azure

準備

ターゲットのSnowflakeの準備

この演習では、ターゲットのDWHとしてSnowflakeを利用します。Qlik Replicate–Snowflakeターゲットの利用の記事に従ってセットアップを行います。また、データベースの構成として以下の形とします。

ODSデータベース: 複数システムから取得した最新データを保持する領域

  • salesスキーマ: データソースからレプリケートしたSalesデータを格納する領域(Qlik Replicateにより自動生成)
  • ErrorMartスキーマ: 条件に合致しない不正データなどを格納する領域

EDWデータベース: 統合データウェアハウスとしてクレンジングした履歴データを格納する領域

  • EDW: 統合データモデルにクレンジングしたデータを格納する領域
  • DataMart: 統合データモデルから目的別に切り出したデータマートを格納する領域

また、Snowflakeのリソースに対して、Qlik Composeからアクセスするユーザーに対して以下の権限を付与する必要があります。

  • WarehouseへのUsage権限
  • DatabaseとSchemaに対するOwnershipもしくはUsage権限
  • INFORMATION_SCHEMAへのSELECT権限
  • Create Schemaの権限
  • TableのCreate, Select, Insert, Update, Delete, Truncate, References, Drop権限
  • ViewのSelect, Create, Drop権限
  • External FunctionsへのUsage権限

そのためにQlik Replicate–Snowflakeターゲットの利用で作成したODSデータベースを一旦削除し、必要なスキーマを含むODS及びEDWの作成、及び権限の付与を再度実施し直します。そのために以下のSQL文を実行します。

use role sysadmin;
//ODSデータベースを削除
drop database if exists ods;

//ODSデータベースを作成
create database ODS;

//ODSデータベースにErrorMartスキーマを作成
use database ODS;
create schema ErrorMart;

//EDWデータベースを作成
create database EDW;

//EDWデータベースにEDWとDataMartスキーマを作成
use database EDW;
create schema EDW;
create schema DataMart;

use role accountadmin;
//replicateとsysadminロールに対してODSデータベース操作の権限を付与
grant all on database ODS to role replicate;
grant all on all schemas in database ODS  to role replicate;
grant all on future schemas in database ODS  to role replicate;
grant select, insert, update, truncate, delete, references on all tables in database ODS to role replicate;
grant select, insert, update, truncate, delete, references on future tables in database ODS to role replicate;
grant usage on all functions in database ODS to role replicate;
grant usage on future functions in database ODS to role replicate;
grant select on all views in database ODS to role replicate;
grant select on future views in database ODS to role replicate;

grant all on database ODS to role sysadmin;
grant all on all schemas in database ODS  to role sysadmin;
grant all on future schemas in database ODS  to role sysadmin;
grant select, insert, update, truncate, delete, references on all tables in database ODS to role sysadmin;
grant select, insert, update, truncate, delete, references on future tables in database ODS to role sysadmin;
grant usage on all functions in database ODS to role sysadmin;
grant usage on future functions in database ODS to role sysadmin;
grant select on all views in database ODS to role sysadmin;
grant select on future views in database ODS to role sysadmin;

//replicateとsysadminロールに対してEDWデータベース操作の権限を付与
grant all on database EDW to role replicate;
grant all on all schemas in database EDW  to role replicate;
grant all on future schemas in database EDW  to role replicate;
grant select, insert, update, truncate, delete, references on all tables in database EDW to role replicate;
grant select, insert, update, truncate, delete, references on future tables in database EDW to role replicate;
grant select on all views in database EDW to role replicate;
grant select on future views in database EDW to role replicate;
grant usage on all functions in database EDW to role replicate;
grant usage on future functions in database EDW to role replicate;

grant all on database EDW to role sysadmin;
grant all on all schemas in database EDW  to role sysadmin;
grant all on future schemas in database EDW  to role sysadmin;
grant select, insert, update, truncate, delete, references on all tables in database EDW to role sysadmin;
grant select, insert, update, truncate, delete, references on future tables in database EDW to role sysadmin;
grant select on all views in database EDW to role sysadmin;
grant select on future views in database EDW to role sysadmin;
grant usage on all functions in database EDW to role sysadmin;
grant usage on future functions in database EDW to role sysadmin;

上記スクリプトを実行すると、以下の様な構成でデータベースとスキーマが作成されます。

image

※検証実施後、上記で作成したリソースを全て削除したい場合には以下を実行することで削除することができます。(ここでは実行は不要です)

use role sysadmin;
drop database ods;
drop database edw;

ソースのMySQLとQlik Replicateの準備

Qlik Replicate–MySQLベースのDBソースの利用の手順に従ってMySQLのソースをセットアップし、またSnowflakeへのQlik Replicateタスクを作成・実行します。ただし、ここでは変更データをキャプチャーしてターゲットに適用するだけでなく、データウェアハウスに過去の変更履歴データを保存するために、タスクを一旦削除し、再作成時に「Store Changes」も含め3つのTask Optionsを全て有効にして作成します。これにより適用された変更データの履歴が保存されます。その後、Qlik Replicate–MySQLベースのDBソースの利用の同様の手順で再度タスクを作成して実行します。(MySQLのsalesデータベースに含まれる9個のテーブルが、SnowflakeのODSデータベース内のsalesスキーマにレプリケートされます。)

image

上記のQlik Replicateタスクを実行すると、以下の様なテーブルがSnowflake上に作成されます。Task Optionsの「Store Changes」を有効化することで、_ctテーブルが作成され、このテーブルに変更履歴が格納されます。

image

Qlik Composeへのアクセスとプロジェクトの作成

スタートメニューから[Qlik Compose for Data Warehouse Console]を開きます。

image

[New Project]をクリックして[Name]に「DWH」と入力し、[OK]をクリックします。

image

データベース(DWH)とデータソースへの接続の定義

DWHへの接続

最初のステップとしてデータウェアハウスへの接続定義を作成します。[Manage]をクリックします。

image

DATABASEセクションの[New]をクリックします。

image

以下の形でDWHの接続情報と、構成のプロパティを入力して[Test Connections]で接続をテストし、問題が無ければ[OK]をクリックします。

image

データソースへの接続

次にデータソースへの接続の定義を作成します。[New]をクリックします。

image

[Name]に「Sales OLTP」と入力し、[Landing Area Setting」にSnowflake上のODSデータベースのsalesスキーマします。このSnowflake上のデータをデータソースとして、Qlik Composeで分析可能なデータに加工します。(Qlik ReplicateによりソースステムのMySQLから転送されたデータの格納先ターゲットを、Qlik Composeではデータソースとして扱います。)[Error mart schema]はこの後で扱う、データ品質の検証ルールに合致しないデータを配置する場所として「ERRORMART」を指定します。([Associate with Replicate task]では、DWH側へデータを転送するQlik Replicateタスクとの関連付けを行い、Qlik ComposeのMonitor上でQlik Replicateタスクをモニター対象として加えることができます。ただし、Qlik Compose 6.6の時点ではSnowflakeを関連付けの対象として加えようとするとエラーとなり、また以降の演習には影響しないため、ここでは設定を省略します。)

image

スクロールダウンし、[Source database connection]のチェックボックスをONにし、[Type]に[MySQL]を選択して[Connection Properties]にデータの発生源のソースシステム(ここではMySQL)の接続情報を定義します。この接続定義は実データを取得するためのものではなく、主キーや外部参照などのデータモデルに関わるテーブルの属性を取得するための接続定義となります。これらの属性情報はソースシステムのMySQLからSnowflakeへ複製されないため、ソースシステムのMySQLへ直接接続して取得します。後続のデータモデリングの手順で、この接続定義を介して属性情報取得して活用します。[OK]をクリックします。

image

複数のデータソースを定義する場合には同様の手順でデータソースを追加します。ここでは上記のデータソースのみを取り扱います。[Close]をクリックしてプロジェクトのトップ画面に戻ります。

DWHモデルの生成と配置

データベースからのモデルの取り込み

Qlik Composeでは、DWHモデルを作成するための3つの方法が提供されています。 ここではDiscoverの方法を利用して、先ほど定義したMySQLのデータソースからテーブルの論理モデルを取得します。

  1. Discover – ソースデータベースから論理モデルを取得します。
  2. Import from ErWin – ErWin(他社製品)で定義された論理モデルをインポートします。
  3. Manual –エンティティと属性を手動で作成します。

MODELセクションのメニューから[Discover]をクリックします。

image

接続先の一覧が表示されます。「Sales OLTP」はさきほど登録したデータソースのMySQL、「Sale OLTP_Landing」はSnowflakeのLanding領域(Qlik ReplicateによりSnowflake側にレプリケートされたデータ領域)になります。ここでは、前者の「Sales OLTP」を選択します。

image

[Search]をクリックしてテーブル一覧を表示し、employees と customer_alt_contactを除く全てのテーブルを選択して[>]をクリックして右側の選択済み領域に移動し、[OK]をクリックします。

image

モデル取り込みが正常に完了したことを確認し、[Close]をクリックします。

image

モデルの表示

MODELセクションの[Display]をクリックします。

image

ここでは、論理モデルをグラフィカルな表現で見ることができます。左上の[Keys]と[Attributes]のチェックボックスをONにします。エンティティをクリックすると、右側のウインドウにエンティティの属性が表示されます。[Close]をクリックします。

image

モデルの変更

MODELセクションの[Manage]をクリックします。

image

データの論理モデルが表示されます。エンティティを選択すると右側に以下の情報を含む属性の情報が表示されます。

  • Name:属性の名前
  • Attribute domain:属性ドメインは、DWH内でのデータの整合性・標準化を確保するために、属性のデータ型を定義したものです。
  • Data Type: 属性のデータ型(設定された属性ドメインの定義に基づく)
  • HistoryType 1(値が変更された場合は上書きする)、もしくはType 2(値の変更履歴を保持する)のいずれかを選択します。
  • Satellite/Hub :物理テーブルでHubまたどのSatelliteテーブルに属するかを選択します。 Type 1の場合には属性は常にHUBに格納され、Type 2の属性はSatelliteで保持されます。 Satelliteは1から始まる連番を指定してグループ化することができ、例えば顧客の住所関連の属性は一度に纏めて変更される可能性が高いため、一つのSatelliteテーブルにに纏めて管理するといったことができます。

次に、[Manage Attribute Domain]をクリックします。

image

Attribute domain(属性ドメイン)の一覧が表示されます。ここで、属性ドメインに定義されたデータ型の確認や変更を行えます。[Close]をクリックします。

image

「customers」エンティティを選択し、「CompanyName」、「ContactName」、「ContactTitle」をType 1に変更します。(これにより、これらの属性の変更履歴は残さず、値が変更された場合は上書きする設定となります。)

image

「order_details」エンティティをクリックします。この後の手順で数式を定義して計算した結果を格納するため、「LineItemAmount」と「ExpectedUnitsInStock」の属性を追加します。[New Attribute]をクリックします。

image

新たな属性ドメインを作成するために[+]をクリックします。

image

以下の通りに入力して[OK]をクリックします。

image

以下の通りに入力して[OK]をクリックします。

image

再度[New Attribute]をクリックします。

image

[Attribute Domain]で「UnitI」と入力して検索し、「UnitsInStock」を選択します。

image

以下の通りに入力して[OK]をクリックします。

image

物理モデルの確認

[Physical  Model]のタブを開き、[TDWH_customer_HUB]を選択します。これは、「customers」の論理エンティティに対応して作成された物理モデルのHUBテーブルで、先ほどType1に設定した「CompanyName」、「ContactName」、「ContactTitle」が含まれていることが確認できます。また、 「ID」列は自動的に追加されたサロゲートキー(代理キー)と呼ばれるもので、「RUNNO_INSERT 」および「 RUNNO_UPDATE」 は どのETL実行によりデータを挿入・更新されたかを記録するための列となります。

image

[TDWH_customer_S01]を選択します。これはType2に指定された変更の履歴を保存するためのSatelliteテーブルで、01はSatelliteの番号に対応しています。それぞれのレコードがどの期間に対して有効なものかを判別するために「FD(From Date) 」および「 TD(To Date) 」の列を含んでいます。確認が完了したら[Close]をクリックします。

image

物理モデルのNameingの変更

物理のモデルTDWHなどのテーブルのプレフィックスや、FD、TDなどの列名はQlik Composeプロジェクトの設定で管理されています。プロジェクトのメニューを開いて、[Settings]を選択します。

image

以下の様に変更を行って、[OK]をクリックします。

image

MODELセクションの[Manage]をクリックして[Physical Model]のタブを開いて先ほどの画面に戻ると、以下の様に設定が反映されていることが確認できます。確認が完了したら[Close]をクリックします。

image

時刻と時間のエンティティ(カレンダーテーブル)の追加

日付と時刻に基づいた分析を行うためのエンティティ(ディメンション)を追加します。これは、この後の手順でデータマートの作成時に、日付項目に時刻と時間を含んだエンティティ(カレンダーテーブル)を追加することができるようにするためです。MODELセクションのメニューから[Add Data and Time Entities]をクリックします。

image

[OK]をクリックすると日付と時刻のエンティティが作成されます。

image

MODELセクションの[Manage]をクリックすると、論理モデルに「Date」のテーブルが追加されていることを確認できます。確認が完了したら[Close]をクリックします。

image

モデルの検証

モデルに問題が無いか検証を行います。MODELの[Validate]をクリックします。

image

問題が無ければ以下の様なメッセージが表示されます。[Close]をクリックします。

image

ETLマッピングの作成と実行

DWHモデルの設計が完了しました。 次は物理テーブルの設計に基づいてDWHにテーブルを作成し、ETLセットを自動生成してそこにデータをロードします。

DWHのテーブル生成

DATA WAREHOUSEセクションの[Create]をクリックします。

image

テーブルの作成が問題なく完了されたことを確認します。[Close]をクリックします。

image

15個のDWHテーブルが作成されたことが確認できます。15の数字をクリックします。

image

作成されたテーブルの一覧を確認することができます。[Close]をクリックします。

image

プロジェクトのメニューを開いて、[Show DDL Scripts]を選択します。

image

生成されたDDLスクリプトの内容を確認することができます。(オプションでDDLスクリプトのみを生成し、実行しないことも可能です。)確認が完了したら[Close]をクリックします。

image

Snowflake上で作成されたテーブルやビューを確認すると、管理用のテーブルなども含めて以下の様なプロフィックスで始まるテーブルが作成されていることが確認できます。

  • TLOG:ログ情報を含むテーブル
  • TPIL:ETL処理のスクリプトや実行情報を含むテーブル
  • TSTG: ステージング用のテーブル
  • TDWH(当演習ではDWへ変更): DWH用の実データを格納するためのテーブル
  • VDWH: TDWHテーブルへのView

image

ETLマッピングの編集

DATA WAREHOUSEセクションを確認すると、2つのETLセットが作成されているのを確認できます。1つ目の「Sales OLTP」はDWHに初期データロードを行うために生成された一連のETLコマンドです。2つ目の「Sales OLTP_CDC」は、Replicateによって配信された変更データのみを読み取り、それらの差分データをDWHに取り込みます。ここでは「Sales OLTP」をクリックします。

image

Qlik Composeにより自動生成された一連のマッピングが表示されます。マッピングはソースのデータとDWHのテーブルを紐づける定義で、変換処理やデータ品質の検証ルールなどを追加することもできます。先ほどorder_detailsのエンティティに新しい属性を追加したため、ここではそれらのマッピングの編集をおこないます。「Map_order_details_Sales OLTP」をクリックします。

image

計算式の追加

以下の様なマッピング定義が表示されます。追加した「LineItemAmount」と「ExpectedUnitsInStock」の属性にはマッピングの定義がなされていないことが確認できます。「LineItemAmount」にカーソルを合わせて[fx](Create mapping)のアイコンをクリックします。

image

[Build Expression]に「${UnitPrice} * ${Quantity} * (1-${Discount})」と入力します。[Parse Expression]をクリックし[Value to Test]にテスト値を入力して[Test Expression]をクリックするか、[Preview Results]をクリックしてソーステーブルからの結果を確認して計算式を検証し、問題がなければ[OK]をクリックします。

image

Lookupの追加

次に「ExpectedUnitInStock」にカーソルを合わせて[Create Lookup]のアイコンをクリックします。

image

[Table]のドロップダウンから「Products」を選択し、[OK]をクリックします。

image

[Condition]の[Create Expression]をクリックします。

image

[Lookup Table]の一覧で「ProductID」にカーソルを合わせ、右矢印をクリックして条件に追加します。そのあと、「=」ボタンをクリックして追加し、[Landing Table]の一覧で「ProductID」にカーソルを合わせ、右矢印をクリックして条件に追加します。(「${Lookup.ProductID}=${Landing.ProductID}」という式が追加されます。)[Parse Expression]をクリックし、問題が無ければ[OK]をクリックします。

image

[Result Column]の[Create Expression]をクリックします。

image

[Lookup Table]の一覧で「UnitInStock」にカーソルを合わせ、右矢印をクリックして条件に追加します。[Parse Expression]をクリックし、問題が無ければ[OK]をクリックします。

image

これで、以下の様に「product」テーブルから「ProductID」が合致するレコードの「UnitsInStock」を取得して「order_details」テーブルの「ExpectedUnitsInStock」の列に格納する処理の定義が完了しました。[Preview Results]をクリックします。

image

プレビューでLookupの値が想定通りに格納されていることを確認し、[OK]をクリックします。そのあと、Lookupの設定画面でも[OK]をクリックしてLookupの設定を完了します。

image

データプロファイリング

[Data Profiler]をクリックします。

image

Data Profilerはソースデータに関する簡単な統計情報を表示します。ここから、例えば「Discount」フィールドは最小0.00~最大0.25の値を取るといったこが確認できます。確認が完了したら[Close]をクリックします。

image

データ品質検証ルール

[Data Quality]をクリックします。

image

Data Qualityでは2つのデータ品質検証の方法を提供されています。

  • Cleansing Rules:条件に従って不完全・不整合なデータを変更・置き換え・削除するために利用
  • Validation Rules:条件に従って問題となるレコードを検知・拒否してErrorMartに格納

ここでは以下の形で[New]をクリックして「Discount Validation」という名前のValidation Rulesを作成します。[If Condition is false]列で[Accept and report]を選択していますが、これによりデータをDWHにロードするのは許可されますが、エラー対象のレコード情報はErrorMartのテーブルに格納されます。入力が完了したら[OK]をクリックします。

image

以上でマッピングの作業は完了ですので、[OK]をクリックしてマッピングの画面を閉じます。「Sales OLTP_CDC」 ETLセットをクリックして選択し、「Map_order_details_Sales OLTP」をクリックして内容を確認します。これまで「Sales OLTP」(ロード)に対して行った変更が「Sales OLTP_CDC」(変更データのみをキャプチャーしてロード)のETLセットにも反映されていることが確認できます。確認後元の[Manage ETL Sets」の画面に戻ります。

image

ETLセットの生成と実行

[Sales OLTP] ETLセットを選択し、Mappingで全てのチェックボックスをONにして[Generate ]をクリックします。

image

ETLセットの作成が問題なく完了されたことを確認します。[Close]をクリックします。

image

[ETL Commands]をクリックします。

image

作成されたETLコマンドの内容を確認することができます。確認が完了したら[Close]をクリックします。

image

[Run]をクリックします。

image

ETLが実行されると自動的に[Monitor]画面が表示されます。[ETL Run No]はテーブルの「RUNNO_INSERT」、「RUNNO_UPDATE」に格納されるETLの実行番号です。ステータスが100% completedになると処理は完了です。Total Inserts、Total Updates、Total Reported Rowsで処理対象の件数を確認することができます。Total Reported RowsはValidation Ruleに違反したレコードを報告しており、値をクリックすると詳細を確認することができます。また、上部の[View Run Details]、[View Missing References]、[View Log]などでタスクの詳細や過去の実行結果詳細などを参照することができます。

image

以上でDWHの設計と、DWHへの処理データロードを完了しました。次に、[Sales OLTP_CDC]ETLセットの生成も行っておきます。[Designer]タブを開いて[Sales OLTP_CDC] ETLセットを選択し、Mappingで全てのチェックボックスをONにして[Generate ]をクリックします。この時点では初期データロードが完了してデータソースのデータには変更が発生していませんので、このETLセットの[Run](実行)は不要です。ETLセットの生成が完了したら[Close]をクリックしてプロジェクトのトップ画面に戻ります。

image

この時点で、Snowflakeの「EDW」データベースの「EDW」スキーマに含まれる「DW_」のプレフィックスで始まる任意のテーブルを確認すると、データが格納されていることが確認できます。以上で、DWHの設計とデータのロードの作業が完了しました。

image

データマートの設計とロード

スタースキーマの作成

DWHの設計とデータロードが完了しましたので、データ分析の対象領域やビジネス要件に基づいて、スタースキーマのデータマートを作成します。

DATA MARTセクションの[New]をクリックします。

image

[Name]に「Sales Data Mart」と入力し、[OK]をクリックします。

image

データマートのタイプ(Fact Type)として以下の3つのタイプを選択することが可能です。

  • Transactional:ファクトテーブルにはトランザクションデータが含まれており、ディメンションにはType1(古い属性を新しい属性で上書き)、Type2(過去の履歴を残し、過去の時点の情報が参照可)のいずれも含めることが可能なデータマート
  • Aggregated:ファクトテーブルにトランザクションの粒度のデータを含めるのではなく、集計したデータを含めるデータマート
  • State Oriented:ファクトテーブルにType2(過去の履歴を残し、過去の時点の情報が参照可)のデータを含めることが可能で、状態やイベントの遷移を表現することが可能なデータマート

ここでは[Name]に「Sales Fact」と入力して、[Fact Type]として[Transactionalを選択して[Next]をクリックします。

image

まず、ファクトテーブルとして「order_details」を選択します。ここでは単一または複数のテーブルを選択することができ、Qlik Composeは必要に応じてテーブルを自動的に結合して、フラットなファクトテーブルを作成します。[Next]をクリックします。

image

ディメンションの設定を行います。「orders 」 のチェックを外し、「customers」と「ShipVia_Shippers」のチェックボックスをONにします。

image

[Date]タブを開き、「OrderDate」と「ShippedDate」の選択をONにします。 これによりDate属性がこれら両方に自動的に追加され、「OrderDate」と「ShippedDate」の日付を利用した分析が可能になります。

image

以下の様なスタースキーマが表示されます。[Next]をクリックします。

image

Transaction Dateタブでは、Type 2(過去の履歴を残し、過去の時点の情報が参照可)属性の判別に使用する日付を選択します。ここでは、「OrderDate」チェックボックスをONにして[Finish]をクリックします。

image

スタースキーマの編集

スタースキーマを編集して計算項目を追加したり、ディメンションのType1/Type2の属性を変更するなどの設定変更を行うことができます。「Sales Fact」を右クリックして、[Edit]を選択します。

image

[New Attribute]をクリックします。

image

[Name]に「DaysToShip」、[Type]に[Integer]を指定して[OK]をクリックします。

image

「DayToShip」にカーソルを合わせて[fx](Create mapping)のアイコンをクリックします。

image

[Build Expression]に「DATEDIFF(dd, ${orders.OrderDate},${orders.ShippedDate} )」と入力します。[Parse Expression]をクリックし[Value to Test]にテスト値を入力のうえ[Test Expression]をクリックして計算式を検証し、問題がなければ[OK]をクリックし、さらに[OK]をクリックして[Manage Data Mart]の画面に戻ります。

image

「products」を右クリックし、[Edit]を選択します。

image

[General]タブをクリックして[History Type]を[Type 1]に変更します。Qlik ComposeはHistory Typeの設定に基づいて、ディメンションを「Type 1」または「Type 2」として扱います。(注:属性を[Type 1]としてDWHに格納している場合、スタースキーマで[Type 2]で管理することはできません。)[OK]をクリックします。

image

スタースキーマの生成

スタースキーマの設定を確認します。[Settings]をクリックします。

image

[Load Type]が[Incremental loading]となっていることを確認します。これにより、実行の毎に前回からの増分データのみがロードされる形となります。(初回実行時は全件が対象となります。)[OK]をクリックします。

image

スタースキーマの設計に問題が無いか検証します。[Validate ]をクリックします。問題が無ければ以下の様なメッセージが表示されます。[Close]をクリックします。

image

[Create Tables]をクリックします。

image

問題が無ければ以下の様なメッセージが表示されます。[Close]をクリックします。

image

[Generate]をクリックしてデータマートにデータを格納するためのETLを生成します。問題が無ければ以下の様なメッセージが表示されます。[Close]をクリックします。

image

[ETL Commands]で生成されたETLを参照することができます。[Run]をクリックして、ETLのロード処理を実行し、データマートにデータをロードします。

image

ETLが実行されると自動的に[Monitor]画面が表示されます。DWHへのデータロードの時と同様に処理の結果を確認することができ、処理の完了を確認します。

image

Pivotテーブルでのデータの確認

[Pivot]をクリックします。

image

テーブルのドロップダウンで「TDMA_1Fct_order_details 」を選択し、[Clear]ボタンで選択を解除して「LineItemAmount」と「DaysToShip」のチェックボックスをONにします。

image

テーブルのドロップダウンで「TDMA_1Dim_products」を選択し、[Clear]ボタンで選択を解除して「CategoryName」のチェックボックスをONにします。

image

テーブルのドロップダウンで「TDMA_1Dim_Date」を選択し、[Clear]ボタンで選択を解除して「MONTH_NAME」のチェックボックスをONにします。[OK]をクリックします。

image

[Rows]を[1000]に変更し、計算方法を[Average]に変更して計算対象を「DaysToShip」に変更します。「MONTH_NAME」を行軸に、「CategoryName」を列軸にドラッグ&ドロップします。

image

以下の様なPivotテーブルが表示されます。表示を[Heatmap]に変更します。

image

以下の様にヒートマップの表示に変わります。

image

計算方法を[Sum]の[LineItemAmount]に変更します。以下の様に表示が変更されます。このように、作成されたデータマートの出力結果を検証することができます。[Close]をクリックします。

image

DWH上のデータマートの確認

Snowflake上の「EDW」データベースの「DATAMART」スキーマを確認すると、以下の様なテーブルが作成されていることが確認できます。

image

「TDMA_1Dim_customers」テーブルのデータの中身を確認すると、「customers_VID」と「customers_OID」という2つの列がQlik Composeにより追加されていることが確認できます。「_VID」の列はバージョンを示すIDで、Type2の過去の履歴を残す場合に変更バージョンを認識して一意に識別するためのバージョンIDです。「_OID」は一意なレコードを識別するためのオブジェクトIDとなります。

image

「TDMA_1Dim_products」のデータを確認すると、「_OID」のみ含まれており、「_VID」は含まれていません。これは、データマートの生成時に「Products」のディメンションをType1で作成する指定を行ったからです。

image

「TDMA_1Fct_order_details」のデータを確認すると、Customersディメンションに対しては「customers_VID」と「customers_OID」の両方を含んでいますが、Productsディメンションに対しては「products_OID」しか含んでいません。これは、前者がType2で管理されているのに対し、後者がType1で管理されていることを示しています。

image

ETLワークフローの構築とリアルタイムデータ処理

ETLワークフローのスケジュール

プロジェクトのトップ画面で[Monitor]をクリックします。以下のタスクが作成されていることが確認できます。

  1. Sales OLTP: ソースデータをDWHに初期ロードするために作成したETLセット(実行済み)
  2. Sales Data Mart: DWHのデータを作成したデータマートに格納するために作成したETLセット(実行済み)
  3. Default Workflow: 上記の1と2を実行するために自動的に生成された初期データロード用のワークフロー
  4. Sales OLTP_CDC:  ソースデータの変更データをDWHにロードするために作成したETLセット
  5. Default CDC Workflow: 上記の4と2を実行するために自動的に生成された変更データロード用のワークフロー

これまでの作業で1と2については既に実行し、DWHとデータマートに初期データをロードしました。また、1と2を続けて実行する3のDefault Workflowが自動的に作成されており、初期ロードを纏めて実行する場合はこのワークフローを実行します。ここでは、初期データのロードは完了していますので、変更データをDWHとデータマートにロードするワークフローをスケジュール実行する設定を行います。そのためのワークフローとして5の「Default CDC Workflow」が自動的に生成されており、ここではそのワークフローの実行をスケジュールします。「Default CDC Workflow」を選択します。画面下半分に「Sales OLTP_CDC」と「Sales Data Mart」のタスクが順に実行される定義済みのワークフローが表示されます。[Schedule]をクリックします。(尚、今回は自動生成されたワークフローを実行しますが、[New Workflow]をクリックして新たに作成することも可能です。)

image

以下の様に1分毎に繰り返し実行する設定を行い、[OK]をクリックします。

image

ソースデータへの変更の適用

MySQLのサーバー上でMySQL Workbenchを開きます。

image

以下のSQL文を実行します。以下のSQL文は、住所がMadrid, Spainにある、既に購入の実績があるCustomerIDがROMEYという顧客の住所をTokyo, Japanに更新しています。その後、この顧客による新たな購入のレコードを挿入しています。

/*customerテーブルのCustomerIDがROMEYの顧客の住所をMadrid, SpainからTokyo, Japanに変更*/
UPDATE `sales`.`customers` SET `Address` = 'Minatoku 1-6-1', `City` = 'Tokyo', `PostalCode` = '1060032', `Country` = 'Japan', `Phone` = '(81) 3 6277 7079', `Fax` = '' WHERE (`CustomerID` = 'ROMEY');

/*CustomerIDがROMEYの顧客の購入実績をorderテーブルとorder_detailテーブルに追加*/
INSERT INTO `sales`.`orders` (`OrderID`, `CustomerID`, `EmployeeID`, `OrderDate`, `RequiredDate`, `ShippedDate`, `ShipVia`, `Freight`, `ShipName`, `ShipAddress`, `ShipCity`, `ShipRegion`, `ShipPostalCode`, `ShipCountry`) VALUES ('11081', 'ROMEY', '2', Now() + Interval 5 day, Now()  + Interval 5 day, Now()  + Interval 10 day, '1', '12.69', 'ROMEY', 'ROMEY', 'ROMEY', 'ROMEY', 'ROMEY', 'ROMEY');

INSERT INTO `sales`.`order_details` (`odID`, `OrderID`, `ProductID`, `UnitPrice`, `Quantity`, `Discount`) VALUES ('2159', '11081', '29', '123.79', '30', '0.00');

Qlik Replicateのタスクの[Monitor]>[Change Processing]の画面を確認します。以下の様にソースデータの変更が適用され、Qlik Replicateによって変更がターゲットに適用されていることが確認できます。

image

Qlik Composeのプロジェクトのトップ画面で[Monitor]タブを開き、「Sales OLTP_CDC」と「Sales Data Mart」のタスクをそれぞれ選択して[History]タブを開くと実行履歴で変更がDWHとデータマートに反映されていっている状況が確認することができます。

image

image

Pivotテーブルでの確認

[Designer]のタブを開き、DATA MARTセクションの「Sales Data Mart」をクリックします。

image

[Pivot]をクリックします。

image

テーブルのドロップダウンで「TDMA_1Fct_order_details 」を選択し、[Clear]ボタンで選択を解除して「LineItemAmount」のチェックボックスをONにします。

image

テーブルのドロップダウンで「TDMA_1Dim_customers」を選択し、[Clear]ボタンで選択を解除して「CustomerID」と「Country」のチェックボックスをONにします。

image

テーブルのドロップダウンで「TDMA_1Dim_Date」を選択し、[Clear]ボタンで選択を解除して「YEAR」のチェックボックスをONにします。[OK]をクリックします。

image

[Rows]を[All Rows]に変更し、計算方法を[Sum]に変更して計算対象を「LineItemAmount」に変更します。「CustomerID」と「Country」を行軸に、「YEAR」を列軸にドラッグ&ドロップします。

image

「CustomerID」をクリックし、[Select None]をクリックして全ての選択を解除し「ROMEY」のチェックボックスのみをONにして表示を絞り込みます。[OK]をクリックします。

image

以下の様に、顧客ROMEYの過去の購入は、当時所在したSpainの住所で集計されていますが、Japanに住所の変更を行い、その後の購入はJapanの住所に集計されていることが確認できます。customerディメンションはType2で保持されるため、このように住所が変更されても過去の履歴が保持されており、購入当時の住所で集計が行われます。尚、これをType1で行った場合には新しい値で上書きされてしまうため、全ての購入はJapanに紐づいて集計されます。[Close]ボタンをクリックし、さらに[Close]をクリックしてプロジェクトのトップ画面に戻ります。image

プロジェクトのドキュメントの生成

プロジェクトのメニューを開いて、[Generate Project Documentation]を選択します。

image

[OK]をクリックします。

image

Zipファイルがダウンロードされますので、そのファイルを展開して中に含まれるindex.htmlファイルをダブルクリックしてドキュメントを開きます。以下の様のHTMLの形式でプロジェクトの情報をまとめて参照することができます。

image

Qlik Compose for Data Warehousesのチュートリアルは以上になります。