DSXchange

Greetings all-

I'm looking for advice on how to approach a project I'm just starting to work on.

The project requires that I have two tables, the first table will contain transactional records, the second table will contain summarized records from the transaction table. Once I have the records in the summary table, the requirements also stipulate that for each distinct combination of key values from a record in the summary table, I need to provide 42 metrics or counts of the corresponding transactional records. I need to calculate counts which reflect how many records exist in the transactional table for the month, prior month, year-to-date, rolling twelve months and so forth.

If I weren't using DataStage, I'd most likely write a Stored Procedure to create a recordset of the summary records, and loop over the records to obtain each of the counts from the transactionial table, after all the metrics have been calculated, update the 42 columns in the summary table and move on to the next record in the recordset.

Is this even possible in DataStage? If so, how would I go about this?

You could do the same, if you like, via the Stored Procedure stage. Or via the Aggregator stage (perhaps multiple), though I daresay it may not be all that speedy unless your data is presorted per grouping column(s). Or I would assume that the 'aggregation' could be done using straight sql in your source query, then you'd just need some way to put together these 42 metrics.

Another approach is to write the "keys" (grouping columns) into the summary table, setting the measures to null, zero or some other dummy value, then to update it with the measures in a separate DataStage job.