はじめに

Qlik Sense 「ダイレクト・ディスカバリ」を使ったビッグデータ分析(前編)では、ダイレクト・ディスカバリの概要および基本的な使い方についてご説明させて頂きました。

この後編では引き続き、ダイレクト・ディスカバリの複数テーブルの結合方法やより高度な設定方法などについてご説明したいと思います。

ダイレクト・ディスカバリのより高度な設定

NATIVE関数によるDB固有のSQL関数実行

ダイレクト・ディスカバリでは「NATIVE」関数を利用することで、DB固有のSQL関数をステートメントに指定することが可能です。

以下の例では、NATIVE関数にシングルクオートで囲んでmonth()、year()のSQL文関数入力し、「OrderDate」から「OrderMonth」と「OrderYear」を取得する処理を実装しています。


SET DirectTableBoxListThreshold = 1000000;
DIRECT QUERY
 DIMENSION
    SalesOrderID,
    CustomerID,
    SalesPersonID,
    TerritoryID,
    OrderDate,
    NATIVE('month([OrderDate])') AS OrderMonth,
    NATIVE('year([OrderDate])') AS OrderYear
 MEASURE
    SubTotal,
    TaxAmt,
    TotalDue
 DETAIL
    rowguid,
    ModifiedDate
FROM AdventureWorks2012.Sales.SalesOrderHeader; 

以上の様な形でNATIVEを実行すると、以下の様にmonth()のSQL関数が含まれて形でソースシステムにクエリが送信されます。

image

追加した項目は他の項目と同様、以下の形で利用することができます。

image

尚、NATIVE関数で利用することが出来るSQL文はDB固有となり、利用するデータソースのDBに依存しますので事前にサポート状況を確認することが必要です。

WHERE句による抽出条件設定

WHERE句の指定により、分析対象のデータを絞り込む設定も可能となっています。

例えば、以下の様に「WHERE OnlineOrderFlag = 0」の条件をダイレクト・ディスカバリ文に含めることが出来ます。


SET DirectTableBoxListThreshold = 1000000;
DIRECT QUERY
DIMENSION
    SalesOrderID,
    CustomerID,
    SalesPersonID,
    TerritoryID,
    OrderDate,
    NATIVE('month([OrderDate])') AS OrderMonth,
    NATIVE('year([OrderDate])') AS OrderYear
MEASURE
    SubTotal,
    TaxAmt,
    TotalDue
DETAIL
    rowguid,
    ModifiedDate
FROM AdventureWorks2012.Sales.SalesOrderHeader
WHERE OnlineOrderFlag = 0; 

そうすると、以下の様にクエリに指定したWHERE句が含まれたクエリが送信され、条件に合致したデータのみがQlik Senseに返されます。

image

複数テーブルの結合

これまでの例では1つのテーブルのみを利用していましたが、ダイレクト・ディスカバリでは複数のテーブルを結合して利用することも可能です。ここではJOIN句を使った方法と、Qlik Senseのアソシエーション機能を使った方法についてご紹介します。

JOIN句を使った複数テーブルの結合

ダイレクト・ディスカバリでは標準的なSQLのJOIN句がサポートされており、JOIN句を利用して複数テーブルを結合することが可能です。これまでの例では「SalesOrderHeader」の1テーブルのみを分析対象としていましたが、以下ではJOIN句により「SalesOrderHeader」と「SalesOrderDetail」の2テーブルを結合している例となります。


SET DirectTableBoxListThreshold = 1000000;
DIRECT QUERY
DIMENSION
    AdventureWorks2012.Sales.SalesOrderHeader.SalesOrderID AS SalesOrderID,
    CustomerID,
    SalesPersonID,
    TerritoryID,
    OrderDate,
    NATIVE('month([OrderDate])') AS OrderMonth,
    NATIVE('year([OrderDate])') AS OrderYear,
    ProductID
MEASURE
    SubTotal,
    TaxAmt,
    TotalDue,
    OrderQty 
DETAIL
    rowguid,
    ModifiedDate
FROM AdventureWorks2012.Sales.SalesOrderHeader
JOIN  AdventureWorks2012.Sales.SalesOrderDetail 
ON (AdventureWorks2012.Sales.SalesOrderDetail.SalesOrderID 
 = AdventureWorks2012.Sales.SalesOrderHeader.SalesOrderID); 

JOIN句でテーブルの結合を行うことで、以下の様に物理的には別々のテーブルに存在する「SalesOrderHeader」に含まれる「TotalDue」列や、「SalesOrderDetail」に含まれる「OrderQty」列の集計値を一つのテーブルやチャートに表示させる形で利用することなどが可能となります。

image

JOIN句を使ってテーブルを結合した場合、Qlik Senseのデータモデル上では、結合されたテーブルは1つの論理テーブルとして扱われます。

image

そして、このテーブルの列に対して読込みのリクエストが発生した場合、以下の様にJOIN句が含まれて形でソースシステムにクエリが送信され、ソースシステム側でテーブルの結合や集計が実行されます。

image

ここでは「SalesOrderHeader」と「SalesOrderDetail」の2つのテーブルをJOINしましたが、同様の形で「Customer」テーブル、「Product」テーブルなどのディメンションとなるテーブルをJOINで結合することが可能です。

また、今回はINNER JOINを利用しましたが、データソースのDBが対応している範囲内でLEFT、RIGHT OUTER JOINなどを利用することも可能です。

尚、上記の例ではJOIN句を利用しましたが、JOIN句を使わずに以下の様に「FROM..WHERE..」を使ったSQL文の書き方でも同様の結果を得ることができます。

image

マルチ・ファクト・テーブルの構成

また、JOIN句を利用せずに複数のダイレクト・ディスカバリ・テーブルを追加し、マルチ・ファクト・テーブルのデータモデルを作成することも可能です。

まず、データロードエディタ上で作成済みのDIRECT QUERYステートメント後尾にカーソルを配置し、「Adworks」データ接続の「データを選択」アイコンをクリックします。

image

データベースから「AdventureWorks2012」、所有者から「Sales」を選択して「SalesTerritory」テーブルのチェックボックスをONにします。「ロードステートメントを含める」のオプションをOFFにして「スクリプトを挿入」ボタンをクリックします。

image

スクリプトが追加されます。

image

ロードスクリプトを以下の様に修正します。


DIRECT QUERY
DIMENSION
    TerritoryID,
    Name as [Territory Name],
    CountryRegionCode,
    "Group"
MEASURE
    SalesYTD,
    SalesLastYear,
    CostYTD,
    CostLastYear
    //rowguid,
    //ModifiedDate
FROM AdventureWorks2012.Sales.SalesTerritory;

リロードを実行すると、以下の様にTerritoryIDで連結されたテーブルが追加されます。

image

以下では「SalesTerritory」テーブルの「TerritoryName」を軸として、それぞれ別のテーブルに存在する「SalesYTD」と「TotalDue」を集計対象としてチャートを作成しています。

image

尚、JOIN句を使った方法とは異なり、このマルチ・ファクト・テーブルの方式の利用に当たっては以下の点に注意する必要があります。

  • 一つのチャートには一つの論理テーブル上(上記の例では「DirectTable」、「DirectTable-1」のいずれか)のメジャーのみを含むことができます。複数の論理テーブルのメジャーを一つのチャートに含めた場合には以下の様なエラーが表示されます。

image

  • 二つのテーブルを結合するキー(上記の例では「TerritoryID」)のカーディナリティーが低い(=一意な値の種類が少ない)場合のみの利用が推奨されます。この点の詳細については、次の「インメモリのディメンションテーブル追加」の項で触れたいと思います。

インメモリのディメンションテーブル追加

以上、(1)JOINを使った複数テーブルの結合、(2)複数のダイレクト・ディスカバリ・テーブルの追加、の2つの複数テーブル結合のパターンをご紹介しました。

DB上の複数テーブルの結合の場合には上記の方法で対応できますが、次に例えば「ダイレクト・ディスカバリで接続するDBテーブルに格納された明細データに、Excelのマスタデータや他のDBから抽出したデータを結合したい」といったシナリオを想定してみます。

ここではそのようなシナリオに対応することを考え、CSVファイルのデータをQlik Senseのインメモリに取り込み、これまで作成したダイレクト・ディスカバリ・テーブルと結合する手順をご説明したいと思います。

以下から「Product.csv」のファイルをダウンロードして任意のフォルダに保存します。(この例ではC:\CSV以下に保存しています。)

>Product.csvダウンロード

データロードエディタを開いて「フォルダ」の「データ接続」を新規作成します。

image

ファイルを保存したフォルダを指定し、名前を指定して(ここでは「CSV」を指定)「保存」をクリックします。

image

カーソルをロードスクリプト文の後尾に配置し、「データを選択」アイコンをクリックします。

image

「Product.csv」を選択して「選択」をクリックします。

image

設定を確認し「スクリプトを挿入」ボタンをクリックします。

image

スクリプトが追加されます。「データのロード」ボタンをクリックしてロードを実行します。

image

以下の様に「Product」テーブルがディメンションテーブルとして追加されます。

image

新たに追加した「Product」テーブルの「Name」を軸とし、「Sum([TotalDue])」をメジャーとして集計する棒グラフを作成します。尚、項目一覧ではダイレクト・ディスカバリ・テーブルの項目には左側にデータベースのアイコンが付加されており、新たに追加したインメモリ上の「Product」テーブルの項目には付加されていないことが確認できます。

image

以下の様にチャートが表示されます。尚、今回はCSVからディメンションテーブルを追加しましたが、同様にデータベースからデータを抽出してディメンションテーブルとして追加することも可能です。

image

次に、前項の「マルチ・ファクト・テーブルの構成」でも触れた「カーディナリティ」についての説明を補足しておきたいと思います。前項の方法と同様、二つのテーブルをQlik Senseのアソシエーション機能を使って結びつける場合には、二つのテーブルを結合するキー(ここの例では「ProductID」)のカーディナリティーが低い(=一意な値の種類が少ない)場合のみの利用が推奨されます。

例えば、以下のケースを見てみましょう。先ほど作成したチャートにディメンション・テーブル(=「Product」テーブル)に含まれる「ProductSubcategoryID」のフィルタを追加してデータの絞り込みを行っています。

image

この時、絞り込まれた「ProductSubcategoryID」に紐付く「ProductID」の一覧はインメモリ上で判別され、WHERE句にその「ProductID」の一覧(2つのテーブルを結び付けるキーである「ProductID」がWHERE句の条件に利用されます)を埋め込んでダイレクト・ディスカバリ・テーブルにクエリが送信されます。

そのため、一覧に含まれる値の種類が数千・数万と非常に多くなる場合、つまりカーディナリティーが高い(=一意な値の種類が多い)場合にはパフォーマンスに影響を与える可能性があります。

image

一方、「JOIN句を使った複数テーブルの結合」の項目で説明した方法でJOINでテーブルを結合し、同じ手順で「ProductSubcategoryID」フィルタでデータの絞り込みを行うと、以下の様に「ProductSubcategoryID」を利用したクエリが実行されてこの状況は回避することができます。ユーザービリティーやパフォーマンスへの影響等を考慮した上で、実装方法を選択することが望まれます。

image

ダイレクト・ディスカバリの実行状況の確認

ダイレクト・ディスカバリによりソースシステム上で実行されているクエリの実行状況などの確認については、ソースシステムに付随するツールなどで行って下さい。

例えば、今回のデモ環境で利用しているMicrosoft SQL Serverの場合には、「SQL Server Profiler」のツールを利用することで実行されているクエリを確認することができます。

image

また、エラー発生時などに詳細ログを確認するには、ODBC接続のトレースを実行してトレース情報を確認する方法があります。

トレースの取得は「ODBCデータソースアドミニストレーター」ウインドウの「トレース」タブから「トレースの開始」をクリックすることで取得することが可能です。

image

ダイレクト・ディスカバリの変数設定

Qlik Senseではダイレクト・ディスカバリの動作に関わる変数が幾つか用意されています。

例えば、Qlik Senseではクエリの選択状態をメモリ上にキャッシュとして保存され複数のユーザーで共有されます。そのため、同じ種類の選択が複数のユーザー間で行われると、Qlik Senseはソースシステムに対してクエリを実行せず、キャッシュされている結果がユーザーに返されます。このキャッシュ時間は以下の変数設定で制御することができます。

SET DirectCacheSeconds=3600;

クエリ結果のキャッシュ時間はデフォルトで30分間に設定されていますが、上記設定により60分間に変更することができます。

その他に以下の一覧に記載されているような変数が用意されています。尚、データソースに依存した必要な設定等もありますので、詳細については以下リンク先のQlik Sense Helpサイトをご参照ください。

SETステートメント 説明

DirectDateFormat

データベースに送信される日付書式を設定します。

例)Set DirectDateFormat=’MM/DD/YYYY’;

DirectTimestampFormat

データベースに送信される日時書式を設定します。

例)Set DirectTimestampFormat=’M/D/YY hh:mm:ss[.fff]’;

DirectTimeFormat

データベースに送信される時間書式を設定します。

例)Set DirectTimeFormat=’hh:mm:ss’;

DirectMoneyFormat

データベースに送信される通貨書式を設定します。

例)Set DirectMoneyFormat=’#.0000′;

DirectMoneyDecimalSep

データベースに送信される通貨の小数点記号を設定します。

例)Set DirectMoneyDecimalSep=’.’;

DirectCacheSeconds

クエリの結果セットがインメモリのキャッシュに保存される時間を設定します。デフォルトでは1,800秒(30分)となっています。(テーブルのビジュアライゼーションには適用されません)

例)SET DirectCacheSeconds=1800;

DirectTableBoxListThreshold

テーブルのビジュアライゼーションに表示することができるダイレクト・ディスカバリのデータ項目の表示可能行数を設定します。デフォルトでは1,000行となっています。

例)SET DirectTableBoxListThreshold=5000;

DirectConnectionMax

接続プーリングを使いデータベースに対して実行することが出来る非同期の同時呼び出し数を制御します。デフォルトでは1となっています。

例)SET DirectConnectionMax=10

DirectStringQuoteChar

生成されるクエリの文字列の引用符を設定します。デフォルトではシングルクオートとなっています。

例)SET DirectStringQuoteChar= ‘”‘

DirectIdentifierQuoteChar

生成されるクエリの識別子の引用符を制御します。デフォルトではダブルクオートとなっています。

例)SET DirectIdentifierQuoteChar= ‘[]’;

DirectUnicodeStrings

ANSI標準ワイド文字マーカーの’N’を文字列リテラルの前に使用できるようにするための設定を行います。ただし、データベースによってはサポートされておらず、デフォルトではfalseとなっています。

DirectDistinctSupport

DISTINCTをサポートしないデータソースのために、デフォルトシンタックスのSELECT DISTINCTではなく、GROUP BYを利用する場合に利用します。デフォルトではDISTINCTが使用されます。

DirectIdentifierQuoteStyle

非ANSI標準の引用符を利用する場合に利用します。

SQLSessionPrefix

Teradataとの連携時、セッションに対してTeradata Query banding parametersを利用する場合に利用します。

SQLQueryPrefix

Teradataとの連携時、クエリに対してTeradata Query banding parametersを利用する場合に利用します。

注意点

Qlik Senseでインメモリ上のデータを利用する場合とは異なり、ダイレクト・ディスカバリの利用に当たっては以下の様な機能上の注意点があります。

  • SET分析や複雑な数式は利用することができません。(Sum, Count, Min, Max, Avgが利用可能)
  • ダイレクト・ディスカバリ・テーブルでは合成キーは利用できないため、合成キーは解消する必要があります。
  • ダイレクト・ディスカバリ機能を含むアプリケーションを利用する全ユーザーは同じ接続定義を使用するため、ユーザーごとの資格認証には対応していません。
  • Section AccessはQlik Senseのサーバーモードのみで利用可能です。
  • DIMENSIONに指定された項目の一意のレコードはインメモリに格納されます。そのため、明細レコードの変更はその場で反映されますが、レコード追加の反映についてはQlik Senseのリロードが必要となります。

尚、上記に加えてQlikView特有の注意点としては、統計ボックス、並列ステート、PublisherでのLoop and Reduceが利用できないといった制限があります。また、一般的な考慮点として、チャートオブジェクトを多数配置したり、多数のユーザーで同時アクセスを行って利用する場合にはソースシステム上で多数のクエリが実行されてしまうため、システム全体のキャパシティを考慮して設計を行うことが望まれます。

参考資料

ここでご紹介しましたダイレクト・ディスカバリの関連情報は以下のドキュメントでも提供されていますので、併せてご参照ください。

まとめ

以上、前編・後編の二回に分けてQlik Senseのダイレクト・ディスカバリの機能についてご説明をさせて頂きました。Qlik Senseでインメモリ上にデータをロードして分析を行う方が機能的な柔軟性は優れていますが、ビッグデータの膨大なデータへの分析についてはダイレクト・ディスカバリの機能を利用するなど、それぞれの特性を生かして使い分けることが出来るのは一つの大きなメリットではないかと思います。