CODEWITHSUNDEEP
analyticsmdxdata-warehousebusiness-intelligenceolap
kirill fedorov
kirill fedorov

Reputation: 423

OLAP Schema: Dynamic Cube Selection in Virtual Cube with ClickHouse Backend

I'm developing a virtual cube in Pentaho Workbench (version 8.3) that combines two physical cubes:

  1. pp_operations: original cube with detailed operation data
  2. pp_operations_by_orders: optimized cube for order-level aggregations

The goal is to dynamically switch between these cubes based on the dimensions and filters selected by the analyst in Pentaho Saiku Analytics:

To achieve this, I'm creating a virtual cube and calculated members to check for selected operation-related dimensions. However, I'm facing two conflicting issues:

  1. Some dimensions have multiple hierarchies, requiring explicit hierarchy specification.
  2. The backend database is ClickHouse (version 23.12), which causes type mismatch errors when trying to convert MDX to SQL if hierarchies are explicitly specified.

Here are examples of the errors I'm encountering:

The 'operation_date' dimension contains more than one hierarchy, therefore the hierarchy must be explicitly specified.

Cannot convert string 6_day to type UInt16: while executing 'FUNCTION equals(toYear(operation_date_utc_short) : 2, '6_day' : 1) -> equals(toYear(operation_date_utc_short), '6_day') UInt8 : 3'. (TYPE_MISMATCH)

I've tried two approaches, but each solves only one of the issues:

  1. Explicitly specifying hierarchies (solves the multiple hierarchy issue but causes ClickHouse type mismatch):
<CalculatedMember name="HasOperationDateFilter" formula="
  IIF(
    COUNT(EXISTING [operation_date].[1_year].Members) > 1 OR
    COUNT(EXISTING [operation_date].[2_quarter].Members) > 1 OR
    COUNT(EXISTING [operation_date].[3_month].Members) > 1 OR
    COUNT(EXISTING [operation_date].[4_week_of_year].Members) > 1 OR
    COUNT(EXISTING [operation_date].[5_day_of_week].Members) > 1 OR
    COUNT(EXISTING [operation_date].[6_day].Members) > 1,
    1, 0
  )
" dimension="Measures" visible="false">
</CalculatedMember>

<!-- Similar members for other dimensions -->
  1. Using a more generic approach (avoids ClickHouse type mismatch but doesn't solve the multiple hierarchy issue):
<CalculatedMember name="HasOperationDateFilter" formula="
  IIF(
    COUNT(EXISTING [operation_date].Members) > 
    COUNT([operation_date].DefaultMember),
    1, 0
  )
" dimension="Measures" visible="false">
</CalculatedMember>

<!-- Similar members for other dimensions -->

How can I create calculated members that correctly detect selected operation-related dimensions/filters?

Given that these complex MDX expressions will be converted to SQL and executed on ClickHouse, am I potentially over-complicating the solution? Could the performance overhead of these MDX-to-SQL conversions negate any optimization benefits we're trying to achieve?

Upvotes: 0

Views: 58

Answers (0)

Related Questions