Calculated fields. Using TOLAPCube.OnCalculateFields event
Posted by Ivan Pashkov on 11 June 2007 04:18 PM
It often happens that databases hold data in the format that is convenient for storing but not for a user. To let data easily convert from one format to another the TOLAPCube supports calculated fields. A calculated field is the one whose value is comprised from other fields’ values of the same or some other related table. Calculated fields allow a number of typical for multidimensional Cube desing tasks to be performed.
The Types of Calculated Fields
RadarCube allows two types of calculated fields: auto-calculated and event-calculated fields. Event calculated fields can be calculated by any algorithm in the TOLAPCube.OnCalculateField event handler. Auto-calculated fields can be calculated without this event using the value of another field and the format mask.
The calculated fields play the main role when defining the calculated hierarchy or measure for the Cube and so they can be created when defining the structure of the Cube with the methods AddCalculatedColumn, AddCalculatedMeasure or AddCalculatedHierarchy. There are two parameters in each of these methods that can be omitted: SourceColumnName and SourceColumnFormat. If both these parameters are left empty then the event-calculated field, whose value must be calculated in the TOLAPCube.OnCalculateField event handler, will be created (see below). If the SourceColumnName parameter is passed to the method then the auto-calculated field is created. The value of this field is converted automatically from the field given as SourceColumnName parameter. If the source field type implements the IFormattable interface then the conversion is done by the IFormattable.ToString() method with the SourceColumnFormat parameter as the format of conversion. If the source field type doesn't implement IFormattable then the objects ToString() method is called instead. For example the following code adds the auto-calculated field "Year" in the "Orders" table whose values are calculated from the "OrderDate" field:
cube.AddCalculatedHierarchy("Time", d.Orders, typeof(string), "Year", "OrderDate", "yyyy");
The source field can be really located in another table, related to the current with "one-to-many" relation. If so the SourceColumnName may be prefixed with the source table name: <TableName>.<FieldName>.
The essencial part of the calculated fields is the TOLAPCube.OnCalculateField event handler. This event is fired every time a record is read from a source table. When the event is fired it means the following conditions have already been fulfilled:
As a singe parameter the TCalculateFieldArgs object is passed to the event handler. It provides access to any field of any table related to the current one (i.e. that is being read at the time the event is fired). Here are the main three properties of TCalculateFieldArgs class:
These classes are described in details in related article, here we'll just give an example of the event handler.
private void cube_OnCalculateField(object Sender, TCalculateFieldArgs e)
In this example we calculate the "Employee Name" field in the "Employees" table and the "Sales" field in the "Order Details" table. Also in the "Order Details" table we get the master row from the related table "Orders" and fetch the "OrderDate" field from the related record. There is just one thing left to say about the TOLAPCube.OnCalculateField event. Though it is designed to give values to calculated fields, it can be used however to change the values of any other field, not necessarily a calculated one. In this regard the calculated fields are no different from all others. However this event is never fired for the tables that don't have even a single calculated row in them. So if you want to use this event to change the field values "on the fly", you have to provide at least one calculated field (for example with AddCalculatedField method).