Technical data

Using Built-in Functio ns in Analytic Models Chapter 11
3. MINIMUM_SALES_BY_PRODUCT, which uses the PRODUCTS dimension. C alculate this data
cube with the following formula:
GROUPMAX(TRANSACTIONS, SALE_AMOUNT, PRODUCT_SOLD)
You can read thi
s formula as follows: Find the maximum transactions’ sale amounts by product.
To calculate group maximums of all members that meet a condition, use an IF function as the expression,
with #N/A as the third argument. For example, use IF(VALID, SALE_AMOUNT, #N/A) instead of
SALE_AMOUNT in the formula above.
GROUPSUM
Syntax
GROUPSUM(DimensiontoGroup, Expression, Association 1, {Association 2 ...})
Description
Use the GROUPSUM function to sum information by group. Expression contains the data to sum. The
Association(s) indicate what group(s) to sum by.
Example
The followin
g examples provide uses of the GROUPSUM function:
Example 1
For example, suppose you want to sum employee salaries by department. Create an analytic model definition
that contains the following data cubes:
1. EMPLOYEE_SA
LARY, which uses a dimension called EMPLOYEES.
This data cub
e contains the salary for each employee.
2. DEPARTMENT_
SALARY, which uses a dimension called DEPARTMENTS.
This data cub
e contains the total salaries for each department.
3. An associati
on data cube called EMPLOYEE_DEPT, which associates each employee with a particular
department.
Calculate DEPARTMENT_SALARY with the following formula:
GROUPSUM(EMPLOYEES, EMPLOYEE_SALARY, EMPLOYEE_DEPT)
You can read this formula as follows: Sum the employees’ salaries by department.
Example 2
The GROUPSUM function can also perform more complex groupings. For example, suppose you want to sum
employee salaries by branch and department. To do this, perform the following additional steps:
1. Create a dimension called BRANCHES, which contains a dimension of the branches.
2. Create an association data cube called EMPLOYEE_BRANCH, which associates the EMPLOYEES
dimension with the BRANCHES dimension.
3. DefineadatacubecalledSALARY_BY_BRANCH_AND_DEPT, which uses both the BRANCHES and
DEPARTMENTS dimensions. Calculate this data cube with the following formula:
162 Copyright © 1988-2007, Oracl e. All rights reserved.