If you work in FP&A or management reporting, there is a good chance your numbers come from Essbase. It is a multidimensional database — a “cube” — designed for the questions finance people ask all day: What were actual sales for the North region in Q3 compared with budget?

Cubes and dimensions

Think of a normal Excel table: rows and columns, two dimensions. A cube simply has more. A typical finance cube has dimensions such as:

Every number lives at one intersection: Revenue × Q3 × Actual × North × INR. Smart View lets you slice those intersections in Excel.

The outline: hierarchies do the maths

Members are arranged in hierarchies. Q1 is the parent of Jan, Feb and Mar; Gross Margin is Revenue minus COGS. Because the hierarchy knows how to roll up, totals are always consistent — no broken SUM ranges.

BSO vs ASO

Dense and sparse (BSO)

In BSO, dimensions are marked dense (most combinations have data, e.g. Account × Period) or sparse (most combinations are empty, e.g. Product × Customer). Essbase stores a block for each existing sparse combination, containing all dense cells. Getting this right is the biggest factor in cube size and speed.

A first calc script

/* Aggregate Actual for FY25 */
FIX ("Actual", "FY25")
   CALC DIM ("Account");
   AGG ("Entity", "Product");
ENDFIX

FIX limits the calculation to a slice of the cube, which keeps it fast. CALC DIM calculates a dimension including member formulas; AGG simply rolls up sparse dimensions.

A first MDX query

SELECT
  {[Period].[Q1], [Period].[Q2]} ON COLUMNS,
  {[Entity].Children} ON ROWS
FROM [Sample].[Basic]
WHERE ([Scenario].[Actual], [Account].[Revenue])