creating cubes are very slow . ...

[daniel70]

someone could answer this problem:
creating cubes are very slow .
but it take around 10 hours to create 2 cube in one transformer....
therefore i checked the SQL that Impromptu and retrieved all them .they are working well

i have changed auto-partition rate , but it didnt work

could someone tell me how to reduce the creating cube time..

If you require any further detail on this, please let me know.
Expecting your reply asap.

Thanks in advance

daniel!


++Property of Cubs++
Consolidate -> with sort
Consolidate records -> 100,000,000
Desired partition size ->500,000
Maximum number of passes -> 1
and so on..

++from log file +++++++++++++++++++++++++++++++++++++++++++
...
2002-03-16 am 10:55:21 4 0000012B Start Count and Consolidation with 16572692 rows and 24630 categories remainin
2002-03-16 pm 12:26:56 4 0000012B End Count and Consolidation with 11162991 rows and 24630 categories remaining.
...
2002-03-16 pm 08:18:52 4 00000000 Timing, TOTAL TIME (CREATE CUBE),10:40:17
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

 

[bsellick]

Daniel,

I'm going through something similar. Not nearly the same amount of rows as you though. The COGNOS knowledge base does have some articles regarding cube build optimization - especially large cubes. You should do some research there.

I will tell you what I am doing that is helping some - you will have to decide if it is relevant.

When the model was first created, the sources for each dimension simply did a dump of the dimension table it represented - i.e., all products in the product dimension table, all customers in the customer dimension table (for each of four types of customers), etc. So what we had in the model were literally thousands of categories that had no relevance to the fact table source because they were never used in the fact table.

So what I did, very simply, is modified the dimension queries to create distinct datasets for a dimension based on only the categories actually used in the fact table (aka measures). This dramatically impacted the cube build. For example, the number of categories for some of the customer dimensions dropped from 61,000 to 7000.

This simple change cut 7 hours from the cube build!

Don't know if that is something that can help you, but maybe think about it anyway.

- Brad

 

[mjcotter]

Are you using IQD's for your datasources? Try using CSV files for your datasources instead. Talk about speeding up a cube build!!! We just finished converting from IQD's to CSV's. What a difference!

 

[ninfan]

So... are you saying that you are exporting your data from your database into CSV files then building the cubes off them?

Troy

 

[BlueBrand]

Myself, I prefer the .iqd files. I've had some bad experiences with text files and such. I agree with Brad's suggestion of filtering out unwanted data from your .iqd files. No need to include data that is not relevant.

Also, perhaps some analysis is necessary to determine what data is actually required in the cubes. I've seen some cubes that are huge, created with a 'data-warehouse so better include everything' mindset, where millions of rows of detail data are included in a single cube. The build time is lengthy, and the cube itself is often difficult for users to understand. In many of those instances, the users actually only required a summary level cube for analysis, with drill-through .imr or .iwr reports for the detail. I find that it's always good to determine what data is required for analysis and trending, and for what data a report will suffice. Removing unnecessary levels of detail often can reduce cube size significantly.

Other than that, one consideration might be the computer that you are using to perform the builds.

 

[mjcotter]

Yes, ninfan, exactly!

I wrote a series of scripts using Cognos' scripting tool. Basically, each script opens an IMR, retrieves the data, then saves the resultset to a pipe-delimited CSV file.

I then repointed all of my datasources in Transformer to the newly created CSV files rather than the IQD's.

The cube builds fly now!!! I wish Cognos Support had suggested CSV files during one of the half dozen calls I made to them about this problem. I got the CSV idea when I put out an internal company-wide SOS. Several people replied with the CSV suggestion.

On average, it has cut our build times to about 1/3 of what they used to be.

Not to mention, we had a few Transformer models with 109 IQD datasources in them. Transformer was taking a ridiculous amount of time adding them into the model. I'm talking it was going to take somewhere in the neighborhood of 24 years(!!!) to get them all added. With the CSV method, I am able to take the 109 CSV files and run a quick DOS batch file to combine them all into 1 CSV file and then use that as the 1 datasource in my Model.

Definitely something to look into...

 

[BlueBrand]

Any idea why the .csv file build would be faster? The .csv files are not indexed, so how does Transformer read them faster? Is it simply because they are not restricted by the database processing?

 

[Kaiaualad]

By the time you take into account the time it takes to query the database with an IQD then convert to CSV,its not much difference than simply using iqds.

 

[wclaes]

This solution looks not suitable for databases with millions of records ??

The retrieving as CSV would take longer + the time needed to create the cube.

No, the best possible solution is to use IQ from sybase!
We performed a test. The load of data is the slowest part, so IQ solves this. It can reduce loading data tremendously.
Some tests showed that loading the data directly from
AS400 with no serverload took 17 hours. With IQ as a database it only took 10 hours with heavy server load and 9 hours with low server load.

Best regards,
Wim






Best regards,
Wim