読者です 読者をやめる 読者になる 読者になる

crmprogrammer38の日記

プログラマのメモ

【TERADATA】Aggregate Join Indexでさらに検索を早くする

TERADTAに、Join Indexという機能がある。

これはOracleのMaterialized Viewをクエリーリライトに特化したような機能で、あらかじめ結合や集計をした結果を保持する。

もちろん、元のテーブルが更新されれば即座にJoin Indexも更新される。(V4ぐらいでは、元のテーブルが更新されてもJoin Indexは更新されなかったような記憶がありますが、最近のバージョンでは更新されます)

 

BI/DWHシステムとして、BIツールとTERADATAでシステムを構築した場合、なるべく加工(集計)をしないでデータを持っているほうが、利用者は使いやすい。

でも、加工(集計)しないと性能に問題が生じる場合、Join Indexを利用すると性能が改善できる。

 

例えば、次のようなデータモデルで、

f:id:crmprogrammer38:20170208123750p:plain

売上データは集計していなくて件数が2億件あるとした場合、顧客別の分析をしない次のような人には、売上データに顧客コードを除いて集計したJoin Indexが有効となる。

・店舗別で過去数年に渡ってデータを見たい

・商品別で過去数年に渡ったデータを見たい

・店舗/商品別で当年のデータを見たい

 

Join Indexのサンプル
--1.売上テーブルの定義 CREATE SET TABLE Sample1 ( Datestring CHAR(8) CHARACTER SET LATIN CASESPECIFIC ,ProductCode CHAR(10) CHARACTER SET LATIN CASESPECIFIC ,StoreCode CHAR(10) CHARACTER SET LATIN CASESPECIFIC ,AccoundCode CHAR(10) CHARACTER SET LATIN CASESPECIFIC ,Amount DECIMAL(6,0) COMPRESS (0) ) PRIMARY INDEX ( Datestring ,ProductCode ,StoreCode ) PARTITION BY (ZEROIFNULL(( CAST((Datestring) AS INTEGER))) MOD 65535 ) + 1 ; --2.顧客コードを除いたJoin Indexを指定する。 CREATE JOIN INDEX JX_Sample1 AS SELECT Datestring ,ProductCode ,StoreCode ,SUM(Amount) AS Amount FROM Sample1 GROUP BY Datestring ,ProductCode ,StoreCode PRIMARY INDEX ( Datestring ,ProductCode ,StoreCode ) PARTITION BY (CAST(Datestring AS INTEGER) MOD 65535) + 1 ;

Join Indexが有効となるのは、そのJoin Indexを作成した場合、どのくらいデータサイズが小さくなるかがポイントとなる。

Join Indexを作成した結果、さほどデータサイズが小さくならないならそのJoin Indexは不要です。

 

BIツールの設定でも、売上データは1つなのに、商品別サマリーや、店舗別サマリーなどがあると複数のデータモデルを用意する必要があったりでメンテナンスも大変だし、利用者も慣れるのに時間がかかる。

 

個人的な考え

Join Indexは"あらかじめ結合する"、"あらかじめ集計する"、"あらかじめ結合して集計する"を行うことができるが、BIツールを使うと、常にJoin Indexで指定したテーブルと、その結合キーをつかって結合するとは限らないので、"あらかじめ集計する"のが一番使いやすいと思っています。

 

また、下記のように年月日が決まると確実に年月が定まるような項目はファクトに持たせておき、年月に対応するマスタを用意しておくと、年月別で集計できてJoin Indexのサイズをさらに小さくできるよ。

f:id:crmprogrammer38:20170210131922p:plain

 

Join Indexのサンプル
--1.売上テーブルの定義 CREATE SET TABLE Sample2 ( Datestring CHAR(8) CHARACTER SET LATIN CASESPECIFIC ,YearMonth CHAR(6) CHARACTER SET LATIN CASESPECIFIC ,ProductCode CHAR(10) CHARACTER SET LATIN CASESPECIFIC ,StoreCode CHAR(10) CHARACTER SET LATIN CASESPECIFIC ,AccoundCode CHAR(10) CHARACTER SET LATIN CASESPECIFIC ,Amount DECIMAL(6,0) COMPRESS (0) ) PRIMARY INDEX ( Datestring ,ProductCode ,StoreCode ) PARTITION BY (ZEROIFNULL(( CAST((Datestring) AS INTEGER))) MOD 65535 ) + 1 ; --2.顧客コードを除き、さらに年月別別でJoin Indexを指定する。 CREATE JOIN INDEX JX_Sample2 AS SELECT YearMonth ,ProductCode ,StoreCode ,SUM(Amount) AS Amount FROM Sample2 GROUP BY YearMonth ,ProductCode ,StoreCode PRIMARY INDEX ( YearMonth ,ProductCode ,StoreCode ) PARTITION BY (CAST(YearMonth AS INTEGER) MOD 65535) + 1 ;

 テーブル定義では、年月日にパーティション指定されているけど、Join Indexでは、年月にパーティション指定ができるのでパーティションも使えます。

ただ、普通に集計軸の考えだと、年月日のカレンダの上位に年月のカレンダ(年月)をつなぐのが普通なので、年月のカレンダ(年月)を単独で結合するケースはあまりないと思います。

が、BIツールでショートカット結合が用意されていれば、Join Indexをうまく利用できると思います。(Business Objectsではショートカット結合があります)

 

Business Objectsユニバースでのショートカット結合

ショートカット結合で、カレンダ(年月)の年月と売上データの計上年月を結合する。

f:id:crmprogrammer38:20170210160826p:plain

ユニバースでショートカット結合を指定すると、例えば、カレンダ(年月)の年月項目と売上データの売上金額を選択すると、発行されるSQLはテーブルは、カレンダ(年月)と売上データとなる。

そして、結合条件は「カレンダ(年月).年月=売上データ.計上年月」となる。

そのため売上データに対して、計上年月で集計したJoin Indexを利用可能となる。

他のBIツールにもショートカット結合と同様の機能があるかはわかりませんが、Business Objectsなら使えるので、お試しあれ!