Date matching

I have done this twice now. I am sure I will do it again.

I have a date dimension. Among other things the dimension contains columns for a meaningless id and raw date. If I want to translate a date to a the meaningless id for storage in a fact table, I use a look up component. All fine and dandy until I run the data flow and all of the look up's fail.

Now what could be wrong with that? I look at the redirected rows and for a split second think, what is wrong with that data? Then it hits me. There is time information in the source and not in the date dimension.

Wouldn't it be nice if I could match on just the date! Off I go to put in another data conversion component for the lookup.

I know some of you have written custom components. How hard would that be for me to do?So you need to translate date in the source to raw date which you could then lookup in the lookup table? Can you do it with expression through Derived Column transform? If no, then writing custom component would be right. In C# it's not that difficult, 1 - 2 pages of code in one class. You can find some examples in Books Online.|||Yes, I would have used a derived column as well.

regards,
ash|||

Ash Sharma wrote:

Yes, I would have used a derived column as well.

The only successful way I have found is the following equation

(dt_wstr,2)DAY(dtPeriodStartDate) + "/" + (dt_wstr,2)MONTH(dtPeriodStartDate) + "/" + (dt_wstr,4)YEAR(dtPeriodStartDate)

And setting the result column type to date timestamp.

Lets just say this gets old fast. I am open to better ways.|||Ummm...I'd say this IS the best way. What is the problem that you have with it? Its only 1 extra component after all, and its a whizzy derived column transform as well - it shouldn't cause any perf problems!!

-Jamie|||

Jamie Thomson wrote:

What is the problem that you have with it?

My dislike is the copy paste nature of the work and potential for slight errors.

I actually have a fair number of such translations to do.|||So you'd like to be able to share (i.e. reuse - there's that magic word again )expressions across different places?

I think that's a great idea. Possibly a DCR for betaplace? Ashh/Kirk?

-Jamie|||In CTP 15, the FriendlyExpression property on columns in derived column is settable via dataflow property expressions. So conceivably, you could set this via configurations, or from one string variable that will evaluate to desired expression. Of course, you would need to now configure all the derived column expressions that would recieve this common meta-expression, but at least you would only have to edit the common expression in one place...