[Mondrian] Add hour level to Time dimension

Brian Vandenberg phantall at gmail.com
Wed Jul 22 09:12:39 EDT 2009


  Make two dimensions, one is time of day, the other is date.  Trust me,
your life will be simpler if you do.  Not to mention, there will come a time
when you will want to use time and date on separate axes (one on columns,
the other on rows) and you will either need to create another hierarchy for
time of day stuff, or split it out into its own dimension anyway.  Just do
it now and save yourself the heartache.

  In addition to this, you'll also have to use two different tables (date
dimension, time dimension) to handle a date-time dimension hybrid.  If
you're tracking events that are as granular as 1 minute, that's 60 * 60 * 24
* <years> * 365.25 (the 0.25 is for leap years) worth of rows.  If you want
your data warehouse to work for 20 years, that's 631,152,000 rows in JUST
your date/time dimension hybrid.  If you don't separate them into two
separate tables, the fact table <-> datetime joins will be horrendous.  Once
you've split them out, why not just make them two separate dimensions?

-Brian

On Wed, Jul 22, 2009 at 8:24 AM, Lin Chun <franks1984 at gmail.com> wrote:

> Hi
> Thanks,but I don't get it exactly
> Do we seperate the Hour to another dimension or just add it under the level
> of Day?
>
> Re
>
>
>
> On Wed, Jul 22, 2009 at 9:49 AM, Mathias Bogaert <m.bogaert at memenco.com>wrote:
>
>> 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
>> >
>> >
>>
>> _______________________________________________
>> Mondrian mailing list
>> Mondrian at pentaho.org
>> http://lists.pentaho.org/mailman/listinfo/mondrian
>>
>
>
>
> --
> -------------------------
> Lin Chun
> Ted Turner <http://www.brainyquote.com/quotes/authors/t/ted_turner.html> - "Sports is like a war without the killing."
> _______________________________________________
> 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/20090722/c7d7ea42/attachment.html 


More information about the Mondrian mailing list