---
url: /technical/process-utility-export-dimension-table.md
---

# 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](/technical/process-utility-export-cube-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](/technical/using-modlr-data-in-external-systems).

::: tip
For joining Cube data to a Hierarchy, [Utility.Export.Dimension Descendants to Table](/technical/process-utility-export-dimension-descendants-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.

| Column | Description |
| :--- | :--- |
| `dimension` | The Dimension the row belongs to. |
| `hier` | The Hierarchy the relationship exists in. The same `child` appears once per Hierarchy it is placed in. |
| `parent` | The consolidating element. Empty for a Hierarchy's root elements. |
| `child` | The element itself, by its principal name. |
| `child_position` | Zero-based position among its siblings - the order the Hierarchy displays them in. |
| `is_leaf` | `1` if the element has no children of its own, `0` if it consolidates. |
| `weight` | `1` or `-1`: whether the child adds to or subtracts from its parent. |
| `depth` | `0` at the roots, incrementing by one per level down. |
| `element_type` | `N` for numeric, `S` for string. |
| `alias` | The element's alias, from the first of `aliasPreference` present on the Dimension. |
| `elm_instance` | `1` 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](/technical/scheduling-processes) 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.