Foggy Data MCP

English

简体中文

English

简体中文

Appearance

TM Syntax Manual ​

Download this document

TM (Table Model) defines the structure and relationships of database tables. This document provides a complete TM syntax specification.

1. Basic Structure ​

TM files use JavaScript ES6 module syntax, exporting a model object:

javascript
export const model = {
    name: 'FactSalesModel',      // Model name (required, unique identifier)
    caption: 'Sales Fact Table', // Display name
    description: 'Sales order details data', // Description
    tableName: 'fact_sales',     // Database table name (required)
    idColumn: 'sales_key',       // Primary key column

    dimensions: [...],           // Dimension definitions (relationships)
    properties: [...],           // Property definitions (table fields)
    measures: [...]              // Measure definitions (aggregatable fields)
};

1.1 Basic Model Fields ​

FieldTypeRequiredDescription
namestringYesUnique model identifier, referenced by QM
captionstringNoDisplay name, recommended for AI integration
descriptionstringNoDetailed description, recommended for AI integration
tableNamestringYes¹Database table name or MongoDB collection
viewSqlstringNo¹View SQL, alternative to tableName
schemastringNoDatabase schema (for cross-schema access)
idColumnstringNoPrimary key column name
typestringNoModel type: jdbc (default), mongo, or vector
deprecatedbooleanNoMark as deprecated, default false
dataSourceNamestringNoNamed data source reference, supports different models using different data sources
autoLoadDimensionsbooleanNoAuto-load dimension definitions
autoLoadMeasuresbooleanNoAuto-load measure definitions
preAggregationsarrayNoPre-aggregation configuration list, see Chapter 11

¹ tableName and viewSql are mutually exclusive, tableName takes precedence

1.2 AI Enhancement Configuration ​

Add ai configuration to models, dimensions, properties, and measures for AI natural language queries:

javascript
{
    name: 'salesAmount',
    caption: 'Sales Amount',
    type: 'MONEY',
    ai: {
        enabled: true,              // Enable AI analysis (default true)
        prompt: 'Customer actual payment amount', // Override description
        levels: [1, 2]              // Activation levels
    }
}
FieldTypeDescription
enabledbooleanEnable AI analysis, default true
promptstringPrompt text, replaces description if provided
levelsnumber[]Activation levels, field can belong to multiple levels

2. Dimension Definitions (dimensions) ​

Dimensions define relationships with other tables, automatically generating JOINs in queries.

2.1 Basic Dimension ​

javascript
dimensions: [
    {
        name: 'customer',              // Dimension name (for query reference)
        caption: 'Customer',           // Display name
        description: 'Customer who purchased products', // Description

        tableName: 'dim_customer',     // Related dimension table
        foreignKey: 'customer_key',    // Foreign key in fact table
        primaryKey: 'customer_key',    // Primary key in dimension table
        captionColumn: 'customer_name', // Display column

        keyCaption: 'Customer Key',    // Primary key display name
        keyDescription: 'Customer surrogate key, auto-increment integer',

        // Dimension properties (queryable fields from dimension table)
        properties: [
            {
                column: 'customer_id',
                caption: 'Customer ID',
                description: 'Customer unique identifier'
            },
            {
                column: 'customer_type',
                caption: 'Customer Type',
                description: 'Customer type: Individual/Enterprise'
            },
            { column: 'province', caption: 'Province' },
            { column: 'city', caption: 'City' },
            { column: 'member_level', caption: 'Member Level' }
        ]
    }
]

2.2 Dimension Field Reference ​

FieldTypeRequiredDescription
namestringYesDimension name, use dimension$property format in queries
captionstringNoDisplay name
descriptionstringNoDetailed description
tableNamestringYes¹Related dimension table name
viewSqlstringNo¹Dimension view SQL, alternative to tableName
schemastringNoDimension table schema
foreignKeystringYesForeign key field in fact table
primaryKeystringYesPrimary key field in dimension table
captionColumnstringNoDisplay field for dimension$caption (simple form)
captionDefobjectNoCaption advanced definition (higher priority than captionColumn), see below
keyCaptionstringNoPrimary key display name, default ${caption}Key
keyDescriptionstringNoPrimary key description
typestringNoDimension type: NORMAL/DAY/DATETIME/DICT/BOOL/INTEGER/DOUBLE
propertiesarrayNoQueryable properties from dimension table
forceIndexstringNoForce specific index name
aliasstringNoDimension alias (redefine column name prefix)
deprecatedbooleanNoMark as deprecated
dimensionDataSqlfunctionNoDimension data SQL function (for access control)
onBuilderfunctionNoDimension join builder function

DAY vs DATETIME: DAY contains only the date part (yyyy-MM-dd), DATETIME contains both date and time (yyyy-MM-dd HH:mm:ss).

captionDef Advanced Definition ​

When captionColumn is not sufficient (e.g., formulas across database dialects), use captionDef:

javascript
{
    name: 'orderDate',
    captionDef: {
        // Method 1: Universal formula (applies to all dialects)
        formulaDef: {
            value: 'DATE_FORMAT(order_date, "%Y-%m")'
        },

        // Method 2: Dialect-specific formula (highest priority)
        dialectFormulaDef: {
            mysql: { value: 'DATE_FORMAT(order_date, "%Y-%m")' },
            postgresql: { value: "TO_CHAR(order_date, 'YYYY-MM')" },
            sqlserver: { value: "FORMAT(order_date, 'yyyy-MM')" }
        },

        // Method 3: Direct column reference (lowest priority)
        column: 'order_date'
    }
}

Priority: dialectFormulaDef > formulaDef > column > outer captionColumn

¹ tableName and viewSql are mutually exclusive, tableName takes precedence

2.3 Nested Dimensions (Snowflake Schema) ​

Nested dimensions implement snowflake schema where dimension tables have hierarchical relationships.

javascript
{
    // Level 1 dimension: Product (directly linked to fact table)
    name: 'product',
    tableName: 'dim_product',
    foreignKey: 'product_key',       // Foreign key in fact table
    primaryKey: 'product_key',
    captionColumn: 'product_name',
    caption: 'Product',

    properties: [
        { column: 'product_id', caption: 'Product ID' },
        { column: 'brand', caption: 'Brand' },
        { column: 'unit_price', caption: 'Unit Price', type: 'MONEY' }
    ],

    // Nested sub-dimension: Category (linked to product, not fact table)
    dimensions: [
        {
            name: 'category',
            alias: 'productCategory',   // Alias for simpler QM access
            tableName: 'dim_category',
            foreignKey: 'category_key', // Foreign key in parent table (dim_product)
            primaryKey: 'category_key',
            captionColumn: 'category_name',
            caption: 'Category',

            properties: [
                { column: 'category_id', caption: 'Category ID' },
                { column: 'category_level', caption: 'Category Level' }
            ],

            // Continue nesting: Category Group (linked to category)
            dimensions: [
                {
                    name: 'group',
                    alias: 'categoryGroup',
                    tableName: 'dim_category_group',
                    foreignKey: 'group_key',  // Foreign key in parent (dim_category)
                    primaryKey: 'group_key',
                    captionColumn: 'group_name',
                    caption: 'Category Group',

                    properties: [
                        { column: 'group_id', caption: 'Group ID' },
                        { column: 'group_type', caption: 'Group Type' }
                    ]
                }
            ]
        }
    ]
}

Key Points for Nested Dimensions:

FieldDescription
aliasDimension alias for simpler QM column access
foreignKeyImportant: Nested dimension's foreignKey refers to parent dimension table
dimensionsSub-dimension list, can continue nesting for multi-level structures

Syntax Design Principle: Nested dimension references use two separators, each with a distinct role:

SeparatorRoleExample
. (dot)Dimension path navigation — which dimension to locateproduct.category.group
$ (dollar)Property access — which field of the dimensioncategory$caption

Combined: product.category$caption = navigate product → category path, access caption property. Do NOT use multiple $ instead of . (e.g., product$category$caption) — the parser cannot distinguish dimension paths from property names.

Referencing Nested Dimensions in QM (three equivalent formats):

javascript
// Format 1: Alias (recommended, concise)
// Requires alias defined in TM, e.g., alias: 'productCategory'
{ ref: fs.productCategory$caption }
{ ref: fs.categoryGroup$caption }

// Format 2: Full path (precise, no alias needed)
{ ref: fs.product.category$caption }
{ ref: fs.product.category.group$caption }

// Format 3: Underscore format in DSL queries (output column name format)
columns: ["product_category$caption", "product_category_group$caption"]

Output Column Name Conversion: Dots in paths are automatically converted to underscores in output, avoiding JavaScript property name conflicts:

QM ReferenceOutput Column Name
product$captionproduct$caption
product.category$captionproduct_category$caption
product.category.group$captionproduct_category_group$caption

Generated SQL JOIN:

sql
SELECT ...
FROM fact_sales f
LEFT JOIN dim_product p ON f.product_key = p.product_key
LEFT JOIN dim_category c ON p.category_key = c.category_key
LEFT JOIN dim_category_group g ON c.group_key = g.group_key

2.4 Parent-Child Dimensions (Hierarchy) ​

Parent-child dimensions handle tree-structured data (e.g., organization hierarchy, product categories) using closure tables for efficient queries.

javascript
{
    name: 'team',
    tableName: 'dim_team',
    foreignKey: 'team_id',
    primaryKey: 'team_id',
    captionColumn: 'team_name',
    caption: 'Team',
    description: 'Sales team',
    keyDescription: 'Team ID, string format',

    // Parent-child configuration
    closureTableName: 'team_closure',  // Closure table name (required)
    parentKey: 'parent_id',            // Ancestor column in closure table (required)
    childKey: 'team_id',               // Descendant column in closure table (required)

    properties: [
        { column: 'team_id', caption: 'Team ID', type: 'STRING' },
        { column: 'team_name', caption: 'Team Name', type: 'STRING' },
        { column: 'parent_id', caption: 'Parent Team', type: 'STRING' },
        { column: 'team_level', caption: 'Level', type: 'INTEGER' },
        { column: 'manager_name', caption: 'Manager', type: 'STRING' }
    ]
}

Parent-Child Specific Fields:

FieldTypeRequiredDescription
closureTableNamestringYesClosure table name
closureTableSchemastringNoClosure table schema
parentKeystringYesAncestor column in closure table (e.g., parent_id)
childKeystringYesDescendant column in closure table (e.g., team_id)

Closure Table Structure Example:

sql
CREATE TABLE team_closure (
    parent_id VARCHAR(50),  -- Ancestor node ID
    child_id  VARCHAR(50),  -- Descendant node ID
    depth     INT,          -- Hierarchy depth (0 for self)
    PRIMARY KEY (parent_id, child_id)
);

See Parent-Child Dimensions for detailed documentation

3. Property Definitions (properties) ​

Properties define the table's own fields (non-aggregatable).

3.1 Basic Properties ​

javascript
properties: [
    {
        column: 'order_id',        // Database column name (required)
        name: 'orderId',           // Property name (optional)
        caption: 'Order ID',       // Display name
        description: 'Order unique identifier', // Detailed description
        type: 'STRING'             // Data type
    },
    {
        column: 'order_status',
        caption: 'Order Status',
        type: 'STRING'
    },
    {
        column: 'created_at',
        caption: 'Created Time',
        type: 'DATETIME'
    }
]

3.2 Dictionary Reference Properties ​

Use dictRef to map database values to display labels:

javascript
import { dicts } from '../dicts.fsscript';

properties: [
    {
        column: 'order_status',
        caption: 'Order Status',
        type: 'STRING',
        dictRef: dicts.order_status  // Reference dictionary
    },
    {
        column: 'payment_method',
        caption: 'Payment Method',
        type: 'STRING',
        dictRef: dicts.payment_method
    }
]

Dictionary Definition Example (dicts.fsscript):

javascript
import { registerDict } from '@jdbcModelDictService';

export const dicts = {
    order_status: registerDict({
        id: 'order_status',
        caption: 'Order Status',
        items: [
            { value: 'PENDING', label: 'Pending' },
            { value: 'CONFIRMED', label: 'Confirmed' },
            { value: 'SHIPPED', label: 'Shipped' },
            { value: 'COMPLETED', label: 'Completed' },
            { value: 'CANCELLED', label: 'Cancelled' }
        ]
    }),

    payment_method: registerDict({
        id: 'payment_method',
        caption: 'Payment Method',
        items: [
            { value: '1', label: 'Prepaid' },
            { value: '2', label: 'Collect' },
            { value: '3', label: 'Cash on Delivery' }
        ]
    })
};

3.3 Calculated Properties ​

Use formulaDef to define calculated fields. The builder function receives the table alias and returns a raw SQL expression:

javascript
properties: [
    {
        column: 'customer_name',
        name: 'fullName',
        caption: 'Full Name',
        type: 'STRING',
        formulaDef: {
            builder: (alias) => {
                return `CONCAT(${alias}.first_name, ' ', ${alias}.last_name)`;
            },
            description: 'Concatenate first and last name'
        }
    },
    {
        column: 'amount',
        name: 'amountInWan',
        caption: 'Amount (10K)',
        type: 'MONEY',
        formulaDef: {
            builder: (alias) => {
                return `${alias}.amount / 10000`;
            },
            description: 'Convert amount to 10K unit'
        }
    }
]

Dialect Awareness

builder produces raw SQL embedded directly into queries. For dialect-specific operations like JSON extraction, write SQL for your target database:

ScenarioMySQLPostgreSQLSQL Server
JSON text extractcol ->> '$.key'col ->> 'key'JSON_VALUE(col, '$.key')
Type castCAST(col AS SIGNED)col::integerCAST(col AS INT)

Universal functions (CONCAT, COALESCE, ROUND, etc.) are safe across all dialects.

3.4 Property Field Reference ​

FieldTypeRequiredDescription
columnstringYesDatabase column name
namestringNoProperty name, defaults to camelCase of column
aliasstringNoProperty alias
captionstringNoDisplay name
descriptionstringNoDetailed description, helps AI inference
typestringNoData type (see 5. Data Types)
formatstringNoFormat template (for dates, etc.)
dictRefstringNoDictionary reference for value-to-label mapping
formulaDefobjectNoFormula definition (see 3.5)

3.5 Formula Definition (formulaDef) ​

FieldTypeDescription
builderfunctionSQL builder function, parameter alias is table alias
valuestringFormula expression (based on measure names)
descriptionstringText description of formula

builder and value are mutually exclusive, builder is more flexible for direct SQL manipulation

4. Measure Definitions (measures) ​

Measures define aggregatable numeric fields.

4.1 Basic Measures ​

javascript
measures: [
    {
        column: 'quantity',         // Database column name (required)
        name: 'salesQuantity',      // Measure name (optional)
        caption: 'Sales Quantity',  // Display name
        description: 'Product sales count', // Detailed description
        type: 'INTEGER',            // Data type
        aggregation: 'sum'          // Aggregation method (required)
    },
    {
        column: 'sales_amount',
        name: 'salesAmount',
        caption: 'Sales Amount',
        type: 'MONEY',
        aggregation: 'sum'
    },
    {
        column: 'unit_price',
        caption: 'Unit Price',
        type: 'MONEY',
        aggregation: 'avg'          // Average aggregation
    }
]

4.2 Calculated Measures ​

Use formulaDef to define calculated measures:

javascript
measures: [
    {
        column: 'tax_amount',
        name: 'taxAmount2',
        caption: 'Tax Amount*2',
        description: 'For testing calculated fields',
        type: 'MONEY',
        formulaDef: {
            builder: (alias) => {
                return `${alias}.tax_amount + 1`;
            },
            description: 'Tax amount plus one'
        }
    }
]

4.3 COUNT Aggregation ​

Count not based on specific column:

javascript
measures: [
    {
        name: 'recordCount',
        caption: 'Record Count',
        aggregation: 'count',
        type: 'INTEGER'
    }
]

4.4 Measure Field Reference ​

FieldTypeRequiredDescription
columnstringNo¹Database column name
namestringNoMeasure name, defaults to camelCase of column
aliasstringNoMeasure alias
captionstringNoDisplay name
descriptionstringNoDetailed description
typestringNoData type (see 5. Data Types)
aggregationstringYesAggregation method (see 4.5)
formulaDefobjectNoFormula definition (see 3.5)

¹ count aggregation doesn't require column

4.5 Aggregation Methods ​

ValueDescriptionApplicable Types
sumSumNumeric types
avgAverageNumeric types
countCountAll types
maxMaximumNumeric/Date types
minMinimumNumeric/Date types
noneNo aggregationAll types

5. Data Types ​

5.1 Type List ​

TypeAliasesDescriptionJava TypeUse Case
STRINGTEXTStringStringText, codes
INTEGER-IntegerIntegerCounts, enums
BIGINTLONGLong integerLongLarge number PKs
MONEYNUMBER, BigDecimalDecimal/MoneyBigDecimalAmounts, prices
DATETIME-DatetimeDateTimestamps
DAYDATEDateDateDate (yyyy-MM-dd)
BOOLBooleanBooleanBooleanYes/No flags
DICT-DictionaryIntegerDictionary codes
VECTOR-Vector embeddingfloat[]Similarity search

5.2 Type Selection Guidelines ​

  • Amount fields: Use MONEY to avoid floating-point precision issues
  • Primary keys: Surrogate keys use INTEGER or BIGINT, business keys use STRING
  • Date fields: Use DATETIME for timestamps, DAY for date-only
  • Enum fields: Prefer dictRef + STRING over creating dimension tables

6. Complete Examples ​

6.1 Fact Table Model ​

javascript
// FactSalesModel.tm
/**
 * Sales Fact Table Model Definition
 *
 * @description E-commerce test data - Sales fact table (order details)
 *              Includes date, product, customer, store, channel, promotion dimensions
 */
import { dicts } from '../dicts.fsscript';

export const model = {
    name: 'FactSalesModel',
    caption: 'Sales Fact Table',
    tableName: 'fact_sales',
    idColumn: 'sales_key',

    // Dimension definitions
    dimensions: [
        {
            name: 'salesDate',
            tableName: 'dim_date',
            foreignKey: 'date_key',
            primaryKey: 'date_key',
            captionColumn: 'full_date',
            caption: 'Sales Date',
            description: 'Date of order',
            keyDescription: 'Date key, format yyyyMMdd, e.g., 20240101',

            properties: [
                { column: 'year', caption: 'Year', description: 'Year of sale' },
                { column: 'quarter', caption: 'Quarter', description: 'Quarter (1-4)' },
                { column: 'month', caption: 'Month', description: 'Month (1-12)' },
                { column: 'month_name', caption: 'Month Name' },
                { column: 'day_of_week', caption: 'Day of Week' },
                { column: 'is_weekend', caption: 'Is Weekend' }
            ]
        },
        {
            name: 'product',
            tableName: 'dim_product',
            foreignKey: 'product_key',
            primaryKey: 'product_key',
            captionColumn: 'product_name',
            caption: 'Product',
            description: 'Product information',

            properties: [
                { column: 'product_id', caption: 'Product ID' },
                { column: 'category_name', caption: 'Category Name' },
                { column: 'sub_category_name', caption: 'Sub-category Name' },
                { column: 'brand', caption: 'Brand' },
                { column: 'unit_price', caption: 'Unit Price', type: 'MONEY' },
                { column: 'unit_cost', caption: 'Unit Cost', type: 'MONEY' }
            ]
        },
        {
            name: 'customer',
            tableName: 'dim_customer',
            foreignKey: 'customer_key',
            primaryKey: 'customer_key',
            captionColumn: 'customer_name',
            caption: 'Customer',

            properties: [
                { column: 'customer_id', caption: 'Customer ID' },
                { column: 'customer_type', caption: 'Customer Type' },
                { column: 'gender', caption: 'Gender' },
                { column: 'age_group', caption: 'Age Group' },
                { column: 'province', caption: 'Province' },
                { column: 'city', caption: 'City' },
                { column: 'member_level', caption: 'Member Level' }
            ]
        }
    ],

    // Property definitions
    properties: [
        {
            column: 'sales_key',
            caption: 'Sales Key',
            type: 'BIGINT'
        },
        {
            column: 'order_id',
            caption: 'Order ID',
            type: 'STRING'
        },
        {
            column: 'order_line_no',
            caption: 'Order Line',
            type: 'INTEGER'
        },
        {
            column: 'order_status',
            caption: 'Order Status',
            type: 'STRING',
            dictRef: dicts.order_status
        },
        {
            column: 'payment_method',
            caption: 'Payment Method',
            type: 'STRING',
            dictRef: dicts.payment_method
        },
        {
            column: 'created_at',
            caption: 'Created Time',
            type: 'DATETIME'
        }
    ],

    // Measure definitions
    measures: [
        {
            column: 'quantity',
            caption: 'Quantity',
            type: 'INTEGER',
            aggregation: 'sum'
        },
        {
            column: 'sales_amount',
            name: 'salesAmount',
            caption: 'Sales Amount',
            type: 'MONEY',
            aggregation: 'sum'
        },
        {
            column: 'cost_amount',
            name: 'costAmount',
            caption: 'Cost Amount',
            type: 'MONEY',
            aggregation: 'sum'
        },
        {
            column: 'profit_amount',
            name: 'profitAmount',
            caption: 'Profit Amount',
            type: 'MONEY',
            aggregation: 'sum'
        }
    ]
};

6.2 Dimension Table Model ​

javascript
// DimProductModel.tm
/**
 * Product Dimension Model Definition
 *
 * @description E-commerce test data - Product dimension table
 */
export const model = {
    name: 'DimProductModel',
    caption: 'Product Dimension',
    tableName: 'dim_product',
    idColumn: 'product_key',

    dimensions: [],  // Dimension tables typically don't reference other dimensions

    properties: [
        {
            column: 'product_key',
            caption: 'Product Key',
            type: 'INTEGER'
        },
        {
            column: 'product_id',
            caption: 'Product Business ID',
            type: 'STRING'
        },
        {
            column: 'product_name',
            caption: 'Product Name',
            type: 'STRING'
        },
        {
            column: 'category_id',
            caption: 'Category ID',
            type: 'STRING'
        },
        {
            column: 'category_name',
            caption: 'Category Name',
            type: 'STRING'
        },
        {
            column: 'sub_category_id',
            caption: 'Sub-category ID',
            type: 'STRING'
        },
        {
            column: 'sub_category_name',
            caption: 'Sub-category Name',
            type: 'STRING'
        },
        {
            column: 'brand',
            caption: 'Brand',
            type: 'STRING'
        },
        {
            column: 'unit_price',
            caption: 'Price',
            type: 'MONEY'
        },
        {
            column: 'unit_cost',
            caption: 'Cost',
            type: 'MONEY'
        },
        {
            column: 'status',
            caption: 'Status',
            type: 'STRING'
        },
        {
            column: 'created_at',
            caption: 'Created Time',
            type: 'DATETIME'
        }
    ],

    measures: []  // Dimension tables typically have no measures
};

7. Dimension Reuse Best Practices ​

In real projects, the same dimension tables (e.g., date dimension, customer dimension) are often referenced by multiple fact tables. TM files use FSScript (JavaScript-like) syntax, supporting function encapsulation and module imports. You can extract common dimension configurations into factory functions for dimension definition reuse.

7.1 Creating Dimension Builders ​

Encapsulate commonly used dimensions as functions in a separate file:

javascript
// dimensions/common-dims.fsscript
/**
 * Common Dimension Builders
 * Provides reusable dimension definition factory functions
 */

/**
 * Build Date Dimension
 * @param {object} options - Configuration options
 * @param {string} options.name - Dimension name, default 'salesDate'
 * @param {string} options.foreignKey - Foreign key column, default 'date_key'
 * @param {string} options.caption - Display name, default 'Date'
 * @returns {object} Dimension configuration object
 */
export function buildDateDim(options = {}) {
    const {
        name = 'salesDate',
        foreignKey = 'date_key',
        caption = 'Date',
        description = 'Business event date'
    } = options;

    return {
        name,
        tableName: 'dim_date',
        foreignKey,
        primaryKey: 'date_key',
        captionColumn: 'full_date',
        caption,
        description,
        keyDescription: 'Date key, format yyyyMMdd, e.g., 20240101',
        type: 'DATETIME',

        properties: [
            { column: 'year', caption: 'Year', type: 'INTEGER', description: 'Year' },
            { column: 'quarter', caption: 'Quarter', type: 'INTEGER', description: 'Quarter (1-4)' },
            { column: 'month', caption: 'Month', type: 'INTEGER', description: 'Month (1-12)' },
            { column: 'month_name', caption: 'Month Name', type: 'STRING' },
            { column: 'week_of_year', caption: 'Week of Year', type: 'INTEGER' },
            { column: 'day_of_week', caption: 'Day of Week', type: 'INTEGER' },
            { column: 'is_weekend', caption: 'Is Weekend', type: 'BOOL' },
            { column: 'is_holiday', caption: 'Is Holiday', type: 'BOOL' }
        ]
    };
}

/**
 * Build Customer Dimension
 * @param {object} options - Configuration options
 */
export function buildCustomerDim(options = {}) {
    const {
        name = 'customer',
        foreignKey = 'customer_key',
        caption = 'Customer',
        description = 'Customer information'
    } = options;

    return {
        name,
        tableName: 'dim_customer',
        foreignKey,
        primaryKey: 'customer_key',
        captionColumn: 'customer_name',
        caption,
        description,
        keyDescription: 'Customer surrogate key, auto-increment integer',

        properties: [
            { column: 'customer_id', caption: 'Customer ID', description: 'Customer unique identifier' },
            { column: 'customer_type', caption: 'Customer Type', description: 'Individual/Enterprise' },
            { column: 'gender', caption: 'Gender' },
            { column: 'age_group', caption: 'Age Group' },
            { column: 'province', caption: 'Province' },
            { column: 'city', caption: 'City' },
            { column: 'member_level', caption: 'Member Level' }
        ]
    };
}

/**
 * Build Product Dimension
 * @param {object} options - Configuration options
 */
export function buildProductDim(options = {}) {
    const {
        name = 'product',
        foreignKey = 'product_key',
        caption = 'Product',
        description = 'Product information'
    } = options;

    return {
        name,
        tableName: 'dim_product',
        foreignKey,
        primaryKey: 'product_key',
        captionColumn: 'product_name',
        caption,
        description,
        keyDescription: 'Product surrogate key, auto-increment integer',

        properties: [
            { column: 'product_id', caption: 'Product ID' },
            { column: 'category_name', caption: 'Category Name' },
            { column: 'sub_category_name', caption: 'Sub-category Name' },
            { column: 'brand', caption: 'Brand' },
            { column: 'unit_price', caption: 'Unit Price', type: 'MONEY' },
            { column: 'unit_cost', caption: 'Unit Cost', type: 'MONEY' }
        ]
    };
}

/**
 * Build Store Dimension
 */
export function buildStoreDim(options = {}) {
    const {
        name = 'store',
        foreignKey = 'store_key',
        caption = 'Store',
        description = 'Store information'
    } = options;

    return {
        name,
        tableName: 'dim_store',
        foreignKey,
        primaryKey: 'store_key',
        captionColumn: 'store_name',
        caption,
        description,

        properties: [
            { column: 'store_id', caption: 'Store ID' },
            { column: 'store_type', caption: 'Store Type' },
            { column: 'province', caption: 'Province' },
            { column: 'city', caption: 'City' }
        ]
    };
}

7.2 Using Dimension Builders in TM Files ​

javascript
// model/FactSalesModel.tm
import { dicts } from '../dicts.fsscript';
import {
    buildDateDim,
    buildCustomerDim,
    buildProductDim,
    buildStoreDim
} from '../dimensions/common-dims.fsscript';

export const model = {
    name: 'FactSalesModel',
    caption: 'Sales Fact Table',
    tableName: 'fact_sales',
    idColumn: 'sales_key',

    dimensions: [
        // Use builder with custom name and description
        buildDateDim({
            name: 'salesDate',
            caption: 'Sales Date',
            description: 'Order date'
        }),

        // Use default configuration
        buildCustomerDim(),
        buildProductDim(),
        buildStoreDim(),

        // Mixed use: builder + inline dimension
        {
            name: 'channel',
            tableName: 'dim_channel',
            foreignKey: 'channel_key',
            primaryKey: 'channel_key',
            captionColumn: 'channel_name',
            caption: 'Channel',
            properties: [
                { column: 'channel_id', caption: 'Channel ID' },
                { column: 'channel_type', caption: 'Channel Type' }
            ]
        }
    ],

    properties: [
        // ... property definitions
    ],

    measures: [
        // ... measure definitions
    ]
};
javascript
// model/FactOrderModel.tm
import { buildDateDim, buildCustomerDim } from '../dimensions/common-dims.fsscript';

export const model = {
    name: 'FactOrderModel',
    caption: 'Order Fact Table',
    tableName: 'fact_order',
    idColumn: 'order_key',

    dimensions: [
        // Order model uses different dimension name
        buildDateDim({
            name: 'orderDate',
            foreignKey: 'order_date_key',
            caption: 'Order Date'
        }),
        buildCustomerDim(),

        // Order may have multiple date dimensions
        buildDateDim({
            name: 'shipDate',
            foreignKey: 'ship_date_key',
            caption: 'Ship Date',
            description: 'Order shipping date'
        })
    ],

    properties: [...],
    measures: [...]
};

7.3 Advanced Technique: Property Extension and Override ​

Builder-returned objects can be extended or overridden using spread operator:

javascript
import { buildCustomerDim } from '../dimensions/common-dims.fsscript';

export const model = {
    name: 'FactVIPSalesModel',
    caption: 'VIP Sales Fact Table',
    tableName: 'fact_vip_sales',

    dimensions: [
        // Extend customer dimension with additional properties
        {
            ...buildCustomerDim({ caption: 'VIP Customer' }),
            // Add VIP-specific properties
            properties: [
                ...buildCustomerDim().properties,
                { column: 'vip_level', caption: 'VIP Level' },
                { column: 'vip_points', caption: 'Points Balance', type: 'INTEGER' },
                { column: 'vip_expire_date', caption: 'Membership Expiry', type: 'DAY' }
            ]
        }
    ],

    // ...
};

7.4 Reusing Nested Dimensions ​

For snowflake schema nested dimensions, build complete hierarchies with sub-dimensions:

javascript
// dimensions/product-hierarchy.fsscript
/**
 * Build Product Dimension with Category Hierarchy (Snowflake Schema)
 */
export function buildProductWithCategoryDim(options = {}) {
    const {
        name = 'product',
        foreignKey = 'product_key',
        caption = 'Product'
    } = options;

    return {
        name,
        tableName: 'dim_product',
        foreignKey,
        primaryKey: 'product_key',
        captionColumn: 'product_name',
        caption,

        properties: [
            { column: 'product_id', caption: 'Product ID' },
            { column: 'brand', caption: 'Brand' },
            { column: 'unit_price', caption: 'Price', type: 'MONEY' }
        ],

        // Nested category dimension
        dimensions: [
            {
                name: 'category',
                alias: 'productCategory',
                tableName: 'dim_category',
                foreignKey: 'category_key',
                primaryKey: 'category_key',
                captionColumn: 'category_name',
                caption: 'Category',

                properties: [
                    { column: 'category_id', caption: 'Category ID' },
                    { column: 'category_level', caption: 'Category Level' }
                ],

                // Continue nesting with category group
                dimensions: [
                    {
                        name: 'group',
                        alias: 'categoryGroup',
                        tableName: 'dim_category_group',
                        foreignKey: 'group_key',
                        primaryKey: 'group_key',
                        captionColumn: 'group_name',
                        caption: 'Category Group',

                        properties: [
                            { column: 'group_id', caption: 'Group ID' },
                            { column: 'group_type', caption: 'Group Type' }
                        ]
                    }
                ]
            }
        ]
    };
}

7.5 Reusing Parent-Child Dimensions ​

For organization hierarchy and similar parent-child dimensions, parameterize closure table configuration:

javascript
// dimensions/hierarchy-dims.fsscript
/**
 * Build Organization/Team Parent-Child Dimension
 */
export function buildOrgDim(options = {}) {
    const {
        name = 'team',
        tableName = 'dim_team',
        foreignKey = 'team_id',
        closureTableName = 'team_closure',
        caption = 'Team',
        description = 'Organization team'
    } = options;

    return {
        name,
        tableName,
        foreignKey,
        primaryKey: 'team_id',
        captionColumn: 'team_name',
        caption,
        description,

        // Parent-child configuration
        closureTableName,
        parentKey: 'parent_id',
        childKey: 'team_id',

        properties: [
            { column: 'team_id', caption: 'Team ID', type: 'STRING' },
            { column: 'team_name', caption: 'Team Name', type: 'STRING' },
            { column: 'parent_id', caption: 'Parent Team', type: 'STRING' },
            { column: 'team_level', caption: 'Level', type: 'INTEGER' },
            { column: 'manager_name', caption: 'Manager', type: 'STRING' }
        ]
    };
}

/**
 * Build Region Parent-Child Dimension
 */
export function buildRegionDim(options = {}) {
    const {
        name = 'region',
        foreignKey = 'region_id',
        caption = 'Region'
    } = options;

    return {
        name,
        tableName: 'dim_region',
        foreignKey,
        primaryKey: 'region_id',
        captionColumn: 'region_name',
        caption,

        closureTableName: 'region_closure',
        parentKey: 'parent_id',
        childKey: 'region_id',

        properties: [
            { column: 'region_id', caption: 'Region ID', type: 'STRING' },
            { column: 'region_name', caption: 'Region Name', type: 'STRING' },
            { column: 'region_type', caption: 'Region Type', type: 'STRING' },
            { column: 'region_level', caption: 'Level', type: 'INTEGER' }
        ]
    };
}

7.6 Recommended Project Structure ​

templates/
├── dimensions/                    # Dimension builders directory
│   ├── common-dims.fsscript       # Common dimensions (date, customer, product, etc.)
│   ├── hierarchy-dims.fsscript    # Parent-child dimensions (org, region, etc.)
│   └── product-hierarchy.fsscript # Product snowflake dimension
├── model/                         # TM model directory
│   ├── FactSalesModel.tm
│   ├── FactOrderModel.tm
│   └── ...
├── query/                         # QM query model directory
│   └── ...
└── dicts.fsscript                 # Dictionary definitions

7.7 Dimension Reuse Best Practices Summary ​

PracticeDescription
Function EncapsulationEncapsulate reused dimension definitions as factory functions
Parameterized ConfigurationSupport customization through parameters
Default ValuesProvide reasonable defaults for common parameters
Modular OrganizationOrganize by dimension type into different .fsscript files
Property ExtensionUse spread operator ... to extend or override properties
Unified MaintenanceModify in one place, effective globally
Documentation CommentsAdd JSDoc comments to builder functions for easier use

8. Naming Conventions ​

8.1 File Naming ​

  • TM files: {ModelName}Model.tm
  • Fact tables: Fact{BusinessName}Model.tm, e.g., FactSalesModel.tm
  • Dimension tables: Dim{BusinessName}Model.tm, e.g., DimCustomerModel.tm
  • Dictionary file: dicts.fsscript

8.2 Field Naming ​

LocationConventionExample
Model namePascalCaseFactSalesModel, DimCustomerModel
Field namecamelCaseorderId, salesAmount, customerType
Database columnsnake_caseorder_id, sales_amount, customer_type
Dimension property reference$ separatorcustomer$caption, salesDate$year

8.3 Model Design Guidelines ​

  1. Fact Tables:

    • Include business fact measures (sales amount, quantity, etc.)
    • Include foreign keys to dimension tables
    • Define clear granularity (order-line level, order level)

  2. Dimension Tables:

    • Include descriptive attributes
    • Use surrogate keys as primary keys
    • Dimension tables typically don't define measures

  3. Star Schema vs Snowflake Schema:

    • Star schema: Flat dimensions, better query performance (recommended)
    • Snowflake schema: Nested dimensions, saves storage space, use when needed

9. Advanced Features ​

9.1 Extended Data ​

Use extData to store custom metadata:

javascript
{
    name: 'FactSalesModel',
    caption: 'Sales Fact Table',
    extData: {
        businessOwner: 'Sales Department',
        updateFrequency: 'daily',
        customTag: 'core-metric'
    }
}

9.2 Deprecation Marker ​

Mark obsolete models or fields:

javascript
{
    name: 'oldSalesAmount',
    caption: 'Legacy Sales Amount',
    column: 'old_sales_amt',
    type: 'MONEY',
    deprecated: true  // Shows deprecation warning in frontend configuration
}

10. Vector Models ​

Vector models integrate with vector databases (e.g., Milvus) for semantic similarity search.

10.1 Basic Vector Model ​

javascript
// DocumentSearchModel.tm
export const model = {
    name: 'DocumentSearchModel',
    caption: 'Document Search',
    type: 'vector',                    // Model type must be 'vector'
    tableName: 'documents',            // Milvus collection name

    properties: [
        {
            column: 'doc_id',
            caption: 'Document ID',
            type: 'STRING'
        },
        {
            column: 'title',
            caption: 'Title',
            type: 'STRING'
        },
        {
            column: 'content',
            caption: 'Content',
            type: 'STRING'
        },
        {
            column: 'category',
            caption: 'Category',
            type: 'STRING'
        },
        {
            column: 'embedding',
            caption: 'Vector',
            type: 'VECTOR'             // Vector field type
        }
    ],

    measures: []                       // Vector models typically have no measures
};

10.2 Key Points ​

FieldDescription
typeMust be set to vector
tableNameCorresponds to Milvus collection name
VECTOR typeField for vector embeddings, supports similar and hybrid operators

10.3 Query Example ​

json
{
    "param": {
        "columns": ["doc_id", "title", "content", "_score"],
        "slice": [
            {
                "field": "embedding",
                "op": "similar",
                "value": {
                    "text": "sales analysis report",
                    "topK": 10,
                    "minScore": 0.6
                }
            }
        ]
    }
}

The _score field is automatically included in results, representing similarity (0-1). See Query DSL - Vector Operators for complete syntax.

11. Pre-Aggregations (preAggregations) ​

Pre-aggregations significantly improve query performance in large data scenarios by pre-computing and storing aggregated results.

11.1 Basic Structure ​

javascript
export const model = {
    name: 'FactSalesModel',
    tableName: 'fact_sales',

    preAggregations: [
        {
            name: 'daily_product_sales',
            caption: 'Daily Product Pre-aggregation',
            tableName: 'preagg_daily_product_sales',
            priority: 80,
            enabled: true,

            dimensions: ['product', 'salesDate'],
            granularity: {
                salesDate: 'day'            // Time dimension granularity
            },

            measures: [
                { name: 'salesAmount', aggregation: 'SUM' },
                { name: 'salesQuantity', aggregation: 'SUM' },
                { name: 'salesAmount', aggregation: 'COUNT', columnName: 'sales_count' }
            ],

            filters: [
                { field: 'orderStatus', op: '=', value: 'COMPLETED' }
            ],

            refresh: {
                strategy: 'INCREMENTAL',
                schedule: '0 2 * * *',      // Daily at 2am
                watermarkColumn: 'salesDate$caption',
                lookbackDays: 3
            }
        }
    ],

    dimensions: [...],
    measures: [...]
};

11.2 Pre-Aggregation Fields ​

FieldTypeRequiredDescription
namestringYesPre-aggregation name (unique within model)
captionstringNoDisplay name
tableNamestringYesPre-aggregation table name
schemastringNoDatabase schema (defaults to main table schema)
prioritynumberNoPriority 1-100 (default 50, higher = preferred match)
enabledbooleanNoWhether enabled (default true)
dimensionsstring[]YesList of included dimension names
granularityobjectNoTime dimension granularity configuration
measuresarrayYesMeasure definition list
filtersarrayNoPermanent filter conditions
refreshobjectNoRefresh configuration

11.3 Time Granularity (granularity) ​

GranularityDescription
minuteMinute
hourHour
dayDay
weekWeek
monthMonth
quarterQuarter
yearYear

11.4 Measure Definitions (measures) ​

FieldTypeRequiredDescription
namestringYesReference to TM measure name
aggregationstringYesAggregation method (SUM/COUNT/MIN/MAX/AVG)
columnNamestringNoColumn name in pre-aggregation table (default name_aggregation)

11.5 Refresh Configuration (refresh) ​

FieldTypeRequiredDescription
strategystringNoFULL (full) / INCREMENTAL (incremental), default FULL
schedulestringNoCron expression
watermarkColumnstringNoWatermark column name (for incremental refresh)
lookbackDaysnumberNoLookback days (handles late-arriving data)

11.6 Matching Rules ​

The system automatically matches pre-aggregation tables during queries. Matching conditions:

  1. Query dimensions are a subset of pre-aggregation dimensions
  2. Query time granularity >= pre-aggregation time granularity
  3. Query measures and aggregation methods are compatible with pre-aggregation
  4. Query conditions do not conflict with pre-aggregation filters

When multiple pre-aggregations match, the one with higher priority is preferred.

12. Access Control ​

Access control is configured in QM, but relies on TM dimension definitions for row-level/column-level data security.

12.1 Dimension Data SQL ​

Define dimensionDataSql in dimensions to dynamically generate data permission SQL:

javascript
{
    name: 'store',
    tableName: 'dim_store',
    foreignKey: 'store_key',
    primaryKey: 'store_key',
    captionColumn: 'store_name',

    // Return accessible dimension data SQL based on current user
    dimensionDataSql: (context) => {
        return `SELECT store_key FROM user_store_permission WHERE user_id = '${context.userId}'`;
    }
}

12.2 Named Data Sources ​

When different models need to connect to different databases, use dataSourceName:

javascript
export const model = {
    name: 'FactOrderModel',
    tableName: 'fact_orders',
    dataSourceName: 'orderDb',  // Reference named data source in configuration

    dimensions: [...],
    measures: [...]
};

dataSourceName takes priority over the global default data source. The corresponding named data source must be registered in the application configuration.

Next Steps ​