MDX Manipulation · ActiveUI

The MdxApi contains all the functions that extract information from an MDX query string and/or update it.

Design

Usage

Parsing

In order to read or modify an MDX statement, the statement string must first be parsed. This parsing consists of converting the Mdx into a tree of JavaScript objects. If the input MDX string is not syntactically valid, trying to parse it will raise a parsing error and the MDX API will not be able to process this string.

Each node of the tree produced by the parsing is called an MdxNode. This includes the root node, so the result of the parsing of an MDX string is an MdxNode.

We ask that you don't directly mutate MdxNode objects. Applying this constraint allows us to cache MdxNodes produced by the MDX API, speeding up later processing of similar MDX.

If you want to modify an MdxNode, create a clone of the object first. Some third-party libraries provide functions to make this more convenient, such as Lodash’s _.cloneDeep/_.clone and immutability-helper’s update.

Transforming an MDX String

We often need to make consecutive calls to different MDX API methods and parsing is an expensive operation. So all the methods of the MDX API that read or update an MDX statement take as input an MdxNode and return an MdxNode.

Additionally, most of MDX API functions require the server Discovery information to be provided along with the statement. This discovery information is necessary to identify which part of the MDX query corresponds to a hierarchy or level, and what the default members are. This is why most of the functions take as input an object of type StatementAndDiscovery and also return a StatementAndDiscovery to make calls chainable.

So each modification of an MDX string needs to be parsed initially, prior to the required MDX API method being called with an StatementAndDiscovery. Then the statement part of the resulting StatementAndDiscovery is retrieved and converted back to a string. This is done automatically by the transform function of the MdxApi.

Filters

The MDX filtering API is very extensive as this is the part of the API that is more likely to be used in projects.

ActiveFilter

The main object used to manipulate the filters of an MDX statement is called ActiveFilter. "Active" here means that this filter is currently in use in a statement, while the class Filter describes the type of filter, outside of any statement context.

The function mdxApi.filters.selectFilters retrieves the list of active filters from an MDX.

Filter Selectors

Filter Selectors are objects that can be used to filter out some active filters from the list returned in selectFilters. They are plugins because their ultimate goal is to be stored as filter bookmarks so that it is possible to display, in a dashboard, a widget that is filtering a given hierarchy only. Selectors represent the persisted information regarding a filter, so that it is possible to find a particular filter again, even after most of the MDX changed.

The list of important built-in Filter Selector implementations is:

Name	Parameters	Role	Usage
`AllSelector`	None	Keeps all active filters	This is the selector the Wizard uses by default
`UserSelector`	None	Filters out server filters	With this selector one sees the filters contained in the MDX
`KeySelector`	key	Keeps only filters of a given type	If, for instance, only TopCount filters need to be listed
`HierarchySelector`	hierarchy	Keeps only filters filtering a given hierarchy	If, for instance, only filters on Measures need to be listed
`HierarchyKeyIndexSelector`	hierarchy, key, index	Keeps only one active filter with the given parameters	For tracking an active filter, even if the MDX is heavily modified

ActiveFilter Interface

The result of an MDX query is filtered either:

by filters in the statement itself, which are called user filters, because the user wrote the statement.
by members of slicing hierarchies not expressed on the main axes of the select statement. These filters are called server filters because they are applied by the server implicitly. The method remove is not available on this type of filter.

Both these filter types share the same type AugmentedActiveFilter.

Filters Plugins

Each filter plugin describes a type of filter that can be used in a query. These different types can:

recognize their type of filters in the MDX.
change the MDX when an active filter is being created with this key.
render the UI to edit the filter in the Edit filter popup.
render the UI to see/edit the filter in the Wizard.

Each filter plugin is responsible for choosing the value that will be contained in the created ActiveFilter based on the MDX. This value is a JavaScript Object that has a different shape depending on the plugin used.

The list of built-in filters is:

Name	Description	Example of handled MDX	Value shape
`explicit`	List selected members explicitly	`{[D1].[H1].[AllMember].[M1], [D1].[H1].[AllMember].[M1]}`	`{ members: ['[AllMember].[M1]', '[AllMember].[M2]'], mode: 'include'}`
`topcount`	TopCount, BottomPercent etc...	`TopCount(Filter(*, NOT IsEmpty([Measures].[pnl.SUM])), n, [Measures].[pnl.SUM])`	`{measure: '[Measures].[pnl.SUM]', order: 'TOP', mode: 'COUNT', amount: 10, byParent: false, levelDepth: 1}`
`numeric-binary`	Compare measure with value	`Filter(...[L1].Members, [Measures].[pnl.SUM] > 0)`	`{measure: '[Measures].[pnl.SUM]', level: 'L1', constant: 0, op: '>', yoda: false}`
`numeric-between`	Compare measure with two values	`Filter(...[L1].Members, [Measures].[a] > 0 AND [Measures].[a] < 10`	`{measure: '[Measures].[a]', level: 'L1', low: 0, high: 10, inside: true}`
`string-binary`	Compare member label with String	`Filter(...[L1].Members, [D1].[H1].CurrentMember.MEMBER_CAPTION > "a"`	`{level: 'L1', constant: 0, op: '>', yoda: false}`
`string-between`	Compare member label with two Strings	Combine NumericBetween with StringBinary	Combine the values as well
`substring`	Check if member label contains a String, or not	`Filter(...[L].Members, Left([D].[H].CurrentMember.member_caption length) <> str)`	`{level: 'L1', mode: Mode.Left, inverted: true}`
`emptiness`	Check if measure is empty or not	`Filter(...[L].Members, NOT IsEmpty([Measures].[pnl.SUM]))`	`{measure: '[Measures].[pnl.SUM]', level: 'L', empty: false}`

Setting the Default Filter Type per Hierarchy

You might have a hierarchy for which you would always want to use the same filter type when adding a filter to it.

Suppose you always want to default to topcount when adding a filter to the [Trades].[Trades] hierarchy. Then you can define this setting:

"filtering.hierarchyToFilterType.[Trades].[Trades]": "topcount"

Basic Filter Manipulation Example

App.js

index.js

body.html

style.css

import React from 'react';
import {useActiveUI} from '@activeviam/activeui-sdk';
import Input from 'antd/lib/input';
import InputNumber from 'antd/lib/input-number';

const {TextArea} = Input;

const initialValue = `{
  "amount": 10,
  "byParent": false,
  "levelDepth": 1,
  "measure": "[Measures].[contributors.COUNT]",
  "mode": "COUNT",
  "order": "TOP"
}`;

function reducer(state, action) {
  switch (action.type) {
    case 'LOADED_DISCOVERY':
      return {...state, discovery: action.payload};
    case 'CHANGED_HIERARCHY':
      return {...state, hierarchy: action.payload};
    case 'CHANGED_MDX':
      return {...state, mdx: action.payload};
    case 'CHANGED_KEY':
      return {...state, key: action.payload};
    case 'CHANGED_VALUE':
      return {...state, value: action.payload};
    case 'CHANGED_POSITION':
      return {...state, position: action.payload};
    default:
      return state;
  }
}

export function App() {
  const activeUI = useActiveUI();
  const mdxApi = activeUI.mdx;
  const serversPool = activeUI.queries.serversPool;
  const activePivotServer = serversPool.getActivePivotServer(
    "https://your.activepivot.server",
  );
  const [state, dispatch] = React.useReducer(reducer, {
    discovery: null,
    hierarchy: '[Underlyings].[Products]',
    key: 'topcount',
    mdx:
      'SELECT [Measures].[contributors.COUNT] ON COLUMNS FROM [EquityDerivativesCube]',
    position: 0,
    value: initialValue,
  });

  React.useEffect(() => {
    let mounted = true;
    activePivotServer
      .getDiscovery()
      .get()
      .then((discovery) => {
        if (mounted) {
          dispatch({type: 'LOADED_DISCOVERY', payload: discovery});
        }
      });
    return () => {
      mounted = false;
    };
  }, [activePivotServer]);

  function getOutput() {
    if (!state.discovery) {
      return 'Discovery loading...';
    }

    let parsedValue;

    try {
      parsedValue = JSON.parse(state.value);
    } catch (e) {
      return 'Value not parseable';
    }

    try {
      return mdxApi.parsing.toString(
        mdxApi.parsing.parseExpression(
          mdxApi.transform(state.mdx, state.discovery, (snd) =>
            mdxApi.filters.addFilter(
              snd,
              state.position,
              state.key,
              state.hierarchy,
              parsedValue,
            ),
          ),
        ),
        {indent: true, notEmpty: () => true},
      );
    } catch (e) {
      return 'Cannot apply filter';
    }
  }

  const output = getOutput();

  return (
    <div style={{display: 'flex'}}>
      <div style={{width: '50%'}}>
        <h2>Input</h2>
        <p>MDX</p>
        <TextArea
          style={{minHeight: 50}}
          value={state.mdx}
          onChange={(e) =>
            dispatch({type: 'CHANGED_MDX', payload: e.target.value})
          }
        />
        <p>Hierarchy</p>
        <Input
          value={state.hierarchy}
          onChange={(e) =>
            dispatch({type: 'CHANGED_HIERARCHY', payload: e.target.value})
          }
        />
        <p>Key</p>
        <Input
          value={state.key}
          onChange={(e) =>
            dispatch({type: 'CHANGED_KEY', payload: e.target.value})
          }
        />
        <p>Position</p>
        <InputNumber
          value={state.position}
          onChange={(position) =>
            dispatch({type: 'CHANGED_POSITION', payload: position})
          }
        />
        <p>Value</p>
        <TextArea
          style={{minHeight: 200}}
          value={state.value}
          onChange={(e) =>
            dispatch({type: 'CHANGED_VALUE', payload: e.target.value})
          }
        />
      </div>
      <div style={{width: '50%', paddingLeft: 15}}>
        <h2>Output</h2>
        <pre>{output}</pre>
      </div>
    </div>
  );
}

import React from 'react';
import {render} from 'react-dom';
import {createActiveUI, ActiveUIProvider} from '@activeviam/activeui-sdk';

import {App} from './App';

const activeUI = createActiveUI();

const servers = activeUI.queries.serversPool;
servers.addActivePivotServer({url: "https://your.activepivot.server"});

render(
  <ActiveUIProvider activeUI={activeUI}>
    <App />
  </ActiveUIProvider>,
  document.getElementById('root'),
);

<div id="root"></div>

body {
  font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Helvetica, Arial, sans-serif, "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Symbol";
  font-variant: tabular-nums;
}

.CodeMirror pre {
  /* Using !important because CodeMirror will apply its own style after but we want to force this font stack. */
  font-family: "SFMono-Regular", Consolas, "Liberation Mono", Menlo, Courier, monospace !important;
}

body {
  margin: 0;
}

html,
body,
#root {
  height: 100%;
}