mdxpy

1.3.2 · active · verified Thu Apr 16

mdxpy is a Python library designed to simplify the programmatic creation of MDX (Multidimensional Expressions) queries for IBM Planning Analytics (TM1). It eliminates the need for manual string concatenation, reduces syntax errors, and makes MDX generation more robust and easier to refactor. The library is currently at version 1.3.2 and maintains an active release cadence with regular updates.

Common errors

```
NameError: name 'TM1Service' is not defined
```
cause The `TM1py` library, commonly used with `mdxpy` for actual TM1 server interaction, is not installed or not imported.

fix Install `TM1py` using `pip install TM1py` and ensure `from TM1py import TM1Service` is present in your code.
```
AttributeError: 'MdxHierarchySet' object has no attribute 'unions'
```
cause This error occurs when using an `mdxpy` version (e.g., between 0.4 and 1.1) where the `unions` function was temporarily removed from `MdxHierarchySet`.

fix Upgrade to `mdxpy>=1.1` where the `unions` function was re-introduced, or use alternative methods for combining sets available in your specific `mdxpy` version.
```
TypeError: 'str' object cannot be interpreted as an MdxHierarchySet
```
cause An `mdxpy` builder function (e.g., `add_hierarchy_set_to_row_axis`) expects an `MdxHierarchySet` object, but a raw string was provided instead.

fix Ensure you construct `MdxHierarchySet` objects using their static methods, such as `MdxHierarchySet.all_leaves("Dimension", "Hierarchy")` or `MdxHierarchySet.tm1_subset_to_set("Dimension", "Hierarchy", "Subset")`, before passing them to the builder.
```
MDX query not valid: Syntax error at or near '...'
```
cause Attempting to manually concatenate MDX strings, which is prone to syntax errors (e.g., missing brackets, incorrect commas), a problem `mdxpy` is designed to solve.

fix Leverage `mdxpy`'s programmatic builder pattern (`MdxBuilder`, `Member`, `MdxHierarchySet`, etc.) to construct the MDX query. This significantly reduces the likelihood of syntax errors.

Warnings

breaking In version 0.4, object names were standardized to lower case. This may cause issues for applications built with earlier versions that relied on specific casing.
Fix: Review and update object names in your MDXpy code to use lower case where applicable. Test thoroughly after upgrading.
breaking Version 0.4 introduced a significant refactoring that broke `MdxHierarchySet` and `MdxSet` into two distinct classes. Code using `MdxSet` functionality might need to be rewritten to use `MdxHierarchySet` or other appropriate classes.
Fix: Migrate usage of `MdxSet` to the new `MdxHierarchySet` or other dedicated classes based on your query requirements. Consult the documentation for the specific methods.
gotcha The `unions` function on `MdxHierarchySet` was re-introduced in version 1.1. If you previously upgraded from a very old version to one where it was removed, and then upgrade to 1.1+, you might re-encounter this function.
Fix: Ensure your code uses the correct method for combining sets based on your installed `mdxpy` version. For `1.1+`, `unions` is available. For versions where it was absent, alternatives like `union_of_sets` might have been used.
gotcha Version 1.0 introduced the ability to add dimension properties per axis. This enhanced functionality might require updates to existing MDX generation logic if you were previously handling properties differently.
Fix: Adopt the `add_properties_to_row_axis` or `add_properties_to_column_axis` methods with `DimensionProperty.of()` for explicit property inclusion, as demonstrated in the quickstart.

Install

pip install mdxpy Install latest version

Imports

MdxBuilder
```
from mdxpy import MdxBuilder
```
MdxHierarchySet
```
from mdxpy import MdxHierarchySet
```
Member
```
from mdxpy import Member
```
DimensionProperty
```
from mdxpy import DimensionProperty
```
MdxTuple
```
from mdxpy import MdxTuple
```

Quickstart

This quickstart demonstrates building a basic MDX query for a 'SalesCube' cube, retrieving all leaf members from 'Account' and 'Time' dimensions, including properties for 'Account'. It uses `TM1Service` from `TM1py` to illustrate a common integration pattern.

import os
from TM1py import TM1Service
from mdxpy import MdxBuilder, MdxHierarchySet, DimensionProperty

# TM1 connection details from environment variables for security
TM1_ADDRESS = os.environ.get('TM1_ADDRESS', 'localhost')
TM1_PORT = int(os.environ.get('TM1_PORT', 12354))
TM1_SSL = os.environ.get('TM1_SSL', 'True').lower() == 'true'
TM1_USER = os.environ.get('TM1_USER', 'admin')
TM1_PASSWORD = os.environ.get('TM1_PASSWORD', 'apple')

# This example assumes TM1py is installed and TM1 server is accessible
try:
    with TM1Service(
        address=TM1_ADDRESS,
        port=TM1_PORT,
        ssl=TM1_SSL,
        user=TM1_USER,
        password=TM1_PASSWORD
    ) as tm1:
        query = MdxBuilder.from_cube("SalesCube")
        query.add_hierarchy_set_to_row_axis(
            MdxHierarchySet.all_leaves("Account", "Account")
        )
        query.add_properties_to_row_axis(
            DimensionProperty.of("Account", "Description"),
            DimensionProperty.of("Account", "Type")
        )
        query.add_hierarchy_set_to_column_axis(
            MdxHierarchySet.all_leaves("Time", "Time")
        )

        mdx_query = query.to_mdx()
        print("Generated MDX Query:")
        print(mdx_query)

        # Execute the MDX query using TM1py (optional, for full example)
        # df = tm1.execute_mdx_dataframe(mdx_query)
        # print("\nQuery Results (first 5 rows if TM1py executed):")
        # print(df.head())

except ImportError:
    print("TM1py not installed. Install with 'pip install TM1py' to run full example.")
except Exception as e:
    print(f"An error occurred: {e}")

view raw JSON →