CODEWITHSUNDEEP
pentahopentaho-spoonpentaho-data-integrationmondrianpentaho-report-designer
CPHILLIPS1234
CPHILLIPS1234

Reputation: 65

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

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.

Upvotes: 0

Views: 205

Answers (2)

Benny Chow
Benny Chow

Reputation: 49

By design, if Previous Week is Week 49 and it spans a month (or year), when filtering on Previous Week, the MDX that Analyzer generates should be able to figure out there are two members and include both Week 49 members (i.e. the one from November and the one from December).

If you are able to upgrade to 9.x, then there are a bunch of analyzer.properties that you can configure so that Analyzer can generate the correct week number and week year. As long as Analyzer can figure out the correct current week member based on the current date, then it can look back to the prior member to see if it is also the same week number but possibly different month or year.

These new properties are as follows:

filter.relative.dates.week.firstDayOfWeek
filter.relative.dates.week.minimalDaysInFirstWeek
filter.relative.dates.week.firstWeekOnJanuary1st
filter.relative.dates.week.firstWeekOnJanuary1st.fromOne

See https://help.hitachivantara.com/Documentation/Pentaho/9.0/Products/Adapt_Mondrian_schemas_to_work_with_Analyzer#Relative_date_filters_for_weeks

BTW, there's a link in Admin->MDX->Check AnalyzerDateFormat that will print out the current through previous 100 time period members for each date level so that you can verify that the AnalyzerDateFormat annotation and the above properties are setup correctly for relative date filtering on your date dimension.

enter image description here

Upvotes: 0

CPHILLIPS1234
CPHILLIPS1234

Reputation: 65

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)

Upvotes: 0

Related Questions