i have target dimension table DIM_OUTLET which we are loading with TPT on upsert mode.
we have 10 different data sources which are loading data to this dim_outlet.
we want to update this Dimension in every hour, but the bottleneck is that this table get stuck in Mload as more than one source try to load data in this table.
is there any way to avoid such locking or Mload on that table. so that we can run these jobs in parallel.
You're directly loading into the target table?
Are the sources flat files?
Do they arrive at the same time?
What' s the size of the target and the updated percentage?
Maybe switching to STREAM or SQL MERGE
there are some calculations/ lookups involved too, so we have made seperate ETL jobs for them.
each job take around 30 mins to load to various dimensions, most of time i think around 10 mins would be for dim_outlet.
we have a time shift of 10 - 15 min between 10 sources, but sometimes the second job tries to insert into same dimension table which is being loaded already causing job failure.
that why we are looking for parallel load into same table in different jobs.
You can't load the same table with multiple jobs at the same time, the first job to run will get a write lock and the remaining jobs will queue up waiting for the preceding write lock to be released.
In order to achieve what it sounds like you're trying to do, you could load each job into it's own target table, "in parallel", with the calculations etc and then run a final job on TD to complete an ANSI-Merge into the target table