07-01-2011 11:42 AM
Hi all, I'm using DI Studio 4.2
I'm creating a job that prepares data for use in Web Report Studio - the report in WRS consists of a series of bar charts. In some cases the bar height is just a value from the source data and in some cases it is one number divided by another number (i.e. numerator / denominator), sometimes the denominator is the sum of numerators (to give a distribution / percentage) and in some cases the denominator is population data to give a per 100,000 population measure.
My plans are to create 3 seperate parameterised jobs, one for each of the types of charts mentioned above, which will give me (among others) 3 columns of data - numerator, denominator and bar height.
Then I will have a control job which will read in parameters for each chart from excel and loop through the chart jobs appending the data to a single output table (this will be used by Information Map and WRS)
I can sort out the parameterised jobs and I can parameterise the column mappings so that my jobs can be generic and controlled entirely by the parameter spreadsheet, but I'm struggling to work out how to set the control job up so that if the bar chart required is numerator / denominator it will select the appropriate job to use and run only that job. My spreadsheet will contain a parameter that will identify which job should be used for each chart.
If there is no way to only execute certain jobs in this way, is it possible to only execute certain transforms within a job and that this execution is controlled by the data itself? (i.e. of a variable = 1 do these transforms, if the variable = 2 do these transforms or if the variable = 3 do these one)
I suppose I'm trying to do something similar to using the %IF - %THEN %DO / %ELSE %DO functionality of Enterprise Guide, any ideas, suggestions, help will be gratefully received.
07-02-2011 05:46 PM
In DIS 4.21 you can use the loop controls for transformations inside a job, not just for embedded jobs. I suggest to use a splitter that will trigger the loop you want based on data values.
HTH - Jan.
07-05-2011 08:21 AM
With careful use of the %macro and %mend statements in the Precode and Postcode blocks of a transformation. It is perfectly possible to determine an execution order of the transformations and influence the entire control flow of an ETL job.