INFOMART - Time Dimensions

[gustav]

Has anyone tried to create interval reports using the TIME_OF_DAY dimension in GIM?  It has a record for every minute on the day, however, there is no reporting label column like the DATE_TIME table has.  Ie. I want a report off the Interaction_Segment table using TIME_OF_DAY to look like:

Interval Label    Total
12:00 - 12:30      150
12:30 - 01:00      204
01:00 - 01:30      178

etc...

[tony]

I think you need to be looking for Primary and Foreign Keys within the Tables you want to extract the data from...? Interaction_Segment contains many different permutations of the date/time key and using the TIME_OF_DAY Table as the reference point should mean you have a single Primary>Foreign Key available to both.

Which Fields in which Tables are you referring to, exactly?

Tony

[gustav]

If you look at the DATE_TIME dimension table it breaks down intervals.  If you look at the TIME_OF_DAY dimension (which interaction_segment_fact uses)  It has numeric intervals, but not english labels that can be used for a final report.  It would be easy to create a custom extension table for this purpose, but i dont see the same labels that exist in the DATE_TIME table:

ie. DATE_TIME table, LABEL_YYY_MM_DD_HH24_30INT column.  Im using the DATE_TIME table as a dimension of MEDIATION_SEGMENT_FACT, but i cant link this dimension to INTERACTION_SEGMENT_FACT (no surrogate key).

[tony]

Are you sure you are looking at Infomart Tables - and not ICON Tables...?

[gustav]

Yes it is the InfoMart schema (7.6)

[eugene]

Gustav, I think the date_time dimension table is only useful for the 1st & 2nd generation aggregated tables.  You should really be building your final report off of those anyways. 

If you're using the interaction_segment_fact table then I assume you're doing your own sql to aggregate??  How about creating your own date time label column by applying some logic against the GMT_START_TIME or GMT_END_TIME (depending on the rules you use).

[tony]

Good point eugene...  I was a little confused as to why someone would want to use an external Darte/Time Table to reference as a look-up when Date/Times are available (in quite a few different formats) within the Interaction_Segment_Fact Table itself...

Tony

[gustav]

Thanks, Im not using the AGG tables because they are too limited (no custom dimensions, missing metrics, and metrics calculated differently than we need them).

As for the data/time dimension - I want to be consistent with a dimensional model using surrogate keys to a time dim table vs, leverage the database time/date conversion processing at run time which would just not work with the amound of data we are using. 

I dug up some information on the GIM user guide which explicitly shows these dimensions used on the base tables - I have been able to use them successsfully, but I may just try to build out my own conformed date/time dim.