MDX API

The MDX API enables Cube to connect to Microsoft Excel. It derives its name from multidimensional data expressions, a query language for OLAP in the Microsoft ecosystem.

Unlike Cube Cloud for Excel, it only works with Excel on Microsoft Windows. However, it allows using the data from the MDX API with the native PivotTable in Excel.

Available on Enterprise plan.

The MDX API is currently in preview.

Key features:

• <b>Direct connectivity:</b> Connect Excel directly to Cube Cloud using standard XMLA protocols.
• <b>Advanced analytical functions:</b> Utilize the power of MDX to execute sophisticated queries that include slicing, dicing, drilling down, and rolling up of data.
• <b>Real-time access:</b> Fetch live data from Cube Cloud, ensuring that your analyses and reports always reflect the most current information.

Configuration

While the MDX API is in preview, your Cube account team will enable and configure it for you.

To enable or disable the MDX API on a specific deployment, go to Settings in the Cube Cloud sidebar, then Configuration, and then toggle the Enable MDX API option.

Performance considerations

To ensure the best user experience in Excel, the MDX API should be able to respond to requests with a subsecond latency. Consider the following recommendations:

• The deployment should be collocated with users, so deploy it a region that is closest to your users.
• Queries should hit pre-aggregations whenever possible. Consider turning on the rollup-only mode to disallow queries that go directly to the upstream data source.
• If some queries still go to the upstream data source, it should respond with a subsecond latency. Consider tuning the concurrency and quotas to achieve that.

Date hierarchies

By default, the MDX API creates additional hierarchies for all [time dimensions][ref-time-dimensions] and organizes them in a separate folder called "Calendar" for each dimension. The following hierarchies are created:

- Dimension Calendar:
  - Year
  - Quarter
  - Month
  - Day
- Dimension Calendar Weeks:
  - Year
  - Week
- Dimension Calendar Quarter of Year:
  - Quarter
- Dimension Calendar Year:
  - Year

The Calendar Quarter of Year and Calendar Year hierarchies are particularly useful in Excel because they allow you to filter your data without needing to drill down or expand all levels. You can use them as slicers while placing other dimensions on the axes.

You can set the CUBE_MDX_CREATE_DATE_HIERARCHIES environment variable to false to disable this behavior.

Measure format

The MDX API respects the format parameter of measures so that the values are displayed accordingly in Excel, i.e., percent formats values as percentages and currency formats values as monetary values.

Currency formatting is locale-aware and responds to the language configuration set via the CUBE_XMLA_LANGUAGE environment variable.

Using MDX API with Excel

The MDX API works only with views, not cubes.

The following section describes Excel-specific configuration options.

Dimension hierarchies

MDX API supports dimension hierarchies. You can define multiple hierarchies. Each level in the hierarchy is a dimension from the view.

views:
  - name: orders_view
    description: "Data about orders, amount, count and breakdown by status and geography."
    meta:
      hierarchies:
        - name: "Geography"
          levels:
            - country
            - state
            - city

For historical reasons, the syntax shown above differ from how hierarchies are supposed to be defined in the data model. This is going to be harmonized in the future.

Dimension keys

You can define a member that will be used as a key for a dimension in the cube's model file.

cubes:
  - name: users
    sql_table: USERS
    public: false

    dimensions:
      - name: id
        sql: "{CUBE}.ID"
        type: number
        primary_key: true

      - name: first_name
        sql: FIRST_NAME
        type: string
        meta:
          key_member: users_id

Dimension labels

You can define a member that will be used as a label for a dimension in the cube's model file.

cubes:
  - name: users
    sql_table: USERS
    public: false

    dimensions:
      - name: id
        sql: "{CUBE}.ID"
        type: number
        meta:
          label_member: users_first_name

Custom properties

You can define custom properties for dimensions in the cube's model file.

cubes:
  - name: users
    sql_table: USERS
    public: false

    dimensions:
      - name: id
        sql: "{CUBE}.ID"
        type: number
        meta:
          properties:
            - name: "Property A"
              column: users_first_name
            - name: "Property B"
              value: users_city

Measure groups

MDX API supports organizing measures into groups (folders). You can define measure groups in the view's model file.

views:
  - name: orders_view
    description: "Data about orders, amount, count and breakdown by status and geography."
    meta:
      folders:
          - name: "Folder A"
            members:
              - total_amount
              - average_order_value
          - name: "Folder B"
            members:
              - completed_count
              - completed_percentage

For historical reasons, the syntax shown above differ from how folders are supposed to be defined in the data model. This is going to be harmonized in the future.

Authentication and authorization

Authentication and authorization work the same as for the SQL API.