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…
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
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).
Are you sure you are looking at Infomart Tables - and not ICON Tables…?
Yes it is the InfoMart schema (7.6)
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).
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
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.