Hi,
Thanks for sharing the solution.
I tried to reproduce your test case and found that Mondrian + ClickHouse are failing when it comes to dimension hierarchies. If you take look at the screenshot below you will see that January-December total for 2017 is exactly the same as the grand total for all years. There must be an error in how Mondrian queries ClickHouse. Did you encounter such an issue?
Well... answering my own question: the problem is with schema file. Everything works fine if I change Date dimension definition to:
<Dimension name="Date" type='TimeDimension'>
<Hierarchy hasAll="true" allMemberName="All Dates" primaryKey="FlightDate">
<Level name="Year" column="Year" uniqueMembers="true" levelType="TimeYears" type="Numeric" />
<Level name="Month" column="Month" uniqueMembers="false" ordinalColumn="Month" levelType="TimeMonths" type="Numeric">
<CaptionExpression>
<SQL dialect="generic">
CASE Month WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
WHEN 12 THEN 'December'
ELSE 'Unknown' END
</SQL>
</CaptionExpression>
</Level>
<Level name="Day" column="DayofMonth" uniqueMembers="false" ordinalColumn="DayofMonth" nameColumn="FlightDate" levelType="TimeDays" type="Numeric"/>
</Hierarchy>
</Dimension>
Sergei, thanks again for sharing your project. Very useful indeed
Hi,
Thanks for sharing the solution.
I tried to reproduce your test case and found that Mondrian + ClickHouse are failing when it comes to dimension hierarchies. If you take look at the screenshot below you will see that January-December total for 2017 is exactly the same as the grand total for all years. There must be an error in how Mondrian queries ClickHouse. Did you encounter such an issue?
Well... answering my own question: the problem is with schema file. Everything works fine if I change
Date
dimension definition to:Sergei, thanks again for sharing your project. Very useful indeed
Thank you Vlad! Really, there were issues in Date dimension. I've fixed them in article. You can check it out.