How to increase Fact table data loading performance

Support Queries Astera DW Builder
1

I’ve designed a model consisting of a Fact table which is supposed to process millions of records in the future. I’m not if Astera Data Warehouse Builder can handle and process this load? As per my knowledge, I would expect this to be a prolonged and resource-intensive process as the data will first be read from the source tables, transformed (retrieving all the applicable surrogate keys from the slowly changing dimensions), and then loaded onto the fact table.

Consider the source database is on SQL Server, and the data warehouse database is on Oracle. This is another thing I am more worried about. In my case, I’ll be having thousands of records moving through ETL one by one from SQL Server to Oracle, retrieving the surrogate keys based on slowly changing dimensions before the final data is loaded into the Fact table.

If this is the case, do you have any optimizations in place?

2

The loading of fact table is always time consuming precisely because of the problem you described above. In Astera DWBuilder, we have managed to overcome this issue using the staging table.

The first thing you need to do is move all the relevant records from source tables to the staging table created on the data warehouse. In your case, the source data will be transferred from SQL Server to the Oracle database.

Next, apply all the transformations on the target server database (Oracle database, in this case) rather than moving back and forth through the ETL process. In other words, we do this the ELT way rather than the ETL way.

Once done, create a single pushdown query to create the staging table, move all the source records there, perform a lookup for the surrogate keys based on slowly changing dimensions, and insert all the data to the Final Fact table.