I have a server with sql2008 and cognos 8 transformer on it when building a cube the cube takes 2 times longer to build. when I move the database to another server and build the cube it takes 1/2 the time. I looks like just some query takes longer. We have alway installed transformer and sql on same box without a problem.
IMO, if the same query executes faster when the DB is located on a different box, then the query itself is not likely to be the problem.
The problem is likely the server resources that are used up by Transformer. The DB itself will require some resources and unless these resources are freed up the response to the query will be in queue, and Transformer will keep waiting for it
Transformer takes up an awful lot of memory and CPU cycles during the category creation, metadata update and partitioning processes...you might want to consider having a Sys Admin monitor resources during cube build to see if that leaves sufficient resources for the DB to use or no.