PowerPlay cube build time

[devcog]

Hi,
We have a cube with the following structure:
No of dimensions: 6
No of Time dimension: 1 (with two levels - Year and Quarter)

There is a dimension with 5 levels and another one with 6 levels while the remaining three have only one level within them.
The dimension with 6 levels has one alternate drill-down path as well.

And the model has 84 measures with around 20 of them having Time state rollup applied while the remaining have Regular rollup.

The cube reads 2.6 million records of data from the source and it takes an hour and 15-30 mins to build the cube.

Please let me know whether a cube of similar structure can be built faster...?

Thanks,

 

[DoubleD]

Having that many measures in a single cube will slow down the build time. Realistically a 75-90 minute build time is not bad. I would suggest evaluating your cube design if it has 84 measures. This tends to create information overload and actually makes the cube less useful because its difficult to get to the info you need.
My suggestion would be to build several cubes out of that MDL file, with only the appropriate measures in each cube.
In that instance, the category generation should only happen once, then the separate cube builds may actually run faster.

Pain is stress leaving the body.

DoubleD

 

[drlex]

In general, alternate drill-downs and measure roll-ups will cause processing time to increase. You don't specify whether the time you state is for read and processing or just processing. It doesn't sound excessive considering the number of records and the model structure.
A faster server/PC will decrease the processing time, as does bus speed and disk access criteria. There are also some transformer cache values you can set to improve performance. Plus a Unix environment is undoubtedly faster than Windows.

However, eighty four measures seems like an attempt at a cognos record. Are you sure you can't separate them out into multiple cubes? I'd be very sceptical that users need all in one cube - it's more likely to put most users off, and thereby negate the benefits of Cognos.

soi la, soi carre

 

[GJParker]

84 ? WOW

listen to DoubleD and drlex, I think you need to build multiple cubes.



Gary Parker
MIS Data Analyst
Manchester, England

 

[john76]

First of all, I agree with all of the above in that 84 seems to be incredibly excessive and most likely should be split among several cubes.

Secondly, I don't believe that the build time you stated is unreasonable, unless you have half a dozen other cubes with similar times that need to be refreshed daily!

And lastly, if you really do need to speed up build times, using multiple data sources such as csv text files that were "pushed" from your DBMS instead of using Cognos catalogs to access the data can speed up build times tremendously.

HTH,
John

 

[DoubleD]

john76,
I agree with you that "pushed" csv files MAY speed up build times. But I would expect that you will only see gains if your DB access time is the majority of that 75-90 minutes. If the DB access time is only 10 minutes, then csv files shouldn't speed up your process at all.

Pain is stress leaving the body.

DoubleD

 

[mayo04]

Good comments but I would also add that I think by looking at your SQL and your model and analysing these could possibly give you great performance results in the case of using "unique" and breaking the queries up into Structural and Transactional, so as to eliminate needless processing for dimension data for each fact record but this depends on your data and your dimensions. Also consider writing the IQD yourself and ignoring Impromptu as you are in control of the SQL passed to the database server and you can examine the performance of the SQL, maybe see where some indexes are needed.

mayoman

 

[devcog]

Hi Everybody,
Thanks a lot for your useful tips...

They are certainly helpful to optimize the cube build time.
Regarding the measures I am afraid I can't split them into separate cubes as the user would like all of them in one single cube.

The time taken is to read the datasource. It takes around 30 mins to read 2.6 million records. (Please note that when I run the same query in SQL*Plus and fetch the count of records it returns within 2 mins)

Thanks again,