Data Model

3.1 Overview

Casabase Cube does not require you to restructure or migrate your data. It works with two types of tables that likely already exist in your Snowflake account:

• Fact tables: Tables containing the numeric measures you want to aggregate (revenue, units, cost, etc.)
• Dimension tables: Tables containing parent-child relationships that define how your data rolls up (account hierarchies, period hierarchies, entity hierarchies, etc.)

This section explains what your data needs to look like, how Casabase Cube maps to it, and how to prepare your tables for use with the application.

3.2 Fact Tables

A fact table is any Snowflake table or view containing the numeric data you want to aggregate. Casabase Cube reads directly from your fact table at query time. It does not copy, stage, or transform your data.

Requirements

Requirement	Details
Amount column	At least one numeric column containing the values to aggregate (e.g., AMT, AMOUNT, VALUE, UNITS).
Dimension columns	One column per dimension whose values match the leaf-level member names in the corresponding hierarchy.
Accessibility	The table must be granted SELECT to the Casabase Cube application.

Example Fact Table

ACCOUNT	PERIOD	ENTITY	PRODUCT	SCENARIO	AMT
Revenue	Jan	East	Widget A	Actual	150000
Revenue	Feb	East	Widget A	Actual	162000
COGS	Jan	East	Widget A	Actual	85000
Revenue	Jan	West	Widget B	Budget	200000

Key points:

• The fact table should contain leaf-level data, the lowest level of detail for each dimension. Casabase Cube dynamically aggregates upward from bottom-level members at query time.
• Column names do not need to match dimension names. When creating a hierarchy, you specify which fact table column corresponds to each dimension.
• A single fact table can serve multiple cubes, or different cubes can point to different tables.
• Views work just as well as tables. If your data requires transformation or joins before use, create a Snowflake view and point Casabase Cube at the view.

3.3 Dimension Tables

A dimension table defines the parent-child relationships for a single dimension. Casabase Cube uses these relationships to build hierarchies that support dynamic aggregation, member selection, and drill-down.

Required Columns

Column	Type	Description
Parent	VARCHAR	The parent member name. For the root (top) member, this should be NULL or an empty string ''.
Child	VARCHAR	The member name. Must be unique within the dimension (except for shared members).
Operator	VARCHAR	The aggregation operator: + (add to parent), - (subtract from parent), or ~ (no aggregation).

Optional Columns

Column	Type	Description
Alias	VARCHAR	A display-friendly name for the member (e.g., "Revenue - Domestic" instead of "REV_DOM"). Multiple alias columns can be defined for multi-language or multi-purpose aliases by passing a JSON object to ALIAS_COL during hierarchy creation (e.g., {"Default": "ALIAS", "French": "ALIAS_FR"}).
Shared	VARCHAR	Indicates whether the member is a shared member ('true' / 'false'). Shared members roll up under multiple parents.
Formula	VARCHAR	A formula expression for calculated members (e.g., "Revenue" - "Cost of Sales").
Time Balance Type	VARCHAR	An account-level property: SUM, FIRST, LAST, or AVG. Controls how values aggregate across time periods. Set on accounts, not on the time dimension.
Time Balance Skip	VARCHAR	What to skip during time balance aggregation: NONE, MISSING, ZEROS, or MISSING_AND_ZEROS.
Sort Order	INTEGER	Custom sort order for controlling the display order of members.
UDA	VARCHAR	User Defined Attribute — a custom tag for use in formulas via HASUDA().

Example Dimension Table (Account with Time Balance)

PARENT	CHILD	OPERATOR	ALIAS	TB_TYPE	TB_SKIP
	Total P&L	~	Total Profit & Loss	SUM	NONE
Total P&L	Revenue	+	Revenue	SUM	NONE
Total P&L	Cost of Sales	–	Cost of Sales	SUM	NONE
Revenue	Product Revenue	+	Product Revenue	SUM	NONE
Revenue	Service Revenue	+	Service Revenue	SUM	NONE
Cost of Sales	Direct Costs	+	Direct Costs	SUM	NONE
Cost of Sales	Indirect Costs	+	Indirect Costs	SUM	NONE
Total P&L	Headcount	~	Headcount	LAST	MISSING
Total P&L	Cash Balance	~	Cash Balance	LAST	MISSING

In this example:

• Total P&L is the root member (empty parent) with operator ~ (no aggregation — it is computed from its children).
• Revenue rolls up to Total P&L with + (added).
• Cost of Sales rolls up to Total P&L with - (subtracted).
• The resulting hierarchy: Total P&L = Revenue − Cost of Sales.
• TB_TYPE is an account-level property that controls how each account aggregates across time periods. Flow accounts like Revenue use SUM; point-in-time accounts like Headcount or Cash Balance use LAST (or FIRST, AVG).
• TB_SKIP controls what to skip during time balance aggregation: NONE (default), MISSING, ZEROS, or MISSING_AND_ZEROS. Required when TB_TYPE is specified.

Example Dimension Table (Period)

PARENT	CHILD	OPERATOR	SORTORDER
	YearTotal	~	0
YearTotal	Q1	+	1
Q1	Jan	+	2
Q1	Feb	+	3
Q1	Mar	+	4
YearTotal	Q2	+	5
Q2	Apr	+	6
Q2	May	+	7
Q2	Jun	+	8
YearTotal	Q3	+	9
Q3	Jul	+	10
Q3	Aug	+	11
Q3	Sep	+	12
YearTotal	Q4	+	13
Q4	Oct	+	14
Q4	Nov	+	15
Q4	Dec	+	16

Example Shared Members

Shared members allow a single member to roll up under multiple parents. This is useful when a single hierarchy needs to present the same data in two different groupings — for example, a primary grouping by product line and an alternate grouping by marketing channel:

PARENT	CHILD	OPERATOR	MEMBER_IS_SHARED
	Products	~	false
Products	Product Lines	~	false
Products	Marketing Groups	~	false
Product Lines	Widget X	+	false
Product Lines	Widget Y	+	false
Marketing Groups	Widget X	+	true
Marketing Groups	Widget Z	+	false

In this example, Widget X is a primary member under Product Lines and a shared member under Marketing Groups. Because Product Lines and Marketing Groups are separate branches with ~ (no aggregation), the root Products does not double-count shared data. The primary rollup comes through Product Lines, while Marketing Groups provides an alternate view of the same data.

3.4 How Dimensions Map to Fact Tables

When you create a hierarchy in Casabase Cube, you specify which column in the fact table corresponds to that dimension. This is the data table column name parameter.

For example, given a fact table with columns ACCOUNT, PERIOD, ENTITY, PRODUCT, AMT:

Hierarchy Name	Dimension Table	Data Table Column
Account	DB.SCHEMA.DIM_ACCOUNT	ACCOUNT
Period	DB.SCHEMA.DIM_PERIOD	PERIOD
Entity	DB.SCHEMA.DIM_ENTITY	ENTITY
Product	DB.SCHEMA.DIM_PRODUCT	PRODUCT

At query time, Casabase Cube joins the hierarchy’s leaf members with the matching fact table column to retrieve and aggregate data.

Important: The values in the fact table’s dimension column must match the child member names (bottom level) in the corresponding dimension table. If your fact table uses codes (e.g., ACCT_001) but your dimension table uses descriptions (e.g., Revenue), you will need to align them, either by adjusting the dimension table or creating a view over the fact table.

3.5 Using Views as Dimension Sources

Dimension tables do not need to be physical tables — Snowflake views work equally well. Views are especially useful when:

• Your source dimension data does not have the exact column names or formats Casabase Cube expects
• You need to transform or filter data before it is used as a hierarchy (e.g., filtering by plan type)
• You want to combine data from multiple source tables into a single hierarchy
• You are migrating from an EPM platform and want to reshape exported metadata

Example: Creating a Dimension View

CREATE OR REPLACE VIEW MY_DB.MY_SCHEMA.DIM_ACCOUNT_V AS

-- Root member (required — defines the top of the hierarchy)
SELECT NULL       AS PARENT,
       'Account'  AS CHILD,
       NULL       AS ALIAS,
       '~'        AS OPERATOR
FROM DUAL

UNION ALL

-- Members from your source table
SELECT PARENT_MEMBER  AS PARENT,
       MEMBER_NAME    AS CHILD,
       DISPLAY_NAME   AS ALIAS,
       CASE
           WHEN AGGREGATION_TYPE = 'NEVER' THEN '~'
           WHEN AGGREGATION_TYPE = 'SUBTRACTION' THEN '-'
           ELSE '+'
       END            AS OPERATOR
FROM MY_DB.MY_SCHEMA.SOURCE_ACCOUNT_TABLE;

The root member row (with NULL parent and ~ operator) defines the top of the hierarchy. All other members ultimately roll up to this root.

3.6 Naming Conventions & Best Practices

Member Names

Guideline	Recommendation
Uniqueness	Member names must be unique within a dimension (except shared members).
Case sensitivity	Member names are case-sensitive. "Revenue" and "revenue" are treated as different members.
Special characters	Member names can contain spaces and special characters, but avoid leading/trailing whitespace.
Consistency	Ensure fact table values exactly match leaf member names in the dimension table.

Dimension Design

Guideline	Recommendation
Root member	Every hierarchy must have exactly one root member (a member with a NULL or empty parent).
Root operator	The root member should typically use ~ (no aggregation), since its value is derived from its children.
Leaf members	Bottom-level members are the lowest-level members that correspond to rows in your fact table.
Intermediate members	Non-leaf members exist purely for aggregation and do not need to exist in the fact table.
Aggregation operators	Choose operators carefully. Use + for standard roll-up, - for contra accounts or eliminations, and ~ for labels or calculated-only members.

Fact Table Design

Guideline	Recommendation
Grain	Store data at the lowest grain (leaf level) for each dimension. Casabase Cube aggregates upward dynamically.
Pre-aggregation	Do not pre-aggregate data. If your fact table contains both detail and summary rows, filter to leaf-level only (using a view if necessary).
Amount column	Use a single numeric column for the measure. If you have multiple measures, use a dimension to distinguish them (e.g., an Account dimension with members like Revenue, Units, Cost).
NULLs	NULL values in the amount column are treated as zero during aggregation.

3.7 Multiple Cubes

Casabase Cube supports multiple cube definitions within a single installation. Common use cases:

Pattern	Example
Different data sets	A financial cube pointing to FINANCE_FACT and an operations cube pointing to OPS_FACT.
Different granularity	A summary cube at monthly/department level and a detail cube at daily/cost-center level.
Different dimensions	A revenue cube with Product, Region, Channel and a headcount cube with Department, Job Level, Location.
Same data, different views	Two cubes pointing to the same fact table but with different hierarchies or formulas.

Each cube has its own set of hierarchies, formulas, saved queries, variables, security grants, and write-back data — fully independent of other cubes.

3.8 Data Access Requirements

Before Casabase Cube can use your data, your Snowflake administrator must grant access:

-- Grant access to the database and schema containing your data
GRANT USAGE ON DATABASE your_database TO APPLICATION CASABASE_CUBE;
GRANT USAGE ON SCHEMA your_database.your_schema TO APPLICATION CASABASE_CUBE;

-- Grant SELECT on fact tables
GRANT SELECT ON TABLE your_database.your_schema.fact_table
  TO APPLICATION CASABASE_CUBE;

-- Grant SELECT on dimension tables or views
GRANT SELECT ON TABLE your_database.your_schema.dim_account
  TO APPLICATION CASABASE_CUBE;

GRANT SELECT ON VIEW your_database.your_schema.dim_period_v
  TO APPLICATION CASABASE_CUBE;

Key points:

• Casabase Cube only requires SELECT. It never writes to, modifies, or deletes data from your source tables.
• You must grant USAGE on both the database and schema, in addition to SELECT on the individual tables and views.
• Grants can be revoked at any time to immediately remove the application’s access to your data.
• If your data spans multiple databases or schemas, grant access to each one.