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.<br>
<br>  In addition to this, you&#39;ll also have to use two different tables (date dimension, time dimension) to handle a date-time dimension hybrid.  If you&#39;re tracking events that are as granular as 1 minute, that&#39;s 60 * 60 * 24 * &lt;years&gt; * 365.25 (the 0.25 is for leap years) worth of rows.  If you want your data warehouse to work for 20 years, that&#39;s 631,152,000 rows in JUST your date/time dimension hybrid.  If you don&#39;t separate them into two separate tables, the fact table &lt;-&gt; datetime joins will be horrendous.  Once you&#39;ve split them out, why not just make them two separate dimensions?<br>
<br>-Brian<br><br><div class="gmail_quote">On Wed, Jul 22, 2009 at 8:24 AM, Lin Chun <span dir="ltr">&lt;<a href="mailto:franks1984@gmail.com">franks1984@gmail.com</a>&gt;</span> wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Hi<br>Thanks,but I don&#39;t get it exactly<br>Do we seperate the Hour to another dimension or just add it under the level of Day?<br><br>Re<div><div></div><div class="h5"><br><br><br><div class="gmail_quote">On Wed, Jul 22, 2009 at 9:49 AM, Mathias Bogaert <span dir="ltr">&lt;<a href="mailto:m.bogaert@memenco.com" target="_blank">m.bogaert@memenco.com</a>&gt;</span> wrote:<br>

<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">FYI here are my Dimension definitions:<br>
<br>
&lt;Dimension name=&quot;Time&quot; type=&quot;TimeDimension&quot;&gt;<br>
        &lt;Hierarchy hasAll=&quot;true&quot; allMemberName=&quot;All Periods&quot; primaryKey=&quot;id&quot;&gt;<br>
            &lt;Table name=&quot;time_dimension&quot;/&gt;<br>
            &lt;Level name=&quot;Year&quot; column=&quot;the_year&quot; type=&quot;Numeric&quot;<br>
uniqueMembers=&quot;true&quot; levelType=&quot;TimeYears&quot;/&gt;<br>
            &lt;Level name=&quot;Month&quot; column=&quot;month_of_year&quot; type=&quot;Numeric&quot;<br>
uniqueMembers=&quot;false&quot; levelType=&quot;TimeMonths&quot;/&gt;<br>
            &lt;Level name=&quot;Day&quot; column=&quot;day_of_month&quot; type=&quot;Numeric&quot;<br>
uniqueMembers=&quot;false&quot; levelType=&quot;TimeDays&quot;/&gt;<br>
        &lt;/Hierarchy&gt;<br>
        &lt;!-- [Time.Quarterly].[2008].[Q3] --&gt;<br>
        &lt;Hierarchy name=&quot;Quarterly&quot; hasAll=&quot;true&quot; allMemberName=&quot;All<br>
Quarters&quot; primaryKey=&quot;id&quot;&gt;<br>
            &lt;Table name=&quot;time_dimension&quot;/&gt;<br>
            &lt;Level name=&quot;Year&quot; column=&quot;the_year&quot; type=&quot;Numeric&quot;<br>
uniqueMembers=&quot;true&quot; levelType=&quot;TimeYears&quot;/&gt;<br>
            &lt;Level name=&quot;Quarter&quot; column=&quot;quarter&quot;<br>
uniqueMembers=&quot;false&quot; levelType=&quot;TimeQuarters&quot;/&gt;<br>
            &lt;Level name=&quot;Month&quot; column=&quot;month_of_year&quot; type=&quot;Numeric&quot;<br>
uniqueMembers=&quot;false&quot; levelType=&quot;TimeMonths&quot;/&gt;<br>
            &lt;Level name=&quot;Day&quot; column=&quot;day_of_month&quot; type=&quot;Numeric&quot;<br>
uniqueMembers=&quot;false&quot; levelType=&quot;TimeDays&quot;/&gt;<br>
        &lt;/Hierarchy&gt;<br>
        &lt;!-- [Time.Weekly].[2008].[44] --&gt;<br>
        &lt;Hierarchy name=&quot;Weekly&quot; hasAll=&quot;true&quot; allMemberName=&quot;All<br>
Weeks&quot; primaryKey=&quot;id&quot;&gt;<br>
            &lt;Table name=&quot;time_dimension&quot;/&gt;<br>
            &lt;Level name=&quot;Year&quot; column=&quot;the_year&quot; type=&quot;Numeric&quot;<br>
uniqueMembers=&quot;true&quot; levelType=&quot;TimeYears&quot;/&gt;<br>
            &lt;Level name=&quot;Week&quot; column=&quot;week_of_year&quot; type=&quot;Numeric&quot;<br>
uniqueMembers=&quot;false&quot; levelType=&quot;TimeWeeks&quot;/&gt;<br>
            &lt;Level name=&quot;Day&quot; column=&quot;day_of_week&quot; type=&quot;Numeric&quot;<br>
uniqueMembers=&quot;false&quot; levelType=&quot;TimeDays&quot;/&gt;<br>
        &lt;/Hierarchy&gt;<br>
    &lt;/Dimension&gt;<br>
<br>
    &lt;Dimension name=&quot;TimeHour&quot;&gt;<br>
        &lt;Hierarchy hasAll=&quot;true&quot; allMemberName=&quot;All Hours&quot; primaryKey=&quot;id&quot;&gt;<br>
            &lt;Table name=&quot;time_hour_dimension&quot;/&gt;<br>
            &lt;Level name=&quot;Year&quot; column=&quot;the_year&quot; type=&quot;Numeric&quot;<br>
uniqueMembers=&quot;true&quot; /&gt;<br>
            &lt;Level name=&quot;Month&quot; column=&quot;month_of_year&quot; type=&quot;Numeric&quot;<br>
uniqueMembers=&quot;false&quot; /&gt;<br>
            &lt;Level name=&quot;Day&quot; column=&quot;day_of_month&quot; type=&quot;Numeric&quot;<br>
uniqueMembers=&quot;false&quot; /&gt;<br>
            &lt;Level name=&quot;Hour&quot; column=&quot;hour_of_day&quot; type=&quot;Numeric&quot;<br>
uniqueMembers=&quot;false&quot; /&gt;<br>
        &lt;/Hierarchy&gt;<br>
    &lt;/Dimension&gt;<br>
<br>
Then, in the cube define them like this:<br>
<br>
        &lt;DimensionUsage source=&quot;Time&quot; name=&quot;Time&quot;<br>
foreignKey=&quot;fk_time_dimension&quot;/&gt;<br>
        &lt;DimensionUsage source=&quot;TimeHour&quot; name=&quot;TimeHour&quot;<br>
foreignKey=&quot;fk_time_hour_dimension&quot;/&gt;<br>
<br>
Enjoy!<br>
<font color="#888888"><br>
Mathias<br>
</font><div><div></div><div><br>
On Tue, Jul 21, 2009 at 11:56 PM, Julian Hyde&lt;<a href="mailto:jhyde@pentaho.com" target="_blank">jhyde@pentaho.com</a>&gt; wrote:<br>
&gt; By the way, I added level types Hours, Minutes, Seconds (also HalfTear and<br>
&gt; Unspecified) in change 12749; I also added to the olap4j API. Will be in the<br>
&gt; next mondrian release.<br>
&gt;<br>
&gt; But I agree with the wizards on this thread: level types don&#39;t give you<br>
&gt; much, and are no substitute for good schema design.<br>
&gt;<br>
&gt; Julian<br>
&gt;<br>
&gt; ________________________________<br>
&gt; From: <a href="mailto:mondrian-bounces@pentaho.org" target="_blank">mondrian-bounces@pentaho.org</a> [mailto:<a href="mailto:mondrian-bounces@pentaho.org" target="_blank">mondrian-bounces@pentaho.org</a>] On<br>
&gt; Behalf Of Nicholas Goodman<br>
&gt; Sent: Tuesday, July 21, 2009 9:53 AM<br>
&gt; To: Mondrian developer mailing list<br>
&gt; Subject: Re: [Mondrian] Add hour level to Time dimension<br>
&gt;<br>
&gt; +1.  I&#39;ve always split out time of day as well.<br>
&gt;<br>
&gt; Really the only reason to use the special Mondrian time dimension<br>
&gt; configuration is to be able to do &quot;YTD()&quot; and other special time MDX<br>
&gt; functions.  I don&#39;t think there&#39;s any HTD() (hour to date) or other<br>
&gt; shortcuts that would benefit you.<br>
&gt;<br>
&gt; Is there some reason, other than your performance requirement, that you&#39;d<br>
&gt; like it part of the same time dimension?<br>
&gt;<br>
&gt;<br>
&gt; On Tue, Jul 21, 2009 at 5:52 AM, Pedro Alves &lt;<a href="mailto:pmgalves@gmail.com" target="_blank">pmgalves@gmail.com</a>&gt; wrote:<br>
&gt;&gt;<br>
&gt;&gt;<br>
&gt;&gt;&gt; Considering the performance , my solution is to separate the time<br>
&gt;&gt;&gt; dimension(which i think is not good, cause I cant drill down through<br>
&gt;<br>
&gt;<br>
</div></div><div><div></div><div>&gt; _______________________________________________<br>
&gt; Mondrian mailing list<br>
&gt; <a href="mailto:Mondrian@pentaho.org" target="_blank">Mondrian@pentaho.org</a><br>
&gt; <a href="http://lists.pentaho.org/mailman/listinfo/mondrian" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br>
&gt;<br>
&gt;<br>
<br>
_______________________________________________<br>
Mondrian mailing list<br>
<a href="mailto:Mondrian@pentaho.org" target="_blank">Mondrian@pentaho.org</a><br>
<a href="http://lists.pentaho.org/mailman/listinfo/mondrian" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br>
</div></div></blockquote></div><br><br clear="all"><br></div></div><font color="#888888">-- <br>-------------------------<br>Lin Chun<br><a href="http://www.brainyquote.com/quotes/authors/t/ted_turner.html" target="_blank">Ted Turner</a>  - &quot;Sports is like a war without the killing.&quot;
</font><br>_______________________________________________<br>
Mondrian mailing list<br>
<a href="mailto:Mondrian@pentaho.org">Mondrian@pentaho.org</a><br>
<a href="http://lists.pentaho.org/mailman/listinfo/mondrian" target="_blank">http://lists.pentaho.org/mailman/listinfo/mondrian</a><br>
<br></blockquote></div><br>