Logo Passei Direto

A maior rede de estudos do Brasil

6 pág.
Using the Decision Cube without the BDE

Pré-visualização | Página 1 de 2

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.
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);
 bParsed := false;
 bDataSetMatch := false;
 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);
 // Here is where you will put the code for step 3.
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
 // Part A: Configure dimensions
 for nCount := 0 to [Number of Dimensions - 1] do
 Items[nCount].DimensionType := dimDimension;
 Items[nCount].BaseName := 'TABLE.FIELDNAME';
 //If the dimension is a date and should be grouped, use this code
 //If the dimension is not a date, or is a date but should not be grouped,use this code
 // Part B: Configure summaries
 for nCount:=[Number of Dimensions] to [Number of Fields-1] do
 Items[nCount].DimensionType := dimCount;
 // 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
 else if (p1='') then
 else if (p2='') then
 // Otherwise, use CompareStr,