Utility.Export.Dimension Descendants to Table

The below code exports a flattened view of every Dimension's Hierarchies - one row for every element and each of the leaf elements beneath it, however many levels down they sit, carrying the sign that leaf consolidates with.

Where Utility.Export.Dimensions to Table exports the structure one level at a time, as parent/child pairs, this collapses those levels away. Asking "every element that actually holds data under Total Revenue, and whether it adds or subtracts" becomes a single equality filter rather than a recursive query, which is what makes it practical to join a Cube export to a Hierarchy in a reporting tool.

TIP

The two are complements, not alternatives. The parent/child export is what you use to draw a Hierarchy, indent it, or walk it level by level. This one is what you filter and join on.

Process Code

/**
 * MODLR PROCESS SCRIPT: Dimension Descendants -> MySQL Export
 * -----------------------------------------------------------
 * Purpose:
 *   Writes one row per (element, leaf descendant) pair for every Hierarchy
 *   of every Dimension - the flattened form of the parent/child structure.
 *
 *   Only leaf elements are written as the child. An intermediate
 *   consolidation is a parent in its own right and gets its own rows, but
 *   it never appears as somebody else's child, so a filter on any element
 *   returns exactly the elements holding data beneath it - nothing that
 *   would double count if it were summed.
 *
 *   Each row carries a weight: the product of the hierarchy weights along
 *   the path from that ancestor down to the leaf. A leaf three levels under
 *   a subtracted subtotal carries -1, so SUM(value * weight) reproduces what
 *   MODLR consolidates rather than just adding everything together.
 *
 *   An element at the bottom of the tree has no descendants, so it is
 *   written once against itself with a weight of 1: parent and child hold
 *   the same value. That way a filter on a leaf returns the leaf.
 *
 *   Re-running the process truncates and reloads the table rather than
 *   dropping/recreating it, so downstream reports pointed at it keep working.
 */

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.
const defaultDataType = "VARCHAR(191)";

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

// Dimensions to export. Leave the array empty to export every Dimension.
const dimensions = [];

// Hierarchies skipped in every Dimension.
const excludedHierarchies = [];

const fields = {
    dimension: "",
    hier: "",
    parent: "",
    child: "",
    weight: ""
};

const fieldsDataType = {
    weight: fieldMapping.decimal
};

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".
    let dimensionList = dimensions;
    if (dimensionList.length == 0) {
        dimensionList = JSON.parse(dimension.list()).map(d => d.name);
    }

    for (const dim of dimensionList) {
        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("Flattening", dim, ">", hier);

            // "" as the parent starts the walk at the hierarchy's root
            // elements, with an empty chain - the roots have no ancestors.
            flattenHierarchy(batch, dim, hier, "", []);

            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.');
}

/**
 * flattenHierarchy()
 * Walks the hierarchy once, carrying the chain of ancestors above the element
 * currently being visited. Rows are written only when a leaf is reached: one
 * per ancestor above it, plus the leaf against itself.
 *
 * `chain` holds one entry per ancestor of `parent`, ordered root-most first:
 *   name    - the ancestor's element name
 *   product - the product of the hierarchy weights from that ancestor down
 *             to `parent`. Multiplying it by the current child's own weight
 *             is what carries a -1 down through however many levels follow.
 */
function flattenHierarchy(batch, dim, hier, parent, chain) {
    if (script.IsCancelled()) {
        return;
    }

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

        // Extend every ancestor's running product through this child, then add
        // the immediate parent to the chain. "" is the virtual root above the
        // hierarchy's top elements, so it never becomes an ancestor itself.
        const childChain = [];

        for (let i = 0; i < chain.length; i++) {
            childChain.push({
                name: chain[i].name,
                product: chain[i].product * child.weight
            });
        }

        if (parent != "") {
            childChain.push({ name: parent, product: child.weight });
        }

        if (child.childCount > 0) {
            // A consolidation. It gets its own rows as a parent further down
            // the walk, but is never written as somebody else's child.
            flattenHierarchy(batch, dim, hier, name, childChain);
            continue;
        }

        // Bottom of the tree. One row per ancestor, each carrying the sign
        // this leaf contributes with when rolled up to that ancestor.
        for (let i = 0; i < childChain.length; i++) {
            batch.insert([
                dim,
                hier,
                childChain[i].name,
                name,
                childChain[i].product
            ]);
        }

        // ...and the leaf against itself, so a filter on a leaf returns it.
        batch.insert([dim, hier, name, name, 1]);
    }
}

/**
 * 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()
 * idx_parent is the one that matters: it serves the "everything under X"
 * filter this table exists for. idx_child serves the reverse question,
 * "which consolidations does this element roll into".
 */
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

Column	Description
dimension	The Dimension the row belongs to.
hier	The Hierarchy the relationship exists in.
parent	The element being rolled up to - any ancestor, at any distance.
child	A leaf element somewhere beneath it, or the element itself where it is a leaf.
weight	1 if the leaf adds to that ancestor, -1 if it subtracts - the product of the hierarchy weights along the path between them.

Every child is a leaf. Consolidations appear only in the parent column, which is what makes the table safe to aggregate over without filtering.

How the weight is worked out

A Hierarchy can add or subtract each child from its parent. That sign applies at one level, so an element's effect on an ancestor several levels up is the product of every sign between them.

Take a Hierarchy where Gross Profit adds Revenue and subtracts Cost of Sales, and the cost accounts themselves are held as positive numbers:

Gross Profit
    Revenue                (+1)
        Sales Revenue      (+1)
        Service Revenue    (+1)
    Cost of Sales          (-1)
        Direct Materials   (+1)
        Direct Labour      (+1)

Direct Materials adds to Cost of Sales, and Cost of Sales subtracts from Gross Profit, so against Gross Profit it carries +1 x -1 = -1:

parent	child	weight
Gross Profit	Sales Revenue	1
Gross Profit	Service Revenue	1
Gross Profit	Direct Materials	-1
Gross Profit	Direct Labour	-1
Cost of Sales	Direct Materials	1
Cost of Sales	Direct Labour	1
Revenue	Sales Revenue	1
Revenue	Service Revenue	1

The same leaf carries a different sign depending on which ancestor it is being rolled up to - +1 into Cost of Sales, -1 into Gross Profit. That is the whole point of storing the product per pair rather than the weight per step.

Models that store costs as negatives

Plenty of models hold cost accounts as negative values and add them at every level, rather than holding them positive and subtracting. There every weight in the table is 1, and SUM(value * weight) gives the same answer as SUM(value). Writing the query with the multiplication regardless means it stays correct if the model later changes, and it costs nothing.

Using it

Everything that holds data beneath an element, and how it contributes:

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

Joined to a Cube export, which is what the table is for:

SELECT      c.department,
            c.time,
            SUM(CAST(c.value AS DECIMAL(18, 4)) * d.weight) AS total_revenue
FROM        `cube_data`.`profit_and_loss`               c
JOIN        `dimension_data`.`dimension_descendants`    d
       ON   d.dimension = 'Account'
      AND   d.hier      = 'Default'
      AND   d.child     = c.account
WHERE       d.parent    = 'Total Revenue'
GROUP BY    c.department, c.time;

d.parent can name a top-level total or a sub-sub-total and the query is identical either way. The same question against the parent/child export reaches one level only, or needs a recursive CTE to go further.

An element placed twice in one Hierarchy produces two rows

MODLR allows the same element to sit under more than one parent in the same Hierarchy. Where two of those paths reach a common ancestor, that ancestor gets one row per path - which is correct for SUM(value * weight), because the element genuinely contributes twice, and the two signs may even cancel.

It does mean SELECT child is a list of contributions rather than a distinct member list. Use SELECT DISTINCT child where you want the latter.

Size

This table grows with depth, not just element count: a leaf eight levels down produces a row against each of its eight ancestors, plus its self-row. That is the trade being made - a bigger table in exchange for no recursion at query time. Restrict dimensions and excludedHierarchies to what the external system actually reports on.

Scheduling it

Run it on the same schedule as the parent/child export, after the Dimension builds that feed it. See Using MODLR Data in External Systems for how it fits alongside the Cube export.