Introduction

Analytic views provide a logical dimension model, mappings to physical tables and views that may be queried by applications using SQL. They provide an easy method to extend a star schema with structural and descriptive metadata, aggregate level data and measures calculations. Analytic views present aggregate data and measure calculations as pre-joined and pre-solved rows and columns, simplifying the SQL that needs to be generated by business intelligence applications.

The logical dimensional model includes structural elements such as attributes, hierarchies and measures and descriptive elements such as 'friendly' display names that can be used to represent the model to end users. For example, a hierarchy named GEOGRAPHY_HIER might be represented to a business user as 'Geography'.

The mappings to physical tables include mappings to columns uses as keys (key attributes)for data are various levels of aggregation and typically including mappings to columns that provide 'friendly' names for hierarchy values. For example a key attribute might be mapped to integer key values and descriptive attributes might be mapped to a column with text values.

Analytic views provide support using multiple languages with both the descriptive elements of the model and mappings to descriptive elements, providing business users with a reporting experience in their native or preferred language.

This tutorial will add German language support to an analytic view that uses English as the default language.

Setup

If you have not already done so, run the setup script for this tutorial by clicking on Execute the SQL required for this tutorial link which precedes the list of modules in this tutorial. This script will:

• Copy dimension tables and the fact table in the AV schema to the current schema.
• Add new columns to the dimension tables that will be used for German language data.
• Update the dimension tables with German language data.

Examining the Sample Data

The dimension tables include columns for both English and German language data values that can be used as descriptive labels in hierarchies. The German language column names can be identified by the _DE suffix.

View the values in the TIME_DIM table.

SELECT * FROM time_dim ORDER BY month_end_date;

Note that descriptive columns for months are available in English and German.

View the values in the PRODUCT_DIM table.

SELECT * FROM product_dim;

View the values in the GEOGRAPHY_DIM table.

SELECT * FROM geography_dim;

The _ID columns are used as keys and are joined to the fact table. No changes to the to fact table are needed for support additional languages.

SELECT * FROM sales_fact WHERE rownum <= 10;

Creating a Hierarchy With Only a Default Lanuage

We will start by defining an attribute dimension and hierarchy with support for only a default language and then add support for German.

If support is not added to analytic view objects for specific languages, the classifications and descriptive labels of hierarchy values will be used for all users regardless of the NLS_LANGUAGE setting of the session.

The following attribute dimension and hierarchy objects do not include support for multiple languages. Note that CLASSIFICATIONs do not include a language keyword and only one column is mapped to each MEMBER_NAME, MEMBER_CAPTION and MEMBER_DESCRIPTION property of the levels.

Create the attribute dimension for Time.

CREATE OR REPLACE ATTRIBUTE DIMENSION time_attr_dim
  CLASSIFICATION caption VALUE 'Time Attribute Dimension'
  CLASSIFICATION description VALUE 'Company standard time attributes and levels'
USING time_dim
ATTRIBUTES
 (year_id
    CLASSIFICATION caption VALUE 'Year Key Value'
    CLASSIFICATION description VALUE 'Year Key Value',
  year_name
    CLASSIFICATION caption VALUE 'Year Descriptive Value'
    CLASSIFICATION description VALUE 'Year Descriptive Value',
  quarter_id
    CLASSIFICATION caption VALUE 'Quarter Key Value'
    CLASSIFICATION description VALUE 'Quarter Key Value',
  quarter_name
    CLASSIFICATION caption VALUE 'Quarter Descriptive Value'
    CLASSIFICATION description VALUE 'Quarter Descriptive Value',
  month_id
    CLASSIFICATION caption VALUE 'Month Key Value'
    CLASSIFICATION description VALUE 'Month Key Value',
  month_name
    CLASSIFICATION caption VALUE 'Month Short Descriptive Value'
    CLASSIFICATION description VALUE 'Month Short Descriptive Value',
  month_long_name
    CLASSIFICATION caption VALUE 'Month Long Descriptive Value'
    CLASSIFICATION description VALUE 'Month Long Descriptive Value',
  month_end_date
    CLASSIFICATION caption VALUE 'Ending Date of Month Key Value'
    CLASSIFICATION description VALUE 'Ending Date of Month Key Value')
LEVEL MONTH
  CLASSIFICATION caption VALUE 'Month Level'
  CLASSIFICATION description VALUE 'Month Aggregate Groupings'
  KEY month_id
  MEMBER NAME month_name
  MEMBER CAPTION month_name
  MEMBER DESCRIPTION month_long_name
  ORDER BY month_end_date
  DETERMINES (quarter_id, month_end_date)
LEVEL QUARTER
  CLASSIFICATION caption VALUE 'Quarter Level'
  CLASSIFICATION description VALUE 'Quarter Aggregate Groupings'
  KEY quarter_id
  MEMBER NAME quarter_name
  MEMBER CAPTION quarter_name
  MEMBER DESCRIPTION quarter_name
  ORDER BY MAX month_end_date
  DETERMINES (year_id)
LEVEL YEAR
  CLASSIFICATION caption VALUE 'Year Level'
  CLASSIFICATION description VALUE 'Year Aggregate Groupings'
  KEY year_id
  MEMBER NAME year_name
  MEMBER CAPTION year_name
  MEMBER DESCRIPTION year_name
  ORDER BY MAX month_end_date;

Create the hierarchy for Time.

CREATE OR REPLACE HIERARCHY time_hier
  CLASSIFICATION caption VALUE 'Time'
  CLASSIFICATION description VALUE 'Time Hierarchy'
USING time_attr_dim
 (month  CHILD OF
 quarter CHILD OF
 year);

The following dictionary queries return only one value for each classification and the LANGUAGE column is NULL.

SELECT * FROM user_attribute_dim_class;

SELECT * FROM user_attribute_dim_attr_class;

SELECT * FROM user_attribute_dim_lvl_class;

SELECT * FROM user_hier_class;

The MEMBER_NAME, MEMBER_CAPTION and MEMBER_DESCRIPTION columns return the same values regardless of the current NLS_LANGUAGE value.

Check the current NLS_LANGUAGE value.

SELECT sys_context('USERENV','LANGUAGE') FROM dual;

View the MEMBER_NAME columns in the TIME_HIER hierarchy at the Month level.

SELECT member_name, member_caption, member_description FROM time_hier WHERE level_name = 'MONTH';

Change the language in the current session to German.

ALTER SESSION SET nls_language = GERMAN;

Note the values of MEMBER_NAME are unchanged.

SELECT member_name, member_caption, member_description FROM time_hier WHERE level_name = 'MONTH';

Adding Language Support to Classifications

Use the LANGUAGE keyword to specify a language for a CLASSIFICATION. When multiple language support is added to analytic view objects, the CLASSIFICATION is typically defined once without a language keyword to be used as the default and repeated with a language keyword to support additional languages.

The following attribute dimension includes classifications with support for German.

 CREATE OR REPLACE ATTRIBUTE DIMENSION time_attr_dim
  CLASSIFICATION caption VALUE 'Time Attribute Dimension'
  CLASSIFICATION description VALUE 'Company standard time attributes and levels'
  CLASSIFICATION caption VALUE 'Time Attribute Dimension' LANGUAGE 'GERMAN'
  CLASSIFICATION description VALUE 'Company standard time attributes and levels' LANGUAGE 'GERMAN'
USING time_dim
ATTRIBUTES
 (year_id
    CLASSIFICATION caption VALUE 'Year Key Value'
    CLASSIFICATION description VALUE 'Year Key Value'
    CLASSIFICATION caption VALUE 'Jahr-ID-Wert' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Jahr-ID-Wert' LANGUAGE 'GERMAN',
  year_name
    CLASSIFICATION caption VALUE 'Year Descriptive Value'
    CLASSIFICATION description VALUE 'Year Descriptive Value'
    CLASSIFICATION caption VALUE 'Jahr Text Wert' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Jahr Text Wert' LANGUAGE 'GERMAN',
  quarter_id
    CLASSIFICATION caption VALUE 'Quarter Key Value'
    CLASSIFICATION description VALUE 'Quarter Key Value'
    CLASSIFICATION caption VALUE 'Viertel-ID-Wert' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Viertel-ID-Wert' LANGUAGE 'GERMAN',
  quarter_name
    CLASSIFICATION caption VALUE 'Quarter Descriptive Value'
    CLASSIFICATION description VALUE 'Quarter Descriptive Value'
    CLASSIFICATION caption VALUE 'Quarter Textwert' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Quarter Textwert' LANGUAGE 'GERMAN',
  month_id
    CLASSIFICATION caption VALUE 'Month Key Value'
    CLASSIFICATION description VALUE 'Month Key Value'
    CLASSIFICATION caption VALUE 'Monatlicher ID-Wert' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Monatlicher ID-Wert' LANGUAGE 'GERMAN',
  month_name
    CLASSIFICATION caption VALUE 'Month Short Descriptive Value'
    CLASSIFICATION description VALUE 'Month Short Descriptive Value'
    CLASSIFICATION caption VALUE 'Text des kurzen Monats' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Text des kurzen Monats' LANGUAGE 'GERMAN',
  month_long_name
    CLASSIFICATION caption VALUE 'Month Long Descriptive Value'
    CLASSIFICATION description VALUE 'Month Long Descriptive Value'
    CLASSIFICATION caption VALUE 'Text des langen Monats' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Text des langen Monats' LANGUAGE 'GERMAN',
  month_end_date
    CLASSIFICATION caption VALUE 'Ending Date of Month Key Value'
    CLASSIFICATION description VALUE 'Ending Date of Month Key Value'
    CLASSIFICATION caption VALUE 'Enddatum des Monats' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Enddatum des Monats' LANGUAGE 'GERMAN')
LEVEL MONTH
  CLASSIFICATION caption VALUE 'Month Level'
  CLASSIFICATION description VALUE 'Month Aggregate Groupings'
  CLASSIFICATION caption VALUE 'Monatsebene' LANGUAGE 'GERMAN'
  CLASSIFICATION description VALUE 'Monat Aggregatgruppierung' LANGUAGE 'GERMAN'
  KEY month_id
  MEMBER NAME month_name
  MEMBER CAPTION month_name
  MEMBER DESCRIPTION month_long_name
  ORDER BY month_end_date
  DETERMINES (quarter_id, month_end_date)
LEVEL QUARTER
  CLASSIFICATION caption VALUE 'Quarter Level'
  CLASSIFICATION description VALUE 'Quarter Aggregate Groupings'
  CLASSIFICATION caption VALUE 'Viertelniveau' LANGUAGE 'GERMAN'
  CLASSIFICATION description VALUE 'Quarter Aggregate Gruppierung' LANGUAGE 'GERMAN'
  KEY quarter_id
  MEMBER NAME quarter_name
  MEMBER CAPTION quarter_name
  MEMBER DESCRIPTION quarter_name
  ORDER BY MAX month_end_date
  DETERMINES (year_id)
LEVEL YEAR
  CLASSIFICATION caption VALUE 'Year Level'
  CLASSIFICATION description VALUE 'Year Aggregate Groupings'
  CLASSIFICATION caption VALUE 'Jahresniveau' LANGUAGE 'GERMAN'
  CLASSIFICATION description VALUE 'Jahres-Aggregatgruppierung' LANGUAGE 'GERMAN'
  KEY year_id
  MEMBER NAME year_name
  MEMBER CAPTION year_name
  MEMBER DESCRIPTION year_name
  ORDER BY MAX month_end_date;

The following hierarchy includes classifications with support for German.

CREATE OR REPLACE HIERARCHY time_hier
  CLASSIFICATION caption VALUE 'Time'
  CLASSIFICATION description VALUE 'Time Hierarchy'
  CLASSIFICATION caption VALUE 'Zeit' LANGUAGE 'GERMAN'
  CLASSIFICATION description VALUE 'Zeithierarchie' LANGUAGE 'GERMAN'
USING time_attr_dim
 (month  CHILD OF
 quarter CHILD OF
 year);

Classifications are available from the data dictionary in both the default language and German.

SELECT * FROM user_attribute_dim_class;

SELECT * FROM user_attribute_dim_attr_class;

SELECT * FROM user_attribute_dim_lvl_class;

SELECT * FROM user_hier_class;

Adding Language Support to Hierarchical Attributes

To map hierarchical attributes to different columns based on language:

• Add attributes to the attribute dimension for each column in the dimension table.
• Use a CASE statement to map to the appropriate attribute.

The following attribute dimension includes hierarchical attributes at the Month level with support for German.

CREATE OR REPLACE ATTRIBUTE DIMENSION time_attr_dim
  CLASSIFICATION caption VALUE 'Time Attribute Dimension'
  CLASSIFICATION description VALUE 'Company standard time attributes and levels'
  CLASSIFICATION caption VALUE 'Time Attribute Dimension' LANGUAGE 'GERMAN'
  CLASSIFICATION description VALUE 'Company standard time attributes and levels' LANGUAGE 'GERMAN'
USING time_dim
ATTRIBUTES
 (year_id
    CLASSIFICATION caption VALUE 'Year Key Value'
    CLASSIFICATION description VALUE 'Year Key Value'
    CLASSIFICATION caption VALUE 'Jahr-ID-Wert' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Jahr-ID-Wert' LANGUAGE 'GERMAN',
  year_name
    CLASSIFICATION caption VALUE 'Year Descriptive Value'
    CLASSIFICATION description VALUE 'Year Descriptive Value'
    CLASSIFICATION caption VALUE 'Jahr Text Wert' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Jahr Text Wert' LANGUAGE 'GERMAN',
  quarter_id
    CLASSIFICATION caption VALUE 'Quarter Key Value'
    CLASSIFICATION description VALUE 'Quarter Key Value'
    CLASSIFICATION caption VALUE 'Viertel-ID-Wert' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Viertel-ID-Wert' LANGUAGE 'GERMAN',
  quarter_name
    CLASSIFICATION caption VALUE 'Quarter Descriptive Value'
    CLASSIFICATION description VALUE 'Quarter Descriptive Value'
    CLASSIFICATION caption VALUE 'Quarter Textwert' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Quarter Textwert' LANGUAGE 'GERMAN',
  month_id
    CLASSIFICATION caption VALUE 'Month Key Value'
    CLASSIFICATION description VALUE 'Month Key Value'
    CLASSIFICATION caption VALUE 'Monatlicher ID-Wert' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Monatlicher ID-Wert' LANGUAGE 'GERMAN',
  month_name
    CLASSIFICATION caption VALUE 'Month Short Descriptive Value'
    CLASSIFICATION description VALUE 'Month Short Descriptive Value'
    CLASSIFICATION caption VALUE 'Text des kurzen Monats' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Text des kurzen Monats' LANGUAGE 'GERMAN',
  month_long_name
    CLASSIFICATION caption VALUE 'Month Long Descriptive Value'
    CLASSIFICATION description VALUE 'Month Long Descriptive Value'
    CLASSIFICATION caption VALUE 'Text des langen Monats' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Text des langen Monats' LANGUAGE 'GERMAN',
  month_name_de
    CLASSIFICATION caption VALUE 'Month Short Descriptive Value'
    CLASSIFICATION description VALUE 'Month Short Descriptive Value'
    CLASSIFICATION caption VALUE 'Text des kurzen Monats' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Text des kurzen Monats' LANGUAGE 'GERMAN',
  month_long_name_de
    CLASSIFICATION caption VALUE 'Month Long Descriptive Value'
    CLASSIFICATION description VALUE 'Month Long Descriptive Value'
    CLASSIFICATION caption VALUE 'Text des langen Monats' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Text des langen Monats' LANGUAGE 'GERMAN',
  month_end_date
    CLASSIFICATION caption VALUE 'Ending Date of Month Key Value'
    CLASSIFICATION description VALUE 'Ending Date of Month Key Value'
    CLASSIFICATION caption VALUE 'Enddatum des Monats' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Enddatum des Monats' LANGUAGE 'GERMAN')
LEVEL MONTH
  CLASSIFICATION caption VALUE 'Month Level'
  CLASSIFICATION description VALUE 'Month Aggregate Groupings'
  CLASSIFICATION caption VALUE 'Monatsebene' LANGUAGE 'GERMAN'
  CLASSIFICATION description VALUE 'Monat Aggregatgruppierung' LANGUAGE 'GERMAN'
  KEY month_id
  MEMBER NAME 
    CASE
      WHEN sys_context('USERENV','LANGUAGE') LIKE 'GERMAN%'
        THEN month_name_de
      ELSE month_name
    END
  MEMBER CAPTION 
    CASE
      WHEN sys_context('USERENV','LANGUAGE') LIKE 'GERMAN%'
        THEN month_name_de
      ELSE month_name
    END
  MEMBER DESCRIPTION 
    CASE
      WHEN sys_context('USERENV','LANGUAGE') LIKE 'GERMAN%'
        THEN month_long_name_de
      ELSE month_long_name
    END
  ORDER BY month_end_date
  DETERMINES (quarter_id, month_end_date)
LEVEL QUARTER
  CLASSIFICATION caption VALUE 'Quarter Level'
  CLASSIFICATION description VALUE 'Quarter Aggregate Groupings'
  CLASSIFICATION caption VALUE 'Viertelniveau' LANGUAGE 'GERMAN'
  CLASSIFICATION description VALUE 'Quarter Aggregate Gruppierung' LANGUAGE 'GERMAN'
  KEY quarter_id
  MEMBER NAME quarter_name
  MEMBER CAPTION quarter_name
  MEMBER DESCRIPTION quarter_name
  ORDER BY MAX month_end_date
  DETERMINES (year_id)
LEVEL YEAR
  CLASSIFICATION caption VALUE 'Year Level'
  CLASSIFICATION description VALUE 'Year Aggregate Groupings'
  CLASSIFICATION caption VALUE 'Jahresniveau' LANGUAGE 'GERMAN'
  CLASSIFICATION description VALUE 'Jahres-Aggregatgruppierung' LANGUAGE 'GERMAN'
  KEY year_id
  MEMBER NAME year_name
  MEMBER CAPTION year_name
  MEMBER DESCRIPTION year_name
  ORDER BY MAX month_end_date;

No changes are required to the TIME_HIER hierarchy. The hierarchy will inherit the changes from the attribute dimension.

Set NLS_LANGUAGE = AMERICAN

ALTER SESSION SET nls_language = AMERICAN;

Select the MEMBER_NAME, MEMBER_CAPTION and MEMBER_DESCRIPTION attributes at the Month level

SELECT member_name,
  member_caption,
  member_description
FROM time_hier
WHERE level_name = 'MONTH';

Change the language in the current session to German.

ALTER SESSION SET nls_language = GERMAN;

Note the values of MEMBER_NAME are now returned from the attributes mapped to the _DE columns of the dimension table.

SELECT member_name,
  member_caption,
  member_description
FROM time_hier
WHERE level_name = 'MONTH';

Product and Geography Hierarchies

Use the following statements to create additional attribute dimension and hierarchies with support for German.

Product attribute dimension.

CREATE OR REPLACE ATTRIBUTE DIMENSION product_attr_dim
  CLASSIFICATION caption VALUE 'Product Attribute Dimension'
  CLASSIFICATION description VALUE 'Product attributes and levels'
  CLASSIFICATION caption VALUE 'Produkt-Attribute und Ebenen' LANGUAGE 'GERMAN'
  CLASSIFICATION description VALUE 'Produkt-Attribute und Ebenen' LANGUAGE 'GERMAN'
USING product_dim 
ATTRIBUTES
 (department_id
    CLASSIFICATION caption VALUE 'Department Key Value'
    CLASSIFICATION description VALUE 'Department Key Value'
    CLASSIFICATION caption VALUE 'Abteilungs-ID-Wert' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Abteilungs-ID-Wert' LANGUAGE 'GERMAN',
  department_name
    CLASSIFICATION caption VALUE 'Department Text Value'
    CLASSIFICATION description VALUE 'Department Text Value'   
    CLASSIFICATION caption VALUE 'Abteilung Text Wert (Englisch)' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Abteilung Text Wert (Englisch)' LANGUAGE 'GERMAN',
  department_name_de
    CLASSIFICATION caption VALUE 'Department Text Value (German)'
    CLASSIFICATION description VALUE 'Department Text Value (German)'   
    CLASSIFICATION caption VALUE 'Abteilung Text Wert' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Abteilung Text Wert' LANGUAGE 'GERMAN',
  category_id
    CLASSIFICATION caption VALUE 'Category Key Value'
    CLASSIFICATION description VALUE 'Category Key Value'
    CLASSIFICATION caption VALUE 'Kategorie-ID-Wert' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Kategorie-ID-Wert' LANGUAGE 'GERMAN',
  category_name
    CLASSIFICATION caption VALUE 'Category Text Value'
    CLASSIFICATION description VALUE 'Category Text Value' 
    CLASSIFICATION caption VALUE 'Kategorien Text Wert (Englisch)' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Kategorien Text Wert (Englisch)' LANGUAGE 'GERMAN',
  category_name_de
    CLASSIFICATION caption VALUE 'Category Text Value (German)'
    CLASSIFICATION description VALUE 'Category Text Value (German)' 
    CLASSIFICATION caption VALUE 'Kategorien Text Wert' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Kategorien Text Wert' LANGUAGE 'GERMAN')
LEVEL DEPARTMENT
    CLASSIFICATION caption VALUE 'Department'
    CLASSIFICATION description VALUE 'Department Aggregate Grouping'
    CLASSIFICATION caption VALUE 'Abteilungsebene' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Abteilung Aggregatgruppierung' LANGUAGE 'GERMAN'
  KEY department_id
  MEMBER NAME 
    CASE
      WHEN sys_context('USERENV','LANGUAGE') LIKE 'GERMAN%'
        THEN department_name_de
      ELSE department_name
    END
  MEMBER CAPTION
    CASE
      WHEN sys_context('USERENV','LANGUAGE') LIKE 'GERMAN%'
        THEN department_name_de
      ELSE department_name
    END
  MEMBER DESCRIPTION
    CASE
      WHEN sys_context('USERENV','LANGUAGE') LIKE 'GERMAN%'
        THEN department_name_de
      ELSE department_name
    END
  ORDER BY department_name
LEVEL CATEGORY
  CLASSIFICATION caption VALUE 'Category'
  CLASSIFICATION description VALUE 'Category Aggregate Grouping'
  CLASSIFICATION caption VALUE 'Kategorien Ebene' LANGUAGE 'GERMAN'
  CLASSIFICATION description VALUE 'Kategorie Aggregatgruppierung' LANGUAGE 'GERMAN'
  KEY category_id
  MEMBER NAME
    CASE
      WHEN sys_context('USERENV','LANGUAGE') LIKE 'GERMAN%'
        THEN category_name_de
      ELSE category_name
    END
  MEMBER CAPTION
    CASE
      WHEN sys_context('USERENV','LANGUAGE') LIKE 'GERMAN%'
        THEN category_name_de
      ELSE category_name
    END
  MEMBER DESCRIPTION
    CASE
      WHEN sys_context('USERENV','LANGUAGE') LIKE 'GERMAN%'
        THEN category_name_de
      ELSE category_name
    END
  ORDER BY category_name
  DETERMINES(department_id)
ALL MEMBER NAME 'ALL PRODUCTS';

Product hierarchy.

CREATE OR REPLACE HIERARCHY product_hier
  CLASSIFICATION caption VALUE 'Product Hierarchy'
  CLASSIFICATION description VALUE 'Product Hierarchy'
  CLASSIFICATION caption VALUE 'Produkthierarchie' LANGUAGE 'GERMAN'
  CLASSIFICATION description VALUE 'Produkthierarchie' LANGUAGE 'GERMAN'
USING product_attr_dim
 (CATEGORY
  CHILD OF department);

Geography attribute dimension. Note that German is available in the dimension table for only the Region and Country levels.

CREATE OR REPLACE ATTRIBUTE DIMENSION geography_attr_dim
  CLASSIFICATION caption VALUE 'Geography Attribute Dimension'
  CLASSIFICATION description VALUE 'Geography attributes and levels'
  CLASSIFICATION caption VALUE 'Erdkunde-Attribute und Ebenen' LANGUAGE 'GERMAN'
  CLASSIFICATION description VALUE 'Erdkunde-Attribute und Ebenen' LANGUAGE 'GERMAN'
USING geography_dim
ATTRIBUTES
 (region_id
   CLASSIFICATION caption VALUE 'Region Key Value'
    CLASSIFICATION description VALUE 'Region Key Value'
    CLASSIFICATION caption VALUE 'Region-ID-Wert' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Region-ID-Wert' LANGUAGE 'GERMAN',
  region_name
    CLASSIFICATION caption VALUE 'Region Text Value'
    CLASSIFICATION description VALUE 'Region Text Value'
    CLASSIFICATION caption VALUE 'Region-Text-Wert (Englisch)' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Region-Text-Wert (Englisch)' LANGUAGE 'GERMAN', 
  region_name_de
    CLASSIFICATION caption VALUE 'Region Text Value (German)'
    CLASSIFICATION description VALUE 'Region Text Value (German)'
    CLASSIFICATION caption VALUE 'Region-Text-Wert' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Region-Text-Wert' LANGUAGE 'GERMAN', 
  country_id
    CLASSIFICATION caption VALUE 'Country Key Value'
    CLASSIFICATION description VALUE 'Country Key Value'
    CLASSIFICATION caption VALUE 'Land-ID-Wert' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Land-ID-Wert' LANGUAGE 'GERMAN',
  country_name
    CLASSIFICATION caption VALUE 'Country Text Value'
    CLASSIFICATION description VALUE 'Country Text Value'
    CLASSIFICATION caption VALUE 'Land-Text-Wert (Englisch)' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Land-Text-Wert (Englisch)' LANGUAGE 'GERMAN',
  country_name_de
    CLASSIFICATION caption VALUE 'Country Text Value (German)'
    CLASSIFICATION description VALUE 'Country Text Value (German)'
    CLASSIFICATION caption VALUE 'Land-Text-Wert' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Land-Text-Wert' LANGUAGE 'GERMAN',
  state_province_id
    CLASSIFICATION caption VALUE 'State/Province Key Value'
    CLASSIFICATION description VALUE 'State/Province Key Value'
    CLASSIFICATION caption VALUE 'aBundesland-ID-Wert' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Bundesland-ID-Wert' LANGUAGE 'GERMAN',  
  state_province_name
    CLASSIFICATION caption VALUE 'State/Province Text Value'
    CLASSIFICATION description VALUE 'State/Province Text Value'
    CLASSIFICATION caption VALUE 'Bundesland-Text-Wert (Englisch)' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Bundesland-Text-Wert (Englisch)' LANGUAGE 'GERMAN'
  )
LEVEL REGION
  KEY region_id
  ALTERNATE KEY region_name
  MEMBER NAME
    CASE
      WHEN sys_context('USERENV','LANGUAGE') LIKE 'GERMAN%'
        THEN region_name_de
      ELSE region_name
    END
  MEMBER CAPTION
    CASE
      WHEN sys_context('USERENV','LANGUAGE') LIKE 'GERMAN%'
        THEN region_name_de
      ELSE region_name
    END
  MEMBER DESCRIPTION
    CASE
      WHEN sys_context('USERENV','LANGUAGE') LIKE 'GERMAN%'
        THEN region_name_de
      ELSE region_name
    END
  ORDER BY region_name
LEVEL COUNTRY
  KEY country_id
  MEMBER NAME
    CASE
      WHEN sys_context('USERENV','LANGUAGE') LIKE 'GERMAN%'
        THEN country_name_de
      ELSE country_name
    END
  MEMBER CAPTION
    CASE
      WHEN sys_context('USERENV','LANGUAGE') LIKE 'GERMAN%'
        THEN country_name_de
      ELSE country_name
    END
  MEMBER DESCRIPTION
    CASE
      WHEN sys_context('USERENV','LANGUAGE') LIKE 'GERMAN%'
        THEN country_name_de
      ELSE country_name
    END
  ORDER BY country_name
  DETERMINES(region_id)
LEVEL STATE_PROVINCE
  KEY state_province_id
  -- Note:  State/Province names only provided in English.
  MEMBER NAME state_province_name
  MEMBER CAPTION state_province_name
  MEMBER DESCRIPTION state_province_name
  ORDER BY state_province_name
  DETERMINES(country_id)
ALL MEMBER NAME 'ALL CUSTOMERS';

Geography hierarchy.

CREATE OR REPLACE HIERARCHY geography_hier
  CLASSIFICATION caption VALUE 'Geography Hierarchy'
  CLASSIFICATION description VALUE 'Geography Hierarchy'
  CLASSIFICATION caption VALUE 'Geographische Hierarchie' LANGUAGE 'GERMAN'
  CLASSIFICATION description VALUE 'Geographische Hierarchie' LANGUAGE 'GERMAN'
USING geography_attr_dim
 (state_province
  CHILD OF country
  CHILD OF region);

Set NLS_LANGUAGE = AMERICAN

ALTER SESSION SET nls_language = AMERICAN;

Query attributes and hierarchical attributes from the PRODUCT_HIER hierarchy. Note that both English and German are available from attributes and the hierarchical attributes return English values.

SELECT
  department_name,
  category_name,
  department_name_de,
  category_name_de,
  member_name,
  member_caption,
  member_description
FROM product_hier
WHERE level_name = 'CATEGORY'

;

Set NLS_LANGUAGE = GERMAN

ALTER SESSION SET nls_language = GERMAN;

Query attributes and hierarchical attributes from the PRODUCT_HIER hierarchy. Note that the attributes continue to return English and German values and the hierarchical attributes now return German values.

SELECT
  department_name,
  category_name,
  department_name_de,
  category_name_de,
  member_name,
  member_caption,
  member_description
FROM product_hier
WHERE level_name = 'CATEGORY'

;

Creating the Analytic View

The analytic view inherits attributes from hierarchies so only the classifications need to be updated to support German.

CREATE OR REPLACE ANALYTIC VIEW sales_av with default and German classifications.

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
    CLASSIFICATION caption VALUE 'Sales Analytic View'
    CLASSIFICATION description VALUE 'Sales Analytic View'
    CLASSIFICATION caption VALUE 'Vertrieb Analytische Sicht' LANGUAGE 'GERMAN'
    CLASSIFICATION description VALUE 'Vertrieb Analytische Sicht' LANGUAGE 'GERMAN',
 sales_year_ago AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1 ACROSS ANCESTOR AT LEVEL year))
   CLASSIFICATION caption VALUE 'Sales Analytic View'
   CLASSIFICATION description VALUE 'Sales Analytic View'
   CLASSIFICATION caption VALUE 'Vertrieb Analytische Sicht' LANGUAGE 'GERMAN'
   CLASSIFICATION description VALUE 'Vertrieb Analytische Sicht' LANGUAGE 'GERMAN',
 units FACT units
   CLASSIFICATION caption VALUE 'Units Sold'
   CLASSIFICATION description VALUE 'Units Sold'
   CLASSIFICATION caption VALUE 'Verkäufe vor einem Jahr' LANGUAGE 'GERMAN'
   CLASSIFICATION description VALUE 'Verkäufe vor einem Jahr' LANGUAGE 'GERMAN'
  )
DEFAULT MEASURE SALES;

Set NLS_LANGUAGE = AMERICAN

ALTER SESSION SET nls_language = AMERICAN;

Run a query against the analytic view.

SELECT time_hier.member_name AS Time,
 product_hier.member_name AS Product,
 geography_hier.member_name AS Geography,
 sales,
 sales_year_ago
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')
  AND time_hier.member_name in ('CY2014','CY2015')
ORDER BY time_hier.hier_order,
  product_hier.hier_order,
  geography_hier.hier_order;

Set NLS_LANGUAGE = GERMAN

ALTER SESSION SET nls_language = GERMAN;

Run the same query against the analytic view.

SELECT time_hier.member_name AS Time,
 product_hier.member_name AS Product,
 geography_hier.member_name AS Geography,
 sales,
 sales_year_ago
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')
  AND time_hier.member_name in ('CY2014','CY2015')
ORDER BY time_hier.hier_order,
  product_hier.hier_order,
  geography_hier.hier_order;

Questions or Comments?

Get in touch at william.endress@oracle.com.