Oracle OLAP

Oracle Express OLAP Cube  

These are my personal notes that I use as a quick help in my work.
You are welcome to read them.

Contents of current page Top-level home page
 
Index  Java Internet Oracle Notes
Linux Basics Web Basics SQL Notes
Informatica Servlets Apache BkpRstore SQL*Plus
Teradata   LDAP Storage PL/SQL
Windows     Tables OEM
UML Lang.   Net8 Portal
SQL Server Python perl Performance OLAP
Vmware Visual Basic PHP/MySQL User Mgmt  
Git        
More technical pages here

Contents

 

Introduction

  

These notes correspond to version 6.3 of OLAP Express

Start administrator:

 

 

Dimensions

See "start administrator" in Introduction for attaching the databases

Right click on a dimension:

 

Programming

See "start administrator" in Introduction for attaching the databases

 

 

Sample program

arg _Month text
" Pass a text argument, use underscore to distinguish parameters

" comments are with double quotes. Note: not double double quote: ""

trap on _error noprint
" _error is a label at the end of the program

call LOG_OPEN('MAIN_CALCUL',0)
" call a sub-program, case in-sensitive

_Month = nafill(_Month, CALC_CURR_MONTH(CURR_PREV_PERIOD 'Current Month'))
" put in a value if the variable is not defined
" calc_curr_month is ??

call ROLLUP_ACCTG_BAL(_Month, 'Actual', 'Actual')
" call sub-program with 3 parameters

return
" end program

_error:
call LOG_ERROR(errorname, errortext)
call LOG_CLOSE()
signal errorname errortext
return

Remember to not only save the program after modifying, but also save the whole cube.
Compile the program with the little icon showing a cog wheel.
Run the program with the icon showing an arrow.

 

Limiting

 

Example 1

Dimension down: dim2, dimension across: dim1

try1
dim1_1
dim1_2
dim1_3
dim2_1
999
999
999
dim2_2
999
999
999
dim2_3
999
999
999

limit dim1 to all
limit dim1 to 'dim1_1'
limit dim2 to 'dim2_1'
try1 = 222 "--> only one cell affected

try1
dim1_1
dim1_2
dim1_3
dim2_1
222
999
999
dim2_2
999
999
999
dim2_3
999
999
999

limit dim1 to all
try1 = 333 "--> three cells affected, dim2 is still limited to 'dim2_1'

try1
dim1_1
dim1_2
dim1_3
dim2_1
333
333
333
dim2_2
999
999
999
dim2_3
999
999
999

Example 2

limit dim1 to all
limit dim2 to all
try1=999

limit dim1 to 'dim1_1'
try1(dim2 'dim2_2')=222 "--> only one cell effected, but dim2 is not limited

try1
dim1_1
dim1_2
dim1_3
dim2_1
999
999
999
dim2_2
222
999
999
dim2_3
999
999
999

limit dim1 to 'dim1_1'
try1 = 888 "--> one column affected, because dim2 is not limited

try1
dim1_1
dim1_2
dim1_3
dim2_1
888
999
999
dim2_2
888
999
999
dim2_3
888
999
999

try1(dim2 'dim2_2')=222 "--> one cell affected

try1
dim1_1
dim1_2
dim1_3
dim2_1
888
999
999
dim2_2
222
999
999
dim2_3
888
999
999

 

Example 3

limit dim1 to all
limit dim2 to all
try1=0    " all put to 0

Program lim1
limit dim1 to 'dim1_1'
limit dim2 to 'dim2_1'
try1 = 111   "  see (1)
call lim2       -->




                <--
try1 = 222   "  see (3)






Program lim2
limit dim1 to 'dim1_2'
limit dim2 to 'dim2_3' 'dim2_2'
try1 = 333  "  see (2)

 

Result at point (1) Result at point (2)

Result at end, point (3)
The limitations remain from program lim2
dim1_1
dim1_2
dim1_3
dim2_1
111
0
0
dim2_2
0
0
0
dim2_3
0
0
0
dim1_1
dim1_2
dim1_3
dim2_1
111
0
0
dim2_2
0
333
0
dim2_3
0
333
0
dim1_1
dim1_2
dim1_3
dim2_1
111
0
0
dim2_2
0
222
0
dim2_3
0
222
0

 

Example 4

Call to lim2 is commented out

limit dim1 to all
limit dim2 to all
try1=0    " all put to 0

Program lim1
limit dim1 to 'dim1_1'
limit dim2 to 'dim2_1'
try1 = 111   "  see (1)
"call lim2       -->




                <--
try1 = 222   "  see (3)






Program lim2
limit dim1 to 'dim1_2'
limit dim2 to 'dim2_3' 'dim2_2'
try1 = 333  "  see (2)

 

Result at point (1) Result at point (2)
Call to lim2 commented out

Result at end, point (3)
limitations from lim1 apply
dim1_1
dim1_2
dim1_3
dim2_1
111
0
0
dim2_2
0
0
0
dim2_3
0
0
0
dim1_1
dim1_2
dim1_3
dim2_1
111
0
0
dim2_2
0
0
0
dim2_3
0
0
0
dim1_1
dim1_2
dim1_3
dim2_1
222
0
0
dim2_2
0
0
0
dim2_3
0
0
0

 

Example 5

Use push and pop

limit dim1 to all
limit dim2 to all
try1=0    " all put to 0

Program lim1
limit dim1 to 'dim1_1'
limit dim2 to 'dim2_1'
try1 = 111   "  see (1)
call lim2       -->










                <--
try1 = 222   "  see (3)






Program lim2

push dim1
push dim2

limit dim1 to 'dim1_2'
limit dim2 to 'dim2_3' 'dim2_2'
try1 = 333  "  see (2)

pop dim1
pop dim2

 

Result at point (1) Result at point (2)

Result at end, point (3)
Writes as limited in lim1
dim1_1
dim1_2
dim1_3
dim2_1
111
0
0
dim2_2
0
0
0
dim2_3
0
0
0
dim1_1
dim1_2
dim1_3
dim2_1
111
0
0
dim2_2
0
333
0
dim2_3
0
333
0
dim1_1
dim1_2
dim1_3
dim2_1
222
0
0
dim2_2
0
333
0
dim2_3
0
333
0

 

Example 6

Difference between limit .. keep and limit .. to

limit dim1 to all
limit dim2 to all
try1=0    " all put to 0
limit dim1 keep 'dim1_1'
try1(dim2 'dim2_1') = 111 " see (1)
limit dim1 keep 'dim1_2'
try1(dim2 'dim2_2') = 222

(1)

Result:
The keep restricts: no 222 written
dim1_1
dim1_2
dim1_3
dim2_1
111
0
0
dim2_2
0
0
0
dim2_3
0
0
0
dim1_1
dim1_2
dim1_3
dim2_1
111
0
0
dim2_2
0
0
0
dim2_3
0
0
0

limit dim1 to all
limit dim2 to all
try1=0    " all put to 0
limit dim1 to 'dim1_1'
try1(dim2 'dim2_1') = 111 " see (1)
limit dim1 to 'dim1_2'
try1(dim2 'dim2_2') = 222

(1)

Result: The second "limit" undoes the previous command
dim1_1
dim1_2
dim1_3
dim2_1
111
0
0
dim2_2
0
0
0
dim2_3
0
0
0
dim1_1
dim1_2
dim1_3
dim2_1
111
0
0
dim2_2
0
222
0
dim2_3
0
0
0

 

Example 7

continue here, I still have to understand this

Use of limit .. to any

limit dim1 to all
limit dim2 to all
try1 = NA
try1(dim1 'dim1_1') = 111   " see (1)
limit dim2 to any(try1 ne NA, dim2)
try1 = 333   " see (2)
limit dim1 to any(try1 ne NA, dim1)
try1 = 222

limit dim1 to all
limit dim2 to all
try1 = NA
try1(dim1 'dim1_1') = 111   " see (1)
limit dim1 to any(try1 ne NA, dim1)
try1 = 333   " see (2)
limit dim2 to any(try1 ne NA, dim2)
try1 = 222

(1)
dim1_1
dim1_2
dim1_3
dim2_1
111
 
 
dim2_2
111
 
 
dim2_3
111
 
 
(1)
dim1_1
dim1_2
dim1_3
dim2_1
111
 
 
dim2_2
111
 
 
dim2_3
111
 
 

 

(2)????????????

Result:

dim1_1
dim1_2
dim1_3
dim2_1
333
 
 
dim2_2
333
 
 
dim2_3
333
 
 
dim1_1
dim1_2
dim1_3
dim2_1
222
222
222
dim2_2
222
222
222
dim2_3
222
222
222

 

(2)???????????

Result:

dim1_1
dim1_2
dim1_3
dim2_1
333
 
 
dim2_2
333
 
 
dim2_3
333
 
 
dim1_1
dim1_2
dim1_3
dim2_1
222
 
 
dim2_2
222
 
 
dim2_3
222
 
 

 

 

Example 7

 

 

 

Result at point (1) Result at point (2)

Result at end, point (3)

dim1_1
dim1_2
dim1_3
dim2_1
0
0
0
dim2_2
0
0
0
dim2_3
0
0
0
dim1_1
dim1_2
dim1_3
dim2_1
0
0
0
dim2_2
0
0
0
dim2_3
0
0
0
dim1_1
dim1_2
dim1_3
dim2_1
0
0
0
dim2_2
0
0
0
dim2_3
0
0
0

 

 

 

Example 8

 

 

 

Result at point (1) Result at point (2)

Result at end, point (3)

dim1_1
dim1_2
dim1_3
dim2_1
0
0
0
dim2_2
0
0
0
dim2_3
0
0
0
dim1_1
dim1_2
dim1_3
dim2_1
0
0
0
dim2_2
0
0
0
dim2_3
0
0
0
dim1_1
dim1_2
dim1_3
dim2_1
0
0
0
dim2_2
0
0
0
dim2_3
0
0
0

 

 

 

Example 9

 

 

 

 

Result at point (1) Result at point (2)

Result at end, point (3)

dim1_1
dim1_2
dim1_3
dim2_1
0
0
0
dim2_2
0
0
0
dim2_3
0
0
0
dim1_1
dim1_2
dim1_3
dim2_1
0
0
0
dim2_2
0
0
0
dim2_3
0
0
0
dim1_1
dim1_2
dim1_3
dim2_1
0
0
0
dim2_2
0
0
0
dim2_3
0
0
0

 

 

 

 

 

 

dim1_1
dim1_2
dim1_3
dim2_1
999
999
999
dim2_2
222
999
999
dim2_3
999
999
999