ETL Fact Load in SSIS

View previous topic View next topic Go down

ETL Fact Load in SSIS

Post  piyushtamaskar21 on Fri Jun 13, 2014 7:42 am

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 !

piyushtamaskar21

Posts : 2
Join date : 2014-03-16

View user profile

Back to top Go down

Re: ETL Fact Load in SSIS

Post  nathanjones77 on Fri Jun 13, 2014 8:53 am

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

nathanjones77

Posts : 11
Join date : 2014-06-03
Location : Zurich

View user profile

Back to top Go down

Re: ETL Fact Load in SSIS

Post  vastonsmith on Tue Apr 28, 2015 1:29 am

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.

vastonsmith

Posts : 1
Join date : 2015-04-28

View user profile

Back to top Go down

Re: ETL Fact Load in SSIS

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum