Building Fact Table and doing lookup on Time Dimension
-
Our source data has time specified as number of seconds since 1/1/1970. The conversion from this number to date is accomplished with the following SQL:
select dateadd(second, txTime, '01/01/1970') as 'date' from <source_table>
(I hate the use of 'date' for a column name; but it predates me... pun intended).
Because I have two time fields in the same record that must have rows in the time_dim table, it is repeated for the second field. Then prior to building the fact table there is a string of lookups that each add the appropriate foreign key.
The current SQL for the dates is:
select time_key_id, date as txTime, date as orgTxTime from time_dim
The problem is that I cannot map an input column to a reference column... it complains of incompatible column data types. If I say to use a table instead of a query it automatically maps one column - but still gives the error.
I have already done the data conversion and I'm pretty sure of what I am mapping; but I cannot seem to get it. Anyone (Kirk, Jamie) have any ideas? Sure looks like a bug to me; but I'm always willing to accept operator error if I can get the job done.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply