7 Replies Latest reply on Nov 30, 2012 10:57 AM by Shankar S.-Oracle

# OLAP Expression Analytical Functions and NA Values

Hello,

I am trying to use the SUM and MAX functions over a hierarchy where there are potentially NA values. I believe in OLAP DML, the natural behavior is to skip these values. Can a skip be accomplished with either the SUM or MAX OLAP Expression Syntax functions?

Cheers!
• ###### 1. Re: OLAP Expression Analytical Functions and NA Values
Not even sure if it should be a question to include zero values for nulls.

If I am taking a sum, and a value is empty why would a zero be added as part of my sum?
• ###### 2. Re: OLAP Expression Analytical Functions and NA Values
The OLAP Expression Syntax is implemented internally using OLAP DML. You can see the generated forumla by running a "DESCRIBE" command on the underlying FORMULA object. So the usual OLAP DML rules apply and the expression should respond to the NASKIP option.
• ###### 3. Re: OLAP Expression Analytical Functions and NA Values
Interesting. Based on the documentation:

How the Moving Functions Handle Out-of-Range and NA Values

As a moving function loops through the values, at each step in the loop, if the in-loop dimension position is NA or out of range, then the function considers expression as an NA value. The function treats these NA values in the manner specified by the setting of the NASKIP option (by default, as NA) so, typically, the function ignores out-of-range (NA) values and does not evaluate the expression for that step in the loop.

I am seeing NA values being treated as zeros or an additional loop is not performed if iterating over an NA value.

So if my look back is 3

and I have values 1 2 3 NA 4 I am getting a return value of 2.33 instead of 3.

How can I modify the existing function to change this behavior?
• ###### 4. Re: OLAP Expression Analytical Functions and NA Values
Simple sum here or movingtotal based on 2 preceding and current.

DAY_02-JAN-11 NA NA
DAY_03-JAN-11 1.25 1.25
DAY_04-JAN-11 0.75 2.00
DAY_05-JAN-11 1.00 3.00
DAY_06-JAN-11 1.25 3.00
DAY_07-JAN-11 NA NA
DAY_08-JAN-11 NA NA
WEEK_01-2011 7.25 7.25
DAY_09-JAN-11 NA NA
DAY_10-JAN-11 1.00 2.25 <- wrong, should be 3.25
DAY_11-JAN-11 1.00 2.00 <- wrong, should be 3.25
DAY_12-JAN-11 0.75 2.75 <- correct
DAY_13-JAN-11 3.50 5.25 <- correct
• ###### 5. Re: OLAP Expression Analytical Functions and NA Values
Pre-requisites:
===============

Time dimension with level=DAY.... i have restricted data to 1 month approx.. 20100101 to 20100201 (32 days).
Measure of interest - a (say)
Time Dimension attribute which indicates WEEKDAY.... if you have END_DATE attribute with date datatype so we can extract the DAY (MON/TUE/WED/...) from it and decipher wkday/wkend status for DAY.
Sort time as per END_DATE ..
Take care of other dimensions during testing... restrict all other dimensions of cube to single value. Final formula would be independent of other dimensions but this helps development/testing.

Step 1:
======

"rpr down time
" w 10 heading 't long' time_long_description
" w 10 heading 't end date' time_end_date
" w 20 heading 'Day Type' convert(time_end_date text 'DY')
" a
NOTE: version 1 of moving total
" heading 'moving minus 2 all' movingtotal(a, -2, 0, 1, time status)
" w 20 heading 'Day Type' convert(time_end_date text 'DY')
" heading 'a wkday' if convert(time_end_date text 'DY') ne 'SAT' and convert(time_end_date text 'DY') ne 'SUN' then a else na
NOTE: version 2 of moving total
" heading 'moving minus 2 wkday' movingtotal(a, -2, 0, 1, time convert(time_end_date text 'DY') ne 'SAT' and convert(time_end_date text 'DY') ne 'SUN')
" w 20 heading 'Day Type' convert(time_end_date text 'DY')
" heading 'a wkday non-na' if convert(time_end_date text 'DY') ne 'SAT' and convert(time_end_date text 'DY') ne 'SUN' and a ne na then a else na
NOTE: version 3 of moving total
" heading 'moving minus 2 wkday non-na' movingtotal(a, -2, 0, 1, time convert(time_end_date text 'DY') ne 'SAT' and convert(time_end_date text 'DY') ne 'SUN' and a ne na)

OLAP DML Command:
rpr down time w 10 heading 't long' time_long_description w 10 heading 't end date' time_end_date w 20 heading 'Day Type' convert(time_end_date text 'DY') a heading 'moving minus 2 all' movingtotal(a, -2, 0, 1, time status) w 20 heading 'Day Type' convert(time_end_date text 'DY') heading 'a wkday' if convert(time_end_date text 'DY') ne 'SAT' and convert(time_end_date text 'DY') ne 'SUN' then a else na heading 'moving minus 2 wkday' movingtotal(a, -2, 0, 1, time convert(time_end_date text 'DY') ne 'SAT' and convert(time_end_date text 'DY') ne 'SUN') w 20 heading 'Day Type' convert(time_end_date text 'DY') heading 'a wkday non-na' if convert(time_end_date text 'DY') ne 'SAT' and convert(time_end_date text 'DY') ne 'SUN' and a ne na then a else na heading 'moving minus 2 wkday non-na' movingtotal(a, -2, 0, 1, time convert(time_end_date text 'DY') ne 'SAT' and convert(time_end_date text 'DY') ne 'SUN' and a ne na)

Step 2:
======

"Define additional measure to contain the required/desired formula implementing the business requirements (version 3 above)

" create formula AF1 which points to last column... i.e. OLAP_DML_EXPRESSION
dfn af1 formula movingtotal(a, -2, 0, 1, time convert(time_end_date text 'DY') ne 'SAT' and convert(time_end_date text 'DY') ne 'SUN' and a ne na)
"NOTE: Do this via AWM using calculated member with template type = OLAP_DML_EXPRESSION so that the cube view for cube contains a column for measure AF1

OLAP DML Command:
rpr down time w 10 heading 't long' time_long_description w 10 heading 't end date' time_end_date w 20 heading 'Day Type' convert(time_end_date text 'DY') a heading 'a wkday non-na' if convert(time_end_date text 'DY') ne 'SAT' and convert(time_end_date text 'DY') ne 'SUN' and a ne na then a else na heading 'moving minus 2 wkday non-na (AF1)' af1

->

Step 3:
=======
Extend Oracle OLAP with regular SQL functionality like SQL ANALYTICAL functions to fill up the gaps for intermediate week days like DAY_20100104 (TUE), DAY_20100105 (WED) etc.

Use: SQL Analytical Function LAST_VALUE() in query.. i.e. in report or query.. dont use AF1 but use LAST_VALUE(af1).... as below pseudo-code:
LAST_VALUE(cube_view.af1) over (partition by <product, organization, ... non-time dimensions> order by <DAY_KEY_Col> range unbounded preceeding and current row)

HTH
Shankar
• ###### 6. Re: OLAP Expression Analytical Functions and NA Values
movingtotal(a, -2, 0, 1, time status)
Try
movingtotal(a, -2, 0, 1, time a ne na)
• ###### 7. Re: OLAP Expression Analytical Functions and NA Values
NOTE: As a visual aid to the soln.
a) movingtotal(a, -2, 0, 1, time status) gave the following results
DAY_02-JAN-11 NA NA
DAY_03-JAN-11 1.25 1.25
DAY_04-JAN-11 0.75 2.00
DAY_05-JAN-11 1.00 3.00
DAY_06-JAN-11 1.25 3.00
DAY_07-JAN-11 NA NA
DAY_08-JAN-11 NA NA
WEEK_01-2011 7.25 7.25
DAY_09-JAN-11 NA NA
DAY_10-JAN-11 1.00 2.25 <- wrong, should be 3.25
DAY_11-JAN-11 1.00 2.00 <- wrong, should be 3.25
DAY_12-JAN-11 0.75 2.75 <- correct
DAY_13-JAN-11 3.50 5.25 <- correct

b) movingtotal(a, -2, 0, 1, time a ne na) should give you what you need.
DAY_02-JAN-11 NA NA
DAY_03-JAN-11 1.25 1.25
DAY_04-JAN-11 0.75 2.00
DAY_05-JAN-11 1.00 3.00
DAY_06-JAN-11 1.25 3.00
DAY_07-JAN-11 NA NA
DAY_08-JAN-11 NA NA

WEEK_01-2011 7.25 7.25 <--- ignoring this as its not a day level record/member

DAY_09-JAN-11 NA NA
DAY_10-JAN-11 1.00 3.25
DAY_11-JAN-11 1.00 3.25
DAY_12-JAN-11 0.75 2.75
DAY_13-JAN-11 3.50 5.25

c) However the 3 part soln listed out earlier (rather painfully, i admit :-) ) will densify the data along dates resulting in the last non nul value being used for days w/o data or with na data.
i.e. End result of step1/2/3 in soln will give last non null value for days like 07-Jan, 08-Jan and 09-Jan for which the base measure is na.
DAY_02-JAN-11 NA NA
DAY_03-JAN-11 1.25 1.25
DAY_04-JAN-11 0.75 2.00
DAY_05-JAN-11 1.00 3.00
DAY_06-JAN-11 1.25 3.00
DAY_07-JAN-11 NA 3.00
DAY_08-JAN-11 NA 3.00

WEEK_01-2011 7.25 7.25 <--- ignoring this as its not a day level record/member

DAY_09-JAN-11 NA 3.00
DAY_10-JAN-11 1.00 3.25
DAY_11-JAN-11 1.00 3.25
DAY_12-JAN-11 0.75 2.75
DAY_13-JAN-11 3.50 5.25

HTH