CREATE DIMENSION [ schema. ] dimension
level_clause ...
{ hierarchy_clause
| attribute_clause
| extended_attribute_clause
}...
;
level_clause ::=
LEVEL level IS
{ level_table.level_column
| (level_table.level_column
[, level_table.level_column ]...
)
}
hierarchy_clause ::=
HIERARCHY hierarchy
(child_level { CHILD OF parent_level }...
[ dimension_join_clause ]
)
dimension_join_clause ::=
{ JOIN KEY
{ child_key_column
| (child_key_column [, child_key_column ]...)
}
REFERENCES parent_level
}...
attribute_clause ::=
ATTRIBUTE level DETERMINES
{ dependent_column
| ( dependent_column
[, dependent_column ]... )
}
extended_attribute_clause::=
ATTRIBUTE attribute
{ LEVEL level
DETERMINES { dependent_column
| (dependent_column [, dependent_column ]... )
}
}...
Example
CREATE DIMENSION customers_dim
LEVEL customer IS (customers.cust_id)
LEVEL city IS (customers.cust_city)
LEVEL state IS (customers.cust_state_province)
LEVEL country IS (countries.country_id)
LEVEL subregion IS (countries.country_subregion)
LEVEL region IS (countries.country_region)
HIERARCHY geog_rollup (
customer CHILD OF
city CHILD OF
state CHILD OF
country CHILD OF
subregion CHILD OF
region
JOIN KEY (customers.country_id) REFERENCES country
)
ATTRIBUTE customer_info LEVEL customer DETERMINES
(cust_first_name, cust_last_name, cust_gender,
cust_marital_status, cust_year_of_birth,
cust_income_level, cust_credit_limit)
ATTRIBUTE country DETERMINES (countries.country_name);