Technical data

ManualsBrandsSimplicity ManualsDo-It-Yourself tools4564

181

182

183

184

185

186

187

188

189

190

Chapter 11 Using Built-in Functions in Analytic M odels

GROUPSUM(EMPLOYEES, EMPLOYEE_SALARY, EMPLOYEE_BRANCH, EMPLOYEE_DEPT)

You can read thi

s formula as follows: Sum the employees’ salar ies by branch and department.

As this example demonstrates, you can summarize detail information for a combination of dimensions by

using an association for each dimension.

Example 3

In many cases,

it is useful to summarize information by date. In these cases, use a data cube with a Date

format instea

d of an association.

For example, suppose you want to summarize sales information by product and month. Create an analytic

model definition that contains the following dimensions:

1. TRANSACTIONS, which contains a series of sales transactions.

2. PRODUCTS, which contains a dimension of products.

3. MONTHS, which contains a series of months.

Define the fol

lowing data cubes:

1. SALE_AMOUNT, which uses the TRANSACTIONS dimension.

This data cube contains the amount of each sale.

2. An association data cube called PRODUCT_SOLD, which associates TRANSACTIONS with

PRODUCTS.

3. SALE_DATE, which uses the TRANSACTIONS dimension and the YYYY/MM/DD format. This

data cube contains the date for each transaction.

4. MONTHLY_SALES, which uses the PRODUCTS and MONTHS dimension. Calculate this data cube

with the following formula:

GROUPSUM(TRANSACTIONS, SALE_AMOUNT, PRODUCT_SOLD, SALE_DATE)

You can read this formula as follows : Sum the transactions’ sale amounts by product and sale date. Because

SALE_DATE is Date formatted (YYYY/MM/DD), the GROUPSUM function knows to sum by date.

To calculate

group sums of all members which meet a condition, use an IF function as the expression,

with #N/A as t

he third argument. For example, use IF(Valid, SALE_AMOUNT, #N/A) instead of

SALE_AMOUNT

in the MONTHLY_SALES formula above.

GROW

Syntax

GROW(Dimens

ion, Start Value, Growth Rate)

Descriptio

The GROW function returns a number representing the specified Growth Rate per Member from Start Value.

This is a straight line growth function.

Example

Suppose that an analytic model contains single value data cubes called SALES_START and

ANNUAL_GROWTH. You can project the monthly sales with the following formula: