Big Problems with many Dimensions

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

Moderator: Alex Zencovich

Big Problems with many Dimensions

Postby snoopy_spy » Thu Dec 02, 2010 11:27 am

I have big problems with ac ube where i have many Dimension Items ...

There are over 250000 Items, and after load, PivotData will remove many of them

so i get into problem, that the column sum are not correct, and after filtering the data are changing

I found the problems, but don't know how to remove the problem

First in cube.pas on line 8752
Code: Select all
procedure TCube._SyncDimensionGroups(const Map: IMap; DimIndex: Integer);
....
if TDimensionObject(DimList[DimIndex]).EmptyItems = diRemove then
   begin
   SetLength(mi.DFilters[DimIndex],$FFFA);
//   FillChar(mi.DFilters[DimIndex],$FFFA,Ord(False));
   SetLength(mi.VFilters[DimIndex],$FFFA);
//   FillChar(mi.VFilters[DimIndex],$FFFA,Ord(False));
   end


Here you see that only $FFFA Count will be reserved!


And here the sum is calculated

in cube.pas line 3730
Code: Select all
procedure TCube.FillTotalCell(mi : TMapInfo);
...
   pi := p.First;
   while (pi <> nil) do
         begin
         for i := 0 to DimList.Count - 1 do
             begin
             if mi.DFilters[i][pi.key[i]] then


pi.key[i] is 235212 ... and DFilters[i] is only allocated for 65500 Items !!!!!

What can i do that i get ride of the problem

In my test the dimension is loading 235000 Items and after load it removes 234980 items so that only 20 items resides in the list ... And i get still this problem


But in real world application i have Dim Items with more than 65550 Items !!!!


Please Help very urgent ... :(
snoopy_spy
User
 
Posts: 7
Joined: Wed Dec 02, 2009 10:55 am

Re: Big Problems with many Dimensions

Postby AlexZencovich » Fri Dec 03, 2010 7:37 pm

Yes, sorry but there is limitation to only 65000 items in dimension as there reserved a word ($FFFF) datatype to store item ID. You have to change datatype to LongWord to keep more items but that will also dramatically increase memory occupation.

We usually suggest to restructure cubes to avoid so long dimensions as that is useless for end user - open a grid with 250000 rows or even similar - it will kill RAM to have millions rows in a grid
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

Re: Big Problems with many Dimensions

Postby snoopy_spy » Sat Dec 04, 2010 10:13 am

Hmmm, yes, but the problem is, that we use links
The Fact Table only loads the id from the records (for example customers) and the dimension use a link to a sql command to get the text for the customer

And the problem is, that in the moment it seems that the cube loads ALL of the customers, and after that removes the unused customers.
The result is, that the array for the filter is very big with about 240000 items, but only 12 items are used .. but the index of these items are greater than $FFFF

And this is my problem ....

Is it possible to reindex after the delete unused dimensions?

Is it possible to load only the dimensions which are needed in the cube?
Or what is the best practise? (I thought it is a good idea, to not load the dimension text in the fact table, because i need the id and text, and therefore it is better to load it extra for each dimension)
snoopy_spy
User
 
Posts: 7
Joined: Wed Dec 02, 2009 10:55 am

Re: Big Problems with many Dimensions

Postby AlexZencovich » Sat Dec 04, 2010 11:51 am

You may use next query to load dimension table

select id,name from dimension where id in (select distinct DimID from facts)

or

select distinct f.DimID, d.Name from facts f, dimension d where d.id = f.DimID

or similar (depend on SQL server you use to get better performance)

That will load only used items from dimension table
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 5 guests

cron