I have a time dimension defined something like the following:
<Dimension name="optin" type="TimeDimension">
<Hierarchy name="optin" hasAll="true" allMemberName="all optin" primaryKey="profile_id">
<Table schema="schema1" name="profiles"/>
<Level name="optin_year" column="optin_year" uniqueMembers="false" type="Numeric" levelType="TimeYears"/>
<Level name="optin_quarter" column="optin_quarter" uniqueMembers="false" type="Numeric" levelType="TimeQuarters"/>
<Level name="optin_month" column="optin_month" uniqueMembers="false" type="Numeric" levelType="TimeMonths"/>
<Level name="optin_day_in_month" column="optin_day_in_month" uniqueMembers="false" type="Numeric" levelType="TimeDays"/>
</Hierarchy>
</Dimension>
This works well for segmenting on various date parts (year, quarter,
month, day). However, I want to also be able to report the number of
profiles which have the optin field filled in (non-NULL). For
non-time dimensions adding this hierarchy to the above dimension
works:
<Hierarchy name="defined" hasAll="true" allMemberName="all optin" primaryKey="profile_id">
<Table schema="schema1" name="profiles"/>
<Level name="defined" uniqueMembers="true" type="Boolean">
<KeyExpression>
<SQL>optin_day_in_month IS NOT NULL</SQL>
</KeyExpression>
</Level>
</Hierarchy>
However, when I add this to a time dimension I get the following exception from Mondrian:
Level '[optin.defined].[defined]' belongs to a time hierarchy, so
its level-type must be 'Years', 'Quarters', 'Months', 'Weeks' or
'Days'.
I can place the hierarchy defined into a separate dimension, but
then I end up with two non-orthogonal dimensions which don't make any
sense to segment on simultaneously. These really are two
hierarchies within the same dimension. I can also turn the optin
dimension into a non-time dimension but then I lose the ability to
use time series functions (PARALLELPERIOD, YTD, etc.).
What's the best way to achieve what I want--to be able to segment on a date field parts and on whether or not it's empty?