About Field Names

The selection of field names for use in defining Measures and Dimensions is very important.

The field names you define in the O3 Designer can be independent of their names in the original datasources.

The mapping of these names to the actual fields in the datasources is made through a reference to the field's relative position in the source. This is for instance the ASCII column number, or the field position in the result set returned by a SELECT statement for a SQL query.

While this independence suggests freedom of choice, there are some implications in selecting field names.

Through the similarity of field names, O3 can make implicit joins between data from different sources, including combinations of SQL queries and text files. This capability allows the following:

• Extraction of the hierarchy levels for a dimension from different sources.
• Description associations for the dimension values from an additional source.
• Association of key nodes of the hierarchies and the registers data in the Measures sources.

These three cases are examined through the following examples.

Example I

Let's consider two Data Sources, each one with the following fields:

Suppose that you are creating a Salesperson dimension with the following hierarchy:

• Country
• State
• Branch
• Salesperson

To do this, you must associate the Country field to Level 1 of the dimension, the State field to Level 2, and so on. (See Defining Dimensions).

Because the necessary data for the creation of this hierarchy comes from two different Data Sources, the Salesperson definition and the Branches, you must indicate which field links the sources. This allows the join between the sources when you create the Cube. Use the same field name in each source. Thus, the same name, Branch, is given to the field representing the Branch code in both sources.

This example also requires the definition of the Unique Level to "Branch" in the dimension.

This notifies O3 that a Branch will only belong to a State thus defining it as the key allowing the join of both datasources.

Example II

Now consider the following Data Sources:

Suppose the hierarchy of Products Dimension is constituted by Products type. In this case, we assign the Product type field to Level 1 of the dimension and the Product field to Level 2.

• Product Type
• Product

Now, suppose that instead of its code, the description of the product displays for the user of the O3 Browser. You can do this by defining the Description field as the label of Level 2. (See Defining Dimensions).

Again, because the relationship between type and product do not come from the same source as the description, you must define the fields that represent the product code with the same name, Product, in both sources.This example also requires the definition of the Unique Level to "Product" in the dimension.

This notifies O3 that a Product will only belong to a Product Type thus defining it as the key allowing the join of both datasources.

Example III

Dimensions and measures are frequently defined by different Data Sources. If this is the case, you must define some kind of link between the dimension keys and the registers containing the measures.

The Measures Source providing the quantity measure for the definition of the model includes the Product and Customer codes. In addition, the Products and Customer sources allow the creation of the corresponding dimensions, by means of descriptions. The name similarity (Product and Customer) between the fields is required so that O3 Designer can assign Quantity values of the different registers to the dimensions.