Transformation Tbl Advice Needed ASAP

[andi36]

When a user pulls up say Jan. 2001, they need to view the pmt date 12 months from that date (Jan. 2002) AND the svc date 18 months out from Jan. 2001.

What would you use, various transformation tables, one table and different views or some other solution.

Forgive me, I know I'm leaving out pertinent information, but I'm rushed right now. This problem has been vexing us for weeks. Our DBA is swamped, ETL is swamped but we don't want to make this hard on the user.

Thanks so much - in advance!

 

[reisw]

If you are creating metrics then you could apply transformations to those metrics to get the values from those dates. You could do it with just one transformation table that has three columns. One for the date (in this case Jan 2001) another one for the same date last year (Jan 2002) and another for 18 months ago (June 2000?) Since it sounds like you are under a time crunch you could also build the transformations without using a table as well. Just put some logic into the transformation to figure out what the correct date would be. For example we have a transformation that gives us the same month last year. All we do is take our month id (200201) and subtract 100 from it to get 200101. Hope this helps.