Big Problems with many Dimensions

[snoopy_spy]

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 ...

[AlexZencovich]

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

[snoopy_spy]

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)

[AlexZencovich]

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