Dimension Definition Tables

A dimension definition table is a Snowflake table that describes the members, hierarchy relationships, and properties used to build a Casabase Cube dimension.

Dimension definition tables are used as the source metadata for dimensions.

They can originate in two ways:

Generated by Casabase Cube from an imported Oracle Essbase or Oracle Cloud EPM outline (.otl).
Created by the customer when building a multidimensional model directly from Snowflake data.

In either case, the table represents the source definition from which Casabase Cube constructs and maintains the dimension’s multidimensional structures.

How Dimension Definition Tables Fit into the Model

Conceptually:

Dimension Definition Table
          │
          ▼
   Dimension Configuration
          │
          ▼
   Build / Rebuild
          │
          ▼
Dimension and Hierarchy
      Structures
          │
          ▼
    Casabase Cube
          │
          ▼
Navigation, Aggregation,
Formulas, Security, Queries

The dimension definition table describes the logical metadata.

Casabase Cube builds the application-managed structures required to use that metadata as a multidimensional dimension.

Dimension Definition Tables and Cube Data

Dimension metadata and cube data serve different purposes.

Dimension Definition Table
        │
        └── Defines members and hierarchy


Bottom-Level Cube Data
        │
        └── Contains dimensional coordinates and values

For example, an ENTITY definition table might define:

Total Entity
├── North America
│   ├── United States
│   └── Canada
└── EMEA
    ├── Germany
    └── France

while bottom-level cube data contains records such as:

ACCOUNT   ENTITY          PERIOD   SCENARIO   VALUE
--------  --------------  -------  ---------  -----
Sales     United States   Jan      Actual     125000
Sales     Canada          Jan      Actual      42000
Sales     Germany         Jan      Actual      38000

The dimension definition establishes what United States, Canada, and Germany mean within the hierarchy.

The cube data supplies values associated with those members.

Table Location

The location of a dimension definition table depends on how it was created.

Dimension definition tables generated by Casabase Cube from an imported Oracle Essbase or Oracle Cloud EPM outline are stored in the application’s:

SHARED_DATA

schema.

For an application installed as:

CASABASE_CUBE

generated definition tables are located under:

CASABASE_CUBE.SHARED_DATA

Customer-created dimension definition tables do not need to be stored in the application database.

They can reside in another Snowflake database and schema as long as the Casabase Cube Native App has the required access to the source object.

For example:

FINANCE_DB.PLANNING.DIM_ACCOUNT
FINANCE_DB.PLANNING.DIM_ENTITY
FINANCE_DB.PLANNING.DIM_PERIOD

can be used as dimension definition tables when the application has the required USAGE and SELECT grants.

The source table is identified through the fully qualified INPUT_TABLE_NAME supplied when the dimension is created.

The physical table name and location do not determine the logical dimension name.

That association is established by the dimension configuration.

See Roles and Privileges for source-object access.

Tables Generated from an Essbase Outline

When Casabase Cube imports an Oracle Essbase or Oracle Cloud EPM outline using:

CUBE.IMPORT_ESSBASE_OTL

Casabase Cube creates dimension definition tables from the outline metadata.

Generated tables use the naming convention:

<CUBE_NAME>_<DIMENSION_NAME>_OTL

For example:

ASOSAMP_AGE_OTL
ASOSAMP_GEOGRAPHY_OTL
ASOSAMP_INCOME_LEVEL_OTL
ASOSAMP_MEASURES_OTL
ASOSAMP_PRODUCTS_OTL

The _OTL suffix indicates that the definition table originated from an imported Essbase outline.

The imported metadata can preserve supported multidimensional characteristics such as:

Parent-child relationships
Aliases
Aggregation operators
Shared members
Formulas
Time Balance properties
UDAs
Comments
Storage properties
Sort order
Solve order
Other supported source metadata

Customer-Created Definition Tables

When creating a multidimensional model directly from Snowflake data, customers create their own dimension definition tables.

A customer-created table does not need to reproduce every column present in a Casabase-generated _OTL table.

It needs only the columns required to describe the dimension and the properties the model uses.

For a simple dimension, that can be as little as the hierarchy relationship itself.

For example:

CREATE TABLE FINANCE_DB.PLANNING.DIM_ENTITY (
    PARENT VARCHAR,
    CHILD VARCHAR
);

A richer definition might include:

CREATE TABLE FINANCE_DB.PLANNING.DIM_ACCOUNT (
    PARENT VARCHAR,
    CHILD VARCHAR,
    ALIAS VARCHAR,
    OPERATOR VARCHAR DEFAULT '+',
    MEMBER_IS_SHARED VARCHAR DEFAULT 'false',
    FORMULA VARCHAR,
    TB_TYPE VARCHAR,
    TB_TYPE_SKIP_VALUE VARCHAR DEFAULT 'NONE',
    UDA VARCHAR,
    MEMBER_COMMENT VARCHAR,
    DATA_STORAGE VARCHAR,
    SORTORDER NUMBER(38,0)
);

The appropriate structure depends on the multidimensional properties required by the dimension.

Column Names Are Flexible

Customer-created definition tables do not need to use Casabase Cube’s generated column names.

For example, a source table could use:

PARENT_MEMBER
MEMBER_NAME
DISPLAY_NAME
CONSOLIDATION

instead of:

PARENT
CHILD
ALIAS
OPERATOR

The columns are mapped when the dimension is configured.

Conceptually:

Customer Column          Dimension Property
---------------          ------------------
PARENT_MEMBER      ───►  Parent
MEMBER_NAME        ───►  Member
DISPLAY_NAME       ───►  Alias
CONSOLIDATION      ───►  Aggregation Operator

This allows existing Snowflake metadata tables to be used without requiring their columns to be renamed to Casabase conventions.

Hierarchy Structure

The parent and child mappings define the hierarchy.

For example:

PARENT	CHILD	OPERATOR
`NULL`	`Total Entity`	`+`
`Total Entity`	`North America`	`+`
`Total Entity`	`EMEA`	`+`
`North America`	`United States`	`+`
`North America`	`Canada`	`+`
`EMEA`	`Germany`	`+`
`EMEA`	`France`	`+`

produces:

Total Entity
├── North America
│   ├── United States
│   └── Canada
└── EMEA
    ├── Germany
    └── France

Each row identifies a member and its relationship to a parent.

The root member has no parent.

Casabase-Generated Definition Structure

Definition tables generated from imported Essbase metadata can contain the following columns:

Property	Snowflake Type	Purpose
`PARENT`	`VARCHAR`	Parent member name.
`CHILD`	`VARCHAR`	Member name.
`ALIAS`	`VARCHAR`	Primary display alias.
`OPERATOR`	`VARCHAR`	Aggregation operator.
`MEMBER_IS_SHARED`	`VARCHAR`	Shared-member indicator.
`FORMULA`	`VARCHAR`	Member formula.
`TB_TYPE`	`VARCHAR`	Time Balance behavior.
`TB_TYPE_SKIP_VALUE`	`VARCHAR`	Time Balance Skip behavior.
`UDA`	`VARCHAR`	User Defined Attribute metadata.
`MEMBER_COMMENT`	`VARCHAR`	Member comment.
`DATA_STORAGE`	`VARCHAR`	Storage-related source metadata.
`SORTORDER`	`NUMBER(38,0)`	Member ordering information.
`SOLVE_ORDER`	`NUMBER(38,0)`	Calculation precedence metadata.
`IS_TWO_PASS`	`BOOLEAN`	Imported two-pass source property where present.
`ALIASES`	`VARCHAR`	Additional alias information.

Generated tables define defaults including:

OPERATOR            = '+'
MEMBER_IS_SHARED    = 'false'
TB_TYPE_SKIP_VALUE  = 'NONE'

Logical Requirements vs. Snowflake Nullability

Columns in a generated definition table can be nullable at the Snowflake table level.

That does not mean every property is logically optional.

For example, a valid member normally requires a member name even if the generated CHILD column is technically nullable.

Dimension validity is determined by the logical metadata and Casabase Cube validation rules rather than Snowflake column nullability alone.

Parent and Member Mappings

The parent and child/member columns establish the hierarchy relationships.

For example:

PARENT          CHILD
--------------  ----------------
Total Product   Electronics
Total Product   Furniture
Electronics     Computers
Electronics     Phones
Furniture       Desks
Furniture       Chairs

produces:

Total Product
├── Electronics
│   ├── Computers
│   └── Phones
└── Furniture
    ├── Desks
    └── Chairs

The physical source columns do not have to be named PARENT and CHILD; those names are supplied through the dimension mapping.

Aggregation Operators

Aggregation operators determine how members contribute to their parents.

A definition table can contain an operator column such as:

OPERATOR

For standard dimensions, supported aggregation operators are:

+
-
~

These allow members to be added, subtracted, or excluded from normal aggregation behavior.

Attribute dimensions support + aggregation. When migrating from Oracle Essbase or Oracle Cloud EPM, unsupported source aggregation operators are normalized according to the migration compatibility rules. See Compatibility Notes.

For example:

Net Revenue
├── Revenue          +
└── Discounts        -

allows the hierarchy itself to describe the consolidation behavior.

See Hierarchy Capabilities for the hierarchy and aggregation model.

Aliases

Definition tables can contain aliases that provide display-friendly names separately from member names.

The Casabase-generated structure includes:

ALIAS
ALIASES

For example:

Member Name        Alias
-----------------  ----------------
US_WEST            Western Region
US_EAST            Eastern Region

The member name remains the multidimensional identity while the alias provides an alternate display value.

Shared Members

Shared members allow the same underlying business member to appear in more than one hierarchy location.

A source table can identify shared occurrences through a mapped indicator column.

The Casabase-generated structure uses:

MEMBER_IS_SHARED

with a default value of:

false

When configuring the dimension, the shared-member mapping identifies both:

The source column containing the indicator
The source value that means the occurrence is shared

For example:

IS_SHARED_COL = 'MEMBER_IS_SHARED'
SHARED_VALUE  = 'true'

This allows the customer source table to use its own representation for shared-member occurrences.

Formulas

Calculated members can contain formulas in the dimension definition.

The Casabase-generated structure uses:

FORMULA

For example:

Measures
├── Sales
├── Units
└── Avg Selling Price

where:

Avg Selling Price

contains a formula while Sales and Units are stored measures.

Formula expressions are evaluated dynamically by the Casabase Cube calculation engine.

See Formula Capabilities and the Formulas documentation.

Solve Order

Imported definition metadata can include:

SOLVE_ORDER

Solve order controls calculation precedence when calculated members interact.

For example:

SOLVE_ORDER = 10

can establish the relative calculation order of a calculated member.

SOLVE_ORDER is part of the generated definition metadata where applicable. Its presence in the generated source structure should not be confused with a dedicated positional CREATE_DIMENSION parameter.

See Formula Capabilities for the calculation model.

Two-Pass Source Metadata

The generated definition structure can also include:

IS_TWO_PASS

This allows Casabase Cube to retain the corresponding property when it is present in imported source metadata.

Casabase Cube uses solve order to control calculation precedence rather than exposing two-pass calculation as a separate calculation mechanism.

As with SOLVE_ORDER, IS_TWO_PASS is source metadata and is not a dedicated positional parameter in the documented CREATE_DIMENSION signature.

See Formula Capabilities.

Time Balance

Definition tables can contain Time Balance metadata:

TB_TYPE
TB_TYPE_SKIP_VALUE

Supported Time Balance values are:

FLOW
FIRST
LAST
AVERAGE

FLOW is the normal additive behavior across periods. FIRST, LAST, and AVERAGE provide non-additive time behavior.

Supported Time Balance Skip values include:

NONE
MISSING
ZEROS
MISSING_AND_ZEROS

These properties control how applicable members aggregate across the cube’s time dimension.

For example, a balance-sheet account can use a different time aggregation behavior from a flow account.

The cube must have an appropriate time dimension for Time Balance behavior to apply.

See Hierarchy Capabilities for the multidimensional behavior.

User Defined Attributes

The definition can associate User Defined Attributes with members through a mapped UDA column.

The Casabase-generated structure uses:

UDA

UDAs provide additional member metadata that can be referenced by supported formula functions such as:

HASUDA()

This allows formulas to respond to member classifications or other business metadata.

Member Comments

A definition table can include descriptive member comments.

The generated structure uses:

MEMBER_COMMENT

When used, the source column is mapped through the dimension configuration.

Comments are metadata and do not determine hierarchy relationships.

Data Storage Metadata

Imported Essbase metadata can contain storage-related information such as:

storeData
dynamic

The Casabase-generated definition structure stores this information in:

DATA_STORAGE

The corresponding source column can be mapped when the dimension is created.

Storage metadata should be understood in the context of the Casabase Cube calculation and query architecture rather than assumed to imply identical physical behavior to Oracle Essbase.

For migrations, formulas attached to stored-data members and other source behaviors that are not supported by Casabase Cube are handled according to the documented migration compatibility rules. See Compatibility Notes.

Sort Order

A dimension definition can contain explicit member ordering information.

The Casabase-generated structure uses:

SORTORDER

with Snowflake type:

NUMBER(38,0)

This information can be used when Casabase Cube constructs hierarchy ordering.

Additional Properties

CREATE_DIMENSION also includes:

PROPERTY_COLS

for supported additional member-property mappings.

Use this capability only for properties supported by the Casabase Cube dimension configuration contract.

The exact PROPERTY_COLS specification should be followed when configuring the dimension rather than inferred from arbitrary columns present in the source table.

Mapping with CREATE_DIMENSION

For a customer-created Snowflake model, CREATE_DIMENSION maps source-table columns to Casabase Cube dimension and member properties.

The documented procedure signature is:

CREATE_DIMENSION(
    CUBE_NAME,
    DIMENSION_NAME,
    INPUT_TABLE_NAME,
    PARENT_COL,
    CHILD_COL,
    ALIAS_COL,
    OPERATOR_COL,
    DATA_TABLE_COL_NAME,
    USE_SECURITY,
    IS_SHARED_COL,
    SHARED_VALUE,
    FORMULA_COL,
    TB_TYPE_COL,
    TB_TYPE_SKIP_COL,
    SORTORDER_COL,
    IS_TIME_DIMENSION,
    UDA_COL,
    SKIP_SNAPSHOT,
    COMMENT_COL,
    STORAGE_COL,
    PROPERTY_COLS,
    SKIP_HIERARCHY_VIEW
)

For example:

CALL CASABASE_CUBE.CUBE.CREATE_DIMENSION(
    'FINANCE',
    'ACCOUNT',
    'CASABASE_CUBE.SHARED_DATA.FINANCE_ACCOUNT',
    'PARENT',
    'CHILD',
    'ALIAS',
    'OPERATOR',
    'ACCOUNT',
    FALSE,
    'MEMBER_IS_SHARED',
    'true',
    'FORMULA',
    'TB_TYPE',
    'TB_TYPE_SKIP_VALUE',
    'SORTORDER',
    FALSE,
    'UDA',
    FALSE,
    'MEMBER_COMMENT',
    'DATA_STORAGE',
    NULL,
    FALSE
);

This call maps:

Source Table Column       Casabase Cube Use
-------------------       ------------------
PARENT               ───► Parent
CHILD                ───► Member
ALIAS                ───► Alias
OPERATOR             ───► Aggregation
MEMBER_IS_SHARED     ───► Shared-member flag
FORMULA              ───► Formula
TB_TYPE              ───► Time Balance
TB_TYPE_SKIP_VALUE   ───► Time Balance Skip
SORTORDER            ───► Sort order
UDA                  ───► UDA
MEMBER_COMMENT       ───► Comment
DATA_STORAGE         ───► Storage metadata

A customer source table can use different column names as long as the mappings identify the correct columns.

Required and Optional Mappings

Not every CREATE_DIMENSION mapping needs to point to a populated source column for every dimension.

A simple organizational dimension might require only:

Parent
Member
Data-column mapping

while a richer Account or Measures dimension might also use:

Alias
Aggregation Operator
Formula
Time Balance
UDA
Comment
Storage Metadata
Sort Order

Use only the metadata required by the model.

This keeps customer-created definition tables understandable and avoids adding unused source columns merely to imitate an imported _OTL table.

Mapping the Dimension to Cube Data

Every dimension must identify the corresponding dimension column in the cube’s bottom-level data.

This mapping is supplied through:

DATA_TABLE_COL_NAME

For example:

Dimension	Cube Data Column
`ACCOUNT`	`ACCOUNT`
`ENTITY`	`ENTITY`
`PERIOD`	`PERIOD`
`YEARS`	`YEARS`
`SCENARIO`	`SCENARIO`

The names do not have to match.

For example:

Dimension Name     Data Column
--------------     -----------
ENTITY        ───► COST_CENTER

can be valid when that mapping accurately represents the model.

Conceptually:

Dimension Definition
       │
       ▼
Logical ENTITY Members
       │
       ▼
DATA_TABLE_COL_NAME
       │
       ▼
COST_CENTER Column
in Bottom-Level Data

The mapping connects multidimensional members to their corresponding coordinates in the cube data.

Security Configuration

CREATE_DIMENSION includes:

USE_SECURITY

to determine whether the dimension participates in Casabase Cube row-level security.

For example:

USE_SECURITY = TRUE

makes the dimension security-enabled.

This does not automatically restrict every user.

Casabase Cube row-level security is opt-in per user. Security filtering applies when the cube has a security-enabled dimension and the executing user has at least one active security rule for the cube.

See Access Control for the complete security model.

Time Dimension Configuration

CREATE_DIMENSION also includes:

IS_TIME_DIMENSION

to identify a dimension as the cube’s time dimension where appropriate.

For example:

PERIOD

can be configured as the time dimension so that supported time-aware behaviors can operate against it.

This setting is distinct from member-level Time Balance properties.

Conceptually:

PERIOD
  │
  └── IS_TIME_DIMENSION = TRUE


ACCOUNT Members
  │
  └── TB_TYPE / TB_TYPE_SKIP_VALUE

The time dimension provides the time hierarchy; Time Balance metadata defines how applicable members behave across it.

Definition Tables Remain the Source

After a dimension has been created, its definition table remains the source metadata for subsequent rebuilds.

Changing the source table alone does not immediately change the application-managed hierarchy structures.

The dimension must be rebuilt.

Conceptually:

Modify Definition Table
          │
          ▼
Source Metadata Changed
          │
          ▼
REBUILD_DIMENSIONS
          │
          ▼
Managed Dimension Rebuilt
          │
          ▼
Queries Use New Structure

See Rebuilding Dimensions.

Validate Definition Changes

Dimension metadata should be validated before and after significant structural changes.

A safe workflow is:

Modify Definition Table
          │
          ▼
Validate Source Structure
          │
          ▼
Rebuild Dimension
          │
          ▼
Validate Built Dimension
          │
          ▼
Run Verification Query

Validation is particularly important after changes involving:

Parent-child relationships
Shared members
Hierarchy roots
Formulas
Time configuration
Member-property mappings

See Dimension Validation for the available validation procedures.

Application-wide health validation is covered in Health and Diagnostics.

Essbase-Generated vs. Customer-Created Definitions

Both approaches ultimately produce Casabase Cube dimensions, but the source metadata originates differently.

	Essbase / Cloud EPM Import	Customer-Created
Source	Native Essbase `.otl`	Snowflake metadata table
Definition created by	Casabase Cube import processing	Customer
Location	Application `SHARED_DATA`	Any accessible Snowflake database/schema
Typical name	`<CUBE>_<DIMENSION>_OTL`	Customer-defined
`_OTL` suffix	Added automatically	Not required
Column names	Casabase-generated structure	Customer-defined
Property mappings	Derived from imported metadata	Defined through dimension configuration
Result	Casabase Cube dimension	Casabase Cube dimension

Once built, both participate in the same Casabase Cube multidimensional model.

Example: Simple Entity Dimension

A new Entity dimension does not need the complete generated _OTL structure.

For example:

CREATE TABLE FINANCE_DB.PLANNING.DIM_ENTITY (
    PARENT VARCHAR,
    MEMBER VARCHAR
);

with:

PARENT          MEMBER
--------------  ----------------
NULL            Total Entity
Total Entity    North America
Total Entity    EMEA
North America   United States
North America   Canada
EMEA            Germany
EMEA            France

can describe:

Total Entity
├── North America
│   ├── United States
│   └── Canada
└── EMEA
    ├── Germany
    └── France

The dimension configuration then maps:

PARENT ───► Parent
MEMBER ───► Member

and maps the logical ENTITY dimension to the appropriate column in the cube data.

Example: Rich Account Dimension

An Account dimension often requires more metadata:

PARENT
MEMBER
ALIAS
OPERATOR
FORMULA
TB_TYPE
TB_SKIP
UDA
SORT_ORDER

This can represent behavior such as:

Net Income
├── Revenue                  +
├── Cost of Sales            -
└── Operating Expenses       -

along with:

Display aliases
Calculated accounts
Time Balance behavior
UDAs
Explicit hierarchy ordering

The same CREATE_DIMENSION interface maps those source columns to their corresponding Casabase Cube properties.

Recommended Practices

When designing customer-created dimension definition tables:

Keep the source structure as simple as the dimension allows.
Include only the metadata required by the model.
Use clear and stable member identifiers.
Represent hierarchy relationships explicitly through parent/member mappings.
Keep aggregation behavior in a dedicated source column when it varies by member.
Preserve aliases separately from member identities.
Identify shared-member occurrences explicitly where required.
Keep formulas in a dedicated formula column.
Configure one appropriate time dimension when time-aware behavior is required.
Validate hierarchy changes before production use.
Rebuild dimensions after changing their source definitions.
Do not assume that the Snowflake nullability of a generated column determines whether the property is logically optional.
Do not assume customer-created tables must duplicate the complete _OTL table structure.

Key Principle

A dimension definition table is the source metadata contract for a dimension.

Conceptually:

Snowflake Table
      │
      ├── Members
      ├── Relationships
      ├── Aggregation
      ├── Aliases
      ├── Formulas
      └── Properties
      │
      ▼
Dimension Mapping
      │
      ▼
Casabase Cube
      │
      ▼
Multidimensional Dimension

The physical source-column names are flexible.

What matters is that the configuration maps those columns to the correct multidimensional meaning.

Dimension Definition Tables

How Dimension Definition Tables Fit into the Model

Dimension Definition Tables and Cube Data

Table Location

Tables Generated from an Essbase Outline

Customer-Created Definition Tables

Column Names Are Flexible

Hierarchy Structure

Casabase-Generated Definition Structure

Logical Requirements vs. Snowflake Nullability

Parent and Member Mappings

Aggregation Operators

Aliases

Shared Members

Formulas

Solve Order

Two-Pass Source Metadata

Time Balance

User Defined Attributes

Member Comments

Data Storage Metadata

Sort Order

Additional Properties

Mapping with CREATE_DIMENSION

Required and Optional Mappings

Mapping the Dimension to Cube Data

Security Configuration

Time Dimension Configuration

Definition Tables Remain the Source

Validate Definition Changes

Essbase-Generated vs. Customer-Created Definitions

Example: Simple Entity Dimension

Example: Rich Account Dimension

Recommended Practices

Key Principle

Related Topics