Old forum topics (part 1)

Useful shares from PivotCube VCL - code example, algorithm solutions etc

Moderator: Alex Zencovich

Old forum topics (part 1)

Postby AlexZencovich » Mon Jun 19, 2006 5:15 am

Performance Question with big dimensions
Author: Dirk
date: Dec 21, 2004 - 7:48

Hi Sir,

I'm testing your component out for integration into my application and in general i'm impressed with the performance.
My test cube has some 200.000 records, 10 dimensions.
For the moment I also added a ( partial ) key as a ( big) dimension : 50.000 values. The reason for this is that it's very easy to track incorrect value problems in the input records by drilling down into 'suspiciou

answer | answer with quote

Author: Alex Zencovich
date: Dec 21, 2004 - 8:43

There is problem with Windows native TreeView object - too slow for adding a new items.
It can be fixed by replacing a Windows native TreeView with TVirtualTreeView which much more faster (50000 will take less than 1 sec). But it is available for VCL source versions only. TVirtualTreeView developed as freeware, under GPL/MPL, so I cannot put that component to compiled (trial) packages.
But we provide (in VCL source package) a replacement of standard drop-down form with similar for TVirtualTreeView.

How to update PivotGrid when data changes
Author: Alan Humphries
date: Jan 03, 2005 - 5:13

Sorry if this is a bit of a dumb question.

I have just found PivotCube and I am considering it for a small job I am doing. I have the PivotCube linked to an in-memory table and then to a PivotMap and PivotGrid. I want the PivotGrid to update when I make a change to the table data but the only way I can find to do it is to make the PivotCube inactive and then activate again. Is this right? The effect is that the PivotGrid pauses and then diappears and then re-appears. This looks bit bit odd to the user.

If I have to do it this way, how can I stop the flashing?

Thanks for any help.

Alan Humphries

answer | answer with quote

Author: Alex Zencovich
date: Jan 03, 2005 - 8:57

Yes, you are right. The way to refresh data is decactivate PivotCube and reactivate it again.
To prevent refresh of PivotGrid you may try to use system call

LockWindowUpdate(PivotGrid.Handle); <- lock paint
PivotCube.Active := False;
PivotCube.Active := True;
LockWindowUpdate(0) <- unlock paint


answer | answer with quote

Author: Alan Humphries
date: Jan 03, 2005 - 10:47

I will try that. Thank you for the very quick response.

Regards

Alan Humphries

answer | answer with quote

Author: Alex Zencovich
date: Jan 03, 2005 - 12:12

Please note - if you data change mean adding new records to fact table you not needed to deactivate cube. See demo sources for "Upgrade" feature

answer | answer with quote


all discution themes | start new topic


Alan Humphries

answer | answer with quote

Author: Alex Zencovich
date: Jan 03, 2005 - 12:12


Great product, few questions
Author: Cameron
date: Jan 10, 2005 - 16:28

1) When compiling the latest zcube source, my Delphi 7 compiled version of the DLL is almost twice the size at 338k. I don't have debug turned on. Is there some other project switch I might be missing?

2) When loading saved maps, the cube is sometimes doubling the records. Do I need to refresh the cube after a map load?

3) I noticed in an earlier post you mentioned TVirtualTreeView for a performance boost on loading large dimensions. Is there something I need to do to use this control in the source?

4) When saving a map, is there any way to remove the dimension data so the map file will be smaller and then refresh it from the database after reloading it? My dimension data never changes, but their location does. Would be nice to just save the location/filters/etc and skip saving every value in a dimension. My map files are huge and saving them zipped back to our servers is still multiple megabytes.

5) Any good tips for speeding up the loading of a cube? Some of our cubes are huge and it can take several seconds to load.

6) I noticed someone mentioned new forum software. What you have is fine, but really needs a search feature. I am betting most of my questions here have been asked and answered before.

BTW, this is a wonderful product and we have been very impressed with how well built it is. I find it difficult to guage how well a product is designed until I get the source and start integrating it. I have been very impressed and so far everything has been pretty easy.

answer | answer with quote

Author: Alex Zencovich
date: Jan 10, 2005 - 19:32

1. Use packing utility like AsPack (www.aspack.com)
2. No. If you call PivotCube.Build it will double current cube. Use Build to load 'additional' data only. For last day for example.
3. See PivotCubeVCL.inc Comment $DEFINE NATIVETREE to use TVirtualTreeView
4. By default no. But you may see sources and create own save/load routine. It is very simple, if you not need to save/load dimension items.
Another solution - use XML as storage and remove dimension-related branches before save.
5. Use fastest drive (for binary files). The most time (up to 85%)earned by disk-related ReadStream procedures. Probably read-ahead buffer will improve perfomance too.
6. I know. Please, use Yahoo groups - it is allow to search and you will get answers directly by mail.

answer | answer with quote

Add/Remove Columns and Rows at runtime
Author: Maurizio
date: Jan 19, 2005 - 16:59

Hi, is possible to add or remove columns or rows in pivot table at runtime ? I use Activex. If yes, can you send me some example? Thanks.

Maurizio

answer | answer with quote

Author: Alex
date: Jan 22, 2005 - 3:29

Look to
PivotCubeX.MapIntf.DimensionToRow(DimensionToColumn)
Remarks

Cube must be ACTIVE


answer | answer with quote

How to get Cell rect in pixels??
Author: fabianse
date: Jan 25, 2005 - 22:37

I'm testing the component.
My idea is to put an edit control, TEdit, in the place of the selected cell, when the users press a key.
So, I need to get the keydown event of tpivotgrid, or TForm,
and I need the cell coordinates of the current selected cell.
I'm using
PivotGrid1.CellRect(ColIndex,RowIndex)
in the event:
PivotGrid1FocusedCell(Sender: TObject; ColIndex, RowIndex, MeasureIndex, ViewIndex: Integer);

but This is not the current real top, left position in pixels.

Any Idea?

answer | answer with quote

Author: fabianse
date: Jan 25, 2005 - 23:14

fast solution, but not elegant :S

this event handler, put an TEdit called edit1, inside of the clicled cell

procedure TForm1.PivotGrid1DataCellClick(Sender: TObject; X, Y, ColIndex,
RowIndex: Integer);
var
rect:TRect;
w,h,col,row:integer;
begin
rect:= PivotGrid1.CellRect(ColIndex,RowIndex);
col := PivotGrid1.ColWidths[colindex];
row := PivotGrid1.RowHeights[rowindex];
edit1.Ctl3D:=FALSE;
edit1.top:=rect.Top;
edit1.left:=rect.Left;
edit1.width:=col+2;
edit1.height:=row+2;
edit1.text:= pivotgrid1.Cells[ColIndex,RowIndex];
end;

answer | answer with quote

Author: fabianse
date: Jan 25, 2005 - 22:41

I solved the issue:
just get cellrect inside the event:

PivotGrid1DataCellClick(Sender: TObject; X, Y, ColIndex, RowIndex: Integer);
var
rect:TRect;
begin
rect:= PivotGrid1.CellRect(ColIndex,RowIndex);
edit1.top:=rect.Top;
edit1.left:=rect.Left;
end;



answer | answer with quote

Author: fabianse
date: Jan 25, 2005 - 22:59

Cellrect give me a good left and top.
but it does not give me good Right and bottom values :S

answer | answer with quote

Author: Alex
date: Jan 28, 2005 - 9:34

You may to see to PivoGrid.FocusedCell property, get row and column indexes and get RowHeights and ColWidhs values for specifed cell.

Alignment of Columns / Rows / Cells
Author: Greg
date: Feb 02, 2005 - 18:36

How can we change the alignment of columns or rows or cells?

For example we want all the cells to be center aligned can we do this?

Thanks

answer | answer with quote

Author: Alex
date: Feb 03, 2005 - 11:56

There is event in PivotGrid - OnCustomDrawDataCell for data cells. The custom painting for headers is not provided because it is too complex to calculate a right position for string if you have expanded dimensions with autowidth(height)

answer | answer with quote

Help "total by column"
Author: Mauro
date: Feb 08, 2005 - 2:41


Hello, I am working with a project business intelligence , with Delphi 6 and PivotCube 7.07.
We need to show another similar row (horizontal) to "total by column" to make other I calculate different.

Example:

Total by Column (Summa)
Total by Column (AddPrevCol)


thank you!!!!!

answer | answer with quote

Author: Alex Zencovich
date: Feb 09, 2005 - 9:17

The only one way I can suggest - use custom painting of data cell to print 2 values (in different rows) for column totals. And change 'Total by Column' to
'Total by column' + $D + $A + 'Some text in another row'

answer | answer with quote

intraweb
Author: gary ashton
date: Jan 31, 2005 - 15:34

Hi
I'm trying to use the activex component with intraweb, but so far without success.
has anybody successfully used pivotcube and intraweb together?
do you have any examples??
cheers
Gary

answer | answer with quote

Author: Alex
date: Feb 03, 2005 - 11:46

There is some problem with security settings when you try to use ActiveX in IntraWeb. You need to set your IntraWeb security settings to right. On pity I could not to get access to my my knowlegebase to get direct answer.

answer | answer with quote

Author: gary ashton
date: Feb 04, 2005 - 14:44

Hi Alex,

thanks for that. But I can actually get the pivotcube activex component to be displayed.
My problem is trying to get the data to load.
I note that in delphi you you "connectionstring" and in vb you use "cs".
Using intraweb extratagparams I can set the "cs" to the correct db however I get a "no cube structure defined" when I try to cubeactive := true.
I tried creating a cube through a normal application and saving as "mycube.cub" then using cubeload "mycube.cub", but this doesn't work either.

d7 intraweb 5.1.26

any help would be great

cheers
Gary

answer | answer with quote

Author: Alex Zencovich
date: Feb 09, 2005 - 9:24

1. I think you need to set cube structure manually, via code because VB resorces may be unreachanble in IntraWeb for ActiveX application (due tos ecurity reasons). If resources is unavailable you will get the error you get (no cube structure...)
2. CubeLoad will not work in network environment (except if you not have a NetBios- compatible address as file path or have mapped drive onto network server. To load cube from server you XML based cube save/load methods

answer | answer with quote

Calc member
Author: anonim
date: Feb 11, 2005 - 17:09

What the problem?

begin
Result = "SALES" * -1;
end;

Not working!!!

answer | answer with quote

Author: Alex Zencovich
date: Feb 11, 2005 - 17:59

Unary modifiers not supportet. Change formula to
begin
Result = "SALES" * (0-1);
end;


answer | answer with quote

hiding fx button
Author: makowski
date: Feb 20, 2005 - 13:44

How can I hide the Fx button in TPVMeasureToolBar
I don't want that my users see it

answer | answer with quote

Author: Alex Zencovich
date: Feb 20, 2005 - 19:39

There are no way to hide it (at least withou source change). But you may prevent any action by OnDropDown event and set Allow := False;
So user may click on that button but nothing will happened or user will get message like 'Operation not allowed'

answer | answer with quote

Author: makowski
date: Feb 21, 2005 - 13:28

Allow := False; ???
can you help me more ?

procedure TfmTableauCroise.PVMeasureToolBar1CalcMeasureDropDown(
Sender: TObject; Existed, Deleted: TStrings; var Apply: Boolean);
begin
showmessage('Operation not allowed');
Allow := False; ???

end;


answer | answer with quote

Author: Alex Zencovich
date: Feb 21, 2005 - 15:05

Oh sorry, Apply := True; :(

It is described in manual.

ActiveX problem with BorlandC++builder 6
Author: Oliver
date: Mar 02, 2005 - 16:28

Hi,

I can't import the activeX correctly using Borland C++ builder 6.
It seems to happen a compilation problem :

"cannot create instance of absract class IMap" in zCube_TLB.h ...

Any idea?

thanks

answer | answer with quote

Author: Alex Zencovich
date: Mar 02, 2005 - 21:55

You never not need to create IMap-based objects in PivotCubeX. Only IPivotCubeX (CoPivotCubeX) allowed for creation. After activation, the property IPivotCubeX.MapIntf will point to PivotMap interface.

answer | answer with quote

Author: oliver
date: Mar 03, 2005 - 9:12

I understand what you say, but I'm still a the importation stage of the activeX. Then Borland wants to compile it in the package, then it produces these errors. I haven't coded anything yet.
So I still wonder what can I do to make it compile right

thanks for answering

answer | answer with quote

Author: Alex Zencovich
date: Mar 03, 2005 - 19:02

Ok I see the problem. There is porble with installing zCube.dll wrapper. it really has IMap interface, which is declared in that dll, but not implemented (it implemented in PivotCube VCL). So Borland code generator cannot find refernce to interface methods in dll.
You may see it as next error message you get (zCube::IMap->Clear()= 0)

I'm unsure is it possible to fix that problem withou manual changes in zCube_TLB.h (probably just remove all IMap section).

By the way - WHy you not use PivotCube VCL? It works with C++ Builder 6 and has more abilities than ActiveX.

answer | answer with quote

Author: olivier
date: Mar 04, 2005 - 9:05

Ok, that's a clear answer,
thanks very much Alex

answer | answer with quote

About ColWidths
Author: Mauro
date: Feb 24, 2005 - 14:58


Hello,

How to use the property ColWidths in excecution time using code?

Example:

PivotGrid.ColWidths[1] := 200;
PivotGrid.Refresh;






answer | answer with quote

Author: Alex Zencovich
date: Feb 24, 2005 - 16:30

Yes. But you need make 2 thing to get it work
1. Disable autowidth feature by set PivotGrid.FixedColWidth := 100 (for example)
2. Set code, which will reset widths in PivotGrid.OnAfterRefresh event, so it will executed after user will make any changes to grid or map


answer | answer with quote

Average instead of Total
Author: César Piñera
date: Mar 09, 2005 - 14:57

Hi,

Is there a way to show average instead of SubTotal and Total in PivotCube?

Example:

Item1 $2.00
Item2 $3.00
Item3 $5.00

Average = (2 + 3 + 5) / 3 = $3.33

Thank you very much for your support.

answer | answer with quote

Author: Alex Zencovich
date: Mar 09, 2005 - 19:02

Use OnGetDataCell event to replace total value with your own calculated value in same cell.

like

procedure TForm1.PivotGrid1GetCellValue(Sender: TObject; X,Y, RowIndex,
ColumnIndex, MeasureIndex, ViewIndex: Integer; var Value: Double;
var CellString: String; Cell: TpvCellDrawing);
var
f : double;
c : integer;
begin
if (Cell.Color = pvgridTotalColor) or (Cell.Color = pvgridOddColor) then
begin
c := PivotMap1.Cells[ColumnIndex,RowIndex,MeasureIndex,3]; //get count
Value := f / c; // get average
end;
end;

Note: If you have ctCount measure in your cube, you may use
c := PivotMap1.Cells[ColumnIndex,RowIndex,CountMeasureIndex,0]; //get count

instead. Otherwise, you may need to set PivotCube.AlwaysSaveCount to True.


answer | answer with quote
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 1 guest

cron