データベース・アクションのデータ分析を使って分析ビュー(Analytic View)を作成する

 以前の記事 - 分析ビュー(Analytic View)を使う - にて、分析ビューの作成とそれを使うAPEXアプリケーションを作りました。LiveSQL.oracle.comにあったDDLを実行して、分析ビューを作っています。

Autonomous Databaseに含まれているデータベース・アクションの分析ツールを使用すると、画面操作によって分析ビュー(Analytic View)の作成ができます。どの程度簡単に分析ビューが作れるか確認するため、以前の記事で作成した分析ビューAVT_EMP_SALES_AVを、データベース・アクションの分析ツールを使って作成してみます。

分析ビューを作成したら、以前の記事で作成したAPEXアプリケーションを使って動作を確認します。

以下より、分析ビューAVT_EMP_SALES_AVの作成手順になります。

サンプル・データセットのEMP/DEPTのインストール

サンプル・データセットのEMP/DEPTを、あらかじめインストールしておきます。詳しい手順は元記事にあります。

サンプル・データセットはSQLワークショップのユーティリティに含まれています。

APEXワークスペース・スキーマの準備

APEXのワークスペース・スキーマにロールDWROLEを割り当てます。このロールを割り当てると、データベース・アクションの画面に分析ツールが現れます。また、APEXのワークスペース・スキーマの権限でデータベース・アクションに接続できるように、Webアクセス（RESTサービス）の有効化を行います。

データベース・アクションに管理者ユーザーADMINで接続します。

管理のデータベース・ユーザーを開きます。

今回の例で使用するAPEXワークスペース・スキーマをAPEXDEVとします。異なるスキーマを対象とする場合は、APEXDEVの部分を自分のスキーマ名に読み替えてください。

右上端の操作メニューより、編集を実行します。

画面右にドロワーが開きます。WebアクセスをONにします。これでユーザーAPEXDEVにて、データベース・アクションに接続できるようになります。

付与されたロールのタブを開き、ロールDWROLEの付与済とデフォルトにチェックを入れます。その後、変更の適用を実行します。

ユーザーAPEXDEVの準備ができました。

ユーザーAPEXDEVにて、データベース・アクションに接続します。

ユーザー名にAPEXDEV、パスワードにデータベース・ユーザーのパスワードを入力し、サインインを実行します。

データ・ツールにデータ分析が現れています。

ディメンジョン表とファクト表の作成

サンプル・データセットの表EMPを元にして、分析ビューの元となるファクト表AVT_EMP_SALESと２つのディメンジョン表AVT_CALENDAR、AVT_EMPLOYEESを作成します。

データベース・アクションのSQLより、以下のスクリプトを実行します。

	/*
	* 組織階層
	*/
	create table avt_employees
	as
	select
	rownum row_wid
	, v.empno
	, v.ename
	, coalesce(v.lvl1_mgr, v.ename) lvl1_mgr
	, coalesce(v.lvl2_mgr, v.lvl1_mgr, v.ename) lvl2_mgr
	, coalesce(v.lvl3_mgr, v.lvl2_mgr, lvl1_mgr, v.ename) lvl3_mgr
	from
	(
	select
	e.empno
	, e.ename
	, trim('|' from regexp_substr(sys_connect_by_path(e.ename,'|'),'\|[^\|]+',1,1)) lvl1_mgr
	, trim('|' from regexp_substr(sys_connect_by_path(e.ename,'|'),'\|[^\|]+',1,2)) lvl2_mgr
	, trim('|' from regexp_substr(sys_connect_by_path(e.ename,'|'),'\|[^\|]+',1,3)) lvl3_mgr
	from emp e
	start with e.mgr is null
	connect by prior e.empno = e.mgr
	) v
	;
	/*
	* 期間
	*/
	create table avt_calendar
	as
	select
	to_char(v.day_dt, 'YYYYMMDD') row_wid -- 日
	, to_char(v.day_dt, 'IW') week_num -- 週
	, to_char(v.day_dt, 'MM') month_name -- 月
	, to_char(v.day_dt, 'Q') qtr_name -- 四半期
	, to_char(v.day_dt, 'YYYY') year_name -- 年
	from
	(
	/*
	* 2022年だと2022-01-01から2022-12-31までを日付として生成する。
	* levelは 1 から 365 が与えられる。
	*/
	select trunc(sysdate,'YYYY') + level - 1 day_dt
	from dual
	/*
	* trunc(sysdate,'YYYY')は実行時の西暦なので、
	* add_months(trunc(sysdate,'YYYY'),12) - trunc(sysdate,'YYYY')
	* は、その時の日数。つまり 1から365(閏年は366)までがLEVELに代入される。
	*/
	connect by level <= add_months(trunc(sysdate,'YYYY'),12) - trunc(sysdate,'YYYY')
	) v
	;
	/*
	* ファクト表
	*/
	create table avt_emp_sales
	as
	select
	to_char(trunc(sysdate,'YYYY')+round(dbms_random.value(0,364)),'YYYYMMDD') date_wid
	, round(dbms_random.value(1,(select count(*) from emp))) emp_wid
	, round(dbms_random.value(1,1000),2) amount
	from dual
	connect by level < 100000
	;

view raw prep_av.sql hosted with ❤ by GitHub

表の作成は以上で完了です。

分析ビューAVT_EMP_SALES_AVの作成

データベース・アクションよりデータ分析を実行します。

（分析ビューがなければ）分析ビューの作成を行うための、ドロワーが開きます。

名前はAVT_EMP_SALES_AV、ファクト表はAVT_EMP_SALESです。ディメンジョン表の検索にチェックを入れ、Generate Hierarchies and Measuresを実行します。

右下の作成は一番最後にクリックします。このボタンを押すと、分析ビューを生成するDDLが実行されます。分析ビューの作成ウィザードは生成ボタンをクリックすると終了します。作成された後の分析ビューの修正は、分析ビューを編集する機能(ALTER文の生成と実行を行うUI)を使って実施します。

ポップアップが表示され、自動検出の進捗が表示されます。分析ビュー（Attribute DimenstionおよびHierarchyを含む）を作成するDDLが生成されたら、この作業は完了です。閉じるをクリックします。

APEXアプリケーションを修正しなくても済むように、検出された階層やレベルの名前を変更します。

階層AVT_CALENDARを選択し、階層名とレベル名を変更します。階層名はAVT_CALENDAR_HIERとします。

左ペインのツリーにある階層AVT_CALENDAR_HIER（階層名変更後）を開いて、それぞれのレベルのレベル名を変更します。

それぞれ、CAL_YEAR、CAL_QTR、CAL_MONTH、CAL_DAYとなるように変更します。

レベル名は、大分類が上位になるように配置します。

続いて、階層AVT_EMPLOYEESを編集します。

左ペインにて階層AVT_EMPLOYEESを選択し、階層名をAVT_EMPLOYEE_HIERに変更します。

レベルとしてLVL3_MGRが検出されていません。

階層AVT_EMPLOYEE_HIER（階層名変更後）の上でコンテキスト・メニューを表示させ、レベルの追加を実行します。

追加したレベルのレベル名はLVL3_MGRとします。レベル・キーにLVL3_MGRを選択すると、その他の値にデフォルト値が設定されます。これで、レベルLVL3_MGRの追加ができました。

階層AVT_EMPLOYEE_HIERを開き、レベルLVL3_MGRの位置を修正します。LVL2_MGRの下、ROW_WIDの上に移動します。

レベルROW_WIDを選択し、レベル名をEMPに変更します。

デフォルト以外のメジャーを作成します。

メジャー上でコンテキスト・メニューを表示させ、列からのメジャーの追加からAMOUNTを選んで実行します。

メジャー名をAVG_AMOUNTとして、式にAVGを選択します。

同様の手順を繰り返し、メジャーCNT_AMOUNT(式はCOUNT)、MIN_AMOUNT(式はMIN)、MAX_AMOUNT(式はMAX)を作成します。

以上で分析ビューAVT_EMP_SALES_AVの設定は完了です。

作成をクリックして、分析ビューを作成するDDLを実行します。

分析ビューを作成しますか？と聞かれるので、はいをクリックします。

分析ビューAVT_EMP_SALES_AVが作成されました。

APEXアプリケーションの確認

以前に作成したAPEXアプリケーションをワークスペースにインポートします。

インポートしたアプリケーションを少し修正します。

ページ・アイテムP1_CAL_DEPTHのLOVのSQL問合せに含まれる、以下の条件をコメントアウトします。

    -- and dimension_alias = 'AVT_DATE_DIM'

データベース・アクションで分析ビューを作成すると、属性ディメンジョンの名称を画面から設定する方法がありません。しかし、dimension_alliasはhier_aliasと同じ値が設定されているので、この条件を除いてもアプリケーションの動作に影響はありません。

同様にP1_EMP_DEPTHの以下の条件もコメントアウトします。

    -- and dimension_alias = 'AVT_EMP_DIM'

以上の変更を行うことにより、APEXアプリケーションの動作を確認することができました。

データベース・アクションのデータ分析からは、レベル名やメンバー名の日本語化はできないようです。

ソースとなるファクト表およびディメンジョン表が作成済みであれば、DDLを記述しなくても分析ビューを作成することができました。

作成済みの分析ビューの編集もメニューから呼び出すことができ、画面上で作業を行えます。

分析ビューを作成するDDLを記述するのは相当に難しいので、このような機能は大変便利です。

完