[Mondrian] Add hour level to Time dimension

Mathias Bogaert m.bogaert at memenco.com
Wed Jul 22 03:49:20 EDT 2009


FYI here are my Dimension definitions:

<Dimension name="Time" type="TimeDimension">
        <Hierarchy hasAll="true" allMemberName="All Periods" primaryKey="id">
            <Table name="time_dimension"/>
            <Level name="Year" column="the_year" type="Numeric"
uniqueMembers="true" levelType="TimeYears"/>
            <Level name="Month" column="month_of_year" type="Numeric"
uniqueMembers="false" levelType="TimeMonths"/>
            <Level name="Day" column="day_of_month" type="Numeric"
uniqueMembers="false" levelType="TimeDays"/>
        </Hierarchy>
        <!-- [Time.Quarterly].[2008].[Q3] -->
        <Hierarchy name="Quarterly" hasAll="true" allMemberName="All
Quarters" primaryKey="id">
            <Table name="time_dimension"/>
            <Level name="Year" column="the_year" type="Numeric"
uniqueMembers="true" levelType="TimeYears"/>
            <Level name="Quarter" column="quarter"
uniqueMembers="false" levelType="TimeQuarters"/>
            <Level name="Month" column="month_of_year" type="Numeric"
uniqueMembers="false" levelType="TimeMonths"/>
            <Level name="Day" column="day_of_month" type="Numeric"
uniqueMembers="false" levelType="TimeDays"/>
        </Hierarchy>
        <!-- [Time.Weekly].[2008].[44] -->
        <Hierarchy name="Weekly" hasAll="true" allMemberName="All
Weeks" primaryKey="id">
            <Table name="time_dimension"/>
            <Level name="Year" column="the_year" type="Numeric"
uniqueMembers="true" levelType="TimeYears"/>
            <Level name="Week" column="week_of_year" type="Numeric"
uniqueMembers="false" levelType="TimeWeeks"/>
            <Level name="Day" column="day_of_week" type="Numeric"
uniqueMembers="false" levelType="TimeDays"/>
        </Hierarchy>
    </Dimension>

    <Dimension name="TimeHour">
        <Hierarchy hasAll="true" allMemberName="All Hours" primaryKey="id">
            <Table name="time_hour_dimension"/>
            <Level name="Year" column="the_year" type="Numeric"
uniqueMembers="true" />
            <Level name="Month" column="month_of_year" type="Numeric"
uniqueMembers="false" />
            <Level name="Day" column="day_of_month" type="Numeric"
uniqueMembers="false" />
            <Level name="Hour" column="hour_of_day" type="Numeric"
uniqueMembers="false" />
        </Hierarchy>
    </Dimension>

Then, in the cube define them like this:

        <DimensionUsage source="Time" name="Time"
foreignKey="fk_time_dimension"/>
        <DimensionUsage source="TimeHour" name="TimeHour"
foreignKey="fk_time_hour_dimension"/>

Enjoy!

Mathias

On Tue, Jul 21, 2009 at 11:56 PM, Julian Hyde<jhyde at pentaho.com> wrote:
> By the way, I added level types Hours, Minutes, Seconds (also HalfTear and
> Unspecified) in change 12749; I also added to the olap4j API. Will be in the
> next mondrian release.
>
> But I agree with the wizards on this thread: level types don't give you
> much, and are no substitute for good schema design.
>
> Julian
>
> ________________________________
> From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On
> Behalf Of Nicholas Goodman
> Sent: Tuesday, July 21, 2009 9:53 AM
> To: Mondrian developer mailing list
> Subject: Re: [Mondrian] Add hour level to Time dimension
>
> +1.  I've always split out time of day as well.
>
> Really the only reason to use the special Mondrian time dimension
> configuration is to be able to do "YTD()" and other special time MDX
> functions.  I don't think there's any HTD() (hour to date) or other
> shortcuts that would benefit you.
>
> Is there some reason, other than your performance requirement, that you'd
> like it part of the same time dimension?
>
>
> On Tue, Jul 21, 2009 at 5:52 AM, Pedro Alves <pmgalves at gmail.com> wrote:
>>
>>
>>> Considering the performance , my solution is to separate the time
>>> dimension(which i think is not good, cause I cant drill down through
>
>
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
>
>




More information about the Mondrian mailing list