Four Stars

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

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?




 

 

2 REPLIES
Moderator

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

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

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Four Stars

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

Hi Sabrina,
Below is the job flow
Step1.pngstep2.png


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

Thanks 
Gulshan