About Virtual Fields

In addition to the different data sources supported by O3, it is possible to define whar are called Virtual Fields.

Virtual Fields are defined in the Properties Pane for the Sources element in the Design Tree.

As sugested by theri name, virtual fields can be used in the model like any other field from the datasources even though they do not correspond to an actual field in a datasource.

Examples of Virtual Fields usage are:

• Obtain measures not in the sources
• Include complex calculations
• Create levels for hierarchies and labels for the values

Virtual fields are calculated for each record coming from data sources during cube creation. Once the calculation is complete, the virtual field is available for access and there is no distinction between it and a basic field.

These fields are defined through expressions which can include:

• Fields from the data sources
• Fields from local tables
• Specified operators and functions

You can define each virtual field as one of the following types:

• Integer
• Double
• String
• Date

Note
You cannot use a virtual field in the expression of another virtual field.

Virtual fields are not related to a specific data source, but can be used by all data sources. This allows the definition of virtual fields available for more than one source.

To determine which data sources in the model the virtual fields will be added to, during the build process the global list of all virtual fields is verified in each source.

The expression defining each field is analyzed and if all fields referenced are available from the source, the virtual field is added to the records obtained.

If you define a virtual field with a constant expression, it is added to all the model's sources.

Note
If you define a virtual field, Sample2 = Sample1*0.10, then the Sample2 is added to the records of all the sources that include a Sample1 field.
If any of the sources with Sample1 already contain a basic Sample2 field, the virtual field, Sample2, does not overwrite it.

Defining Virtual Fields:

1. Select the Sources branch in the Design tree. The property pane displays with the Virtual Fields page.
2. Choose the New Field button. A new field inserts at the end of the virtual field list.
3. Double-click in the name field.
4. Enter the virtual field name.
5. Double-click in the Definition field.
6. Enter the expression.
7. Double-click in the Type field. A list of available types displays.
8. Choose a type from the list.

Examples of Virtual Field Use

Following are four examples of using virtual fields. They represent just a sample of the practical application of virtual fields.

Example 1

A data source contains the following basic fields:

• GrossSale
• Discount

You can define a virtual field as

NetSale = (GrossSale - Discount)

Then, the NetSale virtual field can be used in the definition of a new Measure in the model.

Example 2

A data source contains records of the following structure:

with TypeOfClient being one of

• Major accounts
• Medium-sized accounts
• Small accounts

You can define a virtual field that allows the user to define a measure called Commission in the model:

Commission = (TypeOfClient == "Major Accounts")
? (GrossSale * 0.05)
: (GrossSale * 0.1)

Example 3

The two previous examples are oriented towards virtual field calculations for use in the Measures definition of the model. You can also define virtual fields that are subsequently used in building dimensions.

The Date dimension included in O3 Designer allows the generation of hierarchies of Year, Quarter, Month, Week, and Day types. The following describes the ability to define hierarchies for the dates that handle other hierarchy types using virtual fields.

The record is similar to Example 2:

Then, You can define the following virtual fields:

Year = Year(Date)
Month = MonthName(Date)

and a PerMonth dimension with the following hierarchy:

• Month
• Year

Using these, you can compare the amounts sold per month in the different years. This is not possible with a Date-type hierarchy.

Example 4

You can also use virtual fields as a mechanism to create labels from more than one basic field for the O3 Browser end user.

A data source contains records with the following structure:

You can define a description for the product which includes its code and label with a virtual field such as:

ProdDescrip = Concatenate(ProductCode, Concatenate(" - ", ProductLabel))

Ejemplo V

Another typical example for Virtual Fields is to generate user friendly labels for information coded in the database.

Suppose for instance a datasource that codes a "Gender" field as "F" or "M" por Feminine and Masculine respectively.

Building a datamart directly with that field would result in a poorly formatted dimension. In order to achieve a better solution it would be possible to create the following Virtual Field.

GenderDesc = (Gender == "M") ? "Masculine" : "Feminine"