[Mondrian] time dimension oddities

[Kristopher Kiger]

Hey all, I'm having some problems getting a time dimension to play nicely with a simple calculated measure.  The calculated measure is pretty simple, it looks at the previous member in time and returns that member to me.  I've tried this with three time hierarchies:

Hierarchy #1:  year -> quarter -> month -> day
Hierarchy #2:  year -> week -> day
Hierarchy #3:  year -> quarter -> month

Here is a sample of the query I'm using:  http://pastebin.com/4j06KXzx
Note that "All Periods" is representative of Hierarchy #1.

In any case, I've modified this query to work exclusively with the three hierarchies I listed above.  Two columns are returned.  The current value and the value in the previous step of time, for the current member.  When I run this against hierarchies #2 & #3, I get the values I expect in both columns.  However, when I run it against hierarchy #1, the results are... off.  The meat of the results start on line 478.

Results:  http://imageshack.us/photo/my-images/713/saikuoutput.jpg/

As you can see, for the first few days of listed values for the month of April, prevMember results are as expected.  The first column has data that is repeated and staggered in the second.  However, after the first week has been completed, you can see how prevMember begins recycling results for the remainder of that month and then abruptly resets when the next month marker is reached.  There shouldn't have been values to stagger for the 2nd week and beyond.  I'm having trouble understanding why hierarchy #2 & #3 work flawlessly, but heirarchy #1 is presenting this oddity.

It may also be worth noting that heirarchy #1 seems to return correct data when I stop at the month level for results.  Going into days causes problems.

Here is the xml behind hierarchy #1, if it helps:

<Hierarchy name="All Periods" visible="true" hasAll="true" primaryKey="time_sk" primaryKeyTable="dim_time">
    <Table name="dim_time">
    </Table>
    <Level name="year" visible="true" column="year" type="Numeric" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never" description="year">
    </Level>
    <Level name="quarter" visible="true" column="quarter" nameColumn="quarter_name" type="Numeric" uniqueMembers="false" levelType="TimeQuarters" hideMemberIf="Never" description="quarter">
    </Level>
    <Level name="month" visible="true" column="month_in_year" nameColumn="month_long_name" ordinalColumn="month_in_year" type="Numeric" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never" description="month">
    </Level>
    <Level name="day" visible="true" column="day_in_month" nameColumn="day_long_name" ordinalColumn="day_in_month" type="Numeric" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never" description="day">
    </Level>
</Hierarchy>


If I can provide any more information, please let me know.  Thanks for taking your time to help me out :)

Cheers,

Kris
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20111228/90567ff2/attachment.html