eMondrian Docs

Modeling on ClickHouse

3 min read

ClickHouse and eMondrian split the work: ClickHouse stores and aggregates the data, eMondrian holds the business model and turns every question into SQL. This page collects practical patterns, using the OnTime sample (schema/OnTime.xml) as the example.

Connection setup: Connect databases.

Dimensions as views over one wide table

Analytical data in ClickHouse is often one wide table. You don’t need separate dimension tables — define views with GROUP BY in the schema:

<View alias="dim_airline">
  <SQL dialect="clickhouse"><![CDATA[
    SELECT Reporting_Airline
    FROM ontime
    GROUP BY Reporting_Airline
  ]]></SQL>
</View>

ClickHouse builds small dimensions like this quickly from the fact table. For large dimensions (millions of customers) consider a real dimension table or a materialized view in ClickHouse.

Computed attributes can be built in the view:

SELECT
  Year * 100 + Month AS YearMonth,
  Year, Quarter, Month,
  concat('Q', toString(Quarter)) AS QuarterName
FROM ontime
GROUP BY Year, Quarter, Month

Keep the fact view narrow

The cube’s fact view should select only the keys and measure columns the cube needs:

<View alias="fact">
  <SQL dialect="clickhouse"><![CDATA[
    SELECT
      Year * 100 + Month AS YearMonth,
      Year, Quarter, Month, DayOfWeek,
      Reporting_Airline,
      Flights, Cancelled, Diverted, ArrDel15, ArrDelayMinutes
    FROM ontime
  ]]></SQL>
</View>

ClickHouse is a column store — fewer columns mean less data read.

Use AttributeMapping to skip joins

When an attribute’s value is also in the fact view, map it:

<AttributeMapping attribute="Airline" factColumn="Reporting_Airline"/>

eMondrian can then group and filter on the fact view directly, without joining the dimension view — usually much faster on ClickHouse.

Check that dimension keys are unique

This is the most common source of wrong numbers. Run for every dimension view:

SELECT key_column, count() AS c
FROM ( <your dimension view SQL> )
GROUP BY key_column
HAVING c > 1

Real example from OnTime: six airport codes (SJU, STT, STX, GUM, USA, SPN) appear with two spellings of their city or state. Grouping by code, city and state gives two rows per code; the join from flights fans out, and 2022 Flights come out as 6,767,533 or 6,729,125 depending on whether the join is used. The schema deliberately doesn’t map Origin Airport and documents why.

Fix duplicates in the view (e.g. any(OriginCityName) grouped by code only) before using AttributeMapping.

Define KPIs once as calculated members

Put ratios and averages in the model, built from additive base measures:

<Measure name="Flights" column="Flights" aggregator="sum"/>
<Measure name="Total Arr Delay" column="ArrDelayMinutes" aggregator="sum" visible="false"/>
 
<CalculatedMember name="Avg Arr Delay" dimension="Measures" formatString="#,##0.00">
  <Formula>[Measures].[Total Arr Delay] / [Measures].[Flights]</Formula>
</CalculatedMember>

ClickHouse sums the base measures; eMondrian computes the ratio. The average is then correct at every level of every hierarchy — unlike an avg aggregator, which averages rows rather than weighting them.

Distinct counts

aggregator="distinct-count" works (e.g. Distinct Tail Numbers), but distinct counts can’t be added up across members, so every combination is a separate ClickHouse query. Use them where they matter.

Large queries

The OnTime connection string raises ClickHouse limits for big generated queries:

max_query_size=100000000&amp;max_ast_elements=100000000&amp;max_expanded_ast_elements=100000000&amp;socket_timeout=300000

Add these if you see ClickHouse errors about query size or AST elements.

Watch the SQL

mondrian-sql.log shows every query sent to ClickHouse (see Logs). Click through a report in Excel or Power BI and watch it: you’ll see which views are joined, whether AttributeMapping takes effect, and which visuals are expensive.

Within a client session, repeated queries are answered from eMondrian’s cache. After loading new data into ClickHouse, restart eMondrian (docker compose restart eMondrian) or reconnect the client to see it.

See also

  • Schema reference
  • schema/OnTime.xml in the Community repository — the complete example

Backlinks