データモデリングの成功を解き放つ：3つの必須のコンテキストテーブル

Unlocking the success of data modeling 3 essential context tables

無料で価値のあるデータを取り込む方法

データモデリングは、分析チームにとって困難な課題です。各組織に固有のビジネスエンティティがあり、各テーブルの適切な構造と粒度を見つけることは無限の可能性があります。しかし、心配しないでください！必要なデータの一部はシンプルで無料であり、最小のストレージを占有します。

データが完全にモデル化されると、以下の利点があります：

• クエリの生成が複雑ではなくなり、より読みやすくなります。
• レポートはスケーラブルになり、ハードコードされた値が減ります。
• 適切なデータがどこにあるかを見つけるのにかかる時間が短くなります。

以下は、チームの分析を効率化できる3つの一般的なテーブルです。これらは次元モデルのコンテキストでデータウェアハウスに取り込むことができます。

🗓️日付ディメンション

タイムシリーズレポート用

• AWSとPower BIを使用して、米国のフライトを調査する
• 日本の介護施設はビッグデータを活用して介護士の支援を強化し、業務負担を軽減しています
• ACIDトランザクションとは何ですか？

ある時点でのビジネス指標を表示する必要がある場合、このテーブルはほぼ必須です。たとえば、次のような質問がされるかもしれません：

• 「FY23の売上はどのようになりましたか？」
• 「クライアントの離反を日次で表示できますか？」

経営陣はしばしばタイムシリーズの視点から洞察を求めます。「時間の経過に伴ってxはどのように成長または縮小しているか？」といった質問です。日付ディメンションにより、異なる日付属性に基づいてさまざまなメトリックの柔軟な分析が可能になります。

ほとんどの日付ディメンションテーブルは、データウェアハウス内でDDLステートメントを直接使用して作成できます。日付関数を組み合わせて使用します。

以下の例では、BigQuery SQLを使用してこれを実行しています：

CREATE OR REPLACE TABLE `your_project.your_dataset.date_dimension` ASSELECTfull_date, EXTRACT(MONTH FROM full_date) AS calendar_month_number, EXTRACT(YEAR FROM full_date) AS calendar_year, EXTRACT(QUARTER FROM full_date) AS calendar_quarter, FORMAT_DATE('%B', full_date) AS calendar_month_name, EXTRACT(DAYOFWEEK FROM full_date) AS week_name, FORMAT_DATE('%A', full_date) AS day_name, CASE    WHEN EXTRACT(DAYOFWEEK FROM full_date) BETWEEN 2 AND 6      THEN TRUE    ELSE FALSE  END AS day_is_weekday, CASE    WHEN EXTRACT(DAYOFWEEK FROM full_date) = 1 THEN DATE_SUB(full_date, INTERVAL 2 DAY) -- Sunday    WHEN EXTRACT(DAYOFWEEK FROM full_date) = 2 THEN DATE_SUB(full_date, INTERVAL 3 DAY) -- Monday    ELSE DATE_SUB(full_date, INTERVAL 1 DAY)  END AS last_weekday, EXTRACT(MONTH FROM DATE_ADD(full_date, INTERVAL 6 MONTH)) AS fiscal_month, EXTRACT(YEAR FROM DATE_ADD(full_date, INTERVAL 6 MONTH)) AS fiscal_year, EXTRACT(QUARTER FROM DATE_ADD(full_date, INTERVAL 6 MONTH)) AS fiscal_quarterFROM UNNEST(GENERATE_DATE_ARRAY('2020-01-01', '2050-12-31', INTERVAL 1 DAY)) AS full_date

この内訳：

• まず、指定した範囲の日付の配列を返すGENERATE_DATE_ARRAY関数を使用します。次に、UNNEST関数を使用して配列の各要素を個別の行に分割します。これは通常のデータベーステーブルと同じです。BigQueryの配列は、複数の値を表示するために1行を使用します。
• 次に、アンネストされた配列から生成されたfull_date列を使用して（XXXX-MM-DD形式の日付を表します）、BigQueryのEXTRACT関数を使用してfull_dateのセグメントを個別のフィールド（月、日、年など）に取得できます。
• FORMAT_DATE関数は、EXTRACTと同様の目的を持っていますが、日付値の表示方法に関してよりカスタマイズが可能です。各「%」文字の意味についての詳細は、Googleのドキュメントに記載されている特殊なフォーマット要素を使用できます。
• DATE_SUB関数も使用して、日付から値を減算します。これは関連する会計年度を取得するために使用されます。この例では、会計年度はカレンダー年の7月に始まります。この関数では、数量（1〜無限大）と間隔（日、月、年など）を指定します。

🌎郵便番号ディメンション

地理空間報告用

ヒートマップの可視化や一般的な地理空間分析を作成するように指示された場合、郵便番号ディメンションはチームにとって非常に便利です。これにより、緯度と経度による要素の可視化、郡名、タイムゾーンでの集計、およびベンチマーク用の人口データの追加が可能になります。

郵便番号ディメンションは顧客テーブルに対する素晴らしい補足テーブルです。郵便番号フィールドを結合キーとして使用することで、顧客ベースが存在する場所に意味のあるコンテキストデータを追加し、それらの背後にあるパターンを把握することができます。

OpendatasoftはオープンソースのAPIコネクタを備えたさまざまな無料のデータセットを提供しています。このケースに最適なものの1つは、米国の郵便番号ポイントデータセットです。このリンクでは、「API」タブにカーソルを合わせると、JSONデータを取得するためのURLを設定できます。

Pythonの数行で次のPandas DataFrameを出力できます：

import requestsimport pandas as pdurl = 'https://data.opendatasoft.com/api/records/1.0/search/?dataset=georef-united-states-of-america-zc-point%40public&q=&facet=stusps_code&facet=ste_name&facet=coty_name&facet=cty_code&facet=zip'response = requests.get(url)zips = response.json()pd.json_normalize(zips,record_path='records')

これを分解すると：

• ここでは、requestsライブラリを使用して、opendatasoftウェブサイトのAPIタブで生成された「url」変数に表示されるURLのデータを取得しています。
• Pandasを使用して、json_normalize関数を使用してJSONデータをPandas DataFrameに変換しています。

📈為替レートファクトテーブル

財務分析用

国際クライアントを持つ組織は、財務報告のためにすべての取引を基軸通貨に変換する必要があります。外国為替レートの変動が収益にどのような影響を与えているかを理解するためには、日々の為替レートフィードが非常に有用です。

これは、タイムシリーズレポートと組み合わせることで特に効果的であり、特定の販売時の為替レートを追加するために使用されます。私の経験では、一定期間にわたるクライアントの収益を表示するダッシュボードを作成する際、ビジネスユーザーは常に収益に対して異なる為替レート値を切り替える機能を高く評価していました。FXレートテーブルを使用すると、これらすべてを実現することができます。

Exchangerate.hostは、日々の為替レートフィードに接続することができる別のオープンソースサイトです。以下はデータを取得する方法の例です。詳細はドキュメントを参照してください：

import requestsimport pandas as pdfrom datetime import date# exchangerate APIから最新の日付を取得するurl = 'https://api.exchangerate.host/latest?base=USD'response = requests.get(url)rates = response.json()# JSONリストをPandas Dataframeに変換してプレビューするrates_list = list(rates['rates'].items())df_rates = pd.DataFrame(rates_list, columns=['currency', 'value'])df_rates['cycle_date'] = date.today()df_rates.head(10)

これを分解すると：

• ここでは、requestsライブラリを使用して、「url」変数で指定されたURLからPythonの辞書形式のデータを取得しています。なお、私はURLをドキュメントに従って編集して、基軸通貨をUSDに指定しました。つまり、すべてのレートはUSDの為替レートに関連します。
• 次に、Pythonの辞書をリストに変換し、データを「rates」キーから取得します。

rates_list = list(rates['rates'].items())

• その後、リストをPandas DataFrameに変換し、列ヘッダーにラベルを付けます：

df_rates = pd.DataFrame(rates_list, columns=['currency', 'value'])

• 最後に、ETLサイクル日付を表す「cycle_date」という列が追加されます。これはデータがデータウェアハウスに取り込まれた日付を示しています。

結論

公開されているデータをデータウェアハウスに組み込むことは、最小限の労力で分析チームに即効性のある価値を提供することができます。これらのテーブルは、適切にモデル化されたデータエンティティと共に、Power BIやTableauのようなBIツールにネストされたビジネスロジックを保存する必要がなくなります。代わりに、複数のアナリストが参照し、報告書作成に一貫して適用できるデータの中央集権的なソースを提供します。このデータモデリングの一貫したアプローチにより、チームは報告書のスケーリングを容易に行い、ソースデータへの透明性を確保することができます。このようなコンテキストテーブルを活用できる能力により、組織は分析プロセスを効率化し、報告書の不一致を排除し、より高度なデータ駆動型の意思決定を実現することができます。

モデリングを楽しんでください！

We will continue to update VoAGI; if you have any questions or suggestions, please contact us!