ETL Fact Load in SSIS

We are well aware with the fact that we always load dimension tables and then fact tables, The tool I am using is SSIS. Ideally we use transaction table and do lookup on dimension and make the entry of that particular row in the destination i.e Fact table. Likewise we load the data in fact table.

What if in our case we don't have transaction table and we still want to load the fact table . What possible approaches should I implement ?

Please Reply


Thank-You !

what are you going to load into the fact table if there isn't a transaction?

This topic pertains to using Postgres foreign tables to assist the ETL process. Postgres has a nice feature for referencing tables in another database - foreign tables. In this case, I'm using the foreign table feature to reference my OLTP tables from within my data warehouse for ETL. The OLTP tables have the usual FKs and PKs. When I create the OLAP (data warehouse) foreign tables that link to the OLTP tables, I can also add FKs and PKs to these foreign tables. My question is a "best practice" question. As usual, the OLTP tables have complex FKs and PKs. Of course, I don't want to duplicate these in the data warehouse. For example, an OLTP "customer" table has about 20 FKs. My customer dimension, built off a foreign table that references the external customer table, has none - only a PK - which I think is one of the goals of the data warehouse. What design considerations should I be aware of in the ETL process? Can I just ETL away on the foreign tables assuming that the underlying data is just fine and not worry about how the foreign tables are keyed (since they are linked to "good" data)? Duplicating the FKs and PKs in the foreign tables doesn't make sense: dual maintenance, etc. Try to explain this in such a way that I may convince my DBA that this is a good practice.