Support Calculated Dimensions with Expression Builder

[cliftonc]

Summary

Enable users to create derived dimensions using expressions, similar to how calculated measures work. This allows creating categories, bins, and transformed fields without modifying the underlying data.

Current State

Only calculated measures are supported. Dimensions must be direct column references—no expressions, CASE statements, or transformations.

Proposed Features

Expression-based Dimensions

defineCube({
  name: 'Sales',
  dimensions: {
    // Simple column reference
    category: { type: 'string', sql: () => products.category },
    
    // Calculated dimension with CASE
    priceRange: {
      type: 'string',
      calculatedSql: \`
        CASE 
          WHEN {price} < 50 THEN 'Budget'
          WHEN {price} < 200 THEN 'Mid-range'
          ELSE 'Premium'
        END
      \`,
      dependencies: ['price']
    },
    
    // Numeric binning
    ageGroup: {
      type: 'string',
      calculatedSql: \`
        CASE 
          WHEN {age} < 25 THEN '18-24'
          WHEN {age} < 35 THEN '25-34'
          WHEN {age} < 45 THEN '35-44'
          ELSE '45+'
        END
      \`,
      dependencies: ['age']
    }
  }
})

Dimension Types

CASE Statements:

• Categorize numeric values into groups
• Map codes to readable labels
• Conditional grouping logic

Numeric Binning:

• Create ranges/buckets from numeric fields
• Configurable bin width or custom breakpoints

String Transformations:

• Extract substrings (first N chars, regex)
• Concatenate multiple fields
• Upper/lower case transformations

Date Part Extraction:

• Fiscal year (custom start month)
• Week number (ISO or custom)
• Quarter/semester
• Day of week

Expression Builder UI

• Visual interface for building expressions
• Function library (CASE, COALESCE, string functions)
• Preview calculated values
• Validation before save

Competitor Reference

• Google Looker Studio: "Calculated fields let you create new metrics and dimensions derived from your data"
• Adobe CJA: "Derived fields allow you to define complex data manipulations through a customizable rule builder"
• Bold BI: "Calculated field comprising of built-in functions, formulas and other columns"

Technical Approach

1. Extend DimensionDefinition with calculatedSql property
2. Create expression parser with type validation
3. Add database-specific SQL generation for CASE, string, date functions
4. Build CalculatedFieldEditor component for query builder
5. Validate expressions during cube registration

Expression Functions to Support

• String: CONCAT, SUBSTRING, UPPER, LOWER, TRIM, REPLACE
• Numeric: ROUND, FLOOR, CEIL, ABS, MOD
• Date: YEAR, MONTH, DAY, DAYOFWEEK, QUARTER
• Logic: CASE, COALESCE, NULLIF, IF
• Type: CAST, TO_CHAR, TO_DATE

Validation

• Type checking for expression output
• Dependency resolution (similar to calculated measures)
• SQL injection prevention through parameterization

Labels

enhancement feature-request server query-builder