Essbase 11.

1 Workshop
Varying Attributes
Updated: JUNE-30-2008
Oracle Corp. Essbase 11.1 Workshop Var!"# $ttr!b%tes 1
Workshop Varying Attributes
Description
&h!s 'orkshop de(o"strates ho' to create a" Essbase c%be that stores )ar!"# attr!b%tes*
ho' to load data !"to the c%be '!th Essbase +t%d!o* a"d ho' to %se the c%be !" report!"#
'!th +(art V!e'.
Background
U"t!l ,elease 11.1* !t had o"l bee" poss!ble to create stat!c attr!b%tes !" Essbase.
-eca%se o. th!s l!(!tat!o"* the attr!b%tes d!d "ot al'as re.lect real l!.e sce"ar!os. /or
e0a(ple* !" the +a(ple.-as!c de(o database* the 1rod%ct d!(e"s!o" co%ld o"l ass!#"
attr!b%tes s%ch as O%"ces a"d 1k# &pe that are stat!c* (ea"!"# that the co%ld "ot )ar
o)er a" d!(e"s!o" s%ch as t!(e.
2" real l!.e* attr!b%tes o.te" )ar o)er t!(e or other d!(e"s!o"s. /or e0a(ple* e(ploees
ca" ha)e d!..ere"t (a"a#ers o)er t!(e* or a packa#e tpe ca" )ar depe"d!"# o" the
#eo#raph!cal area.
W!th the Essbase 11.1 release* !t !s "o' poss!ble to de.!"e a"d a"al3e data based o"
)ar!"# attr!b%tes.
Exercises for Varying Attributes
Overview
&h!s e0erc!se co"s!sts o. three parts:
415 Creat!"# a"d (a"a#!"# )ar!"# attr!b%tes
425 ,eport!"# o" )ar!"# attr!b%tes '!th +(art V!e'
435 6oad!"# data '!th Essbase +t%d!o
Prerequisite Steps
-e.ore be#!""!"# th!s 'orkshop* per.or( the .ollo'!"# task:
7ake a cop o. the +a(ple.-as!c appl!cat!o" b r!#ht-cl!ck!"# o" !t a"d p!ck!"#
Cop .ro( the (e"%. +pec!. the "a(e 8+a(pleV$9 .or the "e' appl!cat!o".
Note: This workshop assumes you have a working knowledge of Essbase and Essbase
Administration Services, and Essbase Studio.
Oracle Corp. Essbase 11.1 Workshop Var!"# $ttr!b%tes 2
Part 1: reating an! "anaging Varying Attributes
1. 2" E$+* ope" the +a(pleV$.-as!c o%tl!"e a"d select the 1ropert!es tab.
2. O" the propert!es tab* cha"#e the 8Var!"# attr!b%tes e"abled9 propert to &r%e as
sho'" belo':
3. +elect the O%tl!"e tab* a"d add a "e' attr!b%te at the e"d called 87a"a#er.9 $dd the
ch!ldre" :e)!" a"d Vlad to the 7a"a#er attr!b%te as sho'" belo':
Oracle Corp. Essbase 11.1 Workshop Var!"# $ttr!b%tes 3
;. ,!#ht-cl!ck o" the 1rod%ct d!(e"s!o" a"d select Ed!t 7e(ber propert!es .ro( the
(e"%.
<. +elect the $ttr!b%te tab. +elect 7a"a#er a"d the" select the check bo0 "e0t to =ear
%"der !"depe"de"t d!(e"s!o"s as sho'" belo':
>. Cl!ck the $ss!#" b%tto"* a"d the" cl!ck O:.
=o% ha)e "o' ass!#"ed the 7a"a#er attr!b%te to the 1rod%ct (e(ber as a co"t!"%o%s
Var!"# $ttr!b%te. &h!s (ea"s that the attr!b%te !s ass!#"ed to a ra"#e o. )al%es !" the
d!(e"s!o" =ear !"stead o. be!"# ass!#"ed to !"d!)!d%al* spec!.!c )al%es !" =ear.
&he "e0t task !s to assoc!ate the !"d!)!d%al 1rod%cts '!th a 7a"a#er across the =ear
d!(e"s!o". &h!s proced%re !s per.or(ed !" the .ollo'!"# steps.
Note: For the Independent dimension, you must specify the association at the leaf level level !".
?. ,!#ht-cl!ck o" 1rod%ct d!(e"s!o" )al%e 100* a"d select Ed!t 7e(ber propert!es.
8. +elect the $ssoc!at!o"s tab.
@. 2" $)a!lable attr!b%tes* cl!ck the pl%s s!#" "e0t to 7a"a#er to d!spla the t'o attr!b%te
)al%es* :e)!" a"d Vlad.
Oracle Corp. Essbase 11.1 Workshop Var!"# $ttr!b%tes ;
10. +elect Vlad a"d the" cl!ck the $dd ra"#e b%tto" as sho'" belo':
11. 2" the +elect !"depe"de"t d!(e"s!o" ra"#es d!alo# bo0* select Ja" as a start!"# )al%e
a"d 7a as a" e"d!"# )al%e .or the ra"#e.
12. Cl!ck $dd ,a"#e.
13. Cl!ck the $ss!#" b%tto" to ass!#" the attr!b%te )al%es to the 1rod%ct d!(e"s!o".
2" the le.t ha"d pa"e o. the 7e(ber 1ropert!es d!alo# bo0* o% '!ll see the assoc!at!o"
87a"a#er: Vlad A4Ja"5 47a5B9 as sho'" belo':
Oracle Corp. Essbase 11.1 Workshop Var!"# $ttr!b%tes <
1;. /ollo'!"# the abo)e proced%re* ass!#" the .ollo'!"# assoc!at!o"s:
1rod%ct 100: Vlad Ja" to Cec
1rod%ct 200: Vlad Ja" to 7a
1rod%ct 200: :e)!" J%" to Cec
1rod%ct 300: Vlad J%l to Cec
1rod%ct 300: :e)!" Ja" to J%"
1rod%ct ;00: :e)!" Ja" to Cec
1<. Ver!. a"d +a)e the o%tl!"e.
Part #: $eporting on Varying Attributes with S%art View
=o% ca" 'ork '!th Var!"# $ttr!b%tes .ro( '!th!" +(art 7e(ber 1ropert!es .ro( the
7e(ber +elect!o" d!alo# a"d .ro( a "e' (e"% opt!o" !" the Dper!o" (e"% called Cata
1erspect!)e. =o% ca" also ed!t the 'orksheet d!rectl as de(o"strated belo'.
Note: #ou must have completed the above section $%reating and &anaging 'arying Attributes(
before starting this section.
1. Co""ect to the +a(pleV$.-as!c database '!th +(art V!e'.
2. 1repare to create a" ad-hoc report. &he #r!d sho%ld appear '!th the de.a%lt lao%t o.
data.
3. Cra# 1rod%ct .ro( the 1OV "a)!#ator o"to the tpe o. =ear* the" dra# =ear o"to
7eas%res.
;. Use the d!(e"s!o" selector to p!ck 7eas%re +ales a"d the (o"th !" the ear.
<. ,!#ht-cl!ck o" col%(" $ a"d choose 2"sert to !"sert a "e' col%(" be.ore col%(" $.
>. 2" cell $3* tpe !" 87a"a#er9* a"d re.resh the sheet
=o% sho%ld be able to dr!ll do'" o" 7a"a#er a"d 1rod%ct a"d see the .ollo'!"# lao%t:
Oracle Corp. Essbase 11.1 Workshop Var!"# $ttr!b%tes >
=o% ca" "o' e0per!(e"t '!th the 7e(ber +elect!o" tool. &r to p!ck 1rod%cts based o"
the attr!b%te ass!#"(e"tsE .or e0a(ple* tr to p!ck prod%cts that 'ere ass!#"ed to :e)!" !"
8Oct.9
=o% ca" also tr to set the Cata 1erspect!)eE .or e0a(ple* tr to set a c%sto( Cata
1erspect!)e to the =ear 87ar9 a"d the" to 8Oct9 to see ho' the calc%lat!o"s )ar as the
h!erarch!es cha"#e.
Part &: 'oa!ing (ata with Essbase Stu!io
Overview
2" th!s sect!o"* 'e '!ll create a "e' c%be '!th Var!"# $ttr!b%tes %s!"# Essbase +t%d!o.
2" th!s e0a(ple* the (a"a#er .or the prod%ct cate#or!es )ar!es o)er the 7arket d!(e"s!o"
at the +tate le)el.
/or s!(pl!c!t* the ass!#"(e"ts are do"e b ,e#!o" b%t o" the +tate le)el as .ollo's:
Vlad !s a (a"a#er .or prod%ct 100 !" the East a"d West +tates
Vlad !s a (a"a#er .or prod%ct 200 !" the +o%th a"d Ce"tral states
Vlad !s the e0cl%s!)e (a"a#er .or prod%ct ;00 !" all states
:e)!" !s a (a"a#er .or prod%ct 100 !" the +o%th a"d Ce"tral states
:e)!" !s a (a"a#er .or prod%ct 200 !" the East a"d West states
:e)!" !s the e0cl%s!)e (a"a#er .or prod%ct 300 !" the all states
+ee the table belo' .or a" o)er)!e':
Product Family States for region Manager
Oracle Corp. Essbase 11.1 Workshop Var!"# $ttr!b%tes ?
100 Easter" states Vlad
100 Wester" states Vlad
100 +o%th states :e)!"
100 Ce"tral states :e)!"
200 Easter" states :e)!"
200 Wester" states :e)!"
200 +o%th states Vlad
200 Ce"tral states Vlad
300 Easter" states :e)!"
300 Wester" states :e)!"
300 +o%th states :e)!"
300 Ce"tral states :e)!"
;00 Easter" states Vlad
;00 Wester" states Vlad
;00 +o%th states Vlad
;00 Ce"tral states Vlad
$.ter the c%be !s b%!lt* o% ca" tr to spec!. a perspect!)e to see alter"ate (a"a#er
ass!#"(e"ts !" d!..ere"t states !" order to proFect poss!ble res%lts. /or e0a(ple* o%
ca" proFect the e0pected res%lts !. all the easter" states had the sa(e (a"a#er or all the
'ester" states had the sa(e (a"a#er.
Oracle Corp. Essbase 11.1 Workshop Var!"# $ttr!b%tes 8
Prerequisites
-e.ore be#!""!"# th!s sect!o"* per.or( the .ollo'!"# tasks:
1. 2"stall the tbc sche(a !" a relat!o"al database !. o% ha)e"Gt alread do"e so.
2. 2"stall the add!t!o"al table .a(!lH(a"a#er b r%""!"# the !"cl%ded scr!pt
install_va_oracle.sql
as the tbc %ser.
Tip: 2. o% #et errors 'he" r%""!"# the Oracle scr!pts* o% (!#ht "eed to cha"#e the date .or(at %s!"#
the .ollo'!"# co((a"d .ro( the +I6 co((a"d l!"e be.ore r%""!"# the scr!pts:
alter session set NLS_DATE_FORMAT='MM/DD/YYYY';
3. +et %p a data so%rce co""ect!"# to o%r Essbase !"sta"ce.
Tip: =o% ca" drop the table a#a!" %s!"# the dropH)aHoracle.sJl scr!pt.
Note: This workshop assumes that you have a working knowledge of Essbase Studio from the
Studio workshops.
E)er*ise: 'oa!ing the (ata
A. reating the "inis*he%a
1. +et %p a data so%rce co""ect!"# to the tbc sche(a* .or e0a(ple tc.
2. Create a (!"!sche(a* "a(!"# !t* .or e0a(ple* tc_!ini.
3. 2"cl%de the .ollo'!"# tables !" the (!"!sche(a:
/a(!l
/a(!lH(a"a#er
7arket
1rod%ct
,e#!o"
+ales.act
+ce"ar!o
;. Create a Fo!" bet'ee" the .a(!lH(a"a#er table as .ollo's:
a. Cra# the c%rsor .ro( the /$726= col%(" o. the .a(!l table to the /$726=
col%(" o. the .a(!lH(a"a#er table. $" arro' '!ll d!spla represe"t!"# the Fo!".
b. Cra# the c%rsor .ro( the ,EK2ON col%(" o. the re#!o" table to the ,EK2ON
col%(" o. the .a(!lH(a"a#er table. $" arro' '!ll d!spla represe"t!"# the Fo!".
Oracle Corp. Essbase 11.1 Workshop Var!"# $ttr!b%tes @
&he Fo!" sho%ld (atch the Fo!" sho'" !" the .ollo'!"# (!"!sche(a:
+. reating the ,ierar*hies -o.!er
1. Create a .older !" the 7etadata Na)!#ator called 8Var $ttr!b%tes.9
2. D!#hl!#ht all tables !" the (!"!sche(a a"d dra# the( to the Var $ttr!b%tes .older.
3. U"der the Var $ttr!b%tes .older* create a .older called 8D!erarch!es9 as sho'" belo':
. reating the Pro!u*t ,ierar*hy
1. U"der the D!erarch!es .older* r!#ht-cl!ck a"d select Ne' L D!erarch.
2. Na(e the h!erarch 81rod%ct.9
3. Cra# the /$726= ele(e"t !" the .a(!l .older to the D!erarch col%(".
;. Cra# the +:U ele(e"t !" the prod%ct .older to the D!erarch col%(" so that +:U !s a
ch!ld o. /$726=.
<. Cra# the 7$N$KE, ele(e"t !" the .a(!lH(a"a#er .older to the D!erarch col%(".
>. Cra# the /$726= ele(e"t !" the .a(!l .older so that /$726= !s a ch!ld o.
7$N$KE,.
Oracle Corp. Essbase 11.1 Workshop Var!"# $ttr!b%tes 10
&he prod%ct h!erarch sho%ld (atch that sho'" belo':
?. 1re)!e' the h!erarch!es a"d cl!ck +a)e.
(. reating the "arket ,ierar*hy
1. U"der the h!erarch!es .older* r!#ht-cl!ck a"d select Ne' L D!erarch.
2. Na(e the h!erarch 87arket.9
3. Cra# the ,EK2ON ele(e"t !" the re#!o" .older to the D!erarch col%(".
;. Cra# the +&$&E ele(e"t !" the (arket .older so that +&$&E !s a ch!ld o. ,EK2ON.
&he h!erarch sho%ld (atch that sho'" belo':
<. 1re)!e' the h!erarch a"d cl!ck +a)e.
Oracle Corp. Essbase 11.1 Workshop Var!"# $ttr!b%tes 11
E. reating the "easures ,ierar*hy
1. U"der the h!erarch!es .older* r!#ht-cl!ck a"d p!ck Ne' L 7eas%re D!erarch.
2. Na(e the h!erarch 87eas%res9.
3. Cra# the +$6E+ ele(e"t !" the sales.act .older to the D!erarch col%(".
&he h!erarch sho%ld (atch that sho'" belo':
;. Cl!ck +a)e b%tto".
-. reating the ube S*he%a
1. ,!#ht-cl!ck o" the Var $ttr!b%tes .older a"d select Ne' L C%be +che(a.
2. Na(e the c%be sche(a 8csVar$ttr9.
3. +elect the 1rod%ct h!erarch o% created !" sect!o" C* abo)e* a"d dra# the 1rod%ct
h!erarch to the D!erarch!es pa"el.
;. +elect the 7arket h!erarch o% created !" sect!o" C* abo)e* a"d dra# the 7arket
h!erarch to the D!erarch!es pa"el.
<. +elect the 7eas%res h!erarch o% created !" sect!o" E* abo)e* a"d dra# the 7eas%res
h!erarch to the 7eas%resM 7eas%re D!erarch!es pa"el.
Oracle Corp. Essbase 11.1 Workshop Var!"# $ttr!b%tes 12
&he c%be sche(a sho%ld (atch that sho'" belo':
Oracle Corp. Essbase 11.1 Workshop Var!"# $ttr!b%tes 13
/. reating the Essbase "o!e.
1. Cl!ck the "e0t b%tto".
&he C%be +che(a W!3ard '!ll d!spla as sho'" belo':
2. +elect the check bo0 8Create Essbase 7odel.9
3. 6ea)e the (odel "a(e as !s a"d cl!ck /!"!sh.
$ c%besche(a 8csVar$ttr9 a"d a c%besche(a (odel 8csVar$ttr7odel9 '!ll be created
!" the 7etadata Na)!#ator.
;. ,!#ht-cl!ck o" the c%besche(a (odel 8csVar$ttr7odel9 a"d select Essbase
propert!es.
<. Cr!ll do'" o" the 1rod%ct h!erarch a"d r!#ht-cl!ck o" 7$N$KE,.
>. 2" the Ke"eral tab* set the .ollo'!"#:
?. +elect the check bo0 8Essbase attr!b%te .or /$726=9.
&he $ttr!b%te d!(e"s!o" "a(e sho%ld a%to(at!call beco(e 7$N$KE,* a"d the
$ttr!b%te tpe sho%ld beco(e +tr!"#.
Oracle Corp. Essbase 11.1 Workshop Var!"# $ttr!b%tes 1;
8. 2" the bo0 8Var!"# $ttr!b%tes +ett!"#s9 press the Ed!t b%tto".
@. O" the Ed!t Var!"# $ttr!b%tes d!alo#* check the bo0 8Create as Var!"# $ttr!b%te9
10. U"der 8Cata load (ode9* select the b%tto" 8O)er'r!te (ode.9
11. -eca%se th!s attr!b%te )ar!es o)er the 7arket d!(e"s!o" o" the +tate le)el* !" the
8Cepe"ds o"9 col%("* select the check bo0 .or 7arket.
12. +elect +&$&E .ro( the p%ll-do'" (e"% !" the +o%rce col%(".
13. -eca%se there !s "o ra"#e .or the states 4as there 'o%ld be .or &!(e d!(e"s!o"s5. +et
the &pe col%(" to C!screte.
1;. 2" the /ro( col%(" cl!ck o" the .!eld a"d cl!ck the Ell!ps!s b%tto" 4. . .5* a"d select the
+tate col%(" .ro( the .a(!lH(a"a#er table.
&he select!o"s o% ha)e (ade !" the Ke"eral tab o. the Essbase 7odel 1ropert!es
sho%ld (atch those sho'" belo' 4Note: d!alo# has cha"#ed5:
1<. Cl!ck $ppl.
1>. Cl!ck Close.
,. (ep.oying the ube
1. ,!#ht-cl!ck o" the c%besche(a (odel 8csVar$ttr7odel9 a"d select the C%be
Ceplo(e"t '!3ard .ro( the pop-%p (e"%.
&he c%be Ceplo(e"t W!3ard '!ll d!spla.
Note: The Essbase Server %onnection te)t bo) should display the Essbase connection.
Oracle Corp. Essbase 11.1 Workshop Var!"# $ttr!b%tes 1<
2. 2" the $ppl!cat!o" te0t bo0* tpe !" the "a(e o. the Essbase appl!cat!o" that o% '!sh
to deplo to* .or e0a(ple +a(pl+V$.
3. 2" the Catabase te0t bo0* e"ter the "a(e o. the database* .or e0a(ple bas!c.
Note: *e careful not to overwrite your application from the first e)ercise which is called
Sample'A.*asic.
;. +elect the rad!o b%tto" 8-%!ld o%tl!"e a"d load data.9
&he select!o"s o% ha)e (ade !" the C%be Ceplo(e"t W!3ard sho%ld (atch those
sho'" belo':
<. Cl!ck /!"!sh. =o% sho%ld see a" a"!(at!o" 'h!le the (odel !s be!"# deploed to
Essbase. Whe" the process !s .!"!shed* o% see a 8s%ccess.%l deplo(e"t9 (essa#e.
0. Viewing the Out.ine
1. +tart E$+.
2. /!"d the $ppl!cat!o" +a(pl+V$* a"d )!e' the o%tl!"e. 2t sho%ld rese(ble the
.ollo'!"#:
Oracle Corp. Essbase 11.1 Workshop Var!"# $ttr!b%tes 1>
3. -e.ore o% )!e' the c%be !" +(art V!e'* r!#ht-cl!ck o" the database !" E$+ a"d r%"
the de.a%lt calc%lat!o" to calc%late the c%be.
=o% ca" "o' co""ect '!th +(art V!e' to look at the c%be. &r e0per!(e"t!"# '!th
sett!"# the perspect!)e. 2" add!t!o"* tr other tasks* s%ch as select!"# d!(e"s!o" (e(bers
b attr!b%te .ro( the 7e(ber +elect!o" d!alo#.
Workshop evaluation
Dere are a .e' !te(s to co"s!der as o% e)al%ate th!s 'orkshop:
$re o% pla""!"# o" %s!"# Var!"# $ttr!b%tes (eas%res '!th EssbaseN
2. es* 'hat !s the p%rpose o. the reports o% '!ll be creat!"#N
C!d o% .!"d a"th!"# (!ss!"# .ro( th!s .eat%reN
Other co((e"tsN
Oracle Corp. Essbase 11.1 Workshop Var!"# $ttr!b%tes 1?