Self-Join in Informatica
i have a simple fact table, and would like to create an ETL that creates rolling aggregates... meaning for any given row, i want to sum a certain column for the previous 4 occurrences along the time dimension. for ease, i've created an SDE load that brings in the operable data dimension into the fact staging table. so my table looks like:
product_key,
period_key,
dollars
i want to output the kind of result given by this SQL statement:
SELECT
a.product_key
,a.period_key
,a.dollars
,sum(b.dollars) as dollars_4period_aggregate
FROM my_table a
LEFT OUTER JOIN mytable b ON
(b.product_key = a.product_key
AND b.period_no > a.period_no - 4
AND b.period_no <= a.period_no)
... which gives me the current period dollars in one column and a rolling dollars aggregate of the last four periods. quite simple really, but i just can't figure out how to do this in informatica powercenter.
i've tried to simply use a source qualifier and override the SQL, but it gives me unmapped columns since i don't have a source for the SUM column. do i need add an alias for the source table? i've thought about using the aggregator transformation, but I can't seem to be able to get that either.
any hints?