Skip to content
Saiku Cloud

Building a query

A Saiku query is built by dragging fields onto shelves. There’s no SQL to write and no MDX either — the query is composed as you go. This page covers the moves you’ll use over and over.

Almost everything beyond the first drag lives in a right-click menu. That’s worth knowing up front, because nothing on screen advertises it: right-click a chip on a shelf, a row or column header in the grid, or a data cell, and you get a menu tuned to what you clicked.

The minimum useful query

  1. Open the cube from Analyze, or pick one from the cube dropdown.

  2. Drag a measure — Revenue, say — onto the MEASURES shelf.

  3. Drag a dimension level — Time › Year — onto ROWS.

  4. The result appears. There’s nothing to click.

That’s a year-by-year revenue table. Add a second measure and it becomes a second column; add a second level to ROWS and Saiku nests them.

Drilling down

Right-click a row or column header and choose Include Level ▸, then the level you want. Product Family gains Product Department beneath it; do it again for Product Category. Each level nests inside the one above, and the parent totals stay visible.

Two neighbours in the same menu do the related jobs:

  • Keep Only — narrow the query to just this member. The fast way to go from “all product families” to “only Drink” without opening a filter dialog.
  • Remove Level — take the deepest level back off.
  • Filter Level… — open the member selector for the level you clicked.

Filtering

Four ways in, depending on what you’re restricting.

By member. Right-click a hierarchy chip on a shelf and choose Edit selections…. Tick the members you want, untick the rest. This is your “only the South-West region”, “only these three product lines” tool.

By date. Right-click a date hierarchy chip and choose Date filter… — a dialog built for date ranges rather than a list of members to tick.

By value. Right-click a measure chip and choose Filter by value…. Pick an operator (>, >=, <, <=, =, !=, BETWEEN, NOT BETWEEN) and a value. This is how you get “only rows where Unit Sales is over 100,000” without writing anything.

By MDX. Open the shelf’s axis options (⋯) and choose Filter (MDX)… for a free-form boolean expression, with editor support. The escape hatch for anything the other three can’t say.

Ranking and limiting

The axis options menu (⋯ on a shelf) is where ranking lives — not in a filter dialog:

  • Order… — sort the axis by a measure, ascending or descending.
  • Top count… — keep the highest N.
  • Bottom count… — keep the lowest N.
  • Limit… — cap how many members come back at all.

So “top 10 products by revenue” is: Product Name onto ROWS, Revenue onto MEASURES, then Top count… on the Rows axis with N = 10 and Revenue as the measure. That’s genuinely a top-10 — Mondrian ranks server-side and returns ten rows, rather than fetching everything and hiding the rest.

Rearranging without re-dragging

The chip right-click menu also moves things about:

  • Move to Columns / Move to Rows — flip one hierarchy across without picking it up.
  • Position ▸ — control whether measures sit inside or outside the dimension on each axis (Rows | Measures vs Measures | Rows, and the same for columns), plus Reset to default.
  • Swap axes on the toolbar — exchange Rows and Columns wholesale.

Extra calculations

Two things you can add on a measure without touching the schema:

  • Format as percentage… — show a measure as a share rather than an absolute.
  • Growth calculation… — period-over-period growth off an existing measure.

For anything more, calculated members let you define a new measure from an MDX formula — [Measures].[Revenue] - [Measures].[Cost] — with a name and a format string like #,##0. It joins the measures list and drags like any other. Calculated members are saved with the workbook, so “save as” is how you spin off a variant that keeps them.

Drillthrough — the rows behind a number

Right-click any data cell and Saiku fetches the raw fact-table rows behind it. This bypasses the cube’s aggregation entirely and goes to the warehouse.

It’s the tool for “why is this number so high?” — and for convincing yourself that a surprising result really is what’s in the data. You can export the drillthrough rows straight to Excel or CSV; see MDX & export.

Drill through… on the Tools menu does the same thing with a dialog, if you’d rather choose the columns you get back.

Drill across

Tools → Drill across… traverses two cubes that share a dimension. If Sales and Inventory are both keyed on Product, this is how you bring stock levels into a sales analysis without building a third cube.

Selecting cells

Drag a selection across the grid and a readout appears with Count, Sum, Average, Min, Max and standard deviation for what you’ve highlighted. It’s the fastest way to sanity-check a subtotal without adding one to the query.

When auto-run gets in the way

Auto-run is on by default, which is right for most work — but not when you’re assembling a heavy pivot a step at a time and each intermediate state is an expensive query nobody wants.

Open the dropdown beside Run and untick Auto-run. Compose freely, then hit Run when the query is the one you meant. The same menu has Async, which pushes long queries into the background so the UI stays usable while they finish.

What you’re actually building

Every drag compiles to MDX. Tools → MDX… shows you the current query and lets you edit it by hand. Most analyses never need it, but it earns its place for:

  • Working out why a result isn’t what you expected.
  • Copying the query into another tool that speaks MDX.
  • Hand-tuning something the shelves can’t quite express.

MDX & export covers the editor properly.

Where to go next