Pentaho dynamic loading name of month in Pentaho User Console

Question

In my dimensional table i have month field with number representation of month 1...12, but in this table i don't have name of that months (Jan, Feb, etc...). In mondrian file i use it like time dimension.

<Dimension type="TimeDimension" visible="true" highCardinality="false" name="timedimension" caption="Datetime">
    <Hierarchy visible="true" hasAll="true" primaryKey="id_date">
      <Table name="dim_date" schema="dbo">
      </Table>
      <Level name="year" visible="true" column="year4" type="String" uniqueMembers="false" levelType="TimeYears" hideMemberIf="Never" caption="year">
        <Annotations>
          <Annotation name="AnalyzerDateFormat">
            <![CDATA[[yyyy]]]>
          </Annotation>
        </Annotations>
      </Level>
      <Level name="month" visible="true" column="month" ordinalColumn="month" type="String" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never" caption="month">
        <Annotations>
          <Annotation name="AnalyzerDateFormat">
            <![CDATA[[yyyy].['Q'q].[M]]]>
          </Annotation>
        </Annotations>
      </Level>
    </Hierarchy>
  </Dimension>

When i show this dimension in Pentaho User Console i would like to show the Name of these months instead of numbers in Anylyzer report. Is this possible without adding this names of months into my dimension table. Exists some internal function for showing of that or some property file or internal dictionary for using of that or some attribute in mondrian file? And i would like to have the names of the month depends on selected language in Pentaho User Console.

Answer 1

If creating a column in the dim table is not an option (although this is the recommended approach), you can do it by adding a KeyExpression element to the Level expression:

<Level name="month" visible="true" ordinalColumn="month" type="String" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never">
  <KeyExpression>
    <SQL dialect="generic">
      CASE 
        WHEN month = 1 THEN 'Jan'
        WHEN month = 2 THEN 'Feb'
        (...)
        WHEN month = 12 THEN 'Dec'
      END
    </SQL>
  </KeyExpression>
  <Annotations>
    <Annotation name="AnalyzerDateFormat">
      <![CDATA[[yyyy].['Q'q].[MMM]]]>
    </Annotation>
  </Annotations>
</Level>

Notice that I removed the column and caption attributes. Also notice the change in the value of AnalyzerDateFormat (if you want full month names you must use MMMM instead of MMM).