Best practice for ELT: moving data from flat staging table to fact table?

Database
Enthusiast

Best practice for ELT: moving data from flat staging table to fact table?

I have figured out how to make the BTEQ work to carve up my import spreadsheet to load a fact table, but it will require me to wrap the BTEQ script in some other language (java or python) with some fancy SED code to parameterize the BTEQ script with the column names I need to loop through to INSERT all the spreadsheet data into my cube.

This does not follow the ELT best practice that is documented in the TPT ref guide that suggests using staging table and the power of SQL to load up the production tables!

Hoping you can help me understand how I can write this Oracle PL/SQL like code in Teradata:

For inx in

(

select columnname

from dbc.columns

where databasename='duema'

and tablename='spend'

and columnname like 'm%'

and columnname <> 'MAX_TTP' ;

) loop

insert into duema.kpi_dashboard

(

measure_name,

month_name,

account_count

)

Values

(

(select variable, text(inx.columnname), inx.columnname from duema.spend)

)

 ;

The first use of the parameter is used as a text value for the month_name and the second use is to pull the field value from the duema.spend tables 30 columns of monthly values.

Hoping you can help teach an Oracle dog a few Teradata tips!

Best regards,

mark