Skip to content
Saiku Cloud

MDX & export

Two related power-user features: the MDX editor (for writing queries by hand or hand-tuning the workbench’s output), and the export options (for getting the result out into another tool or a report).

The MDX editor

Behind the scenes, every drag-and-drop in the workbench compiles to an MDX query — the query language for OLAP cubes. Most analyses never need you to look at MDX; the workbench is faster for the common case. But sometimes you want it:

  • Debugging. A workbench query returns unexpected numbers and you want to see exactly what was asked.
  • Copying. Another tool speaks MDX (or you’re saving a query to a file for version control).
  • Hand-tuning. The workbench gives you a starting point; you refine.
  • Things the workbench can’t express. Some advanced MDX features (named sets, sub-cubes, complex WITH clauses) aren’t surfaced as drop-zone affordances.

Opening the MDX panel

Click Show MDX on the toolbar. A panel slides out showing the current query as MDX.

The panel is read-only by default; click Switch to MDX to make the query editable. From that point you’re authoring MDX directly — the drop zones no longer drive the query. To go back to the workbench mode, Switch back discards your MDX edits and rebuilds from the drop zones.

What MDX looks like

A minimal MDX query:

SELECT
  { [Measures].[Revenue] } ON COLUMNS,
  { [Time].[Year].MEMBERS } ON ROWS
FROM [Sales]

That gives you a year-by-year revenue table — the same as dragging Revenue into Columns and Time › Year into Rows.

More interesting MDX:

WITH
  MEMBER [Measures].[YoY Growth] AS
    ([Measures].[Revenue], [Time].CURRENTMEMBER) /
    ([Measures].[Revenue], [Time].CURRENTMEMBER.PREVMEMBER) - 1,
  FORMAT_STRING = '0.0%'
SELECT
  { [Measures].[Revenue], [Measures].[YoY Growth] } ON COLUMNS,
  { [Time].[Year].MEMBERS } ON ROWS
FROM [Sales]

That adds a year-over-year growth percentage as a calculated measure — the workbench could produce a similar result with the calculated-members dialog, but writing it as MDX gives you exact control.

Helpful MDX patterns

A few patterns worth knowing:

  • TOPCOUNT(set, n, expression) — top-N by a measure. E.g. TOPCOUNT([Customer].[Name].MEMBERS, 10, [Measures].[Revenue]) gives you the 10 highest-revenue customers.
  • FILTER(set, predicate) — restrict a set. FILTER([Region].[Name].MEMBERS, [Measures].[Revenue] > 1000000) keeps only regions with revenue over a million.
  • PARALLELPERIOD — same period last year. Cleaner than manually subtracting 1 from the year level.
  • NON EMPTY on a set — drop rows or columns with all-null measures, equivalent to the workbench’s Non-Empty toggle.

For deeper MDX reference, Microsoft’s MDX documentation is the standard. Mondrian (the engine behind Saiku) implements most of standard MDX with a few quirks documented in the upstream Mondrian docs.

Exporting data

The toolbar has three export buttons. They produce different things for different audiences.

Export to Excel (XLSX)

What you get: a .xlsx file with the result table formatted as a sheet. The pivot structure (rows, columns, totals) is preserved. Multiple measures become multiple columns. Drillthrough children become indented rows in the same sheet.

Best for: handing to someone who’ll keep analysing in Excel, where the pivot shape is useful.

Export to CSV

What you get: a plain CSV with one row per cell of the result table. Header row carries the column names. Totals are excluded by default (a CSV with mixed total + detail rows is confusing for downstream parsers).

Best for: piping into another tool — a Python script, a BI tool, a data warehouse, anything that wants tabular data.

Export to PDF

What you get: a PDF rendering of the current view — the result table OR the chart, whichever is showing. Multi-page output for large tables; the chart fits one page.

Best for: pasting into a report, emailing as a snapshot, archiving a specific finding alongside its visual.

Drillthrough export

Right-click any cell in the result and pick Export drillthrough. This bypasses the cube aggregation entirely and exports the raw fact-table rows underlying that one cell. Useful when someone questions a number and you want to send them the underlying records.

Drillthrough exports are CSV-only, intentionally — the raw rows don’t have a pivot structure to preserve.

When to use which export

  • CSV when the data is going somewhere else for further processing.
  • Excel when a human is going to keep working with the data in Excel.
  • PDF when the output is the final deliverable — a snapshot for a report, an email attachment, a screen-share share-out.
  • Drillthrough when you need the underlying rows behind a specific aggregated number.

A note on row limits

Saiku doesn’t artificially limit export size, but the underlying warehouse query does have to complete. A million-row drillthrough takes a while to run and a while to download. If you’re routinely exporting hundreds of thousands of rows, consider whether the cube aggregation is doing what it should — the whole point of OLAP is to summarise, not to dump rows.

Where to go next