[OBIEE EMG] Hour-Minute Time Dimension

[Andriy Yakushyn]

Good morning,

I wanted to bring a topic of "real" time dimension (being minute hour
and second). I remember reading that Jeff said that this was one of
the times to break dimensional rules and use a timestamp column.
Apparently, there're several scenarios:

a) Common time dimension - days, hours, minutes, seconds - not very
practical at all - 946,080,000 values for a 30-years worth of data
- or only about 15 768 000 if you don't need seconds

b) separate dim table for date and separate dim table for time - it
might be tricky to use time series functions - some prompts and
filters would need to get tweaked

c) creating a common view based on b) - however, it's not really
changing much...

d) using timestamp - negativity is that one looses advantage of time-
series functions and needs to use a lot of TIMESTAMPDIFF and
TIMESTAMPADD

e) using b AND d - slightly redundant, but will offer greater
reporting flexibility - since not all reports need to utilize
timestamp data

f) creating a semi-dynamic, self-populating a) dimension, in case
there's not much recent data...this could be an option for tactical
reporting systems

Please let me know if there're other viable alternatives to this
issue.

Andriy

[Jeff McQuigg]

The relationship between day and time is M:M. This calls for putting
them in separate dimensions if you need to do such a thing. Of course
the first option is not to do so unless you specifically need to track
time in a dimension, which commonly means you are doing hour of day
analysis across days. If you don't need to do so then use a regular
date attribute (not a timestamp - not sure where you read that).

Jeff M.

[Andriy Yakushyn]

Jeff,

I was referring to this thread -
http://siebel.ittoolbox.com/groups/technical-functional/siebel-analytics-l/obieeanswers-to-create-a-new-column-to-combine-date-time-wsearch-capability-2592758

I'm sorry if I didn't communicate in a more clear way or
misrepresented your words.

option b) is actually M:M as you've described it.

I guess there're 2 possible scenarios:
a) reporting across days AND time (such as from 1/1/2010 15:34:29 to
1/2/2010 10:42:01 ) - most difficult scenario in my case
b) reporting across HMS for 1 individual day (1/1/2010 12:30:00 to
1/1/2010 14:23:00 ) - easier since it's possible to build two separate
dim hierarchies for time and date

AY




...

[Jeff McQuigg]

My recommendation is consistent across this post and that one. For
his scenario, he needed just a datetime attribute. But given other
needs that may not be the best approach.

For your option a) below I think just a date attribute will suffice -
no need for a physical dimension to hold Day X Time.

Jeff M.

On May 19, 9:22 am, Andriy Yakushyn <andriy.yakus...@gmail.com> wrote:
> Jeff,
>

> I was referring to this thread -http://siebel.ittoolbox.com/groups/technical-functional/siebel-analyt...