日付ディメンジョンの定義 - 日本語キャプション付き

avt_date_dim_with_caption.sql

create or replace attribute dimension avt_date_dim
    /* ALL_ATTRIBUTE_DIM_CLASS */
    classification caption value 'Date Dimenstion'
    classification caption value '日付ディメンジョン' language 'JAPANESE'
using avt_calendar
attributes(
    /* ALL_ATTRIBUTE_DIM_ATTR_CLASS  - ALL_ANALYTIC_VIEW_ATTR_CLASS */
    year_name
        classification caption value 'Year Name'
        classification caption value '年の名前' language 'JAPANESE'
    , year_name_ja
        classification caption value 'Year Name in Japanese'
        classification caption value '年の名前' language 'JAPANESE'
    , qtr_name
        classification caption value 'Quarter Name'
        classification caption value '四半期の名前' language 'JAPANESE'
    , qtr_name_ja
        classification caption value 'Quarter Name in Japanese'
        classification caption value '四半期の名前' language 'JAPANESE'
    , month_name
        classification caption value 'Month Name'
        classification caption value '月の名前' language 'JAPANESE'
    , month_name_ja
        classification caption value 'Month Name in Japanese'
        classification caption value '月の名前' language 'JAPANESE'
    , row_wid
)
/* ALL_ATTRIBUTE_DIM_LVL_CLASS - ALL_ANALYTIC_VIEW_LEVEL_CLASS */
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'
;