Making Queries Using Source Methods, 7 of 8

Performing Numerical Analysis

The NumberSource class and its subclasses define methods that are numeric-specific 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.

Performing Numerical Operations

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 6-2.

Table 6-2 OLAP API Methods that Perform Basic Numeric Operations

Method	Description
`div(rhs)`	Creates a new `NumberSource` that has the same structure as the base `NumberSource` but whose values are the values of the base `NumberSource` divided by the specified value.
`intpart(`)	Creates a new `NumberSource` that has the same structure as the base `NumberSource` but whose values are the integer portion of the values of the base `NumberSource`.
`minus(rhs)`	Creates a new `NumberSource` that has the same structure as the base `NumberSource` but whose values are the values of the base `NumberSource` minus the specified value.
`negate()`	Creates a new `NumberSource` that has the same structure as the base `NumberSource` but whose values are the values of the base `NumberSource` negated.
`plus(rhs)`	Creates a new `NumberSource` that has the same structure as the base `NumberSource` but whose values are the values of the base `NumberSource` plus the specified value.
`rem(rhs)`	Creates a new `NumberSource` that has the same structure as the base `NumberSource` but whose values are the remainders of the values of the base `NumberSource` when they are divided by the specified value.
`times (rhs`)	Creates a new `NumberSource` that has the same structure as the base `NumberSource` but whose values are the values of the base `NumberSource` multiplied by the specified value.

Subtracting the Same Value From all Values: Example

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	31-DEC-01	10
49780	1998	500
49780	31-DEC-01	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.

Example 6-24 Subtracting the Same Value from all Values

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	31-DEC-01	9
49780	1998	450
49780	...	...
49780	31-DEC-01	8

Subtracting the Values of one NumberSource from Another: Example

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	31-DEC-01	3
49780	1998	250
49780	31-DEC-01	2

Now assume that you want to calculate the non-manufacturing 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.

Example 6-25 Subtracting the Values of one NumberSource from Another

NumberSource nonManufacturingCost = unitCost.minus(unitManufacturingCost);

nonManufacturingCost has the structure and values shown below.

productsDim	timesDim	values
Boys	1998	3400
Boys	31-DEC-01	7
49780	1998	250
49780	31-DEC-01	7

For a more complete explanation of these methods, see OLAP API Javadoc.

Making Numerical Comparisons

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 6-3. For a more complete explanation of these methods, see the OLAP API Javadoc.

Table 6-3 Numerical Comparison Methods

Method	Description
`eq`	Creates a new `BooleanSource`, with the same outputs and inputs as the base `NumberSource`, and with a value of `true` for each value of the `NumberSource` that is equal to the specified value and a value of `false` for each value of the `NumberSource` that is not.
`ge`	Creates a new `BooleanSource`, with the same outputs and inputs as the base `NumberSource`, and with a value of `true` for each value of the `NumberSource` that is greater than or equal to the specified value and a value of `false` for each value of the `NumberSource` that is not.
`gt`	Creates a new `BooleanSource`, with the same outputs and inputs as the base `NumberSource`, and with a value of `true` for each value of the `NumberSource` that is larger than the specified value and a value of `false` for each value of the `NumberSource` that is not.
`le`	Creates a new `BooleanSource`, with the same outputs and inputs as the base `NumberSource`, and with a value of `true` for each value of the `NumberSource` that is lesser than or equal to the specified value and a value of `false` for each value of the `NumberSource` that is not.
`lt`	Creates a new `BooleanSource`, with the same outputs and inputs as the base `NumberSource`, and with a value of `true` for each value of the `NumberSource` that is less than the specified value and a value of `false` for each value of the `NumberSource` that is not.
`ne`	Creates a new `BooleanSource`, with the same outputs and inputs as the base `NumberSource`, and with a value of `true` for each value of the `NumberSource` that is not equal to the specified value and a value of `false` for each value of the `NumberSource` that is equal.

Working with Standard Numerical Functions

The OLAP API has many methods that represent standard numerical functions. These methods include those listed in Table 6-4. 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.

Table 6-4 Methods that Represent Standard Numerical Functions

Method	Description
`abs()`	Creates a new `NumberSource`, with the same outputs and inputs as the base `NumberSource`, whose values are the absolute value of each value of the base `NumberSource`.
`arccos()`	Creates a new `NumberSource`, with the same outputs and inputs as the base `NumberSource`, whose values are the angle value (in radians) of the value (interpreted as a cosine) of each value of the `NumberSource`.
`arcsin()`	Creates a new `NumberSource`, with the same outputs and inputs as the base `NumberSource`, whose values are the angle value (in radians) of the value (interpreted as a sine) of each value of the `NumberSource`.
`arctan()`	Creates a new `NumberSource`, with the same outputs and inputs as the base `NumberSource`, whose values are the angle value (in radians) of the value (interpreted as a tangent) of each value of the `NumberSource`.
`cos()`	Creates a new `NumberSource`, with the same outputs and inputs as the base `NumberSource`, whose values are the cosine of the value (interpreted as an angle value in radians) of each value of the `NumberSource`.
`cosh()`	Creates a new `NumberSource`, with the same outputs and inputs as the base `NumberSource`, whose values are the hyperbolic cosine of the value (interpreted as an angle value in radians) of each value of the `NumberSource`
`log()`	Creates a new `NumberSource`, with the same outputs and inputs as the base `NumberSource`, whose values are the natural logarithm of each value of the `NumberSource`.
`pow(rhs)`	Creates a new `NumberSource`, with the same outputs and inputs as the base `NumberSource`, whose values are each value of the `NumberSource` raised to the specified value.
`round(multiple)`	Creates a new `NumberSource`, with the same outputs and inputs as the base `NumberSource`, whose values are each value of the `NumberSource` rounded to the nearest multiple of the specified value.
`sin()`	Creates a new `NumberSource`, with the same outputs and inputs as the base `NumberSource`, whose values are the sine of the value (interpreted as an angle) of each value of the `NumberSource`.

Working with Aggregation Methods

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:

When the base 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.)
When the base 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 6-5. You can also write your own aggregation functions as described in "Creating Your own Numerical Functions".

Table 6-5 Aggregation Methods

Method	Description When the NumberSource Does Not Have Inputs
`average`	Creates a new `NumberSource`, without any outputs or inputs, whose value is the average of the values of a `NumberSource`.
`maximum`	Creates a new `NumberSource`, without any outputs or inputs, whose value is the largest value of a `NumberSource`.
`minimum`	Creates a new `NumberSource`, without any output or inputs, whose value is the smallest value of a `NumberSource`.
`total`	Creates a new `NumberSource`, without any outputs or inputs, whose value is the sum of the values of a `NumberSource`.

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 .

Calculating the Sum When a Source Has only Outputs: Example

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.

Example 6-26 Calculating the Sum of Values When a Source has only Outputs

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

Calculating the Sum When a Source Has an Output and an Input: Example

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	1300 10050
United States	50 800

countries (output)

values of unitsSoldByCountry

Australia

1300

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.

Example 6-27 Calculating the Sum of Values When a Source has Outputs and Inputs

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

Creating Your own Numerical Functions

The alias method can be used to create parameters. Example 6-28, "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 position-based functions you use the extract method.

Creating Your own Standard Function: Example

Example 6-28 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 6-28 is effectively the same as the built-in 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 6-29. To set the value of the parameter to 100, you use the code shown in Example 6-30. You can then apply the function created in Example 6-28 to a Source named sales as shown in Example 6-31.

Example 6-28 Creating a Standard Function

//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);

Example 6-29 Creating a Parameterized Selection

//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()));

Example 6-30 Setting the Value of the Parameter

Source unitsSoldGT100 = productSelection.join(param, 100);

Example 6-31 Using a Standard Function You Created

//Use the function
NumberSource sales = ...;
NumberSource fsales = function.join(param, sales);

Creating Your own Aggregation Function: Example

Assume that you want to create a weighted average function. To do so, you write the code shown in Example 6-32, "Creating a Weighted Average Function". As with the example of a standard function Example 6-28, "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 6-32, you issue the code shown in Example 6-33.

Example 6-32 Creating a Weighted Average Function

//Define an aggregation function
NumberSource weight = ...;
//Create a parameter
NumberSource param = (NumberSource) number.alias();
//Create a function
NumberSource weightedAverage = param.extract().times(weight).average();

Example 6-33 Using the Weighted Average Function Created in Example 6-32

//Use the aggregation function
NumberSource sales = ...;
NumberSource paramSales = dp.createConstantSource(param.selectValues(sales));
Source weightedSales = weightedAverage.join(paramSales);