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.

    I have taken about five different approaches... including using a union and only processing one column - nothing I do lets me map either the original input column with the function on it, or a "conversion column" (i.e., copy of ___).

    Sure would appreciate a hand here.  Thanks!


    Cheers,

    david russell

  • This was removed by the editor as SPAM

  • And as it turned out this one was operator error.  The datatypes were inconsistent.  I had to add the original field with "dateadd" statement in the SQL as a new field so that it was in my data flow to use for the lookup of the time_dim record.

    data flows are not at all easy to edit, and the package/solution got so locked up that Visual Studio couldn't even be stopped from task manager until the fourth try...

    Granted, the mistake was mine; but the tool is so hard to use that it is rapidly becoming something to avoid.  and all the help I got here... well, I'm quite sure that MS would like me to just go away.  They refused to respond to a list of problems we've had with their tool and told me to post my issues/problems here, in this forum, and they will be addressed one at the time - yet they don't get addressed.  Occasionally there is a defense of their product; but very little help.

    When a product does the job it is supposed to do, and does it well, it becomes a commodity and competitive products are also produced by others.  When the product doesn't work well; but does a half-reasonable job, the vendor has a captive market and can make feeble attempts to help the user - and given enough time, eventually create a good product.

    It strikes me that we are being held captive... for now.


    Cheers,

    david russell

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply