Size problem

Discussion forum for PivotCube VCL users. Common questions. Bug and problem reports

Moderator: Alex Zencovich

Size problem

Postby perikitown » Tue Jan 23, 2007 12:08 am

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>
perikitown
User
 
Posts: 5
Joined: Tue Jul 04, 2006 11:01 pm

Postby AlexZencovich » Tue Jan 23, 2007 3:02 am

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?
Best regards,

Alex Zencovich
-------------------------------------------------------------------------------------------------------------------------------------------
www.pivotcube.com - OLAP solution for Delphi, C++ Builder and ActiveX environment
AlexZencovich
Site Admin
 
Posts: 580
Joined: Sun Jun 18, 2006 10:09 am

Postby perikitown » Tue Jan 23, 2007 9:44 am

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
perikitown
User
 
Posts: 5
Joined: Tue Jul 04, 2006 11:01 pm

Postby AlexZencovich » Tue Jan 23, 2007 5:24 pm

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.
Best regards,

Alex Zencovich
-------------------------------------------------------------------------------------------------------------------------------------------
www.pivotcube.com - OLAP solution for Delphi, C++ Builder and ActiveX environment
AlexZencovich
Site Admin
 
Posts: 580
Joined: Sun Jun 18, 2006 10:09 am


Return to PivotCube VCL

Who is online

Users browsing this forum: No registered users and 3 guests

cron