Import a spreadsheet into a cube

You have a workbook and you want its numbers in a cube. XCubes reads the file server-side and writes the values, so the job is not transcription — it is telling it which part of the sheet is data, and which dimension items the labels correspond to.

That second half is where imports go wrong, and the failure is permanent: every distinct label in the range becomes a dimension item, and a dimension is shared across cubes, so a stray item appears in every cube that uses it. There is no undo. This guide is mostly about not doing that.

Which shape is your sheet?

Matrix — row labels down one column, column items across the header row. The normal layout of a financial model: line items × periods. Two dimensions, one per axis.

Long — one row per record, one column per axis, and a single column of values. The normal shape of a system export.

A long sheet can carry any number of axes; a matrix import handles exactly two. If your workbook has several sections stacked on one tab — assumptions, then a P&L, then a debt schedule — that is several imports into several cubes, not one.

Import or Link?

An import is one-shot: it reads the file now and merges the values into the cube. A Link is a saved definition that can be re-run, refreshed on a schedule, previewed, and reverted, and it can translate codes through a mapping table.

Import when the file is a one-time load or a starting point. Build a Link when the source will be delivered again — a monthly export, an accounting extract — and especially when the file's codes differ from your item codes. The matrix wizard splits the difference: it can save its definition and re-run it against a new file, showing only the rows that changed.

Create the target first

Both imports write into a cube that already exists. Create the cube and its dimensions first, and if you already know the item codes, create the items too — an import into a populated dimension has something to match against, which is what turns "creates 47 items" into "matches 45, creates 2".

Preview before you write

In the UI: the matrix wizard walks you through it — upload, layout, axes, then a Match step listing every label with the item it resolved to, where anything unmatched must be pointed at an existing item or explicitly marked as a new one, and finally a Preview before the commit. The long-format importer has the equivalent in its column mapping and review steps.

Over MCP: nothing walks you through it, so the preview is a call you have to make:

preview_matrix_import({
  "attachmentId": "<file-id>",
  "cubeId": "<cube-id>",
  "rowDimensionId": "<rows>",
  "columnDimensionId": "<cols>",
  "startRow": 4,
  "endRow": 38
})

It writes nothing and takes the same parameters as the import. Read newRowItems and newColumnItems — the labels that would be created, by name — and sharedDimensions, the other cubes those items would show up in. A title row, a units row, a footnote, a source note, or a 2023 (p) provisional marker appears here as an item about to be minted. That is the moment to narrow the range, not after.

Against an existing cube, newRowItemCount: 0 and newColumnItemCount: 0 means every label matched. That is the signal to run for real.

Use the preview tool by name rather than a dry-run flag on the import: a client holding a stale schema silently drops an unknown parameter and the "preview" then imports for real, whereas an unknown tool name fails loudly.

Narrowing the range

Most sheets are not clean rectangles. Cut the range down rather than cleaning the file:

• startRow / endRow bound the data rows, using the same 1-based numbering the sheet reader reports.
• labelColumn says which column holds the row labels.
• columns[] selects a subset of columns, renames the items they map to, or reaches a column with no header at all — a spacer column holding single-value assumptions, say.

A report whose header row is one merged title is still importable: read the real label row, pass those names in columns[] by sheet position, and set startRow past them. Column numbers are bounded by the widest data row, not by the header.

Matching labels to existing items

Labels that differ from an existing item only mechanically — case, whitespace, or a short trailing unit tag, as in sheet Purchase Value against item Purchase Value (M) — reuse that item automatically, and the result lists them.

A label that differs in wording never merges by guess. Sheet Total revenues against item Revenue creates a second item unless you say otherwise, which you do with a rename map — in the wizard by picking the target on the Match step, over MCP with renameMap. The server will not infer a semantic rename, and that is deliberate: guessing one wrong silently merges two different measures.

Reconcile, then run

Before committing an import into a dimension that already has items, check both directions:

• Items your range does not cover come back as existingItemsNotCovered — rows that will stay empty.
• Rows in range with no values come back as itemsWithoutValues.

Either can be correct. What is not correct is claiming the cube will match the file without having looked. After the run, read the cube back and compare it against the source — the import moved values, but only a comparison shows that the formula items above them are wired to the right rows.

For a ledger export with its own chart of accounts, don't import it directly — see Build financial statements from a ledger export, which routes it through a mapping table and ties every line out first.