Issue with relative date filtering Pentaho Workspace(Mondrian) AnalyzerDateFormat Week overlaps two Months only showing beginning days of the week

Question

I have a table dimDate built correctly. I am using a surrogate key DC_tk('20211202') in my fact table to map to the primary key in dimDate CCYYMMDD('20211202') which correlates to date_tk in dimDate. The issue is that when the previous week is selected in the analyzer filter the month that is showing up in the Pentaho Analyzer is November and not December, in other words only the beginning days of the week. I suspect this is because week 49 overlaps Nov and Dec. If the Select from list filter value is set to week 49 then two lines of 49 are available to filter on. The code is as follows:

<Dimension type="TimeDimension" visible="true" highCardinality="false" name="EXCREATEDDATE">
<Hierarchy name="Created Date" visible="true" hasAll="true" primaryKey="date_tk">
  <Table name="DimDate" schema="dbo">
  </Table>
  <Level name="Created Year" visible="true" column="YEAR" type="String" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never">
    <Annotations>
      <Annotation name="AnalyzerDateFormat">
        <![CDATA[[yyyy]]]>
      </Annotation>
    </Annotations>
  </Level>
  <Level name="Created Month" visible="true" column="MONTH" type="String" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never" captionColumn="month_short_desc">
    <Annotations>
      <Annotation name="AnalyzerDateFormat">
        <![CDATA[[yyyy].[M]]]>
      </Annotation>
    </Annotations>
  </Level>
  <Level name="Created Week" visible="true" column="week_of_year" type="String" uniqueMembers="false" levelType="TimeWeeks" hideMemberIf="Never">
    <Annotations>
      <Annotation name="AnalyzerDateFormat">
        <![CDATA[[yyyy].[M].[w]]]>
      </Annotation>
    </Annotations>
  </Level>
  <Level name="Created Day" visible="true" column="day_of_month" type="String" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never">
    <Annotations>
      <Annotation name="AnalyzerDateFormat">
        <![CDATA[[yyyy].[M].[w].[d]]]>
      </Annotation>
    </Annotations>
  </Level>
  <Level name="Created Date" visible="true" column="DateCCYYMMDD" type="String" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never">
    <Annotations>
      <Annotation name="AnalyzerDateFormat">
        <![CDATA[[yyyy].[M].[w].[d].[yyyyMMdd]]]>
        </Annotation>
        </Annotations>
  </Level>
</Hierarchy>

<DimensionUsage source="EXCREATEDDATE" name="EXCREATEDDATE" visible="true" foreignKey="DC_tk" highCardinality="false"></DimensionUsage>

How can I prevent this from happening while using Pentaho's built in relative date filtering? Any help on this issue is appreciated.

Answer 1

I realized that since there are two values for month and this would skew the hierarchy. The recognized hierarchy is:

• [yyyy] (Year)
• [yyyy].[q] (Quarter)
• [yyyy].[q].[M] (Month)
• [yyyy].[q].[M].[w] (Week)
• [yyyy].[q].[M].[w].[yyyy-MM-dd] (Day)

But this fails if a week is extended over two months.

Modifying the hierarchy by switching the order of Month and Week to read as below will work:

• [yyyy] (Year)
• [yyyy].[q] (Quarter)
• [yyyy].[q].[w] (Week)
• [yyyy].[q].[w].[M] (Month)
• [yyyy].[q].[w].[M].[yyyy-MM-dd] (Day)