Hi,
I have question about 'best' apporach to create ETL process.
Let's suppose that I get SQL query - for example this query comes from eBS system from 'business' person who wrote for me complex query from for example HR area.
And my goal is using this query and show data on BI.
I must create ODI process to load data from this query to our DWH.
And now I have question about best approach to do this?
Maybe someone with more experience explain me steps.
I'm beginner in odi but I see two options:
1. Create VIEW (or materialized view) in SOURCE system (source system or in DWH ??) and in etl process my source table is this VIEW - next create target table (copy source table with attributes and paste - create target) and add this VIEW and target table to ETL process.
2. I created temp table like SRC_HR_VIEW with attrubites from query and in ODI change Extract Option / Custom template and paste comple SQL query. Create target table and paste this two tables in ETL process.
Can anyone explain what is better or which is more used in practice? Or maybe some other approach?