Apr 022013
 

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!

  10 Responses to “Relative Date Filtering in Pentaho Analyzer”

  1. Your example looks similiar to what I have but my relative date filter only works for year, not quarter, month, date. I copied your xml snippet in my cube xml file and adjusted for my column names, but it still id not work. I am wondering if I have something that does not match in may date dimension table.
    Can you post a sample of the data in the dim_date table?

  2. I figured out my issue, had to read your article a couple of time to realize the display value and the annotation had to exactly match. Thanks for the article.

  3. This is only working for dates, not time (Hours and Minutes) as I can see it.

    Using [yyyy].[MM].[dd].[HH] for the Hour level does not work.

  4. you’ve got an excellent weblog here! would you like to make some invite posts on my blog?

  5. Typically it will likely be happened in less than one day plus
    certain instances, a bad credit score payday advances lenders could possibly get you the money
    within few hours.

  6. Thanks for sharing and here i wasted my lot of time to reading your blog post but i like it,.

  7. I like to read your post, great suggestions. Your every post seems more like better for conversation and so useful. I never forget to read your next post.

  8. I appreciate your work. i have been looking for the answer for long time and your article helped

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>