Populate Fact Tables incrementally

Question

Populate Fact Tables incrementally

ashish.mehrotra

Old Hand

Points: 318
More actions
June 16, 2011 at 3:13 am

#242196

Hi All,
I have populated my dim tables from many base tables via joins in SCD. That part is working fine.
But I dont know how to populate my fact table incrementally usind dim tables and base tables through joins.
I searched on internet. It is showing me to use lookup transformations but at all the sites I failed to find any demo.
Plz put fwd ur ideas.
Thanks
Ashish

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

you will need to use lookup transformations to add the correct dim key to the fact table.

and as you have some SCDs on your dimensions you will need to use a range-lookup in order to get the current dimension for the fact.

incremental laod depends on what you have on the source data to identify a changed/new record. such as time stamps, or a incremental ID.

what part are your struggling with?

ashish.mehrotra Old Hand Points: 318 More actions · Answer 2

Thanks 4 reply.

See i am new to this SSIS. Previously i was working on OLAP services 2000 where I truncated my dim and fact tables and then reload the dim and fact with the .

In SSIS i found out how to use SCD to replicate change/new data into my dim tables

But now I have a fact table which I am not aware how to populate with the data. Also dnt want to truncate data anymore. Lookup is the solution but i don't know how to use it.

So if u can provide me any video/link etc so that i can have a idea of how to develop it

steveb. SSC-Forever Points: 46733 More actions · Answer 3

I think you are confusing the issues a little bit here.

for an incremental load you need to have something in the source to define that a record is new or updated, do you have this?

without having this then you need to do a lookup on the fact table from your stage table, and either insert the new records or updated the existing records depending on buiness rules.

for inserts set up a destination for rows that do not have a match.

for updates, you can either use a sql task to write an update statement (slowest but easiest to setup) or put the data into a staging table a do a set based upate statemet.