Build Performance

[jdaems]

Dear sir/madam,

What steps can I take to improve the performance of my cube build?

Currently the build takes about 2 hours for one cube. the problem is that we need to have that same cube available in two languages. So I have created two different models that build two cubes. So only after 4 hours of building cubes I can make the cubes available to all users no matter the language they speak. I need to reduce this time.

I have already taken the following steps:

- In cer5.ini I have done the following
      WriteCacheSize=32768  (Replace value by : 65536)
      ReadCacheSize=4096 (Replace value by: 32768)
      SORTMEMORY=512 (Replace value by: 25600)

- In COGDMOR.ini I have done the following:
      Fetch Buffer Size=2048 (Replace value by: 32767)
      Fetch Number of Rows=10 (Replace value by: 100)

- On every IQD I have put on the checkbox of multiprocessing. i have check the explain plans of every IQD. The worst one gives me a cost of 1200. This is the one retrieving the facts. It gives me back 5 million records.

- I have disabled the virusscanner on the cognos directories

- There is a lot of free space on all hard disks

Are there any other steps I can take to improve the performance of the build?

In the IQD that retrives the facts I would like to make the remark that it also retrives a lot of zeroes: the fact table contains a componentid and a value. the componentid tells you what KPI the fact record is dealing about. This can be revenue, gross margin or any other KPI. The IQD retrieving the facts gives me back a result simular to this one:

region - customer - revenue - gross margin - number of phone calls
region1 - customer1 - 125 - 0 - 0
region1 - customer1 - 0 - 587 - 0
region1 - customer1 - 0 - 0 - 21

So it retrieves a lot of zeroes. I cna use a group by, but this will make the SQL statement slower, doesn't it? Or is there a better way to do this?

Thank you all in advance.

Kind regards
J.

[rockytopmark]

I'd group it.  You'll have a little more upfront query processing, but then way less rows for Transformer to process.  In the end it will probably be a resounding win.

[Darek]

What are the timings of each query? How much time does pass between opening a query, starting to receive data and reading the last record for each query? What kind of hardware are you building this on? How big does the cube get?