I should preface this with the fact that I'm a .net developer who has fallen into DBA and performance work because it has become business critical, so I'm missing the DBA skills for this kind of work. Please assume that some of the basic tenets of OLAP+OLTP are things I am unfamiliar with.
In my organization there is a concept of "Consolidating" the OLTP data to a series of tables prefixed by conso which are essentially denormalized structures (the OLAP, I think). This occurs overnight and the jobs which perform the work are extremely slow, on the order of several hours. They also do not scale well as we add more customers the processing time has shot up.
There is also parameters to these consolidation jobs that cause them to run in a smaller context, and this is triggered when a customer updates the data for a particular metric. That is generally pretty fast.
Unfortunately, the night time consolidation jobs are causing deadlocks for some customers and other issues that make the process extremely problematic. We might wake up to alarm bells because the consolidation worked for 1/2 of the customers and not the rest.
What can be done to avoid this issue? Is there a best practice for running OLTP=>OLAP transactions? Determining the source of the deadlocks are outside of my experience and I did not write the consolidation jobs. I would leverage the person who wrote the original jobs, but they are not competent or this wouldn't be happening in the first place (I assume).