Technical data

ManualsBrandsSimplicity ManualsDo-It-Yourself tools4564

211

212

213

214

215

216

217

218

219

220

Chapter 11 Using Built-in Functions in Analytic M odels

Example

The following s

ections provide examples of analyzing a historical trend and analyzing a relationship between

data cubes.

Example 1: Analyzing a Historical Trend

Suppose that you want to analyze the trend in historical sales to forecast future sales. The historical sales

are stored in a data cube called A CTUAL_SALES that uses a d imension called MONTHS. The date of the

last actual value is stored in a data cube called LAST_ACTUAL_DATE. Calculate the sales trend with

the following formulas:

• TREND_SLOPE d

atacubeformula:

SLOPE(MONTHS

, ACTUAL_SALES, MEMBER(MONTHS), MEMBER(MONTHS) <= LAST_ACTUAL_DATE)

• TREND_START data cube formula:

INTERCEPT(MONTHS, ACTUAL_SALES, MEMBER(MONTHS), MEMBER(MONTHS) <= LAST_ACTUAL-DATE)

• SALES_TREND data cube formula:

TREND_START + TREND_SLOPE * MEMBER(MONTHS)

Note. TREND_SLOPE and TREND_START do n ot use the MONTHS dimension.

Example 2: Ana

lyzing the Relationship Between Data Cubes

Suppose that you want to analyze how UNITS_SOLD has affected SUPPORT_COSTS. Both of these data

cubesuseadimensioncalledMONTHS.Thedateofthelast actual value is stored in a data cube called

LAST_ACTUAL_DATE. Enter the estimates for future unit sales in a data cube called SALES_FORECAST,

and then calculate the resulting SUPPORT_FORECAST data cube as follows:

• RELATION_SLOPE data cube formula:

SLOPE(MONTHS, SUPPORT_COSTS, UNITS_SOLD, MEMBER(MONTHS) <= LAST_ACTUAL_DATE)

• RELATION_ST

ARTdatacubeformula:

INTERCEPT(M

ONTHS, SUPPORT_COSTS, UNITS_SOLD, MEMBER(MONTHS) <= LAST_ACTUAL_DATE)

• SUPPORT_FORECAST data cube formula:

IF(DATE( ) > LAST_ACTUAL_DATE, RELATION_START + SALES_FORECAST * RELATION_SLOPE, 0)

Notice that this example uses a different approach than the previous example. In the first example, you

analyzed how sales were affected by time, and then used the results to predict future sales based on the

passage of time. In this example, you analyzed how support wa s affected by sales, and then used the results to

predict future support costs based on future sales.

SQRT

Syntax

SQRT(Data)