Non-time level inside a time dimension

Question

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?

Answer 1

Level '[optin.defined].[defined]' belongs to a time hierarchy, so its level-type must be 'Years', 'Quarters', 'Months', 'Weeks' or 'Days'.

The error tells you, that it is impossible to have "non-time based" (Regular) levels within time dimension (TimeDimension):

<Dimension name="optin" type="TimeDimension">
   ...
   <Level name="defined" uniqueMembers="true" type="Boolean">

A level of the Regular type can be used only with StandardDimension.

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?

I think you don't use the time dimension properly. Standard way is to have time dimension as follows:

• DWH: Preloaded time data in you data warehouse (records for every day from, let say, 1900-01-01 to 2099-12-31) - table dim_time with date_key (e.g. 20150123)
• OLAP schema: Proper time dimension <Dimension name="Time" type="TimeDimension"> which contains levels for "years / quarters / months / weeks / days". Similar to the one you have but this way it contains all days. With primary_key attribute pointing to date_key.

Next step is to adjust the optin (profile):

• DWH: optin row data + optin_date_key (which can be null or have value)
• OLAP schema: Create cube for profile with, use time dimension created before with foreign_key pointing to optin_date_key. And measure which sums all profiles with non-null optin_date_key values.

Sample OLAP code for profile cube:

<DimensionUsage source="Time" name="Time" caption="Profile Date" visible="true" foreignKey="optin_date_key" highCardinality="false">
</DimensionUsage>
<Measure name="#Profiles" aggregator="sum" visible="true">
  <MeasureExpression>
    <SQL dialect="generic">
        <![CDATA[optin_date_key]]>
    </SQL>
  </MeasureExpression>
</Measure>

This way you should be able to report the number of profiles which have the optin field filled in (non-NULL).

What more you can do is using this cube within a virtual cube to with join other measures.