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.
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.
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?