Size problem

[perikitown]

Dear Alex,

After we build and save some cubes, we have .cub files with very big sizes (100 MB to 300 MB are usual), when in fact, the really important information is the agregated one (sum values). The viewer is able to show this cubes, but it's really slow in loading and operating.

I only use a table, and the select SQL retrieves only 6 columns (3 dimensions and 3 measures), but for 10.000.000 records. Since the measures I need are only aggregated, the detail data could be dropped when building the cube. I think we could go with a much more smaller size .cub.

If I undertood correctly, it's better to not using GROUP BY in this type of querys, but now, I'm not sure about this. Any way, with the GROUP BY clause in the query the database is too much slow, and produces great load in the server.

(I'm using ExtendedMode="0", as you could see in the XML...)

We'll need any help in order to reduce the .cub size a little, since our users are asking about doing something to accelerate their work.

Thank you so much
Pedro Pablo Paños


Our XML definition (based in example app) is:
<?xml version="1.0" encoding="UTF-8"?>
<PivotCube.Configuration.Data
ConnectionType="1" Connection="Dsn=XXX" Name="expedientes2005"
SQL="
SELECT
r.clprovin AS clprovin,
r.annomes AS annomes,
r.prventa AS prventa,
r.nuenvase AS nuenvase,
r.clsitadm AS clsitadm,
r.nuenvase AS nuenvases,
FROM
fareceta r
WHERE
r.annomes >= '200609'
"
ExcludeZeros="-1"
AlwaysSaveCounts="-1"
ExtendedMode="0"
Inverted_commas="0"
>
<Dimensions Count="3">
<Dim1 AliasName="Provincia" FieldName="clprovin" DisplayName="clprovin" TableType="-2" TableName="FACTTABLE" KeyField="clprovin" LookupField="clprovin" ParentField="" Forecasting="0" ForecastingType="0" DefaultSort="1" WrapTo="0"/>
<Dim2 AliasName="Ano_MES" FieldName="annomes" DisplayName="annomes" TableType="-2" TableName="FACTTABLE" KeyField="annomes" LookupField="annomes" ParentField="" Forecasting="0" ForecastingType="0" DefaultSort="1" WrapTo="0"/>
<Dim4 AliasName="Sit.Adm." FieldName="clsitadm" DisplayName="clsitadm" TableType="-2" TableName="FACTTABLE" KeyField="clsitadm" LookupField="clsitadm" ParentField="" Forecasting="0" ForecastingType="0" DefaultSort="1" WrapTo="0"/>
</Dimensions>
<Measures Count="3">
<Measure1 AliasName="NumRecetas" FieldName="clprovin" CalcType="0" FormatString="##,###,##0" />
<Measure2 AliasName="Importe" FieldName="prventa" CalcType="0" FormatString="##,###,##0.00 €" />
<Measure3 AliasName="Envases" FieldName="nuenvases" CalcType="0" FormatString="##,###,##0" />
</Measures>
</PivotCube.Configuration.Data>

[AlexZencovich]

1. Are you have enough RAM on PC installed? Which RAM occpation on PC when cube are loaded in viewer?

2. How many dimension items in dimensions?

3. Which database system you have used?

[perikitown]

We are building the cubes (.cub files once per week), and then users access them using Citrx, so all processing is doing in the server. If the server is not so loaded, the time load of this cubes is 5 min. aprox. Once loaded, the viewer is slow but usable, in my opinion.

1. Are you have enough RAM on PC installed? Which RAM occpation on PC when cube are loaded in viewer?
Yes, we have 3GB RAM. In Windows Task Manager, 1GB aprox. is reported to be used by the viewer, so few users could open these cubes at the same time

2. How many dimension items in dimensions?
We have 3 dimensions:
Provincia: 52 dimension items
Sit.Adm: 30 dimension items
Anno_Mes: 4 dimension items

It would be great if .cub file only contains this aggregate values.

3. Which database system you have used?
Informix IDS 9.40 on Alpha Cluster

Best regards
Pedro Pablo Paños

[AlexZencovich]

You should carefully check code you have used to build cube. Max cube size in your conditions is 52 * 30 * 4 = 6240 cells. If you have not use Extended mode, all data will aggregated to these cells. It will quite small cube - 32k or less in size.

Try to check if cube not in Extended mode really - after cube has loaded, check TPivotCube.ExtendedMode value.