【Sisense Data Modeling】インポートクエリを使ってデータベースSQLでインポートする

シャローム！Sato-Gです。
今日から大阪出張。
大阪に行くなら新幹線...というのが普通。
新幹線パックっていうのもある。新幹線＋宿が一緒になっているやつ。
でも今回は楽天のJALパックで探してみたら、2泊3日+JAL(羽田-伊丹往復)で3万円しないというのを見つけた。
しかも結構まともなホテルなんだよね。
今は閑散期なのかもしれないが、賢い出張で経費削減だな。
というわけで、飛行機の中から投稿！
と思ったら、あっという間に着陸態勢となり、WiFiが切れちゃって、今バスの中で書いてる。

さて、今回は前回作成したデータモデルで少し触れた余計な非表示テーブルを作成しない方法で同じデータモデルを作成してみる。

1.インポートクエリとは

前回のコラムでは、インポートしてできたテーブルからカスタムテーブルを作成してみた。
この方法はテーブルを非表示にできるものの、一旦インポートしたテーブルはそのまま残っている。
一方、インポートクエリというのは、データベースからインポートする時点でJOIN, UNIONなどをSQLで行い、求められる形でデータをインポートしてテーブルを生成してしまうものだ。
この２つの方法では基本的にSQLが異なる点に注意が必要だ。

・インポートクエリ
インポート時点でのSQLなので、コネクターで接続するデータベースのSQLを使用する。
関数はデータベース側で使用可能な関数に限られる。

・カスタムテーブル
取り込んでできたテーブルから新たにテーブルを作成するため、SisenseのSQLを使用する。
よって、ここではSisense独自の関数が使える。

2.データベースからSQLでインポートする

2.1 データの準備

まずは、注文ヘッダ、注文明細、配送の３つのテーブルを除く全てのテーブルを予め取り込むように設定しておこう。
そして、以下のリレーションを予め設定しておく。
()内はリレーションのキー項目
・会員-都道府県_ポイントデータ(会員.会員都道府県-都道府県_ポイントデータ.都道府県)
・都道府県_ポイントデータ-都道府県_エリアデータ(都道府県_ポイントデータ.都道府県ID-都道府県_エリアデータ.都道府県ID)
・商品-商品分類(商品.カテゴリコード-商品分類.カテゴリコード)
こんなデータモデルになるはす。

2.2 インポートクエリの編集

新規にインポートクエリ作成をする前に、既存テーブルの接続を利用してSQLを編集してみる。
「会員」テーブルを選択し、「接続設定」→「テーブルの変更」を選択すると、下記のように接続の内容が表示される。

ここで、「編集」をクリックし、SQLも編集を行う。
FROM句で取り込むテーブルの指定は本来は、"データベース名"."テーブル名"だが、今回はSQLiteを使用しているため、テーブル名だけになる。
（そもそもSQLiteはデータベース自体がファイルになっているため、データベース名は不要）

ここでは、性別が1の時は女性、2の時は男性、それ以外は性別不明とし、カラム名を「性別名」とする。
SQLは以下のとおり

SELECT *,
    CASE 性別
    WHEN 1 THEN '女性'
    WHEN 2 THEN '男性'
    ELSE '性別不明'
    END AS 性別名
FROM `会員`

CASE式は条件分岐する場合に使用する構文で以下のように記述する。

SELECT
  CASE
    WHEN 条件式1 THEN 式1
    WHEN 条件式2 THEN 式2
    ELSE 式3
  END
FROM テーブル名;

条件式を使わず、値で指定する場合は下記のとおり

SELECT
  CASE　カラム名
    WHEN 値1 THEN 式1
    WHEN 値2 THEN 式2
    ELSE 式3
  END
FROM テーブル名;

ここで「解析」を押すとSQLのチェックを行ってくれる。
「データのプレビュー」を押すと、SQL実行結果をプレビューしてくれる。
「解析」で問題なくても「データのプレビュー」ができないケースもあるので、プレビューができない場合は何か問題があると思ったほうがいい。
実際、式が正しいのに、性別名は正しくプレビューされていない。
それは、性別名はTEXTなのに、数値と解釈されていまっているからだ。

ここで、一旦編集画面を閉じ、以下のようにデータタイプを「TEXT」に変更しておく。
この段階ではまだビルドされていないので正しく表示はされないが、ビルドを行うと正しく表示されるようになる。

2.3 インポートクエリの作成

[データ]ボタンをクリックし、JDBCでデータベース（ここではSQLite）に接続する設定を行う。
コネクタの選択 > 接続 > データを選択
と進んだら、[インポートクエリの追加]をクリックするとテーブル名とSQLを入力できるようになる。
インポートクエリ名がテーブル名になるので「明細」と設定し、SQLは以下のとおり入力する。

SELECT [配送年月日] AS 年月日KEY,
       '配送' AS 区分,
       [会員番号],
       [注文番号],
       [明細番号],
       [商品コード],
       0 AS 注文数量,
       0 AS 注文金額,
       [数量] AS 配送数量,
       [明細金額] AS 配送金額
FROM (SELECT *
      FROM "配送" d
        LEFT JOIN "注文ヘッダ" o ON d.[注文番号] = o.[注文番号]
        LEFT JOIN "注文明細" od
               ON d.[注文番号] = od.[注文番号]
              AND d.[明細番号] = od.[明細番号]) a
UNION ALL
SELECT [注文年月日] AS 年月日KEY,
       '注文' AS 区分,
       [会員番号],
       [注文番号],
       [明細番号],
       [商品コード],
       [数量] AS 注文数量,
       [明細金額] AS 注文金額,
       0 AS 配送数量,
       0 AS 配送金額
FROM (SELECT *
      FROM "注文ヘッダ" o
        LEFT JOIN "注文明細" od ON o.[注文番号] = od.[注文番号]) b

年月日はSisenseのCreateDateは使用できないので、一旦、年月日KEYとしておく（この時点ではyyyymmddのINT型のカラムとなっている）
その他は、前回のSQLに合わせたので、SQLの説明は前回のコラムを参考にしてほしい。

2.4 データ型に関する留意点

Sisenseのテーブルのカラムの属性はインポートするデータベースのカラムに依存している。
よって、データベースのテーブルのカラムのデータ型も引き継いでくれるのだが、性別名では正しく判定してくれなかった。
また、'配送' AS 区分 のように定義されていないカラムのデータ型はどうなるだろうか？

Sisenseでは、SQLでの型指定やデータの値から識別してデータ型を決定してくれないので、予めデータベースで定義されていない場合は、Sisense側で指定する必要がある。
実際、'配送' AS 区分 の結果はDecimalと判定されてしまう。
よってここも、性別名の時ようにデータタイプを「TEXT」に指定し直す。

これは特にDateTime型にしたい場合は厄介で、SQL側でCASTなどで指定してもDate型にはならない。
そのため、ここでは年月日KEYとしてIntのまま取り込んで、カスタム列で年月日を作成してあげることになる。

CreateDate(
ToInt(Substring(ToString([年月日KEY]),1,4)),
ToInt(Substring(ToString([年月日KEY]),5,2)),
ToInt(Substring(ToString([年月日KEY]),7,2))
)

最後に明細と会員、商品のリレーションシップの設定を行い、最終的にはこのようなデータモデルになる。

このデータモデルから以下のとようなウィジェット表示が可能となる。

3. まとめ

データベースから取り込む際にSQLを自作してインポートする「インポートクエリ」について解説してきた。
インポートクエリでサブクエリを含む複雑なクエリを作成した場合、データベースの負荷も大きくなるため配慮が必要だが、ちょっとした加工ならインポートクエリを使用するとビルド時間の短縮、ディスクスペースの節約にもなるのでSQLが使える人にはお勧めしたい機能だ。

ではまた！