One OLTP table named SHOW_TIMES contains 2/two date columns: SHOW_START_TIME and SHOW_END_TIME.
In Framework Manager 8.3, I select TOOLS-TEST: this table shows DATE & TIME values for all records (i.e. "YY-MM-DD HH:MM:SS AM/PM") in SHOW_START_TIME and SHOW_END_TIME columns.
Using Cognos 8.3 Transformer, I created a cube that uses a package containing several of these OLTP tables as data source.
I create a time dimension named SHOW_START_TIME with the date wizard. I generate the categories for START_TIME level, click SHOW DIAGRAM and view the categories : all categories show the date without the time part (hour:minute:second).
I select the "START_TIME" level and click the TIME tab to set the date function to "Day". I click on "Modify format" to enter a date template to match the one in my OLTP table: I get an error message "INVALID DATE FORMAT".
How can I format the "START_TIME" level to display the hour portion of a date?
Ask yourself this question... if you are speaking in the terms of a Day, how are you expecting to pinpoint a specific hour?
Cubes are not transactional... they are dimensional.
You may nee to expand your Dimension's hierarchy to include the level(s) desired, below Day, since there are many hours in a single day, for example.
This is the essence of a Hierarchy!!!
Year
Quarter
Month
Week
Day
Hour <--- add this if you need it.
Minute?
Second?!?!?!?!?
I'm actually trying to do this same thing in Transformer (essentially add a level to a time dimension below the day level to represent hour) and can't find a way to do so. When I click Insert Level and then click the Time tab, the Date Function drop down list only gives me the Day as the lowest grain level of detail, even though I'm using a datetime field to pull data from. Is there some other method to use to create this Hour level?
Thanks!
You might manually create your time dimensions in your datasource. When I've done time dimensions I've always done it this way, I don't think the transformer wizard will go that far for you. Then just drag this over in transformer.