[Mondrian] Fwd: Date dimension with multiple hierarchies problem

Diethard Steiner diethard.steiner at gmail.com
Fri Oct 16 09:29:59 EDT 2009


Hi Pedro,
I am using the Mondrian version which is include in Pentaho BI Server 3.5
RC2, not too sure which version that is. I hope this helps.
Thanks,
Diddy

On Fri, Oct 16, 2009 at 12:26 PM, Pedro Alves <pmgalves at gmail.com> wrote:

>
> It's always helpful to tell the mondrian version
>
>
>
> On 09/10/16 10:34, Diethard Steiner wrote:
>
>> Hi,
>> After reading some more literature about multidimensional modeling I
>> decided to restructure my date dimension. Before I had a seperate date
>> dimension for Year/Week/Weekday, Year/Quarter/Month/Day and only Date
>> (as in 2009-02-01). Now I tried to combine everything in one dimension:
>>
>>
>> <Dimension type="TimeDimension" name="Date Dimension">
>> <Hierarchy name="Date" hasAll="true" allMemberName="All Dates"
>> allMemberCaption="All Dates" primaryKey="time_dimension_id">
>> <Table name="sn_lt_time_dimension">
>> </Table>
>> <Level name="Date" column="the_date" type="Date" uniqueMembers="true"
>> levelType="TimeDays" hideMemberIf="Never">
>> </Level>
>> </Hierarchy>
>> <Hierarchy name="Monthly Calendar" hasAll="true" allMemberName="Monthly
>> Calendar All" allMemberCaption="Monthly Calendar All"
>> primaryKey="time_dimension_id">
>> <Table name="sn_lt_time_dimension">
>> </Table>
>> <Level name="Year" column="the_year" type="Integer" uniqueMembers="true"
>> levelType="TimeYears" hideMemberIf="Never">
>> </Level>
>> <Level name="Quarter" column="the_quarter" type="String"
>> uniqueMembers="false" levelType="TimeQuarters" hideMemberIf="Never">
>> </Level>
>> <Level name="Month" column="the_month" nameColumn="the_month_name_short"
>> type="String" uniqueMembers="false" levelType="TimeMonths"
>> hideMemberIf="Never">
>> </Level>
>> <Level name="Day" column="the_day_of_the_month" type="Numeric"
>> uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never">
>> </Level>
>> </Hierarchy>
>> <Hierarchy name="Weekly Calendar" hasAll="true" allMemberName="Weekly
>> Calendar All" allMemberCaption="Weekly Calendar All"
>> primaryKey="time_dimension_id">
>> <Table name="sn_lt_time_dimension">
>> </Table>
>> <Level name="Year" column="the_year" type="Integer" uniqueMembers="true"
>> levelType="TimeYears" hideMemberIf="Never">
>> </Level>
>> <Level name="Week" column="the_week_of_the_year" type="Integer"
>> uniqueMembers="false" levelType="TimeWeeks" hideMemberIf="Never">
>> </Level>
>> <Level name="Day" column="the_day_of_the_week"
>> nameColumn="the_day_of_the_week_name_short" type="String"
>> uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never">
>> </Level>
>> </Hierarchy>
>> </Dimension>
>>
>>
>> The problem is, that when I drill down the weekly calender hierarchy to
>> the weekday level, I get an error, because for some reason  Mondrian
>> uses `sn_lt_time_dimension`.`the_day_of_the_week_name_short` instead of
>> `sn_lt_time_dimension_1`.`the_day_of_the_week_name_short`:
>>
>> select
>>     `sn_lt_time_dimension_1`.`the_day_of_the_week` as `c0`,
>>     `sn_lt_time_dimension`.`the_day_of_the_week_name_short` as `c1`
>> from
>>     `sn_lt_time_dimension` as `sn_lt_time_dimension_1`,
>>     `sn_user` as `sn_user`
>> where
>>     `sn_user`.`registration_date_id` =
>> `sn_lt_time_dimension_1`.`time_dimension_id` and
>>     `sn_lt_time_dimension_1`.`the_year` = 2009 and
>>     `sn_lt_time_dimension_1`.`the_week_of_the_year` = 42
>> group by
>>     `sn_lt_time_dimension_1`.`the_day_of_the_week`,
>>     `sn_lt_time_dimension`.`the_day_of_the_week_name_short`
>> order by
>>     ISNULL(`sn_lt_time_dimension_1`.`the_day_of_the_week`),
>> `sn_lt_time_dimension_1`.`the_day_of_the_week` ASC
>>
>> I would appreciate if somebody could tell me what I did wrong. I was
>> first wondering if I had to use the "usage_prefix", but this can only be
>> set for the dimension itself, not the hierarchy and Julian also mentions
>> this in his documentation: "It can be the case that a
>> "level_column_name", the name of a dimension's level column, is the same
>> for more than one dimension". So I am a bit lost now.
>>
>> Thanks,
>> Diddy
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> _______________________________________________
>> Mondrian mailing list
>> Mondrian at pentaho.org
>> http://lists.pentaho.org/mailman/listinfo/mondrian
>>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20091016/c1cac5b7/attachment.html 


More information about the Mondrian mailing list