How to best create numerous individualized reports in DI Studio?

[EinarRoed]

I'd greatly appreciate some advice on one of my first DI Studio projects.

I have a SAS data set that contains data on various types of alerts about customer subscriptions, such as whether the subscription is close to expiring, whether the subscription fee is overdue, etc. The data set also contains basic information about the customer associated with the alert, and about the consultant responsible for the customer. I need to extract the most relevant data from the data set, and ensure that each consultant receives a report listing the subscription alerts of the customer(s) that he/she is responsible for. In example, the report might just list the customer name, customer number, and alert description, of each alert.

The reports are supposed to be emailed out to the individual consultants based on their Consultant ID, but I'll worry about the email part later. For now I'd just appreciate some suggestions on how I might best create a report/list of alerts for each consultant in DI Studio. Do you think the most effective way would be to create a control table containing all the distinct Consultant IDs and loop an individual report/list-creating job for each of them?

I'm mainly asking because I'm still new to this and don't have a good grasp on how to handle parameters & iterations yet, and for all I know there's good alternative approaches.

I'd also appreciate advice on what the final report/list-creating transformation should be, assuming there's anything that creates a better list/report than the basic table loader does.

Thanks for your time.

[Patrick · Accepted solution]

I take the approach with DIS that I use standard transformations where they help me doing my job better and faster or where I believe by using a standard transformation job maintenance will get easier and more consistent. But I try not to overdo it and if something gets too complicated using standard transformations then I take the freedom to implement using user written code.

What I always do is to register all tables I’m using (impact analysis) and also generate the macro variables (user written body). I then use &_input and &_output and eventually also some of the other macro variables generated.

I possibly would approach your task by first code in EG or PC SAS some code which selects the data for one consultant, then generates the report and sends it via email

.
Once that’s done I'd parameterise the job (macro variables) which would be the consultant/email address and the data selection(some parameter in a where clause) and the name of the report (report_for_&consultant could be an option). Still in EG I would pass some values to this macro variable and unit test it.

Only now I would start with the DIS work. One way to go (and I believe you have that already in mind) is a loop job.

I would start with the inner job. Register the table with the alerts and drop it on your job, add a user written node and add your code (the one with parameters) for selecting the data for a specific consultant creating a report, add a second user written node sending the email. (There are also options using status handling (part of job property) to send emails or execute custom code – but I personally don’t like to use most of this stuff as I like to open a job flow and to see everything essential what’s happening in the flow at once.)

You then create an outer job with a control table (and eventually some transformation populating this control table). This control table has all the columns and values which you want to pass as parameter values to your inner job. You then use a loop transformation and chuck in your inner job.

The only thing missing now is to link the variables in your control table to the inner job. You first need to define parameters for your inner job (in the job properties of the inner job as prompts). The name of these prompts must be the macro variables you are using in the code in your inner job. After that you need go to the loop transformation and “link” the variables in your control table to these prompts – and of course you define whether you want to run your stuff in parallel or in sequence. That’s it basically.