2022年2月28日月曜日

分析ビューの日本語対応

 分析ビュー(Analytic View)を活用する際に、レベル名やメンバー名を日本語で表示したいという要望はあるかと思います。最初からレベル名やメンバ名に日本語を使用するというのもひとつの方法ですが、今回はLiveSQL.oracle.comの以下のスクリプトを参考にして、前回の記事で作成したAPEXアプリケーションの日本語対応を行います。

Adding Multiple Language Support to Analytic Views

日付レベルレベル名マネージャー・レベルレベル名日付ディメンションメンバー名を日本語で表示します。



日付ディメンジョンの日本語名の登録


最初に日付ディメンジョンのメンバー名の日本語表記を、表AVT_CALENDARに追加します。

SQLワークショップSQLコマンドより作業を行います。最初に作成済みの表AVT_CALENDARの内容を確認します。以下のSELECT文を実行します。

select * from avt_calendar;


ROW_WIDWEEK_NUMMONTH_NAMEQTR_NAMEYEAR_NAMEが定義されています。この中で、MONTH_NAME、QTR_NAMEおよびYEAR_NAMEについて、日本語表記を登録します。追加する列はそれぞれMONTH_NAME_JAQTR_NAME_JAYEAR_NAME_JAとします。

SQLコマンドより、以下のSQLを一行ずつ実行します。

最初に列MONTH_NAME_JAを追加し、日本語表記を登録します。

alter table avt_calendar add (month_name_ja varchar2(16));
update avt_calendar set month_name_ja = year_name || '年' || month_name || '月';

QTR_NAME_JAを追加し、日本語表記を登録します。

alter table avt_calendar add (qtr_name_ja varchar2(30));
update avt_calendar set qtr_name_ja = year_name || '年第' || qtr_name || '四半期';

YEAR_NAME_JAを追加し、日本語表記を登録します。

alter table avt_calendar add (year_name_ja varchar2(8));
update avt_calendar set year_name_ja = year_name || '年';

表AVT_CALENDARを検索し、追加した列の内容を確認します。



属性ディメンジョンの更新


日付レベルを定義している属性ディメンジョンAVT_DATE_DIMを更新します。

LEVELにそれぞれCLASSIFICATIONとしてCAPTIONを定義し、日本語のレベル名を登録しています。また、MEMBER_NAMEはセッション言語に依存して、英語と日本語を切り替えています。

create or replace attribute dimension avt_date_dim
using avt_calendar
attributes(
year_name
, year_name_ja -- 追加する
, qtr_name
, qtr_name_ja -- 追加する
, month_name
, month_name_ja -- 追加する
, row_wid
)
level cal_day
classification caption value 'Day Level'
classification caption value '日レベル' language 'JAPANESE'
key row_wid member name row_wid order by row_wid determines(month_name)
level cal_month
classification caption value 'Month Level'
classification caption value '月レベル' language 'JAPANESE'
key month_name
member name
case
when SYS_CONTEXT('USERENV','LANG') = 'JA' then
month_name_ja
else
month_name
end
order by month_name determines(qtr_name)
level cal_qtr
classification caption value 'Quater Level'
classification caption value '四半期レベル' language 'JAPANESE'
key qtr_name
member name
case
when SYS_CONTEXT('USERENV','LANG') = 'JA' then
qtr_name_ja
else
qtr_name
end
order by qtr_name determines(year_name)
level cal_year
classification caption value 'Year Level'
classification caption value '年レベル' language 'JAPANESE'
key year_name
member name
case
when SYS_CONTEXT('USERENV','LANG') = 'JA' then
year_name_ja
else
year_name
end
order by year_name
/*
* マニュアルによると ALL MEMBER NAME に続いて expression となっているが、
* CASE文にすると属性ディメンジョンは作成できるが、分析ビューの検索でエラーが発生する。
*/
all member name 'Total'
;

マネージャー・レベルを定義している属性ディメンジョンAVT_EMP_DIMを更新します。

create or replace attribute dimension avt_emp_dim
using avt_employees
attributes(
row_wid
, empno
, lvl1_mgr
, lvl2_mgr
, lvl3_mgr
)
level emp
classification caption value 'Employee Level'
classification caption value '従業員レベル' language 'JAPANESE'
key row_wid member name to_char(empno) order by empno determines(lvl3_mgr)
level lvl3_mgr
classification caption value 'Level 3 Manager Level'
classification caption value '課長レベル' language 'JAPANESE'
key lvl3_mgr member name lvl3_mgr order by lvl3_mgr determines(lvl2_mgr)
level lvl2_mgr
classification caption value 'Level 2 Manager Level'
classification caption value '部長ベル' language 'JAPANESE'
key lvl2_mgr member name lvl2_mgr order by lvl2_mgr determines(lvl1_mgr)
level lvl1_mgr
classification caption value 'Level 1 Manager Level'
classification caption value '社長レベル' language 'JAPANESE'
key lvl1_mgr member name lvl1_mgr order by lvl1_mgr
all member name 'Total'
;

例えば日レベルでは、以下の記述にて英語と日本語のキャプションを設定しています。

level cal_day
classification caption value 'Day Level'
classification caption value '日レベル' language 'JAPANESE'

CLASSIFICATIONとして設定できるのは、CAPTIONDESCRIPTIONになります。それぞれ、LANGUAGE句によって言語を指定できます。

属性ディメンジョンのレベルに設定されたCAPTIONDESCRIPTIONは、ビューUSER_ATTRIBUTE_DIM_LVL_CLASSより確認できます。

select * from user_attribute_dim_lvl_class;



APEXアプリケーションの更新


日付レベル選択リストが、日本語で表示されるようにします。LOVSQL問合せを以下に置き換えます。属性ディメンジョンに定義したキャプションはビューUSER_ANALYTIC_VIEW_LEVEL_CLASSより参照しています。

select value d, depth r
from
(
select to_char(c.value) value, (l.order_num + 1) depth
from user_analytic_view_levels l join user_analytic_view_level_class c
on l.analytic_view_name = c.analytic_view_name
and l.dimension_alias = c.dimension_alias
and l.level_name = c.level_name
where 1=1
and l.analytic_view_name = 'AVT_EMP_SALES_AV'
and l.dimension_alias = 'AVT_DATE_DIM'
and l.hier_alias = 'AVT_CALENDAR_HIER'
and c.classification = 'CAPTION'
and coalesce(c.language,'AMERICAN') =
substr(SYS_CONTEXT('USERENV','LANGUAGE'),1,(instr(SYS_CONTEXT('USERENV','LANGUAGE'),'_')-1))
union
select
case
when SYS_CONTEXT('USERENV','LANG') = 'JA' then
'全体'
else
'Total'
end value
, 0 depth from dual
)
order by depth asc

ページ・デザイナにてホーム・ページを開き、ページ・アイテムP1_CAL_DEPTHを選択します。

LOVSQL問合せを入れ替えます。


ページ・アイテムP1_EMP_DEPTHを選択し、LOVSQL問合せを、以下に置き換えます。

select value d, depth r
from
(
select to_char(c.value) value, (l.order_num + 1) depth
from user_analytic_view_levels l join user_analytic_view_level_class c
on l.analytic_view_name = c.analytic_view_name
and l.dimension_alias = c.dimension_alias
and l.level_name = c.level_name
where 1=1
and l.analytic_view_name = 'AVT_EMP_SALES_AV'
and l.dimension_alias = 'AVT_EMP_DIM'
and l.hier_alias = 'AVT_EMPLOYEE_HIER'
and c.classification = 'CAPTION'
and coalesce(c.language,'AMERICAN') =
substr(SYS_CONTEXT('USERENV','LANGUAGE'),1,(instr(SYS_CONTEXT('USERENV','LANGUAGE'),'_')-1))
union
select
case
when SYS_CONTEXT('USERENV','LANG') = 'JA' then
'全体'
else
'Total'
end value
, 0 depth from dual
)
order by depth asc


以上で、分析ビューを使ったAPEXアプリケーションの日本語対応は完了です。

APEXアプリケーションのプライマリ言語を日本語から英語に変更すると、日付レベルやマネージャー・レベルおよび日付レベルのメンバーの表記が英語になります。


アプリケーションの表示を確認します。


属性ディメンジョンのレベル名の他に、以下のオブジェクトに対して、CLASSIFICATIONとしてCAPTIONおよびDESCRIPTIONを設定することができます。
  1. 属性ディメンジョン(Attribute Dimension)自体
  2. 属性ディメンジョンの属性(Attribute)
  3. 属性ディメンジョンのレベル(Level)
  4. 階層(Hierarchy)自体
  5. 階層の属性(Hierarchical Attribute)
  6. 分析ビュー(Analytic View)自体
  7. 分析ビューのディメンジョン(Dimension)
  8. 分析ビューのメジャー(Measures)
設定済みのCLASSIFICATIONを参照するために使用するビューは以下になります。

 ディクショナリ・ビュー分析ビューのディクショナリ
属性ディメンジョンALL_ATTRIBUTE_DIM_CLASS 
属性ディメンジョンの属性ALL_ATTRIBUTE_DIM_ATTR_CLASSALL_ANALYTIC_VIEW_ATTR_CLASS
属性ディメンジョンのレベルALL_ATTRIBUTE_DIM_LVL_CLASSALL_ANALYTIC_VIEW_LEVEL_CLASS
階層ALL_HIER_CLASS 
階層の属性ALL_HIER_HIER_ATTR_CLASS 
分析ビュー ALL_ANALYTIC_VIEW_CLASS
分析ビューのディメンジョン ALL_ANALYTIC_VIEW_DIM_CLASS
分析ビューのメジャー ALL_ANALYTIC_VIEW_MEAS_CLASS

定義された情報を使ってアプリケーションを多言語に対応させるには、あくまでアプリケーション側でのコーディングが必要になります。今回の例では、ページ・アイテムのLOVのSQL問合せに記述しています。

設定可能なCLASSIFICATIONのCAPTIONを定義したDDLの例になります。
  1. 属性ディメンジョンAVT_EMP_DIM
  2. 属性ディメンジョンAVT_DATE_DIM
  3. 階層AVT_EMPLOYEE_HIER
  4. 階層AVT_CALENDAR_HIER
  5. 分析ビューAVT_EMP_SALES_AV
分析ビューの日本語対応の紹介は以上になります。

LOVのSQL問合せ以外変更はありませんが、今回のAPEXアプリケーションのエクスポートを以下に置きました。
https://github.com/ujnak/apexapps/blob/master/exports/analyticviewsample_lang.sql

Oracle APEXのアプリケーション作成の参考になれば幸いです。

2022年2月25日金曜日

分析ビュー(Analytic View)を使う

 分析ビュー(Analytic View)を使ったAPEXアプリケーションを作ってみます。APEXアプリケーションの作成に使用するスキーマ定義やデータの準備は、LiveSQL.oracle.comAnalytic View Demoを元にしています。

分析ビューについては、オラクルのドキュメントを参照しています。

データ・ウェアハウス・ガイド 19c 第Ⅴ部 分析ビュー

作業には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;



分析ビューの作成に必要な権限が、APEXのワークスペース・スキーマに割り与えられました。


データの準備



分析ビューを作るためのデータを準備します。

2つの属性ディメンジョン(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)と、3つの階層を列として追加しています。

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;

全部で14名の従業員が検索されます。列LVL1_MGRは社長なので、すべての従業員で同じ人になります。ほとんどの従業員は2階層上の上司が社長で(12人)で、LVL3_MGRが自分自身つまりENAMEと同じになります。2人だけ3階層あり、LVL3_MGRとENAMEが異なっています。

個々の従業員を特定するのは主キーである列EMPNOがあれば十分ですが、列ROW_WIDが追加されています。これはファクト表のデータを自動生成する際に、EMPNOの代わりに1から14の数を割り当てていて、その列と関連付けるために追加されています。


この表を使って属性ディメンジョン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'
;


属性ディメンジョンAVT_DATE_DIMに階層AVT_CALENDAR_HIERを作成します。以下のSQLを実行します。

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_EMPUSINGで指定)を指定しています。ディメンジョン(DIMENSION BYで指定)にAVT_DATE_DIMおよびAVT_EMP_DIM、それぞれの階層としてAVT_CALENDAR_HIERAVT_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
/* 全行を集計する - 結果は1行 */
avt_calendar_hier.level_name = 'ALL'
and
avt_employee_hier.level_name = 'ALL'

上記のSELECT文は、ファクト表の全ての行を集計した結果を1行で表示します。


作成したページを実行し、対話モード・レポートの表示を確認します。


ページ・デザイナに戻り、集計結果をドリルダウンするために使用するページ・アイテムを作成します。

日付ディメンジョンのドリルダウンに使用するページ・アイテムとしてP1_CAL_DEPTHP1_CAL_PARENT_UNIQUE_NAME、マネージャー・ディメンジョンのドリルダウンに使用するページ・アイテムとしてP1_EMP_DEPTHP1_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とし、ラベルマネージャー・レベルとします。LOVSQL問合せを以下にします。

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問合せを以下に変更します。


この後、動的アクションを使って対話モード・レポートのリフレッシュを実装するため、送信するページ・アイテムとして、作成した4つのページ・アイテムP1_CAL_DEPTHP1_CAL_PARENT_UNIQUE_NAMEP1_EMP_DEPTHP1_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_NAMEEMP_MEMBER_NAMEの両方を選択し、セキュリティ特殊文字をエスケープOFFに変更します。


リンクをクリックしてレポートのドリルダウンを行う際に、ページ・アイテムP1_CAL_DEPTHやP1_EMP_DEPTHの値が破棄されます。このため画面上に警告が現れますが、この警告は無視できるため、ページ・プロパティのナビゲーション保存されていない変更の警告OFFにします。


最後に売上詳細を表示するページを作成します。

ページの作成を実行し、ページ作成ウィザードを起動します。


レポートを選択します。


対話モード・レポートを選択します。


ページ属性を設定します。ページ番号とします。これはレポートのSQLに埋め込んであるため、他の番号にするとリンクが無効になります。そのため、必ず2を指定します。ページ名売上詳細ページ・モードとしてモーダル・ダイアログを選択します。

へ進みます。


モーダル・ダイアログなので、ナビゲーションのプリファレンスはデフォルトのこのページとナビゲーション・メニュー・エントリを関連付けないのままにします。

へ進みます。


データ・ソースとしてローカル・データベースソース・タイプとして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 NameTotalをクリックします。


日付レベルCAL_YEARに変わります。ただし、表AVT_EMP_SALESには2022年の情報しかないため、集計結果は2022年で集計した一行だけになります。

Cal Member Name2022をクリックします。


日付レベルCAL_QTRに変わります。四半期毎に集計された結果が表示されます。

Cal Member Name3をクリックします。


日付レベルCAL_MONTHに変わります。親日付レベルは[CAL_QTR].&[3]となり、レポートされる月が第三四半期に限定されます。

Cal Member Name08をクリックします。


日付レベルCAL_DAYに変わります。親日付レベル[CAL_MONTH].&[08]となり、レポートされる日付が8月に限定されます。

Cal Member Name20220801をクリックします。


日付ディメンジョンで日付が末端になります。そのため、分析ビューとしてのドリルダウンではなく、ファクト表を検索した結果を表示します。

レポートのソースのSELECT文で、レベルが末端(IS_LEAFが0ではない)の場合、分析ビューをドリルダウンする代わりに、詳細ページを開くようにCASE文を記述しています。


以上で分析ビューの動作が確認できました。

マネージャーの階層も同様の手順で確認できます。

日付レベルCAL_MONTH、親日付レベル[CAL_QTR].&[1]、つまり第一四半期の月毎に、マネージャー・レベルLVL2_MGR、つまり社長直下となるすべてのマネージャーごとに売上を集計したレポートになります。


マネージャーの階層では、レベルの末端になると従業員名の代わりに従業員番号が表示されます。


分析ビューの動作を確認するための最低限のUIですが、分析ビューの使い方の参考にしていただけると幸いです。

分析ビューがあらかじめ作成されていれば、APEXアプリケーションでレポートやチャートを作成する際に、GROUP BYを使った集計関数などをSQLとして記述しなくて済みます。


分析ビューのその他の機能



本記事で紹介した内容は分析ビューの基本です。この他にも色々な機能を使ったサンプルがLiveSQL.oracle.comに載っています。

時刻の計算(Creating Time Series Calculations in Analytic View)
マテリアライズド・ビューによる集計のキャッシュ(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のアプリケーション作成の参考になれば幸いです。