分析ビュー・オブジェクト

26.1 分析ビューについて

が分析ビューでは、ファクト・データのソースを指定して、計算またはデータに対して実行する他の分析操作を説明するメジャーを定義します。が分析ビューでは、分析ビューの行を定義する属性ディメンションおよび階層も指定します。

分析ビューのメジャーは、ファクト表の列から直接導出されるか、計算される式です。分析ビューの問合せによって生成される行は、ファクト表の行、および階層によって提供される集計行によって判別されます。メジャーの計算は分析ビューの列に影響します。

分析ビューのセル(または行)は、各階層のMEMBER_UNIQUE_NAME属性の組合せによって一意に識別されます。分析ビューのメタデータには、階層の属性ディメンションの列に結合されるファクト表の列に関する情報が含まれています。

分析ビューは、次の項目を指定することによって定義します。

データソース
データ・ソースのファクト列
ファクト列に対応する属性、および分析ビューの行が判別される属性を持つ属性ディメンション
分析ビューに関係する階層
任意の数のメジャー

分析ビューには次のオプションの特性もあります。

メタデータまたはそれ自体をアプリケーション・コンテナと共有するように指定できます
1つ以上のメジャーを集計した値のキャッシュとしてメテリアライズド・ビューを指定できます。

26.2 属性ディメンションおよび分析ビュー

階層によって判別される属性ディメンションは、分析ビューにディメンション表の列を提供します。

通常、スター・スキーマの各ディメンション表に1つの属性ディメンションがあります。

属性ディメンションは、分析ビューの定義のdim_key句を使用して指定します。オプションの別名を指定できます。これは、同じディメンションを複数回使用する場合や、異なるスキーマからの同じ名前のディメンションを使用する場合に必要になります。

dim_key句には、KEYキーワードとともに、使用するデータ・ソースの1つ以上のファクト列を指定します。REFERENCES句には、KEYを結合する属性を指定します。属性は、レベルのキー属性または代替キー属性である必要があります。各インスタンスに一意の別名を指定すれば、同じ属性ディメンションを複数回指定できます。

各dim_key句には、HIERARCHIES句に指定した少なくとも1つの階層を含める必要があります。分析ビューのデフォルトの階層として1つの階層を指定できます。指定しない場合は、定義内の最初の階層がデフォルトとなります。

26.3 分析ビューの階層

dim_key句に指定される各属性ディメンションについて、それを使用する少なくとも1つの階層を分析ビューに含める必要があります。

各階層は、分析ビューのコンテンツ全体の各コンポーネントを形成します。分析ビューに指定された各属性ディメンションには、デフォルト階層として指定されている関連付けられた1つの階層があります。デフォルトの階層は、hier_ref句のDEFAULTキーワードを使用して指定できます。指定しない場合は、リスト内の最初の階層がデフォルトとなります。

分析ビューに問い合せたときに、問合せのFROM句のHIERARCHIESフレーズで参照されていないすべての階層は、その階層の集計値を表す単一のメンバーに削減されます。そのような階層の列は問合せで参照できますが、1つの行のみが分析ビューによって公開されます。その階層で選択されるメンバーは、同じ属性ディメンションを使用して定義された他の階層によって異なります。

各階層にはALLメンバーが含まれています。これは、ALLレベルの唯一のメンバーであり、階層の単一のルート・メンバーです(つまり、ALLは階層の他のすべてのメンバーの祖先です)。このため、階層の特定のレベルのメンバーによって他の階層の明示的に定義されているレベルのメンバーが判別されない場合でも、少なくともALLメンバーは必ず判別されます。

問合せでは、属性ディメンションごとに1つのデフォルトの階層のみを含めます。デフォルトの階層をオーバーライドして、属性ディメンションの一連のデフォルトの階層を指定できます。分析ビューの一部の属性ディメンションには複数の階層を指定し、他の属性ディメンションには階層を指定しないようにすることもできます。デフォルトの階層をオーバーライドするには、SELECT文のFROM句で、HIERARCHIESフレーズを使用して1つ以上の階層を指定します。

26.4 分析ビューのメジャー

分析ビューのメジャーには、ファクト・データと計算、またはデータに対して実行する他の操作を指定します。

分析ビューの定義では、ベース・メジャーまたは計算済メジャーを指定できます。

ベース・メジャー

ベース・メジャーはファクト表の列への参照です。オプションで分析ビューのデフォルトの集計方法をオーバーライドするmeas_aggregate_clauseを指定できます。各ベース・メジャーにはデフォルトの集計を指定できます。集計には、SUM、AVGなどの単純な操作、または属性ディメンションによって異なる複雑にネストした操作を使用できます。

default_aggregate_clauseを使用すると、meas_aggregate_clauseがないベース・メジャーにデフォルトの集計方法を指定できます。default_aggregate_clauseのデフォルト値はSUMです。

計算済メジャー

計算済メジャーは式であり、ユーザー定義の式、または多くの事前定義済のアナリティック計算のうちの1つを使用できます。計算済メジャーの式には、他のメジャー、行関数および階層関数を含めることができます。階層関数では、階層内の関連するメンバーの識別および処理に基づいて計算できます。式は分析ビューの他のメジャーを参照することもありますが、ファクト列を参照しないこともあります。計算では他のメジャーを参照できるため、ネストを使用して複雑な計算を簡単に作成できます。

計算済メジャーの式の定義では、分析ビューのメジャーを定義した順序に関係なく、分析ビューの他のメジャーを使用できます。唯一の制限は、計算に循環を使用できないことです。

計算済メジャーの式のカテゴリを次に示します。

分析ビューのメジャー式
分析ビューの単純式
単一行関数式
複合式
日時式
期間式

分析ビューのメジャーの式には、次のような操作があります。

LEADおよびLAG
関連するメンバー
ウィンドウの計算
シェア
修飾データ参照(QDR)

26.5 分析ビューの作成

分析ビューを作成する場合は、1つ以上の階層、および各階層に結合できる少なくとも1つのメジャー列を持つファクト表を指定します。

単純な分析ビューの作成

分析ビューには、ファクト表への参照、および階層に結合できるメジャーがある必要があります。

例26-1 単純な分析ビューの作成

この分析ビューでは、TIME_HIER階層およびSALE_FACT表が使用されています。単一のメジャーSALESが含まれています。

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING sales_fact                      -- Refers to the SALES_FACT table
DIMENSION BY                          -- List of attribute dimensions
  (time_attr_dim                      -- TIME_ATTR_DIM attribute dimension
    KEY month_id REFERENCES month_id  -- Dimension KEY joins to fact column
    HIERARCHIES (                     -- List of hierarchies that use 
      time_hier DEFAULT))             -- the attribute dimension
MEASURES                              -- List of measures
 (sales FACT sales)                   -- SALES measure references SALES column
DEFAULT MEASURE SALES;                -- Default measure of the analytic view

分析ビューから選択する問合せにフィルタがない場合は、大量の行が返される可能性があります。ただし、この問合せでは、SALES_AV分析ビューには86行のみを返す単一の階層が含まれています。

SELECT *
  FROM sales_av HIERARCHIES(time_hier)
  ORDER BY time_hier.hier_order;

返される値の一部を次に示します。

図simple_av_sel.pngの説明

別のベース・メジャーの追加

分析ビューに別のベース・メジャーを追加するには、MEASURESリストにそのメジャーを含めます。

例26-2 分析ビューへのベース・メジャーの追加

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING sales_fact
DIMENSION BY
  (time_attr_dim
    KEY month_id REFERENCES month_id
    HIERARCHIES (
      time_hier DEFAULT))
MEASURES
 (sales FACT sales,
  units FACT units)                   -- Add the UNITS base measure
DEFAULT MEASURE SALES;

分析ビューの問合せでは大量の行が返されることがあるため、通常、問合せではフィルタを使用して結果を制限します。この問合せは、WHERE句で期間をYEARレベルにフィルタ処理し、そのレベルのSALESおよびUNITSのデータのみが返されるようにしています。

SELECT time_hier.member_name as TIME,
 sales,
 units
FROM
 sales_av HIERARCHIES(time_hier)
WHERE time_hier.level_name = 'YEAR'
ORDER BY time_hier.hier_order;

返される値は次のとおりです。

図av_sel_2measures.pngの説明

分析ビューへの階層の追加

通常、分析ビューには1つ以上の属性ディメンションの複数の階層があります。

例26-3 分析ビューへの階層の追加

この例は、属性ディメンションおよび階層を分析ビューのDIMENSION BYリストに追加しています。

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING sales_fact
DIMENSION BY
  (time_attr_dim
    KEY month_id REFERENCES month_id
    HIERARCHIES (
      time_hier DEFAULT),
   product_attr_dim
    KEY category_id REFERENCES category_id
    HIERARCHIES (
      product_hier DEFAULT),
   geography_attr_dim
    KEY state_province_id 
    REFERENCES state_province_id
    HIERARCHIES (
      geography_hier DEFAULT)
   )
MEASURES
 (sales FACT sales,
  units FACT units
  )
DEFAULT MEASURE SALES;

次の問合せは、PRODUCT_HIER階層およびGEOGRAPHY_HIER階層をFROM句のHIERARCHIESフェーズに追加しています。

SELECT time_hier.member_name AS Time,
 product_hier.member_name AS Product,
 geography_hier.member_name AS Geography,
 sales,
 units
FROM
 sales_av HIERARCHIES (time_hier, product_hier, geography_hier)
WHERE time_hier.level_name in ('YEAR')
  AND product_hier.level_name in ('DEPARTMENT')
  AND geography_hier.level_name in ('REGION')
ORDER BY time_hier.hier_order,
  product_hier.hier_order,
  geography_hier.hier_order;

問合せは50行を戻します。次の図は、最初の20行のみを示しています。

図av_sel_all_hiers.pngの説明

例に使用されている表、分析ビュー・コンポーネント・オブジェクトおよび問合せを作成するSQLスクリプトは、Oracle Live SQL Webサイト(https://livesql.oracle.com/apex/livesql/file/index.html)で表示および実行できます。

26.6 計算済メジャーの例

計算済メジャーは、分析ビューのMEASURES句にmeasure_name AS (式)の形式で追加する式です。

LAG式の追加

この例は、LAG操作を使用する計算済メジャーをSALES_AV分析ビューに追加しています。

例26-4 LAG式の追加

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING sales_fact
DIMENSION BY
  (time_attr_dim
    KEY month_id REFERENCES month_id
    HIERARCHIES (
      time_hier DEFAULT),
   product_attr_dim
    KEY category_id REFERENCES category_id
    HIERARCHIES (
      product_hier DEFAULT),
   geography_attr_dim
    KEY state_province_id REFERENCES state_province_id
    HIERARCHIES (
      geography_hier DEFAULT)
   )
MEASURES
 (sales FACT sales,
  units FACT units,
  sales_prior_period AS       -- Add a calculated measure.
    (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1))
  )
DEFAULT MEASURE SALES;

YEARレベルとQUARTERレベルでSALESメジャーとSALES_PRIOR_PERIODメジャーを選択します。

SELECT time_hier.member_name as TIME,
 sales,
 sales_prior_period
FROM
 sales_av HIERARCHIES(time_hier)
WHERE time_hier.level_name IN ('YEAR','QUARTER')
ORDER BY time_hier.hier_order;

この問合せ結果の一部では、LAG式によって同じレベル内の前の期間が返されています。

図av_calc_meas_lag.pngの説明

SHARE OF式

シェアのメジャーは、親の行、祖先の行または現在のレベルのすべての行と現在の行の比率を計算します。たとえば、地理的メンバーとそのメンバーの親の比率です。シェアのメジャーは、SHARE OF式を使用して指定します。

例26-5 SHARE OF式の使用

この例は、SHARE OF操作を使用する計算済メジャーをSALES_AV分析ビューに追加しています。

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING sales_fact
DIMENSION BY
  (time_attr_dim
    KEY month_id REFERENCES month_id
    HIERARCHIES (
      time_hier DEFAULT),
   product_attr_dim
    KEY category_id REFERENCES category_id
    HIERARCHIES (
      product_hier DEFAULT),
   geography_attr_dim
    KEY state_province_id REFERENCES state_province_id
    HIERARCHIES (
      geography_hier DEFAULT)
   )
MEASURES
 (sales FACT sales,
  units FACT units,
  -- Share of calculations
 sales_shr_parent_prod AS
   (SHARE_OF(sales HIERARCHY product_hier PARENT)),
 sales_shr_parent_geog AS
   (SHARE_OF(sales HIERARCHY geography_hier PARENT)),
 sales_shr_region AS
   (SHARE_OF(sales HIERARCHY geography_hier LEVEL REGION)) 
  )
DEFAULT MEASURE SALES;

SALES_SHR_PARENT_PRODメジャーは、CATEGORYレベルまたはDEPARTMENTレベルのSALES値とPRODUCT_HIER階層内の親のSALES値の比率を計算します(Total Server ComputersとComputersのSALESの比率など)。

この問合せは、PRODUCT_HIER階層の各レベルのCY2014のSALESメジャーおよびSALES_SHR_PARENT_PRODメジャーを選択しています。

SELECT time_hier.member_name AS Time,
 product_hier.member_name    AS Product,
 product_hier.level_name     AS Prod_Level,
 sales,
 ROUND(sales_shr_parent_prod,2) AS sales_shr_parent_prod 
FROM
 sales_av HIERARCHIES (time_hier, product_hier)
WHERE time_hier.year_name = 'CY2014'
AND time_hier.level_name = 'YEAR'
ORDER BY product_hier.hier_order;

問合せの結果は次のとおりです。

図av_calc_meas_share.pngの説明

SALE_SHR_REGIONメジャーは、REGIONレベルのSALESに対するSTATEレベルまたはCOUNTRYレベルのSALESのシェアを計算します(たとえば、北米のSALESに対する米国カリフォルニア州のSALESの比率です)。

この問合せは、CY2014年の米国の州のSALESメジャーとSALES_SHR_REGIONメジャーの値を返します。

SELECT time_hier.member_name AS Time,
 geography_hier.member_name  AS Geography,
 geography_hier.level_name   AS Geog_Level,
 sales,
 ROUND(sales_shr_region,2)   AS sales_shr_region 
FROM
 sales_av HIERARCHIES (time_hier, geography_hier)
WHERE time_hier.year_name = 'CY2014'
AND time_hier.level_name = 'YEAR'
AND geography_hier.country_name = 'United States'
AND geography_hier.level_name = 'STATE_PROVINCE'
ORDER BY geography_hier.hier_order;

問合せの結果は次のとおりです。

図av_calc_meas_share_region.pngの説明

QDR式

qdr_expressionは、QUALIFYキーワードを使用して、メジャーの値を単一のディメンション・メンバーに制限します。例としては、CY2011年の売上、または現在の期間とCY2011の売上の相違率があります。QUALIFY式はKEY属性値を参照します。

条件付けされたデータ参照によって指定された単一のディメンション・メンバーにメジャーの値を制限します。

例26-6 QUALIFY式の使用

SALES_2011メジャーおよびSALES_PCT_CHG_2011メジャーを使用してSALES_AV分析ビューを作成します。

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING sales_fact
DIMENSION BY
  (time_attr_dim
    KEY month_id REFERENCES month_id
    HIERARCHIES (
      time_hier DEFAULT),
   product_attr_dim
    KEY category_id REFERENCES category_id
    HIERARCHIES (
      product_hier DEFAULT),
   geography_attr_dim
    KEY state_province_id REFERENCES state_province_id
    HIERARCHIES (
      geography_hier DEFAULT)
   )
MEASURES
 (sales FACT sales,
  units FACT units,
 -- Sales for CY2011
 sales_2011 AS
    (QUALIFY (sales, time_hier = year['11'])),
  -- Sales percent change from 2011.
  sales_pct_chg_2011 AS
    ((sales - (QUALIFY (sales, time_hier = year['11']))) /
    (QUALIFY (sales, time_hier = year['11'])))
  )
DEFAULT MEASURE SALES;

問合せのフィルタに関係なく、SALES_2011は常にCY2011年のデータを返します。SALES_PCT_CHG_2011メジャーは、現在の期間とCY2011の相違率を計算します。

この問合せは、YEARレベルおよびREGIONレベルでSALES、SALES_2011およびSALES_PCT_CHG_2011を選択します。

SELECT time_hier.member_name   AS Time,
 geography_hier.member_name    AS Geography,
 sales,
 sales_2011,
 ROUND(sales_pct_chg_2011,2) as sales_pct_chg_2011
FROM
 sales_av HIERARCHIES (time_hier, geography_hier)
WHERE time_hier.level_name = 'YEAR'
AND geography_hier.level_name = 'REGION'
ORDER BY geography_hier.hier_order,
 time_hier.hier_order;

問合せ結果の一部を示します。各行のSALES_2011には、CY2011のSALESが返されています。

図av_calc_meas_qualify.pngの説明

26.7 属性のレポート

階層の属性ディメンションの属性を使用し、分析ビューでそのデータを集計できます。

属性を使用して、データをフィルタ処理したり、レポートで表示したりできます。属性ごとにデータを取得(集計)することもできます。属性を使用して分析ビューに計算済メジャーを作成できます。分析ビューにはその属性の集計行が示されます。

例26-7 SEASON属性の使用

この例は、SEASONおよびSEASON_ORDERを属性として持つ属性ディメンションを最初に作成しています。これにより、階層および分析ビューでそれらの属性のメタデータを再使用して、属性を他のレベルに関連付けることができます。たとえば、SEASONはMONTH値によって決まります。

-- Create a time attribute dimension with a SEASON attribute.
CREATE OR REPLACE ATTRIBUTE DIMENSION time_attr_dim
DIMENSION TYPE TIME
USING time_dim
ATTRIBUTES
 (year_id,
  year_name,
  year_end_date,
  quarter_id,
  quarter_name,
  quarter_end_date,
  month_id,
  month_name,
  month_long_name,
  month_end_date,
  season,
  season_order)
LEVEL month
  LEVEL TYPE MONTHS
  KEY month_id
  MEMBER NAME month_name
  MEMBER CAPTION month_name
  MEMBER DESCRIPTION month_long_name
  ORDER BY month_end_date
  DETERMINES (quarter_id, season, season_order)
LEVEL quarter
  LEVEL TYPE QUARTERS
  KEY quarter_id
  MEMBER NAME quarter_name
  MEMBER CAPTION quarter_name
  MEMBER DESCRIPTION quarter_name
  ORDER BY quarter_end_date
  DETERMINES (year_id)
LEVEL year
  LEVEL TYPE YEARS
  KEY year_id
  MEMBER NAME year_name
  MEMBER CAPTION year_name
  MEMBER DESCRIPTION year_name
  ORDER BY year_end_date
LEVEL season
  LEVEL TYPE QUARTERS
  KEY season
  MEMBER NAME season
  MEMBER CAPTION season
  MEMBER DESCRIPTION season
  ORDER BY season_order;

MONTHがSEASONの子である階層を作成します。

CREATE OR REPLACE HIERARCHY time_season_hier
USING time_attr_dim 
  (month CHILD OF
   season);

TIME_SEASON_HIER階層からデータを選択します。

SELECT member_name,
  member_unique_name,
  level_name,
  hier_order
FROM time_season_hier
ORDER BY hier_order;

問合せの結果では、TIME_SEASON_HIER階層によってALLレベル、SEASONSおよびMONTHSの行が返されています。この図は、返された最初の20行を示しています。

図av_calc_meas_attr_report.pngの説明

次に、SEASONの集計データを提供する分析ビューを作成します。

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING sales_fact
DIMENSION BY
  (time_attr_dim
    KEY month_id REFERENCES month_id
    HIERARCHIES (
      time_hier DEFAULT,
      time_season_hier),
   product_attr_dim
    KEY category_id REFERENCES category_id
    HIERARCHIES (
      product_hier DEFAULT),
   geography_attr_dim
    KEY state_province_id 
    REFERENCES state_province_id
    HIERARCHIES (
      geography_hier DEFAULT)
   )
MEASURES
 (sales FACT sales,
  units FACT units
  )
DEFAULT MEASURE SALES;

分析ビューからYEARおよびSEASONごとにSALESを直接選択できるようになりました。この問合せは、TIME_HIER階層およびTIME_SEASON_HIER階層からYEARレベルおよびSEASONレベルで選択します。

SELECT  time_hier.member_name      AS Time, 
 time_season_hier.member_name      AS Season,
 ROUND(sales)                      AS Sales
FROM sales_av HIERARCHIES (time_hier, time_season_hier)
WHERE time_hier.level_name = 'YEAR'
  AND time_season_hier.level_name = 'SEASON'
ORDER BY time_hier.hier_order, 
  time_season_hier.hier_order;

この問合せ結果の一部は、返された最初の12行を示しています。

図av_calc_meas_season_sales.pngの説明

例に使用されている表、分析ビュー・コンポーネント・オブジェクトおよび問合せを作成するSQLスクリプトは、Oracle Live SQL Webサイト(https://livesql.oracle.com/apex/livesql/file/index.html)で表示および実行できます。