A week or two ago I had a request for how to create visualizations that leveraged rolling dates. For example, show the event count for the last 4 weeks (rather than specifically filtering on the last four specific weeks of the year). My teammate Wayne Johnson referred me to some documentation on how to modify your Mondrian schema file to enable this capability in Pentaho Analyzer, and it took me a while to really figure out how to get it to work, so I thought I’d share an example that I was successful with.
First, the goal. For this test data set, I wanted to be able to show something like the image below, and have it be correctly up to date each day to show only the prior four days.
So how did I get there? Have a look at the Mondrian schema file:
<Cube name=”Events” visible=”true” cache=”true” enabled=”true”>
<Table name=”fact_events”>
</Table>
<DimensionUsage source=”Date” name=”Date” visible=”true” foreignKey=”date_key” highCardinality=”false”>
</DimensionUsage>
<Measure name=”Events” column=”id” formatString=”#,###;(#,###)” aggregator=”count”>
</Measure>
</Cube>
<Dimension type=”TimeDimension” visible=”true” highCardinality=”false” name=”Date”>
<Hierarchy name=”Date” visible=”true” hasAll=”true” primaryKey=”date_id”>
<Table name=”dim_date”>
</Table>
<Level name=”Year” visible=”true” column=”year_key” type=”Integer” uniqueMembers=”false” levelType=”TimeYears” hideMemberIf=”Never”>
<Annotations>
<Annotation name=”AnalyzerDateFormat”>
<![CDATA[[yyyy]]]>
</Annotation>
</Annotations>
</Level>
<Level name=”Quarter” visible=”true” table=”dim_date” column=”quarter_key” nameColumn=”quarter_desc” type=”Integer” uniqueMembers=”false” levelType=”TimeQuarters” hideMemberIf=”Never”>
<Annotations>
<Annotation name=”AnalyzerDateFormat”>
<![CDATA[[yyyy].['Q'q yyyy]]]>
</Annotation>
</Annotations>
</Level>
<Level name=”Month” visible=”true” column=”month_key” nameColumn=”month_desc” type=”Integer” uniqueMembers=”false” levelType=”TimeMonths” hideMemberIf=”Never”>
<Annotations>
<Annotation name=”AnalyzerDateFormat”>
<![CDATA[[yyyy].['Q'q yyyy].[MMM yyyy]]]>
</Annotation>
</Annotations>
</Level>
<Level name=”Date” visible=”true” column=”date_id” nameColumn=”date_desc” type=”Integer” uniqueMembers=”false” levelType=”TimeDays” hideMemberIf=”Never”>
<Annotations>
<Annotation name=”AnalyzerDateFormat”>
<![CDATA[[yyyy].['Q'q yyyy].[MMM yyyy].[yyyy-MM-dd]]]>
</Annotation>
</Annotations>
</Level>
</Hierarchy>
</Dimension>
The key to getting this to work is matching up the annotation format from each dimension level with the values from the date/time dimension table. In this case, the values from my date dimension looked like this:
As such, I had to provide some string literals within the “AnalyzerDateFormat” annotation and use the year for every level. For example, ‘[yyyy].['Q'q yyyy].[MMM yyyy]‘ matches the year, quarter, and month format. ’[yyyy]‘ is clearly the year format, but the ‘quarter_desc’ column in the dim_date table held values that were non-standard according to the documented formatting rules. The ‘Q’ in ‘['Q'q yyyy]‘ matches directly with the data, whereas the ‘q’ matches with the numeric quarter (1 in the graphic above).
Once I figured out how to configure the format strings, all I had to do was make use of the newly available filtering option for dates. After right clicking on “Date” and choosing “Filter”, now I can “choose a commonly used time period” and select a specific number of previous dates (in this case 25, because my data is a little bit old).
If I set my system clock forward a day and re-run the report, we see that the report automatically updates (in this case showing only three days worth of data):
Hopefully this is helpful!
