W
weituo2002
Unregistered / Unconfirmed
GUEST, unregistred user!
Using the Decision Cube without the BDE
Abstract:The TDecisionCube component that ships with Delphi Client-Server or Enterprise does not work well with non-BDE datasets. But we can change that! By Mark Shapiro.
Beginning with version 3, Borland included a TDecisionCube component in Client/Server and Enterprise editions of Delphi. This component introduced an easy way to provide powerful data analysis capabilities in an application, without having to write very much code. Unfortunatly, it required the BDE in order to function properly. With the migration away from the BDE in favor of alternate datasets like dbExpress, ADO, and Interbase Express, the Decision Cube component was not carried along. This article details what is necessary to get the Decision Cube working again.
This article includes changes to the TDecisionCube source code, and will be of no help if you do not own a license for a Client/Server or Enterprise edition of Delphi
The Problems
There are a handful of problems with getting the Decision Cube to work with a non-BDE dataset, including:
Auto-detection of dimensions and summaries
When you connect a TDecisionCube (and grid and source) to a TQuery or other BDE-based dataset, the decision cube will automatically determine which fields are dimensions and which are summaries -- aggregate fields such as SUM(), COUNT(), AVG(), and so on. This allows you to display a decision grid with virtually no custom code. Unfortunatly this works only with a BDE dataset. The first hurdle, then, is building the dimension map. You can do this automatically, or manually.
Ordering of null values, empty strings and other odd characters
The BDE does more than just provide a mechanism for your application to get at data. It also exposes some functions that your application can use to do things the way the underlying database does. One of these functions is string comparison. Databases can collate strings in different ways, sometimes depending on the character set used. For example, a Spanish character set has characters that the English language does not, which must be collated in the proper order. You could also choose to use a case-insensitive collation, which would sort characters in the order 'AaBbCc' or 'aAbBcC' instead of 'abcABC'. When connected to a BDE Dataset, the decision cube can use the BDE's sorting order. Unfortunatly, without the BDE, the decision cube uses the AnsiCompareStr function - which is great if your database sorts using the same collation order, but fails if your database uses, for example, an ASCII collation order.
Grouping of dates by month/quarter/year into bins
When displaying dates in a decision grid, it is often desirable to group dates by month, quarter, or year, instead of displaying each date (or worse, each date+time) as its own value. When connected to a BDE dataset, the decision cube will allow you to do this by setting the BinType property of the dimension (this can be done via code or via a dialog box.) Without the BDE, the decision cube doesn't even try.
Solutions
The solutions to these problems involves modifying the Decision Cube source code, which is part of the VCL. Because of this, I cannot include the full source to a non-BDE Decision Cube as part of this article. Instead, I will provide only the revised code, with as little of the VCL code as possible. In order to implement this solution, you will need to have the Client/Server or Enterprise edition of Delphi. This code has been tested against Delphi 5 and 6, though it should work with Delphi 3 and 4 as well.
Auto-detection of dimensions and summaries
Setting dimensions can be done manually, at design time, if your decision grid will always display the same fields. Or, it can be done manually at run-time by the user, but I personally don't like that option. Or, it can be done automatically, at run-time, by your code. This allows you the flexibility of changing the grid without burdening the user - I prefer this solution.
Note that this does not require ANY changes to the Decision Cube source - all this is done in your own program source.
Steps to Building a Decision Cube:
Build your query.
Build the data set map.
Configure the dimensions and summaries.
Build your query: Build a SQL statement that gets all the necessary fields and summaries, including any where clause and a group by clause. For example:
select Field1, Field2, count(*), Avg(Field3) from Table group by Field1, Field2
Build the data set map: Most of the work here is done by the Decision Cube. You just need to tell it what to do. This is actually the same code that the decision cube uses with BDE-based datasets, but you have to do more of the work in the next step by yourself.
var myMap: TCubeDims;
nCount: integer; // used in step 3
//DecisionCube1 and IBQuery1 are components on your form. DecisionCube1 is a TDecisionCube,
and IBQuery1 is a TIBQuery, or other TDataset descendant.
myMap := TCubeDims.Create(DecisionCube1, TCubeDim);
try
bParsed := false;
bDataSetMatch := false;
myMap.Assign(TCubeDims(DecisionCube1.DimensionMap));
anError := BuildDataSetMap(IBQuery1, myMap, bParsed, bDataSetMatch);
case anError of
tqeNotInitialized: raise ECubeDesignError.CreateRes(@sQryNotInitialized);
tqeNoDimensions: raise ECubeDesignError.CreateRes(@sNoDims);
tqeNoAggs: raise ECubeDesignError.CreateRes(@sNoAggs);
tqeNotGrouped: raise ECubeDesignError.CreateRes(@sGroupsMissing);
end;
// Here is where you will put the code for step 3.
finally
myMap.Free
end;
Configure the dimensions and summaries: Finally, you must tell the decision cube what to do with each of the fields in the dataset. This is fairly simple. All the code here goes in place of the comment in the code for the previous step.
with myMap do
begin
// Part A: Configure dimensions
for nCount := 0 to [Number of Dimensions - 1] do
begin
Items[nCount].DimensionType := dimDimension;
Items[nCount].BaseName := 'TABLE.FIELDNAME';
//If the dimension is a date and should be grouped, use this code
Items[nCount].FieldType:=ftDateTime;
Items[nCount].BinType:=binYear
{or}
Items[nCount].BinType:=binQuarter
{or}
Items[nCount].BinType:=binMonth
Items[nCount].BinFormat:='mm/dd/yyyy';
Items[nCount].StartValue:=BaseDate;
//If the dimension is not a date, or is a date but should not be grouped,use this code
Items[nCount].BinType:=binNone;
end;
// Part B: Configure summaries
for nCount:=[Number of Dimensions] to [Number of Fields-1] do
Items[nCount].DimensionType := dimCount;
end;
// And finally, apply the new dimension map
DecisionCube1.Refresh(myMap, true);
That's it -- you're done! Open the dataset, and you've got a decision cube. Of course you may want to rearrange the order that fields appear in the cube, but you can do that using the normal decision cube methods that worked well with the BDE.
Ordering of null values, empty strings and other odd characters
The solution to this problem is fairly simple, and requires a modification to the mxarrays.pas source file. Look for this function:
function CmpString(var item1, item2): Integer;
Leave the function header and variable declarations the same, but replace the body of the function with code appropriate to your database's collation sequence. For Interbase, using the default character set and collation, the code could look like this:
// Can't differentiate between a null or empty string, but this places nulls at the end
// of the collation order.
if (p1='') and (p2='') then
result:=0
else if (p1='') then
result:=1
else if (p2='') then
result:=-1
else
// Otherwise, use CompareStr, which uses the ASCII character set.
result:=CompareStr(p1, p2);
Grouping of dates by month/quarter/year into bins
The solution to this problem is fairly easy too, though it does not work with all datasets. I was able to get this to work with a TIBQuery, but not with a TADOQuery. The initial solution involves making some minor changes to mxstore.pas. Find the following method:
procedure TMultiDimDataLink.UpdateCache(Sender: TObject);
In the variable declarations, remove the following variable:
bHaveBDE: Boolean;
Scan down a few lines, and remove this line:
bHaveBDE := True;
Scan down a few more lines, and remove these two lines:
if not (FDataStore.DataSet is TBDEDataSet) then
bHaveBDE := IsBDEAvailable;
And modify the next line:
if (FDataStore.BinData) and (bHaveBDE) then
should become:
if (FDataStore.BinData) then
Also, the method that groups dates according to Quarter seems to me to function a bit oddly, so I replaced it. Still in mxstore.pas, look for the following method:
procedure TCubeDim.QuarterTransform(var Value: Variant; CubeDim: TCubeDim);
It has a nested procedure:
procedure GetQuarterRange(var Mon, Yr: Word);
Replace the nested GetQuarterRange procedure entirely, with this code, which is based on the original code:
procedure GetQuarterRange(var Mon, Yr: Word);
var
I: Integer;
Q, K, YQ, MQ, DQ: Word;
sDate: TDateTime;
CY,CM,CD: Word;
begin
sDate := CubeDim.StartDate;
if (sDate = 0) then
MQ := 1
else
DecodeDate(sDate, YQ, MQ, DQ);
DecodeDate(Date,CY,CM,CD);
Q := 1;
K := MQ;
repeat
for I := 1 to 3 do
begin
if (Mon = K) then
begin
if (Mon >= MQ) then Inc(Yr,(YQ-CY));
if Mon<MQ then inc(yr,(YQ-CY)-1);
Mon := Q;
Exit;
end;
Inc(K);
if k>12 then k:=1;
end;
if (K >= 12) then K := 1;
Inc(Q);
until (Q > 4);
if (Mon >= MQ) then Inc(Yr,(YQ-CY));
if Mon<MQ then inc(yr,(YQ-CY)-1);
Mon := MQ;
end;
Now you can set the dimension's StartDate (or StartValue) property to indicate what month/year to use as the base for the quarter calculation. The Month portion of StartDate indicates what month is the beginning of Q1. The Year portion of StartDate indicates the current year. For example, setting StartDate to 01/01/2001 would result in Jan/Feb/Mar (2001)=Q1 2001, Apr/May/Jun (2001)=Q2 2002, etc. Setting StartDate to 10/01/2002 would result in Jan/Feb/Mar (2001)=Q2 2001, Apr/May/Jun (2001)=Q3 2001, Oct/Nov/Dec (2001) = Q1 2002, and so on. The Day portion of StartDate is ignored.
Assorted minor changes
Decision Cube raises an empty dataset exception on a dataset with 1 record. To fix this "problem," load up mxstore.pas and find the following method:
procedure TMultiDimDataLink.UpdateCache(Sender: TObject);
Scan down a few dozen lines, looking for this line:
if FDataStore.DataSet.RecordCount <= 1 then raise ECacheError.Create(sEmptyDataSet);
and change it to this:
if FDataStore.DataSet.RecordCount < 1 then raise ECacheError.Create(sEmptyDataSet);
Decision Grid does not use an assigned PopupMenu, and instead always uses its own internal PopupMenu. I find this one a bit odd, because the TDecisionGrid publishes the PopupMenu property and then ignores it. This is not a huge problem, but in one case I wanted my own popup menu to control the decision cube. To change this behavior, load up mxgrid.pas and find the following method:
procedure TCustomDecisionGrid.MouseDown(Button: TMouseButton; Shift: TShiftState; X, Y: Integer);
Scan through the method looking for references to FMenu. There are three blocks. One of them begins like this:
FMenu.Clear
The other two begin like this:
with FMenu do
begin
All three of them end with:
FMenu.PopUpAtMe(Self, X,Y);
Add an if...then block around the entire FMenu section, as follows:
if not Assigned(PopupMenu) then
begin
FMenu.Clear;
{or}
with FMenu do
begin
.
.
.
FMenu.PopUpAtMe(Self, X,Y);
end;
Again, you will make three such changes.
If you have any further questions about this subject, please feel free to contact me at Mark.Shapiro@seguetech.com.
Abstract:The TDecisionCube component that ships with Delphi Client-Server or Enterprise does not work well with non-BDE datasets. But we can change that! By Mark Shapiro.
Beginning with version 3, Borland included a TDecisionCube component in Client/Server and Enterprise editions of Delphi. This component introduced an easy way to provide powerful data analysis capabilities in an application, without having to write very much code. Unfortunatly, it required the BDE in order to function properly. With the migration away from the BDE in favor of alternate datasets like dbExpress, ADO, and Interbase Express, the Decision Cube component was not carried along. This article details what is necessary to get the Decision Cube working again.
This article includes changes to the TDecisionCube source code, and will be of no help if you do not own a license for a Client/Server or Enterprise edition of Delphi
The Problems
There are a handful of problems with getting the Decision Cube to work with a non-BDE dataset, including:
Auto-detection of dimensions and summaries
When you connect a TDecisionCube (and grid and source) to a TQuery or other BDE-based dataset, the decision cube will automatically determine which fields are dimensions and which are summaries -- aggregate fields such as SUM(), COUNT(), AVG(), and so on. This allows you to display a decision grid with virtually no custom code. Unfortunatly this works only with a BDE dataset. The first hurdle, then, is building the dimension map. You can do this automatically, or manually.
Ordering of null values, empty strings and other odd characters
The BDE does more than just provide a mechanism for your application to get at data. It also exposes some functions that your application can use to do things the way the underlying database does. One of these functions is string comparison. Databases can collate strings in different ways, sometimes depending on the character set used. For example, a Spanish character set has characters that the English language does not, which must be collated in the proper order. You could also choose to use a case-insensitive collation, which would sort characters in the order 'AaBbCc' or 'aAbBcC' instead of 'abcABC'. When connected to a BDE Dataset, the decision cube can use the BDE's sorting order. Unfortunatly, without the BDE, the decision cube uses the AnsiCompareStr function - which is great if your database sorts using the same collation order, but fails if your database uses, for example, an ASCII collation order.
Grouping of dates by month/quarter/year into bins
When displaying dates in a decision grid, it is often desirable to group dates by month, quarter, or year, instead of displaying each date (or worse, each date+time) as its own value. When connected to a BDE dataset, the decision cube will allow you to do this by setting the BinType property of the dimension (this can be done via code or via a dialog box.) Without the BDE, the decision cube doesn't even try.
Solutions
The solutions to these problems involves modifying the Decision Cube source code, which is part of the VCL. Because of this, I cannot include the full source to a non-BDE Decision Cube as part of this article. Instead, I will provide only the revised code, with as little of the VCL code as possible. In order to implement this solution, you will need to have the Client/Server or Enterprise edition of Delphi. This code has been tested against Delphi 5 and 6, though it should work with Delphi 3 and 4 as well.
Auto-detection of dimensions and summaries
Setting dimensions can be done manually, at design time, if your decision grid will always display the same fields. Or, it can be done manually at run-time by the user, but I personally don't like that option. Or, it can be done automatically, at run-time, by your code. This allows you the flexibility of changing the grid without burdening the user - I prefer this solution.
Note that this does not require ANY changes to the Decision Cube source - all this is done in your own program source.
Steps to Building a Decision Cube:
Build your query.
Build the data set map.
Configure the dimensions and summaries.
Build your query: Build a SQL statement that gets all the necessary fields and summaries, including any where clause and a group by clause. For example:
select Field1, Field2, count(*), Avg(Field3) from Table group by Field1, Field2
Build the data set map: Most of the work here is done by the Decision Cube. You just need to tell it what to do. This is actually the same code that the decision cube uses with BDE-based datasets, but you have to do more of the work in the next step by yourself.
var myMap: TCubeDims;
nCount: integer; // used in step 3
//DecisionCube1 and IBQuery1 are components on your form. DecisionCube1 is a TDecisionCube,
and IBQuery1 is a TIBQuery, or other TDataset descendant.
myMap := TCubeDims.Create(DecisionCube1, TCubeDim);
try
bParsed := false;
bDataSetMatch := false;
myMap.Assign(TCubeDims(DecisionCube1.DimensionMap));
anError := BuildDataSetMap(IBQuery1, myMap, bParsed, bDataSetMatch);
case anError of
tqeNotInitialized: raise ECubeDesignError.CreateRes(@sQryNotInitialized);
tqeNoDimensions: raise ECubeDesignError.CreateRes(@sNoDims);
tqeNoAggs: raise ECubeDesignError.CreateRes(@sNoAggs);
tqeNotGrouped: raise ECubeDesignError.CreateRes(@sGroupsMissing);
end;
// Here is where you will put the code for step 3.
finally
myMap.Free
end;
Configure the dimensions and summaries: Finally, you must tell the decision cube what to do with each of the fields in the dataset. This is fairly simple. All the code here goes in place of the comment in the code for the previous step.
with myMap do
begin
// Part A: Configure dimensions
for nCount := 0 to [Number of Dimensions - 1] do
begin
Items[nCount].DimensionType := dimDimension;
Items[nCount].BaseName := 'TABLE.FIELDNAME';
//If the dimension is a date and should be grouped, use this code
Items[nCount].FieldType:=ftDateTime;
Items[nCount].BinType:=binYear
{or}
Items[nCount].BinType:=binQuarter
{or}
Items[nCount].BinType:=binMonth
Items[nCount].BinFormat:='mm/dd/yyyy';
Items[nCount].StartValue:=BaseDate;
//If the dimension is not a date, or is a date but should not be grouped,use this code
Items[nCount].BinType:=binNone;
end;
// Part B: Configure summaries
for nCount:=[Number of Dimensions] to [Number of Fields-1] do
Items[nCount].DimensionType := dimCount;
end;
// And finally, apply the new dimension map
DecisionCube1.Refresh(myMap, true);
That's it -- you're done! Open the dataset, and you've got a decision cube. Of course you may want to rearrange the order that fields appear in the cube, but you can do that using the normal decision cube methods that worked well with the BDE.
Ordering of null values, empty strings and other odd characters
The solution to this problem is fairly simple, and requires a modification to the mxarrays.pas source file. Look for this function:
function CmpString(var item1, item2): Integer;
Leave the function header and variable declarations the same, but replace the body of the function with code appropriate to your database's collation sequence. For Interbase, using the default character set and collation, the code could look like this:
// Can't differentiate between a null or empty string, but this places nulls at the end
// of the collation order.
if (p1='') and (p2='') then
result:=0
else if (p1='') then
result:=1
else if (p2='') then
result:=-1
else
// Otherwise, use CompareStr, which uses the ASCII character set.
result:=CompareStr(p1, p2);
Grouping of dates by month/quarter/year into bins
The solution to this problem is fairly easy too, though it does not work with all datasets. I was able to get this to work with a TIBQuery, but not with a TADOQuery. The initial solution involves making some minor changes to mxstore.pas. Find the following method:
procedure TMultiDimDataLink.UpdateCache(Sender: TObject);
In the variable declarations, remove the following variable:
bHaveBDE: Boolean;
Scan down a few lines, and remove this line:
bHaveBDE := True;
Scan down a few more lines, and remove these two lines:
if not (FDataStore.DataSet is TBDEDataSet) then
bHaveBDE := IsBDEAvailable;
And modify the next line:
if (FDataStore.BinData) and (bHaveBDE) then
should become:
if (FDataStore.BinData) then
Also, the method that groups dates according to Quarter seems to me to function a bit oddly, so I replaced it. Still in mxstore.pas, look for the following method:
procedure TCubeDim.QuarterTransform(var Value: Variant; CubeDim: TCubeDim);
It has a nested procedure:
procedure GetQuarterRange(var Mon, Yr: Word);
Replace the nested GetQuarterRange procedure entirely, with this code, which is based on the original code:
procedure GetQuarterRange(var Mon, Yr: Word);
var
I: Integer;
Q, K, YQ, MQ, DQ: Word;
sDate: TDateTime;
CY,CM,CD: Word;
begin
sDate := CubeDim.StartDate;
if (sDate = 0) then
MQ := 1
else
DecodeDate(sDate, YQ, MQ, DQ);
DecodeDate(Date,CY,CM,CD);
Q := 1;
K := MQ;
repeat
for I := 1 to 3 do
begin
if (Mon = K) then
begin
if (Mon >= MQ) then Inc(Yr,(YQ-CY));
if Mon<MQ then inc(yr,(YQ-CY)-1);
Mon := Q;
Exit;
end;
Inc(K);
if k>12 then k:=1;
end;
if (K >= 12) then K := 1;
Inc(Q);
until (Q > 4);
if (Mon >= MQ) then Inc(Yr,(YQ-CY));
if Mon<MQ then inc(yr,(YQ-CY)-1);
Mon := MQ;
end;
Now you can set the dimension's StartDate (or StartValue) property to indicate what month/year to use as the base for the quarter calculation. The Month portion of StartDate indicates what month is the beginning of Q1. The Year portion of StartDate indicates the current year. For example, setting StartDate to 01/01/2001 would result in Jan/Feb/Mar (2001)=Q1 2001, Apr/May/Jun (2001)=Q2 2002, etc. Setting StartDate to 10/01/2002 would result in Jan/Feb/Mar (2001)=Q2 2001, Apr/May/Jun (2001)=Q3 2001, Oct/Nov/Dec (2001) = Q1 2002, and so on. The Day portion of StartDate is ignored.
Assorted minor changes
Decision Cube raises an empty dataset exception on a dataset with 1 record. To fix this "problem," load up mxstore.pas and find the following method:
procedure TMultiDimDataLink.UpdateCache(Sender: TObject);
Scan down a few dozen lines, looking for this line:
if FDataStore.DataSet.RecordCount <= 1 then raise ECacheError.Create(sEmptyDataSet);
and change it to this:
if FDataStore.DataSet.RecordCount < 1 then raise ECacheError.Create(sEmptyDataSet);
Decision Grid does not use an assigned PopupMenu, and instead always uses its own internal PopupMenu. I find this one a bit odd, because the TDecisionGrid publishes the PopupMenu property and then ignores it. This is not a huge problem, but in one case I wanted my own popup menu to control the decision cube. To change this behavior, load up mxgrid.pas and find the following method:
procedure TCustomDecisionGrid.MouseDown(Button: TMouseButton; Shift: TShiftState; X, Y: Integer);
Scan through the method looking for references to FMenu. There are three blocks. One of them begins like this:
FMenu.Clear
The other two begin like this:
with FMenu do
begin
All three of them end with:
FMenu.PopUpAtMe(Self, X,Y);
Add an if...then block around the entire FMenu section, as follows:
if not Assigned(PopupMenu) then
begin
FMenu.Clear;
{or}
with FMenu do
begin
.
.
.
FMenu.PopUpAtMe(Self, X,Y);
end;
Again, you will make three such changes.
If you have any further questions about this subject, please feel free to contact me at Mark.Shapiro@seguetech.com.
