Same table in source and target

[glenpark]

Well I'm back again =D

So I have a conceptual question...

I've been given a source and target for a mapping that involve the same table. The source is actually a view made up of columns from two tables, one of which is also my target table.

My question is regarding whether there's any danger to loading a table that is both the target and a part of the source? My worry is coordination between the reads and writes of that table.

The target table is a type 2 slowly changing dimension. All tables are SQL Server.

Thanks

 

[ArtieChoke]

We do it all the time, but not quite the way you're doing it. We use the source table and check for changes and apply them to the same table as the target. Shouldn't be a problem.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[glenpark]

Hmm...maybe I'll try it then. The view also contains the primary key from the target table. Sometimes it's null because the row doesn't exist in the target table yet. Perhaps I can use that as an easy indicator for an insert or update.

How are you checking for changes? Via lookup against the target object?

 

[blom0344]

Standard documentation gives you the exact details on how to set up the mapping. Basically you cache the target to a lookup and perform an evaluation on all ports/fields.

Use a filter to avoid processing rows that are unchanged (which should be the vast majority with slowly changing dimensions)

Ties Blom

 

[glenpark]

Thanks, I'll give it a try. The only exception looks like will be switching to a dynamic lookup as I may be confronted with a historical load of that table.

 

[jglatstein]

Has anyone had the issue where not all of the available database tables show up for selection within the informatica tool?

thanks

 

[blom0344]

Usually a question of selecting the 'all' button.
The default setting is that a list is shown for those tables that are in the schema that matches the authorization id. Select 'all' and you see all objects listed.

Ties Blom