Cube size problem 1

[lslomp]

I am having a problem with a cube that I am trying to build. The cubes were originally developed and supported by a consulting company but now we are supporting them.

The cube was around 110 MB and growing a few MB per month. One month it all of a sudden grew to 475 MB and then it has been growing a few MB per month again. This has apparently happened in the past and the solution was to delete and readd the cube definition in the Powercubes window of Tranformer (version 6.6) and then rebuild the cube. This has not fixed the problem this time.

Does anyone have any other ideas on how to get the cube back to the normal size?

Thanks in advance

 

[nagrajm]

A cube can increase in size only in tune with the increase in rows from the data source. Take a look at the underlying query and determine if there was a sudden increase in no. of rows returned by the query.

If the data source is an IQD, it must have an underlying IMR file that you could run in impromptu client for the period in question and compare the rows retrieved.

For instance, if the cube suddenly increased in size in June, run the IMR for Apr, May and June separately and compare the rows returned.

I do not know of any other reason why a cube would grow suddenly large.

 

[CognosProfessional]

Maybe you want to limit the source to bring in only a specific period of time, like the last 12 months, or the last 2 years? If you cube is getting larger, it could be because you have old historical data in it in addition to new data. If this is the case maybe build a historical cube separate from the one that brings in new data. For example a cube that is 1999 through 2000, and another cube that is 2001 through 2002, and another cube that is just 2003?

CP

 

[Waspy]

This can be due to the way Transformer stores data. The current model of partitioning is used, which means after reading source records, transformer makes a number of discreet partitions to hold data. No dimensions can span partitions, so the smallest partition will be large enough to hold the largest dimension tree in entirety. As each partition is the same size (large enough to contain the largest dimension), you can reach a point when building a cube, that there is so much data a new partition must be added. This is not done dynamically with a mind to space saving, Transformer simply adds another partition. This can cause a small amount of data to cause a large increase in cube size.
Think of it this way, 3 dimensions, each needing a partition size of 100 (bytes, meg, gig, it doesn't matter), so the cube size is 300. Adding new data, just a few extra records, the cube size jumps to 400. This would be due to the need for the new partition.

Removing and recreating the categories in the model cause a recalc of the partition size by transformer (building with ALREADY generated categories does not) so sometimes this can reduce the issue, using the above example, a rebuild might mean transformer decides to use a partition size of 120, which would mean only 3 partitions are needed for the data, so the cube size would be 360.
Hope this helps!

 

[BruceReed]

One more possible answer in addition to the above 3 from Waspy, CP and Nagrajm, is the number of suppressed entries in the cube.

If you have many null entries, and the Inclusion tab in transformer is set to include all entries, this would inflate the size of the cube.

Good luck
Bruce

 

[lslomp]

I have been able to fix the problem by adjusting the 'Estimated number of Cosolidated Records' and 'Desired Partition Size' fields. The cube is now 106 MB.

I am not sure why but I had to set the estimated number of records to 4 million when the log shows that there are only 1.8 million records.

Thanks again for your help.

 

[BruceReed]

That's a new one to me.

Thx.
Bruce