Forum Discussion
How to create a calendar table with second granularity?
In the Transaction table does the column you're creating the relationship on have timestamps as well? If not then you won't get any matches because the Dates aren't lining up to the Timestamps.
Generally speaking in data modeling we keep our Dates and Times separate. First, it cuts down on data volumes (only 86400 rows in the Time table and 365/366 rows per year in the Date table).
I would try that - separate your Date and Time dimension tables and separate your Dates and Times in the transaction table. Still gives you the capability to drill-down to the second level without super-sizing your model.
Anonymous
Yes, both tables have timestamps, that's why I don't understand why the relationship doesn't work.
Thanks for the tip, but what if you want to have continuous visuals? Eg: a line chart for a certain week where you may scroll horizontally to the second?
My guess is that the matching on the timestamps simply isn't lining up (although it should - datetimes are stored as numbers under-the-sheets so I'm surprised it's not working). I'd try the "resolve-it-to-yyyymmddhhmmss-format-and-join-on-that" approach.
As far as creating visualizations (if you have separate date and time dimensions) you could simply stack your date fields on the axis shelf and then stack your time fields underneath them and the drill-down/expand-down should work with no issues.