Oracle9i OLAP Developer's Guide to the OLAP API Release 2 (9.2) Part Number A9529701 

Making Queries Using Source Methods, 7 of 8
The NumberSource
class and its subclasses define methods that are numericspecific versions of various Source
methods that you can use to append, insert, select, and remove numeric values. The NumberSource
class and its subclasses also have methods that you can use to perform simple numerical operations such as subtraction and division, make numerical comparisons, perform standard numerical functions such as finding the absolute value of numbers, and aggregate values by summing values. You can also create your own functions to perform numerical analysis that is unique to your program.
Using the OLAP API you perform basic numeric operations using NumberSource
methods such as minus
. There are separate versions of each of these methods that you can use to specify a literal double
, float
, int
, or short
value. There is also a version of each of these method that takes a NumberSource
as an argument.
The OLAP API methods that you use to perform basic numeric operations include those outlined in Table 62.
Assume, as shown below. that there is a NumberSource
named unit_Cost
that has outputs of productsDim
and timesDim
and a type of Integer
.
productsDim  timesDim  unit_Cost 

Boys 
1998 
4000 
Boys 
31DEC01 
10 
49780 
1998 
500 
49780 
31DEC01 
9 
Now assume that you want to subtract 10% of the sales from each value of unit_Cost
to find the adjusted income for each product as shown in which creates a new Source
named percentAjustment
.
NumberSource percentAdjustment = unit_Cost.minus(unit_Cost.times(.10));
The new NumberSource
, named percentAdjustment
, has the following structure and values.
productsDim  timesDim  percentAdjustment 

Boys 
1998 
3600 
Boys 
31DEC01 
9 
49780 
1998 
450 
49780 
... 
... 
49780 
31DEC01 
8 
Assume that you have the NumberSource
named unitCost
described in the previous example and that you also have the NumberSource
named unitManufacturingCost
shown below.
productsDim  timesDim  unit_Cost values 

Boys 
1998 
600 
Boys 
31DEC01 
3 
49780 
1998 
250 
49780 
31DEC01 
2 
Now assume that you want to calculate the nonmanufacturing for each product. To do this you need to subtract the manufacturing costs from the unit costs. To do this you use the following code which creates a new Source
named nonManufacturingCost
by performing the operation on unit_Cost
.
NumberSource nonManufacturingCost = unitCost.minus(unitManufacturingCost);
nonManufacturingCost has the structure and values shown below.
productsDim  timesDim  values 

Boys 
1998 
3400 
Boys 
31DEC01 
7 
49780 
1998 
250 
49780 
31DEC01 
7 
For a more complete explanation of these methods, see OLAP API Javadoc.
The NumberSource
class has a number of methods make numerical comparisons. These methods compare each value in a NumberSource
to a specified value. These methods return a BooleanSource
that has the same structure as the original NumberSource
and that has an value that is true when the comparison for a given value of the original NumberSource
is true, or false when the comparison is false. There are separate versions of each of these methods that you can use to specify a literal double
, float
, int
, or short
value.
The numerical comparison methods provided with the OLAP API include those listed in Table 63. For a more complete explanation of these methods, see the OLAP API Javadoc.
The OLAP API has many methods that represent standard numerical functions. These methods include those listed in Table 64. You can also write your own functions as described in "Creating Your own Numerical Functions".
When you use these functions with a NumberSource
, they return a new NumberSource
that has the same structure as the original NumberSource
and whose values are the values of the original NumberSource
modified according to the function. For example, the abs()
method returns a new NumberSource
each of whose values has the absolute value of the corresponding value in the original NumberSource
.
Standard numerical methods like stdev()
work on each value in a NumberSource
. An aggregation method is a method like total()
that uses the values in a series of Source
values to perform its calculations. The way that Oracle OLAP processes an aggregation function varies depending on whether or not the base NumberSource
has inputs:
NumberSource
does not have any inputs, an aggregation function creates a new NumberSource
, without any outputs or inputs, with a single value that is calculated using all of the values in the base NumberSource
. (See "Calculating the Sum When a Source Has only Outputs: Example" for an example.)NumberSource
has inputs, each set of output values identifies a subset of values (tuples). In this case, an aggregation method works on each subset of data. The aggregation function creates a new NumberSource
, without the same outputs as the base NumberSource
, with one value for each set of output values. These values are calculated using all of values in the base NumberSource
identified by that set of output values. (See "Calculating the Sum When a Source Has only Outputs: Example" for an example.)The numerical aggregation methods provided by the OLAP API include the methods in Table 65. You can also write your own aggregation functions as described in "Creating Your own Numerical Functions".
There are two different versions of each of the numerical aggregation methods. One version excludes all null values when making its calculations. The other version allows you to specify whether or not you want null values included in the calculation.
For more information on how OLAP API methods determine the position of an value and therefore how they determine what values to use when calculating the values of aggregation methods, see Finding the Position of Values.
Assume that you have the Source
named unitsSoldByCountry
that has two outputs (products
and countries
) and whose values are the total number of units for each product sold for each country.
products (output2)  countries (output1)  values of unitsSoldByCountry 

395 
Australia 
1300 
395 
United States 
800 
49780 
Australia 
10050 
49780 
United States 
50 
Now assume that you want to total these values. Since both products
and countries
are outputs, when you issue the code shown below, the new NumberSource
calculates the total number of units sold for all products in all countries.
NumberSource totalUnitsSold = unitsSoldyByCountry.total();
The new NumberSource
called totalUnitsSold
has only a single value that is the total of the values of unitsSoldByCountry
.
value of totalUnitsSold 

11350 
Assume that you have the Source
named unitsSoldByCountry
that has an output of countries
and an input of products
and whose values are the total number of units for each product sold for each country.
countries (output)  values of unitsSoldByCountry 

Australia 
10050 
United States 
800 
Now assume that you total these values. Since product
is input, when you issue the code shown below, the new NumberSource
calculates the total number of units sold for all products in each country;. It does not calculate the total for all products in all countries.
NumberSource totalUnitsSoldByCountry = unitsSoldByCountry.total();
The new NumberSource
called totalUnitsSoldByCountry
has an output of countries
and values shown below.
countries (output)  values of unitsSoldByCountry 

Australia 
11350 
United States 
850 
The alias method can be used to create parameters. Example 628, "Creating a Standard Function" shows how to create a new function using the alias
method. You can only create cell or row calculation functions in this way. To create client aggregation or positionbased functions you use the extract
method.
Example 628 creates a function that takes a number and multiplies it by 1.05. The function has one parameter, called param
, which is created by calling the alias
method on the fundamental Source
representing the Number OLAP API data type which is the set of all numbers. (Note how the value
method is used to make the parameter an input of the function.) The function created in Example 628 is effectively the same as the builtin functions provided by the OLAP API. It can be used by joining the function to the parameter and the required parameter expression.
Assume you want to create a product selection defined to be the set of all products for which the unitsSold
measure is greater than the value specified by a parameter. The parameter must be specified before data can be fetched from this Source
. You can create this parameter as shown in Example 629. To set the value of the parameter to 100, you use the code shown in Example 630. You can then apply the function created in Example 628 to a Source
named sales
as shown in Example 631.
//Get the Source that represents the number data type NumberSource number =(NumberSource)dataProvider .getFundamentalDefinitionProvider() .getNumberDataType() .getSource(); //Create a parameter NumberSource param = (NumberSource)number.alias(); //Create a function NumberSource function = ((NumberSource)param.value()).times(1.05);
//Get the Source that represents the number data type NumberSource number = dataProvider .getFundamentalDefinitionProvider() .getNumberDataType() .getSource(); //Create a parameter NumberSource param = (NumberSource)number.alias(); //Create a parameterized selection Source products = ...; NumberSource unitsSold = ...; Source productSelection = products.select(unitsSold.gt(param.value()));
Source unitsSoldGT100 = productSelection.join(param, 100);
//Use the function NumberSource sales = ...; NumberSource fsales = function.join(param, sales);
Assume that you want to create a weighted average function. To do so, you write the code shown in Example 632, "Creating a Weighted Average Function". As with the example of a standard function Example 628, "Creating a Standard Function", this code first creates a parameter named param
for the function to use. However, since this is an aggregation function, the code uses the extract()
method with param
when it calculates the final result.
To use the weighted average function created in Example 632, you issue the code shown in Example 633.
//Define an aggregation function NumberSource weight = ...; //Create a parameter NumberSource param = (NumberSource) number.alias(); //Create a function NumberSource weightedAverage = param.extract().times(weight).average();
//Use the aggregation function NumberSource sales = ...; NumberSource paramSales = dp.createConstantSource(param.selectValues(sales)); Source weightedSales = weightedAverage.join(paramSales);

Copyright © 2000, 2002 Oracle Corporation. All Rights Reserved. 
