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 editor

Tools → MDX… opens a full editor — syntax highlighting, completion, the lot — pre-filled with the MDX your shelves currently compile to. Three buttons: Copy, Close, and Run MDX.

Run MDX puts the workbench into raw-MDX mode. A banner appears saying so, and the shelves stop driving the query — from that point the MDX is the query. Clearing the MDX hands control back to the shelves.

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

Export on the toolbar is one dropdown with four destinations. They suit genuinely different audiences.

Excel (XLS)

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.

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.

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.

Email

When your server has mail configured, Email sends the current report straight from Saiku rather than making you export, find the file, and attach it. If it’s greyed out, mail isn’t set up on that deployment — the tooltip says as much.

Drillthrough export

Right-click any data cell to run a drillthrough — the raw fact-table rows behind that one number, straight from the warehouse with no cube aggregation in the way. Set Max rows, then Export CSV.

CSV only, deliberately: raw rows have no pivot structure worth preserving, and anything you’d do with them next wants a plain table.

It’s the answer to “I don’t believe that number” — send the person the records.

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, something to archive alongside a decision.
  • Email when the recipient just needs to see it and you’d rather not round-trip through your downloads folder.
  • Drillthrough when you need the individual rows behind one aggregated number.

A note on row limits

Drillthrough has a Max rows setting, and it’s there for a reason: the warehouse query still has to run and the file still has to travel. A million-row drillthrough is slow at both ends.

If you find yourself routinely exporting hundreds of thousands of rows, that’s usually a signal the cube isn’t summarising at the grain you actually work at. The point of OLAP is to answer the question in the cube — dumping rows is the fallback, not the workflow.

Where to go next