Friday, September 12, 2008

Drill into DB2 UDB Tables for Decision Support Using Delphi Studio or C++Builder Studio

Original Source click here

Level: Introductory

Bob Swart (drbob@chello.nl), Trainer and Consultant, Bob Swart Training and Consultancy

24 Apr 2003

This article demonstrates how you can build decision-support functionality by drilling into IBM DB2 Universal Database tables using the Decision Cube component of Borland Delphi Studio or Borland C++Builder Studio.
Show developerWorks content related to my search: DelphiShow developerWorks content related to my search: Delphi


Introduction

In this article, I will demonstrate how you can build decision-support functionality by drilling into IBM DB2® Universal DatabaseTM tables using the Decision Cube component of Borland DelphiTM Studio or Borland C++Builder® Studio (but not available in Borland KylixTM, for reasons that will become clear in a moment).

Note that you'll use Borland dbExpressTM again to connect to the DB2 UDB Personal Edition v7.x database, so make sure you've read the previous articles that describe how to connect from Delphi or C++Builder.

Note: When I write about Delphi in this article, you can also substitute C++Builder and perform the same steps. When showing the source code, I will provide both Delphi source code and C++(Builder) code snippets.



Back to top


Examine Decision Cube Components

You can find the Decision Support components of Delphi and C++Builder on the Decision Cube tab of the Component Palette (see Figure 1).


Figure 1. Decision Cube components
Decision Cube components.

From left to right, the components are TDecisionCube, TDecisionQuery, TDecisionSource, TDecisionPivot, TDecisionGrid, and TDecisionChart. TDecisionCube does all the work (in memory) based on the results of the query that you can build with TDecisionQuery. You use the TDecisionSource component to connect a TDecisionCube component to visual components such as TDecisionPivot, TDecisionGrid, or TDecisionChart. You can use TDecisionPivot to activate dimensions and summaries (the values displayed) that are shown using TDecisionGrid and TDecisionChart.



Back to top


Don't use TDecisionQuery

Back when these components were first released, using the Borland Database Engine (BDE) and SQL Links was the recommended way to connect to databases and tables. But since last year, the BDE has been frozen and SQL Links even deprecated, so now the recommended way to connect is using dbExpress (which I've been using to connect to DB2 UDB in my previous articles). Unfortunately, the TDecisionQuery component is still tied to the BDE, so you cannot use that component to build your decision support query. This is not a big problem, because it's not too hard to build your own query as long as you follow a few simple rules.

The fact that TDecisionQuery is tied to the Borland Database Engine (which is available only on Windows®) is also the reason that this technology is not supported in Kylix on Linux.



Back to top


Build the SQL connection

To build the example application, start Delphi and drop TSQLConnection, TSQLDataSet, TDataSetProvider, and TClientDataSet components. Although the Decision Cube is only reading (drilling into) data, it needs to walk through the dataset. So the read-only, unidirectional TSQLDataSet won't suffice on its own, which is why you also need the TDataSetProvider and TClientDataSet components.

Set TSQLConnection's ConnectionName property to DB2Connection. Also make sure to specify the right values in the connection parameters, such as database name, user_name, and password. Point the SQLConnection property of TSQLDataSet to the TSQLConnection component and verify that the CommandType property is set to ctQuery. You must now construct an SQL query that will feed the Decision Cube component.



Back to top


Build the SQL query

If you click on the ellipsis for the CommandText property from the TSQLDataSet component, you get the Query CommandText Editor (see Figure 2), where you can see which tables and fields are available. In the previous articles, you used the EMPLOYEE table, so you'll focus on this table again. The EMPLOYEE table has 14 fields: EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, and COMM.

The field values you can use as dimensions are WORKDEPT, JOB, EDLEVEL, and SEX. The field values you can use as summaries (display values) are the count of EMPNO (to get the actual number of employees), sum of SALARY, sum of BONUS, and sum of COMM. Because you have the count of EMPNO, you can also view the sum values as average values (sum divided by count) as you'll see in a moment.

Fields used as dimensions must appear as first fields in the SELECT clause, and must also be used-in the same order-in the GROUP BY clause at the end of the query. This applies to the WORKDEPT, JOB, EDLEVEL, and SEX fields. The summary fields appear after the dimension fields in the SELECT clause, which results in this query:

                  SELECT 
workdept, job, edlevel, sex,
count(empno), sum(salary), sum(bonus), sum(comm)
FROM
employee
GROUP BY
workdept, job, edlevel, sex

Note that if you need to join two or more tables-if you also want to include fields from the EMP_ACT, EMP_PHOTO, or EMP_RESUME table, for example-you need to perform an INNER JOIN.


Figure 2. Query CommandText Editor
command text editor.

Test the query by setting TSQLDataSet's Active property to True to verify that you didn't make any typing mistakes.



Back to top


Calculate fields

Double-click on the TSQLDataSet component to get the Fields Editor. Right-click in the Fields Editor and select Add All Fields to create persistent fields for the query you just specified. This results in eight fields (see Figure 3): four with normal names (WORKDEPT, JOB, EDLEVEL, and SEX) and four with numbers only (5, 6, 7, and 8). The last four fields are the count and sum fields.


Figure 3. Fields Editor
Fields Editor.

Using the fields like this would be no problem, but it appears that fields 6, 7, and 8 are of type BCD (Binary-Coded Decimal), which the Decision Cube doesn't support. (The Decision Cube supports integers, strings, and floats.) This means you have to replace fields 6, 7, and 8 with some new fields of type Float that will contain the Float representations of these BCD values instead. And while you're doing that, you can even restructure the information a bit, since you're mainly interested in the sum of the SALARY alone as well as the sum of the SALARY plus BONUS and COMM.

Right-click in the Fields Editor, select New Field, enter the name SumOfSalary in the dialog, specify the type Float, and select the Calculated Field type (see Figure 4).


Figure 4. Add new calculated field SumOfSalary
Add new calculated field SumOfSalary.

Do the same thing for SumOfTotalSalary. Now, select the TSQLDataSet component again, go to the Events tab of the Object Repository, and double-click on the OnCalcFields event handler to write this code:

                  procedure TForm1.SQLDataSet1CalcFields(DataSet: TDataSet); 
begin


with DataSet do


begin
FieldByName('SumOfSalary').AsFloat := FieldByName('6').AsFloat;
FieldByName('SumOfTotalSalary').AsFloat := FieldByName('6').AsFloat +
FieldByName('7').AsFloat + FieldByName('8').AsFloat
end


end;

For C++Builder, use this C++ source code:

 
DataSet->FieldByName("SumOfSalary")->AsFloat = DataSet->FieldByName("5")->AsFloat;
DataSet->FieldByName("SumOfTotalSalary")->AsFloat =
DataSet->FieldByName("5")->AsFloat + DataSet->FieldByName("6")->AsFloat +
DataSet->FieldByName("7")->AsFloat;



Back to top


Connect the ClientDataSet

When you can activate the SQL Query specified by the TSQLDataSet component without problems, you can connect TDataSetProvider's DataSet property to the TSQLDataSet component, and finally TClientDataSet's ProviderName property to TDataSetProvider. This will make sure that the unidirectional data from the dbExpress dataset (pointing to the DB2 SAMPLE database) is retrieved and placed into the TClientDataSet that holds all selected records in memory-including the calculated fields from the TSQLDataSet component that had to be "calculated" only once to fill the TClientDataSet.

Double-click on the TClientDataSet component to start the Fields Editor, but this time on the ClientDataSet. Right-click in the Fields Editor and select Add Fields (not Add All Fields). Make sure to select all fields except fields 6, 7, and 8, which will be replaced by the fields SumOfSalary and SumOfTotalSalary (see Figure 5).


Figure 5. Fields Editor
Fields Editor.

As a helpful step to better format the output (which will appear in the TDecisionGrid shortly), select the SumOfSalary and SumOfTotalSalary fields and set their DisplayFormat properties to "0,000." This indicates that you don't want to see any scientific E-notations or any digits behind the decimal separator, but that you do want to see a thousands separator.



Back to top


Connect the Decision Cube

Now, drop a TDecisionCube component on the form, and point its DataSet property to the TClientDataSet component. This immediately results in an error message saying you must help the Decision Cube determine which fields are dimensions and which fields are summaries (see Figure 6). (The TDecisionQuery component could do this automatically, but as I mentioned, you cannot use that component anymore because it's BDE-specific.)


Figure 6. Dimension types cannot be determined automatically
error message

Right-click on the TDecisionCube component to start the Decision Cube Editor that lists all eight fields (see Figure 7).


Figure 7. Decision Cube Editor: Dimension Settings
dimension settings

Set the Type to Dimension for WORKDEPT, JOB, EDLEVEL, and SEX. If you want to activate dimensions such as the JOB dimension, you can do so here at design time by setting the Active Type to Active.

Set the Type to Count for the field called "5," and set the Type to Sum for the fields SumOfSalary and SumOfTotalSalary. Select the "5" field again, then set its Display Name to "Count" and its Active Type to "Active."

You can see how much (active or needed) dimensions and summaries have been defined and get a feel for how many cells will be generated in memory by clicking on the Memory Control tab (see Figure 8). The Decision Cube performs all operations in memory, so make sure that you're not generating and using millions of cells-that will slow down your machine and possibly crash it if not enough memory is available. Each dimension will grow the number of cells exponentially, based on the number of dimension values; for example, adding a new dimension that has 12 discrete values will result in 12 times more cells. To reduce the cell count, simply avoid having more than a few dimensions.


Figure 8. Decision Cube Editor: Memory Control
memory control


Back to top


Visualize the results

To build the visual presentation layer, drop a TDecisionSource component on the form and connect its DecisionCube property to the TDecisionCube component. You can now drop and connect visual Decision Cube components to this TDecisionSource.

Drop a TDecisionPivot on the form, connect its DecisionSource property to the TDecisionSource component, and set its Align property to alTop. Drop a TDecisionChart component, connect its DecisionSource property to the TDecisionSource component, and set its Align property to alBottom. In the space between them, first drop a TSplitter component from the Additional tab, and set this component's Align property to alBottom as well. Now, drop a TDecisionGrid component in the open space, connect its DecisionSource property to the TDecisionSource component, and set its Align property to alClient.

You can tinker with some of the properties, especially TDecisionGrid. Change its DefaultColWidth property from 100 to 64, and its DefaultRowHeight from 20 to 18. At design time, the form should now resemble Figure 9 (note that I have closed the tables at design time, and will open them at runtime when the form is created).


Figure 9. Decision Cube components at design time
components at design time

The advantage of connecting only at runtime is that you can load the example project in your copy of Delphi or C++Builder without a DB2 SAMPLE database running on your machine:

                  procedure TForm1.FormCreate(Sender: TObject); 
begin
ClientDataSet1.Active := True
end;

For C++Builder, here's the code for the form's OnCreate event handler:

                void __fastcall TForm1::FormCreate(TObject *Sender) 
{
ClientDataSet1->Active = true;
}

Obviously, you should make sure that the TClientDataSet and TSQLDataSet components have their Active properties set to False at design time, and that the Connected property of the TSQLConnection component is also set to False.



Back to top


Drill into data

It's time to save the application and compile it. If you run it, you'll notice a quick small form that shows a progress bar while the Decision Cube is loading the data. If you don't want to see this (or don't want to show this to your customers), set the ShowProgressDialog property of the TDecisionCube component to False.

An important thing about using the Decision Cube component is that it adds decision-support capabilities to your applications that the end user can use and interpret. You did the groundwork by selecting the dimensions and summaries, but the end user can now use the resulting application to select one or more of these dimensions, drill into dimension values, and select summaries to get all kinds of overviews and insightful relations.

For your example EMPLOYEE table, you can display the number of employees per department with their specific jobs using the JOB and WORKDEPT dimensions to show the COUNT summary (see Figure 10).


Figure 10. Decision Cube at runtime
decision cube at runtime.

By selecting the SumOfSalary summary in the upper-left corner, you suddenly see how much money the company and each department pays in SALARY costs (see Figure 11). If you want to see the BONUS and COMM as well, select the SumOfTotalSalary summary.


Figure 11. Decision Cube at runtime, a different view
different view.

As you can see, the combined salaries for the managers ($243,995) takes up about 30 percent of the salary cost of the entire company ($844,465).

You can also get an overview of the jobs against the education level to see if someone with a higher education level doing the same job gets paid more, as well as which jobs are best paid if you have a certain education level. Note that in order to compare the salaries, you have to select the "Average of" summary, which you get automatically by dividing the first sum (the SumOfSalary) by the first count (the number of employees).

An additional dimension to drill into the sex of the employee shows that female managers typically earn less than their male counterparts. Drilling into the JOB dimension (showing only the cells for which JOB equals manager), you can see more clearly the relationship between education level and average salary for male and female managers, as well as the fact that the males appear to get paid more for some reason (I must stress that this is just example data from the DB2 SAMPLE database).



Back to top


Conclusion

In this article, I've demonstrated how you can use the Decision Cube components to add decision-support functionality and capabilities to your Delphi and C++Builder applications, based on database tables such as the EMPLOYEE table from the DB2 SAMPLE database.

The examples that I showed were just a few of the possible relationships that you can get out of the data in the DB2 SAMPLE database-it's always up to the end user to activate dimensions, map dimensions to others, select summaries, and so on to get a better insight into the data and information enclosed within it.




Back to top


0 Comments: