Folks,
We are facing a major issue with the cube creation asking for a large Temp Space from the Oracle DB and I was
wondering if there is anything we can do in Transformer or Cognos that would acquire less Oracle Temp Space?
I have broken done the FM Model that creates the Package for the Transformer to smaller portions (process the data in quarterly basis
and not the entire year? But if there is any parameters can be setup for using less of a Oracle DB Temp Space, it'll be a GREAT
HELP.
We have a deployment scheduled for this weekend so any help at all would be very much appreciated.
Thank you in advance for your time.
The cube creation process is going to run each of the queries you have defined in the Transformer model. So the first place to look is there.
What do you consider large? Are you getting pushback from DBAs that your temp space requirements are too large?
Yeap! I stripped out the Query as much as I could but the DBAs are just not understanding it.
If I break down the Query to Quarterly so the data pulled would be based on QTRs, (not a year or six months), would it
cause the Transformer to require less space?
The exact process Transformer uses to build the cubes is a bit opaque to me. That said, I have observed the progress messages and looked through the logs enough that I think I know how it works. It appears each query subject runs and the result set is spooled to local disk. From there it figures out the categories based on your model, and all the intersections with the facts, and builds the cube file. The main thing for DB temp space, if I am correct, is that the queries each run one time against the database. So the result set of the individual queries is what matters to your DBAs.
So to answer your question, assuming I am correct, going quarterly would reduce the result set and should use less database temp space.
YES. The breakdown of Sub. Query to smaller chunks did the trick.
Thank you for the explanations. :)