Camshaft

Camshaft runs Essbase MDX queries from the command line and writes the results where you want them.

MDX is the most precise way to ask a cube a question, but the answer comes back as a grid meant for people: headers stacked over the columns, members down the side, #Missing where there’s nothing. Getting that into something a load rule, a database or a script can read used to mean spooling it out of MaxL, then stripping off the headers and the rest before you could use it, and hoping the layout never changed. Camshaft turns it into one command: a query in, a clean file out.

It’s open source under the Apache 2.0 license, and on GitHub. It talks to Essbase over the REST API, so it needs no Oracle software.

What You Can Do With It

Get a fact table out of a cube

Ask for the long shape and every value gets a row of its own, saying exactly what it measures. That’s a file any database, BI tool or dataframe can load without knowing what a cube is:

$ camshaft run actuals.mdx --shape long --pov --members alias --names lower -o actuals.csv
$ head -4 actuals.csv
market,product,measures,scenario,year,value
East,Cola,Sales,Actual,Jan,1812
East,Cola,Sales,Actual,Feb,1754
East,Cola,Sales,Actual,Mar,1805

Run tonight’s extracts

Keep the queries in a folder and Camshaft runs them all, writing each result beside the others with one login, ready for a scheduler:

camshaft batch extracts/ -d /data/essbase/today/ --keep-going

Check that two cubes agree

After a migration, a restore or a change to a calc script, run the same query against both and see every value that differs, matched member by member. It exits with status 1 when anything does, so a script or a build can stop on it:

camshaft diff checks.mdx --profile prod --right-profile test --tolerance 0.01

Hand someone a spreadsheet

Essbase writes Excel itself, laid out as you’d see the grid:

camshaft run report.mdx --members alias -o report.xlsx

Answer a quick question

What’s under Colas, and what are they called?

$ camshaft members Sample.Basic Product --children 100
+--------+--------------------+-------+------------+
| MEMBER | ALIAS              | LEVEL | GENERATION |
+--------+--------------------+-------+------------+
| 100-10 | Cola               |     0 |          3 |
| 100-20 | Diet Cola          |     0 |          3 |
| 100-30 | Caffeine Free Cola |     0 |          3 |
+--------+--------------------+-------+------------+

Feed a script

JSON Lines pipes straight into other tools. Here’s first-quarter cola sales by market, totalled by jq:

$ camshaft run actuals.mdx --shape long --members alias -f jsonl \
    | jq -s -c 'group_by(.Market) | map({market: .[0].Market, sales: (map(.Value) | add)})'
[{"market":"Central","sales":8074},{"market":"East","sales":6292},{"market":"South","sales":3732},{"market":"West","sales":6950}]

Camshaft and Thriller

Camshaft runs its queries through Thriller, the MDX JDBC driver, so a result comes out the same either way. Thriller is for putting MDX inside tools that already speak JDBC, such as IDEs and database tools. Camshaft is for the command line and for scripts, where a SQL tool has no notion of batches of queries or comparing two cubes.

Getting It

Version 2.0.0 is on the releases page, as a single jar for Java 17 or newer. The README has the rest: connecting, every command and option, and building it yourself.

History

Camshaft started in 2014, to save running MDX from MaxL and then stripping the headers off the output before it could be loaded. Version 1 was built on the Essbase Java API: 1.0.1 in 2015, an update in 2016, which also explains the name, and 1.0.2 a month later.

Version 2 rewrote it in 2026 over the REST API. The 1.x code is still in the repository’s history, tagged camshaft-1.x.