今回はデータウェアハウスの作成方法のお勉強です。
ワークスペースが必要になりますので、前回までの記事をご参照ください。
データウェアハウスの作成
- Synapse Data Warehouse のホーム ページで、任意の名前で新しいウェアハウスを作成します。少し待つと下のような画面になります。
テーブルの作成とデータの挿入
ウェアハウスは、テーブルやその他のオブジェクトを定義できるリレーショナルデータベースです。
- 新しいウェアハウスで、 [T-SQL を使用してテーブルを作成する] タイルを選択し、既定の SQL コードを次の CREATE TABLE ステートメントに置き換えます。
CREATE TABLE dbo.DimProduct
(
ProductKey INTEGER NOT NULL,
ProductAltKey VARCHAR(25) NULL,
ProductName VARCHAR(50) NOT NULL,
Category VARCHAR(50) NULL,
ListPrice DECIMAL(5,2) NULL
);
GO
2. 実行 ボタンを使用して SQL スクリプトを実行すると、データ ウェアハウスの dbo スキーマに DimProduct という名前の新しいテーブルが作成されます。
3. ツールバーの [更新] ボタンを使用して、ビューを更新します。次に、[エクスプローラー] ウィンドウで [スキーマ] > [dbo > テーブル] を展開し、DimProduct テーブルが作成されていることを確認します。
4. [ホーム] メニュー タブで、[新しい SQL クエリ] ボタンを使用して新しいクエリを作成し、次の INSERT ステートメントを入力します。
INSERT INTO dbo.DimProduct
VALUES
(1, 'RING1', 'Bicycle bell', 'Accessories', 5.99),
(2, 'BRITE1', 'Front light', 'Accessories', 15.49),
(3, 'BRITE2', 'Rear light', 'Accessories', 15.49);
GO
5. クエリが完了したら、データ ウェアハウスのページの下部にある [データ] タブを選択します。エクスプローラー ウィンドウで、DimProduct テーブルを選択し、3 つの行がテーブルに追加されていることを確認します。
6. [ホーム] メニュー タブで、[新しい SQL クエリ] ボタンを使用して新しいクエリを作成します。次に、Transact-SQL コードを https://raw.githubusercontent.com/MicrosoftLearning/dp-data/main/create-dw.txt からコピーして、新しいクエリ ウィンドウに貼り付けます。
7. クエリを実行すると、単純なデータ ウェアハウス スキーマが作成され、データが読み込まれます。スクリプトの実行には約 30 秒かかります。
dbo スキーマに次の 4 つのテーブルが含まれていることを確認します。
* DimCustomer
* DimDate
* DimProduct
* FactSalesOrder
データモデルの定義
リレーショナル データ ウェアハウスは、通常、ファクト テーブルとディメンション テーブルで構成されます。ファクト テーブルには、業績 (売上収益など) を分析するために集計できる数値メジャーが含まれ、ディメンション テーブルには、データを集計できるエンティティの属性 (製品、顧客、時間など) が含まれます。
-
データ ウェアハウスのページの下部で、 [モデル] タブを選択します。
モデル ペインで、次のように FactSalesOrder テーブルが中央に来るようにデータ ウェアハウス内のテーブルを再配置します。
-
FactSalesOrder テーブルから ProductKey フィールドをドラッグし、DimProduct テーブルの ProductKey フィールドにドロップします。次に、次の関係の詳細を確認します。
- 表 1: FactSalesOrder
- 列: ProductKey
- 表 2: DimProduct
- 列: ProductKey
- カーディナリティ: 多対 1 (*:1)
- クロスフィルター方向:シングル
- この関係をアクティブにする: 選択済み
- 参照整合性を前提とする: 未選択
このプロセスを繰り返して、テーブル間のリレーショナルを完成させます。
データウェアハウステーブルのクエリ
データウェアハウスはリレーショナルデータベースであるため、SQLを使用してそのテーブルをクエリできます。
- 新しい SQL クエリを作成し、次のコードを実行します。
SSMS(SQL Server Management Studio)と同じ感覚で使えます。
ビューの作成
ビューもリレーショナルデータベースで使用していたものと同じ機能が備わっています。（ストアドプロシージャも同様）
- 例えば先に作成したSELECT文を次のように変更して、ビューを作成します。
- 新しいビューが作成されていることを確認します。
- 新規ビューに対するSQL文が実行できたらOKです。
ビジュアル クエリを作成する
SQL文を実行する代わりに、グラフィックなクエリデザイナでデータ ウェアハウス内のテーブルに対してクエリを実行することもできます。
-
[ホーム] メニューの [新しいビジュアル クエリ] を選択します
続いて[FactSalesOrder]テーブル をキャンバスにドラッグします。テーブルのプレビューが下の [プレビュー] ウィンドウに表示されます。
さらに、[DimProduct]テーブル をキャンバスにドラッグします。これで、クエリに 2 つのテーブルができました。
ここからSQL文を書く代わりに、キャンバス上の FactSalesOrder テーブルの (+) ボタンを使用して、クエリをマージします。
-
[クエリのマージ] ウィンドウで、マージの適切なテーブルとして [DimProduct] を選択します。両方のクエリで [ProductKey] を選択し、既定の [左外部結合の種類] のままにして、[OK] をクリックします。
-
プレビューで、新しい DimProduct 列が FactSalesOrder テーブルに追加されていることを確認します。列名の右側にある矢印をクリックして、列を展開します。[ProductName] を選択し、[OK] をクリックします。
-
列名の右側にある矢印をクリックして、列を展開します。[ProductName] を選択し、[OK] をクリックします。
-
ProductName 列を使用してクエリ内のデータをフィルター処理できるようになりました。
さらに、[ProductName] 列をフィルター処理して、ケーブル ロック データのみを確認します。
データの可視化
続いて作成したクエリからPower BIでレポートを作成していきます。
-
[エクスプローラー] ウィンドウで、[モデル] ビューを選択します。ファクト テーブルとディメンション テーブルで、レポートの作成に必要のない次の列を非表示にしていきます。(赤線の項目)
-
レポートを作成し、このデータセットを他のユーザーが利用できるようにする準備が整いました。[ホーム] メニューで、[新しいレポート] を選択します。これにより、新しいウィンドウが開き、Power BI レポートを作成できます。
[データ] ペインで、[FactSalesOrder] を展開します。非表示にした列は表示されていないことが確認できます。
ここから先はPowerBIのレポート作成と同じ手順になるので割愛します。
まとめ
データモデルや正規化について知識がある方は、SQL Server Management StudioでCreate tableしたり、Select文発行したりするのと何も変わりないのですんなり入れると思います。
さらにSQL文を知らない方向けにビジュアルクエリも用意されているので、エンジニア以外の一般ユーザの方も慣れれば簡単に操作可能と思われます。
レポートを作成する部分に関しては、Power BIの知識が必要です。こちらはPower BI Desktopの使い方を覚えれば容易に作成できるようになります。