ADBの分析ビューをOACから使ってみる

はじめに

OAC（Oracle Analytics Cloud）では、Oracle Database 19cの分析ビューを使用することができます。
Autonomous Database（ADB）に分析ビューを作成し、OACから利用する方法を紹介します。

OACユーザーにとっての分析ビューのメリット

分析ビューとは、Oracle Databaseに作成するメタデータで、階層やメジャーに簡単にアクセスできます。
通常のリレーショナル表に格納されたデータを扱うよりも簡単に、階層化されたデータを扱うことができます。

分析ビューのその他の特長等、詳しくはこちらを参照してください。

サンプルデータの作成

まずは、ADB（ワークロード・タイプは「データ・ウェアハウス」にしました）にサンプルデータを作成します。

SHJPの準備

こちらの記事の内容を参考に、SHJPスキーマを作成しサンプルデータを作成しました。

分析ビューを作成

SHJPに必要な権限を割り当てます。

GRANT CREATE ATTRIBUTE DIMENSION TO SHJP;
GRANT CREATE HIERARCHY TO SHJP;
GRANT CREATE ANALYTIC VIEW TO SHJP;

SHJPスキーマに顧客階層用のビューを作成します。

CREATE OR REPLACE VIEW SHJP.customers_dim_view AS
SELECT
  a.cust_id,
  a.cust_last_name || ', ' || a.cust_first_name as customer_name,
  a.cust_city || ', ' || a.cust_state_province || ', ' || a.country_id as city_id,
  a.cust_city as city_name,
  a.cust_state_province || ', ' || a.country_id as state_province_id,
  a.cust_state_province as state_province_name,
  b.country_id,
  b.country_name,
  b.country_subregion as subregion,
  b.country_region as region
FROM SHJP.customers a, SHJP.countries b
where a.country_id = b.country_id;

続いて、同じくSHJPスキーマに属性ディメンションと階層を作成します。

CREATE ATTRIBUTE DIMENSION SHJP.TIMES_DIM 
  DIMENSION TYPE TIME 
      CAPTION '時間' 
      DESCRIPTION '時間' 
  USING SHJP.TIMES 
 ATTRIBUTES (
    TIME_ID AS TIME_ID 
      CAPTION '日' 
      DESCRIPTION '日' ,
    CALENDAR_MONTH_DESC AS CALENDAR_MONTH_DESC 
      CAPTION '月' 
      DESCRIPTION '月' ,
    CALENDAR_QUARTER_DESC AS CALENDAR_QUARTER_DESC 
      CAPTION '四半期' 
      DESCRIPTION '四半期' ,
    CALENDAR_YEAR AS CALENDAR_YEAR 
      CAPTION '年' 
      DESCRIPTION '年' 
  )
  LEVEL 日 
    LEVEL TYPE DAYS 
      CAPTION '日' 
      DESCRIPTION '日' 
    KEY ( "TIME_ID") 
    MEMBER NAME TO_CHAR(TIME_ID) 
    MEMBER CAPTION TO_CHAR(TIME_ID) 
    MEMBER DESCRIPTION TO_CHAR(TIME_ID) 
 ORDER BY MIN TIME_ID ASC NULLS LAST 
    DETERMINES ( "CALENDAR_MONTH_DESC") 
  LEVEL 月 
    LEVEL TYPE MONTHS 
      CAPTION '月' 
      DESCRIPTION '月' 
    KEY ( "CALENDAR_MONTH_DESC") 
    MEMBER NAME CALENDAR_MONTH_DESC 
    MEMBER CAPTION CALENDAR_MONTH_DESC 
    MEMBER DESCRIPTION CALENDAR_MONTH_DESC 
 ORDER BY MIN CALENDAR_MONTH_DESC ASC NULLS LAST 
    DETERMINES ( "CALENDAR_QUARTER_DESC") 
  LEVEL 四半期 
    LEVEL TYPE QUARTERS 
      CAPTION '四半期' 
      DESCRIPTION '四半期' 
    KEY ( "CALENDAR_QUARTER_DESC") 
    MEMBER NAME CALENDAR_QUARTER_DESC 
    MEMBER CAPTION CALENDAR_QUARTER_DESC 
    MEMBER DESCRIPTION CALENDAR_QUARTER_DESC 
 ORDER BY MIN CALENDAR_QUARTER_DESC ASC NULLS LAST 
    DETERMINES ( "CALENDAR_YEAR") 
  LEVEL 年 
    LEVEL TYPE YEARS 
      CAPTION '年' 
      DESCRIPTION '年' 
    KEY ( "CALENDAR_YEAR") 
    MEMBER NAME TO_CHAR(CALENDAR_YEAR) 
    MEMBER CAPTION TO_CHAR(CALENDAR_YEAR) 
    MEMBER DESCRIPTION TO_CHAR(CALENDAR_YEAR) 
 ORDER BY MIN CALENDAR_YEAR ASC NULLS LAST 
  ALL
    MEMBER NAME '全年'
    MEMBER CAPTION '全年';

CREATE HIERARCHY SHJP.時間階層 
      CAPTION '年' 
      DESCRIPTION '年' 
  USING SHJP.TIMES_DIM 
  ( "日" CHILD OF "月" CHILD OF "四半期" CHILD OF "年" );

CREATE ATTRIBUTE DIMENSION SHJP.PRODUCTS_DIM 
  DIMENSION TYPE STANDARD 
      CAPTION '製品' 
      DESCRIPTION '製品' 
  USING SHJP.PRODUCTS 
 ATTRIBUTES (
    PROD_ID AS PROD_ID 
      CAPTION '製品ID' 
      DESCRIPTION '製品ID' ,
    PROD_NAME AS PROD_NAME 
      CAPTION '製品' 
      DESCRIPTION '製品' ,
    PROD_SUBCATEGORY AS PROD_SUBCATEGORY 
      CAPTION '製品サブカテゴリ' 
      DESCRIPTION '製品サブカテゴリ' ,
    PROD_CATEGORY AS PROD_CATEGORY 
      CAPTION '製品カテゴリ' 
      DESCRIPTION '製品カテゴリ' 
  )
  LEVEL 製品カテゴリ 
    LEVEL TYPE STANDARD 
      CAPTION '製品カテゴリ' 
      DESCRIPTION '製品カテゴリ' 
    KEY ( "PROD_CATEGORY") 
    MEMBER NAME PROD_CATEGORY 
    MEMBER CAPTION PROD_CATEGORY 
 ORDER BY MIN PROD_CATEGORY ASC NULLS LAST 
  LEVEL 製品サブカテゴリ 
    LEVEL TYPE STANDARD 
      CAPTION '製品サブカテゴリ' 
      DESCRIPTION '製品サブカテゴリ' 
    KEY ( "PROD_SUBCATEGORY") 
    MEMBER NAME PROD_SUBCATEGORY 
    MEMBER CAPTION PROD_SUBCATEGORY 
 ORDER BY MIN PROD_SUBCATEGORY ASC NULLS LAST 
    DETERMINES ( "PROD_CATEGORY") 
  LEVEL 製品名 
    LEVEL TYPE STANDARD 
      CAPTION '製品名' 
      DESCRIPTION '製品名' 
    KEY ( "PROD_NAME") 
    MEMBER NAME PROD_NAME 
    MEMBER CAPTION PROD_NAME 
 ORDER BY MIN PROD_NAME ASC NULLS FIRST 
    DETERMINES ( "PROD_SUBCATEGORY") 
  LEVEL 製品ID 
    LEVEL TYPE STANDARD 
      CAPTION '製品ID' 
      DESCRIPTION '製品ID' 
    KEY ( "PROD_ID") 
    MEMBER NAME TO_CHAR("PROD_ID") 
    MEMBER CAPTION TO_CHAR("PROD_ID") 
    MEMBER DESCRIPTION PROD_NAME 
 ORDER BY MIN PROD_ID ASC NULLS LAST 
    DETERMINES ( "PROD_NAME") 
  ALL
    MEMBER NAME '全製品';

CREATE HIERARCHY SHJP.製品階層 
      CAPTION '製品' 
      DESCRIPTION '製品' 
  USING SHJP.PRODUCTS_DIM 
  ( "製品ID" CHILD OF "製品名" CHILD OF "製品サブカテゴリ" CHILD OF "製品カテゴリ" );

CREATE ATTRIBUTE DIMENSION SHJP.CUSTOMERS_DIM 
  DIMENSION TYPE STANDARD 
      CAPTION '顧客' 
      DESCRIPTION '顧客' 
  USING SHJP.CUSTOMERS_DIM_VIEW 
 ATTRIBUTES (
    CUST_ID AS CUST_ID 
      CAPTION '顧客ID' 
      DESCRIPTION '顧客ID' ,
    CUSTOMER_NAME AS CUSTOMER_NAME 
      CAPTION '顧客' 
      DESCRIPTION '顧客' ,
    CITY_NAME AS CITY_NAME 
      CAPTION '市' 
      DESCRIPTION '市' ,
    STATE_PROVINCE_NAME AS STATE_PROVINCE_NAME 
      CAPTION '州' 
      DESCRIPTION '州または都道府県' ,
    COUNTRY_NAME AS COUNTRY_NAME 
      CAPTION '国' 
      DESCRIPTION '国' ,
    SUBREGION AS SUBREGION 
      CAPTION 'サブ地域' 
      DESCRIPTION 'サブ地域' ,
    REGION AS REGION 
      CAPTION '地域' 
      DESCRIPTION '地域' 
  )
  LEVEL 地域 
    LEVEL TYPE STANDARD 
      CAPTION '地域' 
      DESCRIPTION '地域' 
    KEY ( "REGION") 
    MEMBER NAME REGION 
    MEMBER CAPTION REGION 
 ORDER BY MIN REGION ASC NULLS LAST 
  LEVEL サブ地域 
    LEVEL TYPE STANDARD 
      CAPTION 'サブ地域' 
      DESCRIPTION 'サブ地域' 
    KEY ( "SUBREGION") 
    MEMBER NAME SUBREGION 
    MEMBER CAPTION SUBREGION 
 ORDER BY MIN SUBREGION ASC NULLS LAST 
    DETERMINES ( "REGION") 
  LEVEL 国 
    LEVEL TYPE STANDARD 
      CAPTION '国' 
      DESCRIPTION '国' 
    KEY ( "COUNTRY_NAME") 
    MEMBER NAME COUNTRY_NAME 
    MEMBER CAPTION COUNTRY_NAME 
 ORDER BY MIN COUNTRY_NAME ASC NULLS FIRST 
    DETERMINES ( "SUBREGION") 
  LEVEL 州 
    LEVEL TYPE STANDARD 
      CAPTION '州' 
      DESCRIPTION '州または都道府県' 
    KEY ( "STATE_PROVINCE_NAME") 
    MEMBER NAME STATE_PROVINCE_NAME 
    MEMBER CAPTION STATE_PROVINCE_NAME 
 ORDER BY MIN STATE_PROVINCE_NAME ASC NULLS FIRST 
    DETERMINES ( "COUNTRY_NAME") 
  LEVEL 市 
    LEVEL TYPE STANDARD 
      CAPTION '市' 
      DESCRIPTION '市' 
    KEY ( "CITY_NAME") 
    MEMBER NAME CITY_NAME 
    MEMBER CAPTION CITY_NAME 
 ORDER BY MIN CITY_NAME ASC NULLS FIRST 
    DETERMINES ( "STATE_PROVINCE_NAME") 
  LEVEL 顧客名 
    LEVEL TYPE STANDARD 
      CAPTION '顧客名' 
      DESCRIPTION '顧客名' 
    KEY ( "CUSTOMER_NAME") 
    MEMBER NAME CUSTOMER_NAME 
    MEMBER CAPTION CUSTOMER_NAME 
 ORDER BY MIN COUNTRY_NAME ASC NULLS FIRST 
    DETERMINES ( "CITY_NAME") 
  LEVEL 顧客ID 
    LEVEL TYPE STANDARD 
      CAPTION '顧客ID' 
      DESCRIPTION '顧客ID' 
    KEY ( "CUST_ID") 
    MEMBER NAME TO_CHAR("CUST_ID") 
    MEMBER CAPTION TO_CHAR("CUST_ID") 
    MEMBER DESCRIPTION CUSTOMER_NAME 
 ORDER BY MIN CUSTOMER_NAME ASC NULLS LAST 
    DETERMINES ( "CUSTOMER_NAME") 
  ALL
    MEMBER NAME '全顧客';

CREATE HIERARCHY SHJP.顧客階層 
      CAPTION '顧客' 
      DESCRIPTION '顧客' 
  USING SHJP.CUSTOMERS_DIM 
  ( "顧客ID" CHILD OF "顧客名" CHILD OF "市" CHILD OF "州" CHILD OF "国" CHILD OF "サブ地域" CHILD OF "地域" );

CREATE ATTRIBUTE DIMENSION SHJP.CHANNELS_DIM 
  DIMENSION TYPE STANDARD 
      CAPTION 'チャネル' 
      DESCRIPTION 'チャネル' 
  USING SHJP.CHANNELS 
 ATTRIBUTES (
    CHANNEL_ID AS CHANNEL_ID 
      CAPTION 'チャネルID' 
      DESCRIPTION 'チャネルID' ,
    CHANNEL_DESC AS CHANNEL_DESC 
      CAPTION 'チャネル' 
      DESCRIPTION 'チャネル' ,
    CHANNEL_CLASS AS CHANNEL_CLASS 
      CAPTION 'チャネルカテゴリ' 
      DESCRIPTION 'チャネルカテゴリ' 
  )
  LEVEL チャネルカテゴリ 
    LEVEL TYPE STANDARD 
      CAPTION 'チャネルカテゴリ' 
      DESCRIPTION 'チャネルカテゴリ' 
    KEY ( "CHANNEL_CLASS") 
    MEMBER NAME CHANNEL_CLASS 
    MEMBER CAPTION CHANNEL_CLASS 
 ORDER BY MIN CHANNEL_CLASS ASC NULLS LAST 
  LEVEL チャネル 
    LEVEL TYPE STANDARD 
      CAPTION 'チャネル' 
      DESCRIPTION 'チャネル' 
    KEY ( "CHANNEL_DESC") 
    MEMBER NAME CHANNEL_DESC 
    MEMBER CAPTION CHANNEL_DESC 
 ORDER BY MIN CHANNEL_DESC ASC NULLS LAST 
    DETERMINES ( "CHANNEL_CLASS") 
  LEVEL チャネルID 
    LEVEL TYPE STANDARD 
      CAPTION 'チャネルID' 
      DESCRIPTION 'チャネルID' 
    KEY ( "CHANNEL_ID") 
    MEMBER NAME TO_CHAR("CHANNEL_ID") 
    MEMBER CAPTION TO_CHAR("CHANNEL_ID") 
    MEMBER DESCRIPTION CHANNEL_DESC 
 ORDER BY MIN CHANNEL_ID ASC NULLS FIRST 
    DETERMINES ( "CHANNEL_DESC") 
  ALL
    MEMBER NAME '全チャネル';

CREATE HIERARCHY SHJP.チャネル階層 
      CAPTION 'チャネル' 
      DESCRIPTION 'チャネル' 
  USING SHJP.CHANNELS_DIM 
  ( "チャネルID" CHILD OF "チャネル" CHILD OF "チャネルカテゴリ" );

CREATE ATTRIBUTE DIMENSION SHJP.PROMOTIONS_DIM 
  DIMENSION TYPE STANDARD 
      CAPTION 'プロモーション' 
      DESCRIPTION 'プロモーション' 
  USING SHJP.PROMOTIONS 
 ATTRIBUTES (
    PROMO_ID AS PROMO_ID 
      CAPTION 'プロモーションID' 
      DESCRIPTION 'プロモーションID' ,
    PROMO_NAME AS PROMO_NAME 
      CAPTION 'プロモーション' 
      DESCRIPTION 'プロモーション' ,
    PROMO_SUBCATEGORY AS PROMO_SUBCATEGORY 
      CAPTION 'プロモーション サブカテゴリ' 
      DESCRIPTION 'プロモーション サブカテゴリ' ,
    PROMO_CATEGORY AS PROMO_CATEGORY 
      CAPTION 'プロモーション カテゴリ' 
      DESCRIPTION 'プロモーション カテゴリ' 
  )
  LEVEL プロモーションカテゴリ 
    LEVEL TYPE STANDARD 
      CAPTION 'プロモーション カテゴリ' 
      DESCRIPTION 'プロモーション カテゴリ' 
    KEY ( "PROMO_CATEGORY") 
    MEMBER NAME PROMO_CATEGORY 
    MEMBER CAPTION PROMO_CATEGORY 
 ORDER BY MIN PROMO_CATEGORY ASC NULLS LAST 
  LEVEL プロモーションサブカテゴリ 
    LEVEL TYPE STANDARD 
      CAPTION 'プロモーション サブカテゴリ' 
      DESCRIPTION 'プロモーション サブカテゴリ' 
    KEY ( "PROMO_SUBCATEGORY") 
    MEMBER NAME PROMO_SUBCATEGORY 
    MEMBER CAPTION PROMO_SUBCATEGORY 
 ORDER BY MIN PROMO_SUBCATEGORY ASC NULLS LAST 
    DETERMINES ( "PROMO_CATEGORY") 
  LEVEL プロモーション名 
    LEVEL TYPE STANDARD 
      CAPTION 'プロモーション' 
      DESCRIPTION 'プロモーション' 
    KEY ( "PROMO_NAME") 
    MEMBER NAME PROMO_NAME 
    MEMBER CAPTION PROMO_NAME 
 ORDER BY MIN PROMO_NAME ASC NULLS FIRST 
    DETERMINES ( "PROMO_SUBCATEGORY") 
  LEVEL プロモーションID 
    LEVEL TYPE STANDARD 
      CAPTION 'プロモーションID' 
      DESCRIPTION 'プロモーションID' 
    KEY ( "PROMO_ID") 
    MEMBER NAME TO_CHAR("PROMO_ID") 
    MEMBER CAPTION TO_CHAR("PROMO_ID") 
    MEMBER DESCRIPTION PROMO_NAME 
 ORDER BY MIN PROMO_ID ASC NULLS LAST 
    DETERMINES ( "PROMO_NAME") 
  ALL
    MEMBER NAME '全プロモーション';

CREATE HIERARCHY SHJP.プロモーション階層 
      CAPTION 'プロモーション' 
      DESCRIPTION 'プロモーション' 
  USING SHJP.PROMOTIONS_DIM 
  ( "プロモーションID" CHILD OF "プロモーション名" CHILD OF "プロモーションサブカテゴリ" CHILD OF "プロモーションカテゴリ" );

販売履歴分析という名前の分析ビューを作成します。

CREATE ANALYTIC VIEW SHJP.販売履歴分析 
      CAPTION '販売履歴 (SHサンプルスキーマ)' 
      DESCRIPTION '時間、製品、顧客、チャネル、プロモーション別の販売履歴' 
 USING SHJP.SALES 
 DIMENSION BY ( 
  TIMES_DIM AS TIMES_DIM KEY ( "TIME_ID") REFERENCES ( "TIME_ID")
    HIERARCHIES ( 時間階層 AS 時間階層 DEFAULT ), 
  CHANNELS_DIM AS CHANNELS_DIM KEY ( "CHANNEL_ID") REFERENCES ( "CHANNEL_ID")
    HIERARCHIES ( チャネル階層 AS チャネル階層 DEFAULT ), 
  PRODUCTS_DIM AS PRODUCTS_DIM KEY ( "PROD_ID") REFERENCES ( "PROD_ID")
    HIERARCHIES ( 製品階層 AS 製品階層 DEFAULT ), 
  CUSTOMERS_DIM AS CUSTOMERS_DIM KEY ( "CUST_ID") REFERENCES ( "CUST_ID")
    HIERARCHIES ( 顧客階層 AS 顧客階層 DEFAULT ), 
  PROMOTIONS_DIM AS PROMOTIONS_DIM KEY ( "PROMO_ID") REFERENCES ( "PROMO_ID")
    HIERARCHIES ( プロモーション階層 AS プロモーション階層 DEFAULT ) 
 ) 
 MEASURES ( 
 販売金額 FACT AMOUNT_SOLD CAPTION '販売金額' 
      DESCRIPTION '販売金額' CLASSIFICATION FORMAT_STRING VALUE '999G999G999G999G999G999G990', 
 販売数量 FACT QUANTITY_SOLD CAPTION '販売数量' 
      DESCRIPTION '販売数量' CLASSIFICATION FORMAT_STRING VALUE '999G999G999G999G999G999G990', 
 顧客数 FACT CUST_ID AGGREGATE BY COUNT CAPTION '顧客数' 
      DESCRIPTION '顧客数' CLASSIFICATION FORMAT_STRING VALUE '999G999G999G999G999G999G990' 
 )
 DEFAULT MEASURE 販売金額 
 DEFAULT AGGREGATE BY SUM;

検索できることを確認しておきます。

SELECT
  時間階層.member_name AS 時間,
  製品階層.member_name AS 製品,
  顧客階層.member_name AS 顧客,
  販売数量
FROM SHJP.販売履歴分析
HIERARCHIES (
  時間階層,
  製品階層,
  顧客階層
)
WHERE
  時間階層.level_name = '年' AND
  製品階層.level_name = '製品カテゴリ' AND
  顧客階層.level_name = '地域'

OACから分析ビューに接続

ADBのクライアント資格証明（ウォレット）をダウンロードし、任意の場所に展開します。

「接続」を作成

OACにログインし、「新規」をクリックして「接続」を選択します。

Oracle Analytic Viewsを選択します。

必要な情報を入力し「保存」をクリックします。

ホスト名、ポート、サービス名は、クライアント資格証明のzipを展開した場所にある tnsnames.ora の内容を参考にしてください。
クライアント・ウォレットは、同じく cwallet.sso ファイルを選択します。

データセットの作成

「新規」から「データセット」を選択します。

作成しておいた「接続」をクリックします。

「SHJP.販売履歴分析」をクリックして選択し、「追加」をクリックします。

データセットが作成されました。
分析ビューを基にしたデータセットはプレビュー表示をサポートしていません。
続いて「ワークブックの作成」をクリックします。

ワークブックを作成

メジャーは「Measures」にまとめられ、それぞれのディメンションがフォルダとして表示されていることがわかります。階層は、階層列として認識しています。

「販売数量」「製品カテゴリ」をCtrlキーを押しながら同時に選択し、そのままキャンバスにドラッグ＆ドロップします。

「ソフトウェア・その他」をダブルクリックしてみます。

階層定義に従って、ドリルダウンできました。

ピボットで階層列を使用してみました。

関連情報

• Oracle Autonomous Data Warehouseの分析ビューからのセマンティック・モデルの作成