How to use Cube.create_date_hierarchy()?

[Harold7665]

Hello,
I have a table named time_table with a key MonthKey and one of its fields is QuarterKey. From this field (or column), I would like to create a date hierarchy on year and quarter.
The data extracted from the in-memory table:

MonthKey
2018M04    2018Q2
Name: QuarterKey, dtype: object

The code:

h['Time v2'] = cube.create_date_hierarchy('Time v2', column=time_table['QuarterKey'], levels={'Years':'yyyy', 'Quarters':'QQQ'})

The stacktrace:

---------------------------------------------------------------------------
AtotiJavaException                        Traceback (most recent call last)
Input In [42], in <cell line: 1>()
----> 1 h['Time v2'] = cube.create_date_hierarchy('Time v2', column=time_table['QuarterKey'], levels={'Years':'yyyy', 'Quarters':'QQQ'})

File ~\.conda\envs\atoti-plus-0.7.0\lib\site-packages\atoti\_runtime_type_checking_utils.py:179, in _TypecheckWrapperFactory.create_wrapper.<locals>.typechecked_func_wrapper(*args, **kwargs)
    176 typeguard.check_argument_types(memo)
    178 # Call the actual function.
--> 179 return self._func(*args, **kwargs)

File ~\.conda\envs\atoti-plus-0.7.0\lib\site-packages\atoti\cube.py:709, in Cube.create_date_hierarchy(self, name, column, levels)
    577 def create_date_hierarchy(
    578     self,
    579     name: str,
   (...)
    584     ),
    585 ) -> None:
    586     """Create a multilevel date hierarchy based on a date column.
    587 
    588     The new levels are created by matching a `date pattern <[https://docs.oracle.com/en/java/javase/15/docs/api/java.base/java/time/format/DateTimeFormatter.html#patterns>`_](https://docs.oracle.com/en/java/javase/15/docs/api/java.base/java/time/format/DateTimeFormatter.html#patterns%3E%60_).
   (...)
    707 
    708     """
--> 709     self._java_api.create_date_hierarchy(
    710         cube_name=self.name,
    711         table_name=column._table_name,
    712         field=column.name,
    713         hierarchy_name=name,
    714         levels=levels,
    715     )
    716     self._java_api.refresh()

File ~\.conda\envs\atoti-plus-0.7.0\lib\site-packages\atoti\_java_api.py:158, in _enhance_py4j_errors.<locals>.wrapped_method(self, *args, **kwargs)
    152 except Py4JJavaError as java_exception:
    153     cause = (
    154         str(java_exception)
    155         if get_env_flag(_ATOTI_VERBOSE_JAVA_EXCEPTIONS)
    156         else self.get_throwable_root_cause(java_exception.java_exception)
    157     )
--> 158     raise AtotiJavaException(
    159         cause,
    160         java_traceback=str(java_exception),
    161         java_exception=java_exception,
    162     ) from None

AtotiJavaException: [500] Failed to process update of type MergingStorePartitionUpdate for partition 0 of store `Times`. Failed operation summary: added: 0, deleted: 0, updated: 0

I have also tested the following combinations: y/Q ; y/QQQ ; yyyy/Q.
Do you know what I am missing?

Thanks,
Harold

[bloussou]

Is the type of the column QuarterKey String ? If not what is its type ?

Date hierarchy can only created against column with Temporal type (LOCAL_DATE, LOCAL_DATE_TIME, ...), see the column parameter documentation .

The error message was unclear I'm working to improve it in the next release,

[Harold7665]

Thank you! String, yes, do you know if there is enough information (Y and Q only) to force the type to LOCAL_DATE on the table definition?

[bloussou]

no because this is not a date, it cannot be parsed to local date. What you want to achive is two columns year and quarter and this must be done during the preprocessing I think.

[Harold7665]

Okay, thank you