Best approach for generating table's each column value from SQL query.

[Anonymous]

I have 4 SQL tables named template, dates, calc, company and option.
Option is a target table and rest are source tables.

Tabe Structure:

Template table holds calc_id(calc) for each column.
Example:

   id    COLUMNA        COLUMNB    ......................    COLUMNZ 
   1        9              119                                   5

Calc table holds SQL for each calc_id
Example:   

calc_id                      SQL
 9                         Select sum(abc) from xyz
 119                       Select count(def) from xyz where id is not null

Company table holds unique companies .
Example: 

company_id        company
      1            AABB

Dates table holds date ranges
Example:

date_range_from          date_range_to        date_indicator
 01-01-2016                31-12-2016           Y
 01-01-2016                31-01-2016           M  

     
                   
Option table holds cross join of source tables except for calc table
Example:

 id   comapny_id   date_range_from     date_range_to       columnA      columnB    ColumnC
 1     AABB              01-01-2916    31-01-2016           9             119       125

Option table INNER JOIN Calc Table Output
  Example:

id   comapny_id   date_range_from     date_range_to         columnA                    columnB        ColumnC
1     AABB          01-01-2916        31-01-2016          Select sum(abc) from xyz    SQL Statement  SQL Statement

 

Now  ColumnA value=

Select sum(abc) from xyz where date_range_from >='01-01-2916' and  date_range_to<= '31-01-2016 ' where company_name='AABB'.

I have created ETL for this, check snapshots below.
Problem with this, its taking hours to finish and template and company table is growing very fast.
Is there any component available somewhat like tMap and runs SQL to generate value for each column.
What's the best job design for such kind of requirement?

[Anonymous]

Hi,

Could you please also post your whole work flow screenshots on forum? Have you tried to use t<DB>row component to execute a sql query?

Best regards

Sabrina

[Anonymous]

Hi Sabrina,
Below is the job flow

As company and dates table will grow this job will take days to complete.
The better approach for this scenario will be?

Thanks 
Gulshan