分析ビュー(Analytic View)を使ったAPEXアプリケーションを作ってみます。APEXアプリケーションの作成に使用するスキーマ定義やデータの準備は、LiveSQL.oracle.comのAnalytic View Demoを元にしています。
分析ビューについては、オラクルのドキュメントを参照しています。
作業にはAlways FreeのAutonomous Databaseを使用します。APEXのワークスペースおよびワークスペース・スキーマとして、APEXDEVが作成済みであることを前提とします。違う名前のスキーマの場合は、APEXDEVとなっている部分を置き換える必要があります。ただし、スキーマ名を参照するのはGRANT文くらいで、それほど多くはありません。
権限の割り当て
Autonomous Databaseの管理者ADMINにてデータベース・アクションに接続し、SQLの画面を開きます。以下のGRANT文を実行し、APEXワークスペースで分析ビューを作成するために必要な権限を与えます。
grant create attribute dimension to <ワークスペース・スキーマ>;
grant create hierarchy to <ワークスペース・スキーマ>;
grant create analytic view to <ワークスペース・スキーマ>;
今回の作業で使用するワークスペース・スキーマはAPEXDEVなので、実行するコマンドは以下になります。
grant create attribute dimension to apexdev;
grant create hierarchy to apexdev;
grant create analytic view to apexdev;
データの準備
分析ビューを作るためのデータを準備します。
２つの属性ディメンジョン(Attribute Dimension)を作成します。一つ目の属性ディメンジョンは、マネージャーの階層(Hierarchy)を持つようにします。そのために、サンプル・データセットに含まれる表EMPを使用します。もう一つの属性ディメンジョンは、日付の階層を持つようにします。表EMPに含まれる従業員が、特定の日付に売り上げた金額を記録した表を、ファクト表として作成します。
属性ディメンジョンAVT_EMP_DIMの作成
マネージャーの階層を持つ属性ディメンジョンAVT_EMP_DIMを作成します。
最初にSQLワークショップのユーティリティよりサンプル・データセットを呼び出し、EMP / DEPTをインストールします。
インストールをクリックするとダイアログが開きます。日本語のデータが用意されているので、言語としてJapaneseを選びます。スキーマはデフォルトのまま変更せず、次へ進みます。
確認画面に移ります。データセットのインストールをクリックします。表EMP、DEPTおよびビューEMP_DEPT_Vが作成されます。今回の作業で使用するのは表EMPのみです。
データセットが正常にインストールされたことを確認し、終了をクリックします。この時点ではアプリケーションは作成しません。
データセットがインストール済みになると、アクションのボタンのラベルが更新に変わります。
属性ディメンジョンに使われる表AVT_EMPLOYEESを作成します。表EMPにはマネージャーを保持する列MGRが定義されていますが、表AVT_EMPLOYEESには、従業員の上司（列LVL3_MGR）、その上の上司（列LVL2_MGR）、さらにその上の上司（列LVL1_MGR）と、３つの階層を列として追加しています。
SQLワークショップのSQLコマンドにて、以下の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
;
作成された表AVT_EMPLOYEESの内容を確認してみます。以下のSELECT文を実行します。
select * from avt_employees;
全部で１４名の従業員が検索されます。列LVL1_MGRは社長なので、すべての従業員で同じ人になります。ほとんどの従業員は２階層上の上司が社長で（１２人）で、LVL3_MGRが自分自身つまりENAMEと同じになります。２人だけ３階層あり、LVL3_MGRとENAMEが異なっています。
個々の従業員を特定するのは主キーである列EMPNOがあれば十分ですが、列ROW_WIDが追加されています。これはファクト表のデータを自動生成する際に、EMPNOの代わりに１から１４の数を割り当てていて、その列と関連付けるために追加されています。
この表を使って属性ディメンジョンAVT_EMP_DIMを作成します。CREATE ATTRIBUTE DIMENSIONを実行します。
create or replace attribute dimension avt_emp_dim
using avt_employees
attributes(
row_wid
, empno
, lvl1_mgr
, lvl2_mgr
, lvl3_mgr
)
level emp key row_wid member name to_char(empno) order by empno determines(lvl3_mgr)
level lvl3_mgr key lvl3_mgr member name lvl3_mgr order by lvl3_mgr determines(lvl2_mgr)
level lvl2_mgr key lvl2_mgr member name lvl2_mgr order by lvl2_mgr determines(lvl1_mgr)
level lvl1_mgr key lvl1_mgr member name lvl1_mgr order by lvl1_mgr
all member name 'Total'
;
属性ディメンジョンAVT_EMP_DIMに階層AVT_EMPLOYEE_HIERを作成します。CREATE HIERARCHYを実行します。
create hierarchy avt_employee_hier
using avt_emp_dim (
emp child of
lvl3_mgr child of
lvl2_mgr child of
lvl1_mgr
);
以上で属性ディメンジョンAVT_EMP_DIMに関する設定は完了です。
属性ディメンジョンAVT_DATE_DIMの作成
日付の属性ディメンジョンAVT_DATE_DIMを作成します。
属性ディメンジョンAVT_DATE_DIMに使われる表AVT_CALENDARを作成します。以下のSQLを実行します。
実行した時点での年の1月1日から12月31日までの行が作成され、それぞれの日付に週、月、四半期、年の情報が列として定義されます。
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
作成された表AVT_CALENDARの内容を確認してみます。以下のSELECT文を実行します。
select * from avt_calendar;
列ROW_WIDとして日付、列MONTH_NAMEとして月、列QTR_NAMEに四半期、列YEAR_NAMEに年の情報が含まれています。列WEEK_NUMには年間で第何週にあたるか記載されていますが、これは階層には含めません。列YEAR_NAMEは実行時の年（今回は2022のみ）に限られています。
この表を使って属性ディメンジョンAVT_DATE_DIMを作成します。以下のSQLを実行します。
create or replace attribute dimension avt_date_dim
using avt_calendar
attributes(
year_name
, qtr_name
, month_name
, row_wid
)
level cal_day key row_wid member name row_wid order by row_wid determines(month_name）
level cal_month key month_name member name month_name order by month_name determines(qtr_name)
level cal_qtr key qtr_name member name qtr_name order by qtr_name determines(year_name)
level cal_year key year_name member name year_name order by year_name
all member name 'Total'
;
create hierarchy avt_calendar_hier
using avt_date_dim (
cal_day child of
cal_month child of
cal_qtr child of
cal_year
);
以上で属性ディメンジョンAVT_DATE_DIMに関する設定は完了です。
ファクト表AVT_EMP_SALESの作成
ファクト表AVT_EMP_SALESを作成します。以下のSQLを実行します。全体で99,999行のデータが生成されます。
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
;
表AVT_EMP_SALESの内容を確認します。以下のSELECT文を実行します。
select * from avt_emp_sales;
列DATE_WIDは表AVT_CALENDARの列ROW_WID、列EMP_WIDは表AVT_EMPLOYEESの列ROW_WIDに対応します。これらの列は、表をジョインする際に使用されます。
分析ビューAVT_EMP_SALES_AVの作成
使用する属性ディメンジョンと階層が作成されました。それらを使って分析ビューAVT_EMP_SALES_AVを作成します。CREATE ANALYTIC VIEWを実行します。
create or replace analytic view avt_emp_sales_av
using avt_emp_sales
dimension by (
avt_date_dim
key date_wid references row_wid hierarchies (avt_calendar_hier default)
, avt_emp_dim
key emp_wid references row_wid hierarchies (avt_employee_hier default)
)
measures (
amount fact amount
, avg_amount fact amount aggregate by avg
, cnt_amount fact amount aggregate by count
, min_amount fact amount aggregate by min
, max_amount fact amount aggregate by max
);
ファクト表としてAVT_SALES_EMP（USINGで指定）を指定しています。ディメンジョン（DIMENSION BYで指定）にAVT_DATE_DIMおよびAVT_EMP_DIM、それぞれの階層としてAVT_CALENDAR_HIER、AVT_EMPLOYEE_HIERを指定しています。
集計結果として（MEASURESで指定）、AMOUNT（AGGREGATE BYが未指定の場合は合計 - SUM関数の適用）、AVG_AMOUNT（平均 - AVG関数の適用）、CNT_AMOUNT（件数 - COUNT関数の適用）、MIN_AMOUNT（最小値 - MIN関数の適用）、MAX_AMOUNT（最大値 - MAX関数の適用）を定義しています。
SELECT文を実行して、作成した分析ビューを確認します。以下のSELECT文を実行します。
select avt_calendar_hier.member_name, amount
from avt_emp_sales_av hierarchies(avt_calendar_hier)
where avt_calendar_hier.level_name = 'CAL_MONTH'
order by avt_calendar_hier.member_name asc;
avt_calendar_hier.level_nameとしてCAL_MONTHを指定することにより、月単位の集計結果を求めています。列AMOUNTとして、月単位で集計（合計）された結果がリストされます。
以上で、分析ビューAVT_EMP_SALES_AVの作成は完了です。
分析ビューを使ったAPEXアプリケーションの作成
アプリケーション作成ウィザードを起動し、名前を分析ビューとします。他は設定せずアプリケーションの作成をクリックして、空のアプリケーションを作成します。
アプリケーションが作成されたら、ホーム・ページをページ・デザイナで開きます。
ホーム・ページのContent Body上でコンテキスト・メニューを開き、リージョンの作成を実行します。
作成されたリージョンの識別のタイトルは売上、タイプとして対話モード・レポートを選択します。ソースの位置はローカル・データベース、タイプとしてSQL問合せを選択し、SQL問合せとして以下を記述します。
select
/* 日付ディメンジョンの情報 */
avt_calendar_hier.is_leaf cal_is_leaf
, avt_calendar_hier.member_name cal_member_name
, avt_calendar_hier.member_unique_name cal_member_unique_name
, avt_calendar_hier.parent_unique_name cal_parent_unique_name
, avt_calendar_hier.parent_level_name cal_parent_level_name
, avt_calendar_hier.depth cal_depth
/* 従業員ディメンジョンの情報 */
, avt_employee_hier.is_leaf emp_is_leaf
, avt_employee_hier.member_name emp_member_name
, avt_employee_hier.member_unique_name emp_member_unique_name
, avt_employee_hier.parent_unique_name emp_parent_unique_name
, avt_employee_hier.parent_level_name emp_parent_level_name
, avt_employee_hier.depth emp_depth
/* 各種メジャー */
, amount
, round(avg_amount,0) avg_amount
, cnt_amount
, min_amount
, max_amount
from avt_emp_sales_av
hierarchies (avt_calendar_hier, avt_employee_hier)
where
/* 全行を集計する - 結果は１行 */
avt_calendar_hier.level_name = 'ALL'
and
avt_employee_hier.level_name = 'ALL'
上記のSELECT文は、ファクト表の全ての行を集計した結果を１行で表示します。
作成したページを実行し、対話モード・レポートの表示を確認します。
ページ・デザイナに戻り、集計結果をドリルダウンするために使用するページ・アイテムを作成します。
日付ディメンジョンのドリルダウンに使用するページ・アイテムとしてP1_CAL_DEPTH、P1_CAL_PARENT_UNIQUE_NAME、マネージャー・ディメンジョンのドリルダウンに使用するページ・アイテムとしてP1_EMP_DEPTH、P1_EMP_PARENT_UNIQUE_NAMEを作成します。
対話モード・レポートのリージョン売上の上でコンテキスト・メニューを開き、ページ・アイテムの作成を実行します。
作成されたページ・アイテムの識別の名前はP1_CAL_DEPTH、タイプとして選択リストを選びます。ラベルは日付レベルとします。LOVのタイプとしてSQL問合せを選択し、SQL問合せとして以下を記述します。追加値の表示、NULL値の表示ともにOFFにします。ビューUSER_ANALYTIC_VIEW_LEVELSより、日付ディメンジョンの階層を取り出しています。
select level_name d, depth r
from
(
select level_name, (order_num + 1) depth
from user_analytic_view_levels
where 1=1
and analytic_view_name = 'AVT_EMP_SALES_AV'
and dimension_alias = 'AVT_DATE_DIM'
and hier_alias = 'AVT_CALENDAR_HIER'
union
select 'ALL' level_name, 0 depth from dual
)
order by depth asc
さらにページ・アイテムを作成します。識別の名前はP1_CAL_PARENT_UNIQUE_NAME、タイプには表示のみを選択します。ラベルは親日付レベルとします。レイアウトの新規行の開始はOFFにします。
日付ディメンジョンに続いて、マネージャー・ディメンジョンに関わるページ・アイテムを作成します。
新たにページ・アイテムを作成します。ページ・アイテムP1_CAL_DEPTHと同様の設定ですが、識別の名前をP1_EMP_DEPTHとし、ラベルはマネージャー・レベルとします。LOVのSQL問合せを以下にします。
select level_name d, depth r
from
(
select level_name, (order_num + 1) depth
from user_analytic_view_levels
where 1=1
and analytic_view_name = 'AVT_EMP_SALES_AV'
and dimension_alias = 'AVT_EMP_DIM'
and hier_alias = 'AVT_EMPLOYEE_HIER'
union
select 'ALL' level_name, 0 depth from dual
)
order by depth asc
さらにページ・アイテムを作成し、P1_CAL_PARENT_UNIQUE_NAMEと同様の設定を行います。識別の名前はP1_EMP_PARENT_UNIQUE_NAME、ラベルは親マネージャー・レベルとします。
レポートのドリルダウンに使用するページ・アイテムが作成されたので、対話モード・レポートのソースのSQL問合せを以下に変更します。
この後、動的アクションを使って対話モード・レポートのリフレッシュを実装するため、送信するページ・アイテムとして、作成した４つのページ・アイテムP1_CAL_DEPTH、P1_CAL_PARENT_UNIQUE_NAME、P1_EMP_DEPTH、P1_EMP_PARENT_UNIQUE_NAMEのすべてを指定します。
ページ・アイテムP1_CAL_DEPTHが変更されたときに、リージョン売上をリフレッシュします。
ページ・アイテムP1_CAL_DEPTH上でコンテキスト・メニューを開き、動的アクションの作成を実行します。
作成された動的アクションの識別の名前は日付レベルの変更とします。ページ・アイテム上で動的アクションを作成した場合は、タイミングはデフォルトで、イベントが変更、選択タイプがアイテムで、アイテムとして動的アクションの作成を実行したP1_CAL_DEPTHになります。この設定はそのまま使います。
ページ・アイテムP1_CAL_DEPTHを変更したときは、親となるメンバーでの絞り込み（ドリルダウン）を解除して選択したレベルのすべての行が一覧されるようにします。そのため、ページ・アイテムの変更時にP1_CAL_PARENT_UNIQUE_NAMEをクリアします。
デフォルトで作成されたTRUEアクションである表示を選択します。識別のアクションとしてクリアを選択し、影響を受ける要素の選択タイプにアイテム、アイテムとしてP1_CAL_PARENT_UNIQUE_NAMEを指定します。
ページ・アイテムのクリア後にリージョン売上をリフレッシュします。
Trueのノード上でコンテキスト・メニューを表示させ、TRUEアクションの作成を実行します。
作成されたTRUEアクションの識別のアクションとしてリフレッシュを選択します。影響を受ける要素として、選択タイプにリージョン、リージョンに売上を指定します。
ページ・アイテムP1_CAL_DEPTHに対する動的アクションの設定は以上です。
同様の動的アクションを、ページ・アイテムP1_EMP_DEPTHに対して作成します。
ページ・アイテムP1_EMP_DEPTHに動的アクションを作成します。識別の名前はマネージャー・レベルの変更とします。
クリアするページ・アイテムはP1_EMP_PARENT_UNIQUE_NAMEです。
TRUEアクションとして、リージョン売上のリフレッシュを作成します。
対話モード・レポートの列CAL_MEMBER_NAMEおよびEMP_MEMBER_NAMEは、ドリルダウンをするリンクを生成します。生成されたHTMLがそのまま表示されるよう、列CAL_MEMBER_NAMEとEMP_MEMBER_NAMEの両方を選択し、セキュリティの特殊文字をエスケープをOFFに変更します。
最後に売上詳細を表示するページを作成します。
ページの作成を実行し、ページ作成ウィザードを起動します。
レポートを選択します。
対話モード・レポートを選択します。
次へ進みます。
モーダル・ダイアログなので、ナビゲーションのプリファレンスはデフォルトのこのページとナビゲーション・メニュー・エントリを関連付けないのままにします。
次へ進みます。
データ・ソースとしてローカル・データベース、ソース・タイプとしてSQL問合せを選択します。SQL SELECT文を入力に以下を記述します。
作成をクリックします。
select
e.empno
, e.ename
, s.date_wid
, s.amount
from avt_emp_sales s join avt_employees e
on s.emp_wid = e.row_wid
売上詳細のページが作成されます。
以上で分析ビューを扱うサンプル・アプリケーションが作成できました。
動作確認
本記事の先頭にあるGIF動画の操作になります。
日付レベル、マネージャ・レベルともにALLを選択します。日付（Cal Member Name）、マネージャー（Emp Member Name）ともにTotalで、集計は表AVT_EMP_SALESの全行を対象にしています。
Cal Member NameのTotalをクリックします。
日付レベルがCAL_YEARに変わります。ただし、表AVT_EMP_SALESには2022年の情報しかないため、集計結果は2022年で集計した一行だけになります。
Cal Member Nameの2022をクリックします。
Cal Member Nameの3をクリックします。
日付レベルがCAL_DAYに変わります。親日付レベルは[CAL_MONTH].&[08]となり、レポートされる日付が８月に限定されます。
日付レベルがCAL_MONTHに変わります。親日付レベルは[CAL_QTR].&[3]となり、レポートされる月が第三四半期に限定されます。
Cal Member Nameの08をクリックします。
Cal Member Nameの20220801をクリックします。
日付ディメンジョンで日付が末端になります。そのため、分析ビューとしてのドリルダウンではなく、ファクト表を検索した結果を表示します。
レポートのソースのSELECT文で、レベルが末端（IS_LEAFが0ではない）の場合、分析ビューをドリルダウンする代わりに、詳細ページを開くようにCASE文を記述しています。
以上で分析ビューの動作が確認できました。
マネージャーの階層も同様の手順で確認できます。
日付レベルがCAL_MONTH、親日付レベルが[CAL_QTR].&[1]、つまり第一四半期の月毎に、マネージャー・レベルがLVL2_MGR、つまり社長直下となるすべてのマネージャーごとに売上を集計したレポートになります。
マネージャーの階層では、レベルの末端になると従業員名の代わりに従業員番号が表示されます。
分析ビューの動作を確認するための最低限のUIですが、分析ビューの使い方の参考にしていただけると幸いです。
分析ビューがあらかじめ作成されていれば、APEXアプリケーションでレポートやチャートを作成する際に、GROUP BYを使った集計関数などをSQLとして記述しなくて済みます。
分析ビューのその他の機能
本記事で紹介した内容は分析ビューの基本です。この他にも色々な機能を使ったサンプルがLiveSQL.oracle.comに載っています。
マテリアライズド・ビューによる集計のキャッシュ(Using the Analytic View Materialized Aggregate Cache)
複数言語の対応(Adding Multiple Language Support to Analytic Views)
SHARE_OFの使い方(Creating SHARE_OF (Hierarchical Ratio) Calculations in Analytic View)
FILTER FACTの使い方(Using FILTER FACT to Extend Analytic View Aggregation in SELECT)
今回のデータの準備では索引の作成は省いています。
パフォーマンス面で検討する価値がありそうなのは以下の定義でしょうか。
表AVT_CALENDARの主キー定義
alter table avt_calendar modify (row_id primary key);
表AVT_EMPLOYEESの主キー定義
alter table avt_employees modify (row_wid primary key);
表AVT_EMP_SALESの列DATE_WIDへのビットマップ結合索引
create bitmap index avt_emp_sales_date_bjix
on avt_emp_sales(avt_calendar.row_wid)
from avt_emp_sales, avt_calendar
where avt_emp_sales.date_wid = avt_calendar.row_wid
nologging compute statistics;
表AVT_EMP_SALESの列EMP_WIDへのビットマップ結合索引
create bitmap index avt_emp_sales_emp_bjix
on avt_emp_sales(avt_employees.row_wid)
from avt_emp_sales, avt_employees
where avt_emp_sales.emp_wid = avt_employees.row_wid
nologging compute statistics;
今回作成したAPEXアプリケーションのエクスポートを以下に置きました。事前に準備が必要なので、分析ビューを作成するDDLは含んでいません。
https://github.com/ujnak/apexapps/blob/master/exports/analyticviewsample.sql
Oracle APEXのアプリケーション作成の参考になれば幸いです。
完
