MODLR

Utility.Export.Dimensions to Table ​

The below code is used to export the structure of every Dimension in the model to a Table - one row per parent/child relationship, in every Hierarchy.

Where Utility.Export.Cube to Table gives an external system the numbers, this gives it the structures those numbers roll up through: what consolidates into what, in which Hierarchy, in what order and with what sign. The two together are what a reporting tool needs to rebuild a MODLR view outside MODLR - see Using MODLR Data in External Systems.

TIP

For joining Cube data to a Hierarchy, Utility.Export.Dimension Descendants to Table is usually the table you want - it flattens these levels so that "everything under X" is a single filter rather than a recursive query.

TIP

Every Dimension lands in the same table, separated by the dimension and hier columns, because every Dimension has the same shape. This differs from the Cube export, which writes one table per Cube since each Cube has its own column list.

Process Code ​

js
/**
 * MODLR PROCESS SCRIPT: Dimension Structures -> MySQL Export
 * ----------------------------------------------------------
 * Purpose:
 *   Walks every Hierarchy of every Dimension in the model and writes the
 *   parent -> child structure out to a single MySQL table, along with each
 *   element's position, leaf flag, weight, depth, type and alias.
 *
 *   Re-running the process truncates and reloads that table rather than
 *   dropping/recreating it, so downstream reports pointed at it keep working.
 *   Each run is a full snapshot of the model's structures as at that moment.
 */

const fieldMapping = { //used in conjunction with the fields arg to create a table
    textDefaultField: "VARCHAR(191)",
    dateField: "DATE",
    dateTime: "DATETIME",
    int: "INT(11)",
    tinyint: "TINYINT(1)",
    decimal: "decimal(10, 2)"
};

// VARCHAR(191) rather than (116) or (255) because three of the name columns
// sit in a single index below, and utf8mb4 costs 4 bytes per character -
// 3 x 191 x 4 stays inside InnoDB's index key limit, 3 x 255 x 4 would not.
const defaultDataType = "VARCHAR(191)";

const ds = "Internal Datastore";
const schema = "dimension_data";
const tableName = "dimension_elements";

// Dimensions to export. Leave the array empty to export every Dimension in
// the model. Measures dimensions are worth keeping: they are what names the
// values in the Cube export.
const dimensions = [];

// Hierarchies skipped in every Dimension - typically the "No <dimension>" or
// "Listing" style hierarchies that exist for picking rather than consolidating.
const excludedHierarchies = [];

// Aliases to export, in order of preference. The first one that exists on the
// Dimension is used; if none do, the alias column is left empty.
const aliasPreference = ["Name", "Description"];

// The exported columns, and the data types for the ones that are not text.
const fields = {
    dimension: "",
    hier: "",
    parent: "",
    child: "",
    child_position: "",
    is_leaf: "",
    weight: "",
    depth: "",
    element_type: "",
    alias: "",
    elm_instance: ""
};

const fieldsDataType = {
    child_position: fieldMapping.int,
    is_leaf: fieldMapping.tinyint,
    weight: fieldMapping.decimal,
    depth: fieldMapping.int,
    element_type: "VARCHAR(1)",
    elm_instance: fieldMapping.int
};

function pre() {
    // This function is called once before the processes is executed.
    // Use this to setup prompts.
    script.log('process pre-execution parameters parsed.');
}

function begin() {
    // This function is called once at the start of the process
    script.log('process execution started.');

    if (!schemaExists(ds, schema)) {
        datasource.update(ds, `CREATE SCHEMA \`${schema}\``);
    }

    if (tableExists(ds, schema, tableName)) {
        const sql = `TRUNCATE \`${schema}\`.\`${tableName}\``
        datasource.update(ds, sql)
    } else {
        createTable(ds, schema, tableName, fields, fieldsDataType);
    }

    // Safe to call every run - it adds only the indexes that aren't there yet.
    createIndexes(ds, schema, tableName);

    const batch = datasource.createBatch(
        ds,
        `${schema}.${tableName}`,
        Object.keys(fields)
    );

    // An empty config list means "every dimension in the model".
    // dimension.list() returns them as a JSON string.
    let dimensionList = dimensions;
    if (dimensionList.length == 0) {
        dimensionList = JSON.parse(dimension.list()).map(d => d.name);
    }

    for (const dim of dimensionList) {
        const aliasName = pickAlias(dim);
        const hiers = JSON.parse(dimension.hierarchies(dim));

        for (const hierObj of hiers) {
            const hier = hierObj.name;

            if (isExcluded(hier, excludedHierarchies)) {
                console.log("Skipping hierarchy", dim, hier);
                continue;
            }

            console.log("Exporting", dim, ">", hier,
                aliasName == "" ? "(no alias)" : `(alias: ${aliasName})`);

            // "" as the parent starts the walk at the hierarchy's root
            // elements, so each root gets a row of its own with an empty
            // parent rather than only appearing as other rows' parent.
            exportChildren(batch, dim, hier, "", 0, aliasName, {});

            if (script.IsCancelled()) {
                return;
            }
        }
    }

    batch.flush(); // commit whatever is left in the batch
}

function data(record) {
    // This function is called once for each line of data on the second cycle
    // Use this to build dimensions and push data into cubes
}

function end() {
    // This function is called once at the end of the process
    script.log('process execution finished.');
}

/**
 * exportChildren()
 * Writes one row per child of `parent`, then recurses into any child that has
 * children of its own. hierarchy.iterateChildren() streams a level at a time
 * rather than materialising the whole hierarchy in memory - the equivalent of
 * cube.slice() in the Cube export.
 */
function exportChildren(batch, dim, hier, parent, depth, aliasName, instanceCounts) {
    if (script.IsCancelled()) {
        return;
    }

    for (const child of hierarchy.iterateChildren(dim, hier, parent, false)) {
        const name = child.name;

        // The same element can sit under more than one parent in a hierarchy.
        // elm_instance numbers those appearances 1, 2, 3... so a consumer can
        // tell a genuine second placement from a duplicated row.
        const instance = (instanceCounts[name] || 0) + 1;
        instanceCounts[name] = instance;

        // returnPrincipalIfMissing = true, so elements with no alias set fall
        // back to their principal name rather than exporting an empty cell.
        let aliasValue = "";
        if (aliasName != "") {
            aliasValue = alias.get(dim, aliasName, name, true) + "";
        }

        batch.insert([
            dim,
            hier,
            parent,
            name,
            child.childIndex,                  // position among its siblings
            child.childCount > 0 ? 0 : 1,      // a leaf has no children of its own
            child.weight,                      // 1 or -1: how it signs into its parent
            depth,                             // 0 at the roots, +1 per level down
            child.type,                        // N numeric, S string
            aliasValue.substring(0, 191),
            instance
        ]);

        if (child.childCount > 0) {
            exportChildren(batch, dim, hier, name, depth + 1, aliasName, instanceCounts);
        }
    }
}

/**
 * pickAlias()
 * Returns the first alias in aliasPreference that actually exists on the
 * dimension, or "" if none of them do.
 */
function pickAlias(dim) {
    const available = JSON.parse(dimension.aliases(dim)).map(a => a.name);

    for (const preferred of aliasPreference) {
        if (available.indexOf(preferred) > -1) {
            return preferred;
        }
    }

    return "";
}

/**
 * isExcluded()
 * Case-insensitive full-name match against an exclusion list.
 */
function isExcluded(name, exclusions) {
    return exclusions.some(e => e.toLowerCase() == name.toLowerCase());
}

;
function createTable(ds, schema, tableName, fields = {}, fieldsDataType = {}) {
    const array = []
    if (tableExists(ds, schema, tableName)) {
        console.log("Table Already Exists")
        return
    }
    const primaryKey = `\`${tableName}_id\` `
    let createStatement = `CREATE TABLE \`${schema}\`.\`${tableName}\` (
        ${primaryKey} int(11) NOT NULL AUTO_INCREMENT,`
    for (const property in fields) {
        array.push(property)
        let dataType;
        if (fieldsDataType.hasOwnProperty(property)) {
            dataType = fieldsDataType[property]
        } else {
            dataType = defaultDataType
        }
        createStatement += `\`${property}\` ${dataType} DEFAULT NULL, `
    }
    createStatement += `  PRIMARY KEY (${primaryKey})) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci`
    if (array.length > 0) {
        console.log(`Creating Table \`${schema}\`.\`${tableName}\``, createStatement)
        datasource.update(ds, createStatement)
    } else {
        console.log("Failed to create table")
    }
    return;
}

/**
 * createIndexes()
 * The indexes are the point of the exercise: idx_parent serves "give me the
 * children of X", idx_child serves "give me the parents of X" and the walk up
 * a hierarchy. Checked individually so this can run on every execution,
 * including against a table created before the indexes were added.
 */
function createIndexes(ds, schema, tableName) {
    const indexes = {
        idx_parent: "`dimension`, `hier`, `parent`",
        idx_child: "`dimension`, `hier`, `child`"
    };

    for (const indexName in indexes) {
        if (indexExists(ds, schema, tableName, indexName)) {
            continue;
        }

        const sql = `ALTER TABLE \`${schema}\`.\`${tableName}\` ADD INDEX \`${indexName}\` (${indexes[indexName]})`;
        console.log("Creating index", indexName);
        datasource.update(ds, sql);
    }
}

function indexExists(ds, schema, tableName, indexName) {
    const sql = `SELECT * FROM information_schema.statistics WHERE table_schema = ? AND table_name = ? AND index_name = ?`
    const result = JSON.parse(datasource.select(ds, sql, [schema, tableName, indexName]))
    return result.length > 0
}

function tableExists(ds, schema, tableName) {
    const sql = `SELECT * FROM information_schema.tables WHERE table_schema = ? AND table_name = ?`
    const result = JSON.parse(datasource.select(ds, sql, [schema, tableName]))
    if (result.length > 0) {
        return true
    } else {
        return false
    }
}

function schemaExists(ds, schema) {
    const sql = `SELECT * FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = ?`
    const result = JSON.parse(datasource.select(ds, sql, [schema]))
    return result.length > 0
}

function dropTable(ds, schema, tableName) {
    const sql = `DROP TABLE \`${schema}\`.\`${tableName}\``
    console.log(sql)
    datasource.update(ds, sql)
}

What the table holds ​

One row per parent/child relationship, per Hierarchy, per Dimension.

ColumnDescription
dimensionThe Dimension the row belongs to.
hierThe Hierarchy the relationship exists in. The same child appears once per Hierarchy it is placed in.
parentThe consolidating element. Empty for a Hierarchy's root elements.
childThe element itself, by its principal name.
child_positionZero-based position among its siblings - the order the Hierarchy displays them in.
is_leaf1 if the element has no children of its own, 0 if it consolidates.
weight1 or -1: whether the child adds to or subtracts from its parent.
depth0 at the roots, incrementing by one per level down.
element_typeN for numeric, S for string.
aliasThe element's alias, from the first of aliasPreference present on the Dimension.
elm_instance1 the first time an element appears in a Hierarchy, 2 the second time, and so on.

Checking the result ​

To see the immediate children of an element - here Total Amount in the Default Hierarchy of the Profit and Loss Measures Dimension:

sql
SELECT child, alias, child_position, is_leaf, weight
FROM `dimension_data`.`dimension_elements`
WHERE dimension = 'Profit and Loss Measures'
  AND hier      = 'Default'
  AND parent    = 'Total Amount'
ORDER BY child_position;

To see the whole subtree beneath it rather than one level, recurse with a CTE:

sql
WITH RECURSIVE subtree AS (
    SELECT dimension, hier, parent, child, child_position, is_leaf, weight,
           1 AS level,
           CAST(LPAD(child_position, 4, '0') AS CHAR(600)) AS sort_path
    FROM `dimension_data`.`dimension_elements`
    WHERE dimension = 'Profit and Loss Measures'
      AND hier      = 'Default'
      AND parent    = 'Total Amount'

    UNION ALL

    SELECT d.dimension, d.hier, d.parent, d.child, d.child_position, d.is_leaf, d.weight,
           s.level + 1,
           CONCAT(s.sort_path, '.', LPAD(d.child_position, 4, '0'))
    FROM `dimension_data`.`dimension_elements` d
    JOIN subtree s
      ON d.dimension = s.dimension
     AND d.hier      = s.hier
     AND d.parent    = s.child
)
SELECT CONCAT(REPEAT('    ', level - 1), child) AS structure,
       parent, level, is_leaf, weight
FROM subtree
ORDER BY sort_path;

WARNING

The recursive query assumes the Hierarchy is acyclic, which MODLR enforces. If you point it at a table that has been hand-edited since the export, add AND s.level < 20 to the recursive branch as a guard against an element placed beneath itself.

Scheduling it ​

Like the Cube export, this is meant to run on a schedule rather than on demand - overnight, after the Dimension builds that feed it. An external system then reads a table that is consistent as at the last run, instead of asking the model to assemble a structure while users are in it.

Copyright © 2026 MODLR