August 2010 - Essbase Excel Add-In Spreadsheet Reporting - Oracle Hyperion Training

http://harbinger-group.com/education

---

Closed Caption:

and there we go i keep it simple
definition of space is that it's a
multi-dimensional database whatever that
means i'll explain in a moment designed
for rapid reporting and analysis
dimension is a description of your
business like the products you sell the
customers you sell to the market to sell
in what are you measuring what time
period are you dealing with those kinds
of things in each dimension in this
multi-dimensional database is organized
hierarchically meaning that at the top
of the hierarchy you have a summarized
view of data and as you go deeper into
the hierarchy we have a more detailed
view of data like at the top of a time
hierarchy you might have years and then
you drill into the quarters months and
weeks so forth and this
multi-dimensional organization of data
allows for the fast easy and fast
analysis of multiple facts as you will
see very shortly so as is what what
happens often when i teach the class on
spreadsheet reporting as people have
people do stretching reporting they
raise their hand and say yes and I say
well what do you do
and about eighty percent of the people
say well I get a report every month that
recorder and I retrieve into that report
and if it the quarter is changing
quarter one quarter 2 i'd just type over
quarter one and cell a1 in this case and
do another retrieve and there I've got
my new report well that's not really a
space reporting especially it isn't
analysis you're only at this point using
about 2% of the product and as isn't as
is the case with most software products
eighty percent of the people use about
twenty percent of the product but you're
here to learn more about it and what you
really want to do is to pull data from
from a space on your own creating your
own reports analyzing the data that's
coming in did we make the profit profit
plan for the month which product was
most profitable those kinds of things
and once you're able to do that in
essbase manually create report
you might want to extend that further by
automating reports by adding what are
called excel controls like buttons and
checkboxes and i'll give you an example
of this at the end of the webinar so
that the user can make directions
without even knowing essbase and get a
brand-new analysis of of a report that
exists on the screen and that's where
the real power of essbase lies and you
of course get there through training in
the beginning of that training is taking
one of these webinars and before we get
into the actual manual manipulation of
data
the first thing you really don't need to
know about any database is what it looks
like
so you need to understand your database
I've already mentioned that a space is
multi-dimensional there's more than one
dimension in this database there's more
than two dimensions that would be a
relational table with a two-dimensional
database so to speak is going to be
Muslim qualified dimensionals anywhere
from three to ten dimensions and each
dimension describes a part of your
business the customers the market the
products etc each dimension is arranged
hierarchically with more detail at the
deeper levels and more summarized
description of that data at the higher
levels and the way you know your
database is through experience but there
are a couple of simple tools that will
help you learn your database and the
first simple tool is just simply
retrieve into an empty sheet which I'm
going to do in Excel right now so here i
am in Excel I'm Alex excel 2007 by the
way if you're in the Excel 2007 you
should have an Adam's tab with a face in
it if you see besides sk8-hi . here
that's a different reporting tool called
Smart View the newer one in the essbase
world right now we're talking about the
spreadsheet data and that's here and you
might even have a toolbar available to
you right now I'm just going to go with
this essbase menu in the atom step and
if i want to connect a space i go to
connect screen space system login select
the server
I want to connect to there could be many
i specify my user ID and password here i
can type it correctly that is and when i
click on OK or press my Enter key i get
a list of all of the databases i can
connect to notice that there are many
databases made 'basic here
that's because the database is really a
combination of its application name and
database name its application name is a
folder in which the database files are
caps so i can have ASO basic here and
demo basic here and so forth and so on
each database is different each basic
database is different because it's in a
different folder I'm going to select
sample basic right now and click OK and
at that point it looks like nothing
happened and something has happened but
it doesn't show you visually on the
screen so if you really want to know
that something has happened that you
really connected to a database
uninterruptedly you get out of this
disconnect command which doesn't
disconnect at this point unless you
press the disconnect button but it shows
me that book one sheet 1 is connected to
this server using this application and
this database ptss basis my server
sample is my application and basic is my
database so i have connected but as a
slide said to know your database
retrieve into an empty sheet so one of
the main command here is retrieve and
when I do so I'm asking essbase to give
me data and I'm asking it to give me
data based on nothing
no information and I inputting i'm not
telling space what data I want return so
essbase simply says well since you
didn't give me any information i will
return all of the dimensions in your
database at the highest level in the
hierarchy so i know i have a year
dimension measures product market and
scenario it puts one of the dimensions
and what we call the Rose area its
describing these rows and other
dimensions in the columns area
describing the columns
and at this point the number is simply a
summarization a rollup if you will of
the total year the total measures the
total product total market and total
scenario not too interesting data at
this point but we have learned that we
have five dimensions in the database if
you want to another trick for learning
what's in your database is to select
year as an example here if I go to s
base and there's a tool called member
selection whose purpose is far broader
than what i'm talking about here by
member selection can be used to
determine what your dimension looks like
so since I was on year it came up with
your you see the other dimensions here
and I'm just going to leave in a year i
select year here and there's a button
here called expand the defendants and
when i press that I can see the entire
hierarchy for that year dimension and if
i select measures and select measures
there and press expand the defendants I
can see the entire hierarchy for that
dimension so forth and so on so that's
another way of initially learning your
database don't do anything with this
window other than that right now until
you learn about member selection and
simply cancel at that point so
retrieving into an empty sheet shows you
what the dimensions are how many
dimensions there are but not their
hierarchy and member selection shows you
each dimension and through the use of
the expanse descendants button you can
determine what the hierarchy what the
members are within each dimension
quarter 1 january even year itself are
considered members of the year dimension
in this case but now we want to start
creating a report or even analyzing our
data from what I call the core commands
retrieve which we've already seen zoomin
zoom-out pivot keep only remove all
I'll go back to excel for a moment and
let's zoom in on year and of course
everybody's familiar with Google Maps or
any kind of software product that zoom
in that means give me more detail and
when i zoom in i get more detail i get
the next level in the hierarchy which
are the quarters if i zoom in on quarter
to hear i'm gonna get the next level in
the hierarchy for quarter to which of
course is the month of april may and
june if i zoom in on whichever month i
selected your april and zoom in i don't
get any further information because I
happen to be at the bottom of the
hierarchy
I don't have weekly or daily information
in this database and of course the
opposite of zooming in zooming out so if
I select may hear any one of those
months and zoom-out going to bring me
back to quarter to $TIME and if i select
any one of the quarters and zoom out
it's going to be bringing me back to the
year another way of zooming in depending
upon how you have your options set the
options are here under the essbase menu
and the options control on number of
things but one of the things they
control is zooming when i zoomed in I
got the next level and I have this
button pressed I would have received all
levels the months and the quarters
zooming is also a bit controlled by this
enabled double-clicking and enable
secondary button options that you see
here notice i have both of them selected
enable double-clicking enable secondary
button i click on ok so enable
double-clicking means i can do something
like double click on here and get a zoom
in
double-click on quarter three here and
i'll get the three months in the the
quarter three and if i write to
double-click the secondary button i can
zoom out right double click to zoom out
double click to zoom in again
of course I should be pointing out this
point that the data is changing
obviously these four months these four
quarters excuse me
add up to the year so they're more
detailed information when i zoom in on
quarter one these three months and up to
the quarter now that doesn't always
occur that way but that's another longer
story i'll zoom back out but right now
this number here is my quarter one
number for the total of measures product
market scenario so we can zoom in for
more detail we can zoom out for less
detail but how about zooming in on
something on top here I'll zoom in on
product i won't double quick so you can
see it and space zoom in on product and
notice my products that were up here in
the columns of a sudden automatically
got shifted over here to the Rose area
so these rows described product group
100 these rows described product group
200 so forth and so on they moved over
the Rose areas automatically and the
simple reason for that is because Excel
has a greater maximum for the number of
rows than it does the columns and FB
says well since I've got a greater
maximum I'm gonna automatically move
things over to the rose that you zoom in
on so if i zoom in on market you'll see
the same thing happen by east market is
here i scroll all the way down the row
32 and there's my west market so all of
these rows between row 2 and row 31
inclusive are describing my East market
that set of rows is describing my west
market and within my East market i have
my product one other 200 groups etc and
within my product groups i have my
quarters and year and now this number
two 747 is the quarter one number for
product 100 in the East region for the
total of all my measures in all my
scenarios and if i zoom in on measures
i'll double-click this time they get
moved over and if I double-click on
scenario zoom in on it
notice those don't get moved over
because we have
to have some columns we have to have at
least one set of columns dimension
members so now this number two 747 is my
quarter one product group on other
eastern region profit my actual profit
for the East product group 100 in
quarter one a key factor in essbase
understanding what you're looking at is
that every number is always the
intersection of all of the dimensions in
the database every number is always the
intersection of all of the members all
of the dimensions excuse me in the
database so this is my quarter three
actual profit in the East for product
group 100 i'm getting a little too much
data on the screen so I'm simply going
to delete the entire screen and do
another retrieve and once again because
i didn't supply information space says I
don't know what to supply except the
top-level member of each dimension and
once again i'm going to double-click on
here to zoom in on it and i'm going to
double-click on products and zoom in on
it also and product got moved over here
well maybe that's not the way I want to
look at my analysis here that doesn't
look too great so what I'm going to do
instead is take any one of the members
of my year dimension it doesn't matter
which one just select one of them and
i'm going to use this pivot command
which simply means move something from
the road to the columns are from the
columns to the Rose it's the same
analysis but now it just looks different
this again is my quarter one number for
product group honored for the total of
measures markets and scenarios
this is my quarter one column this is my
product group 100 role but all of this
data is the total of measures market
scenario
those are called page headers a little
tougher
concept to understand and I don't expect
you to understand it at that . but
through pivoting i'll come back here i
can move things from rows to columns as
you see or i can move something like the
quarters again from Collins back to Rose
notice they got moved to the outside
this time and I can move my products
from rose up two columns again same data
all the time just a different way of
looking at it if i zoom in on my market
here one more time they got moved over
to the side and maybe I want them on top
so i can assume that's okay I can pivot
to move them up to the map but here's
another little tip i'll take my products
move them back over to the rows from the
columns to the Rose here's another tip
with that enable secondary button option
checked options global tab enable
secondary button allows me to do
something very special that can only be
done by right clicking and dragging here
i have an analysis of my product groups
by quarters
what if somebody looking at this says
well I wanted analysis my quarters by
product groups
well I can just take any one of my
product groups right click and drag it
you can see the 200 there and i can drop
it to the inside of my quarters and now
it's the same data but in different
analysis quarters by products instead of
products like orders and i can drag my
quarters inside of my products now I've
got my products my quarters again the
only way you can do that is right
clicking and dragging and with right
clicking and dragging I can also use it
in place of the pivot command now i'm
dragging my quarters and year up to the
columns again instead of using the pivot
command but again somebody says well I
don't want the quarters by market i want
the markets by quarters so i can take my
markets right click drag and pull them
to the top and now i have the same data
with a different analysis of each market
region by quarters and years there's my
East data there's my West data
so the pivot command remove rows to
columns can move to mention remove
columns to Rose excuse me or what I call
transposing the orders of Rome multiple
roller Colin dimensions and you can only
do that by right-clicking and drag with
these two you can use either the pivot
man or the right click and drag the zoom
out we've already taken a look at that's
pretty easy self-explanatory but here
are a couple of more very handy our core
commands i'll start with a blank sheet
again do retrieve and by the way i can
do a retrieve by double-clicking on an
empty cell i'll double click here on g8
and there I get my initial retrieve i
double-click on here to zoom in double
click on product i double-click on
market
I right click and drag my products up to
the top
i double-click on my scenarios i right
click and drag my scenarios up to the
top now I've got a rapport with a lot of
columns a through looks like AAS and a
number of that so many rows here just 28
rose not that much but it may be more
data that i want i might want to
pinpoint specific data that i want to
look at here so if i decide i really
only want quarter one quarter three data
to look at in my ear to mention i can
select quarter one quarter three you
have control clicking there and do
what's called a keep only keep only
means keep only the members in the
dimensions that you selected isolated
select a quarter one quarter three from
my year to mention when I do to keep
only I get quarter one quarter three for
all of those markets all right
hey I want to remove things like let's
say I scroll to the right and I really
don't want my variance here
well if I were to try removing my
variance via the I can't remember where
it is on Excel 2007 it's been so yeah
i'm i'm relatively new to it here but if
I all right quick if i delete that
column I really don't want to do that
because when i do that column i'm also
deleting this member measures here and
i'm also doing this product 300-year I
only want to get rid of variance so if i
do an essbase remove only instead I've
gotten rid of that you see variants
percent there but I've gotten rid of
variance for the entire report i can do
the same thing here with let's try
product 300 you see product 300 there
325 cetera i do remove only I've removed
all of the product 300 underneath actual
underneath budget underneath variants
percent etc and keep and remove only can
be used with multiple members from
multiple dimensions
what if i only want east and south and
only want products 100 and 400 and i
only want actual budget i selected two
members from three different dimensions
and i'm going to do a keep only and
immediately I have only 100 and 400 in
the east and south and only my actual
and budget i can zoom in mymy measures
there i can zoom in my profit i decided
i zoom in my margin i decided i only
want my sales my cost of goods sold my
margin i click and drag across those
three since they are contiguous and I do
it keep only on that and that reduce my
report to just my sales cost of goods
sold and my margin from that dimension
so keep and remove only are very handy
tools for getting just the data you want
to fee
so I've taken you very quickly through
the core command with those core
commands you can do quite a bit
not everything by and by far in
spreadsheet add-in but it will certainly
give you a good start and to get started
no that s space is multi-dimensional get
to know your database what dimensions
are there what members are in those
dimensions and what hierarchy levels are
in those dimensions and you can view
that in a number of ways remember
selection or just zooming in until you
get to the bottom we've learned about
the retrieve command that simple
zooming in and zooming out pretty simple
reducing the amount of information via
keep and remove only and we learned how
to use the pivot command to view the
same data differently by removing things
from rows to columns columns two rows or
transposing the order of multiple row or
column dimensions but there's much much
more involved to really knowing at the
SBA spreadsheet and it and so if you
want to pursue it further please feel
free to contact me about some of our
training classes you've got a half day
on the core command concepts one of the
key things you're not learning here in
the webinar is the concepts behind the
commands on why things work the way they
do and that's vitally important and then
you can get into the advanced commands
and concepts and another half day or
just take the four one-day training on
it and in those glasses you think learn
things like like how to build reports
using four different methods milestone
menu what I just showed you basically
member selection there's a freestyle
method there's a query designer method
once you get into member selection
there's four methods of using it i
pointed out the options but there are
many options that affect things in
different ways
members can have aliases different names
do you use those names the concepts of
what is this thing called the database
it has something called an outline that
determines what the database looks like
and more importantly give certain
properties to the members in that
database that drastically affect how
things are reported for example I'll
give you a very quick example i'll do
another initial retrieve zoom in on
years ooh min on product and i'm going
to pivot my quarters of the top you
notice here here on my products and you
would think they would add up to 20 4703
but you see down in the lower right that
they add up to 30 1720 why that's one of
those important concepts that you need
to learn report header concepts i talk
about column headers and row headers
that's great to know but those page
headers and didn't really explain those
in any detail what if you want to add
formulas and there were reports and you
want to retain them once you do assuming
in or retrieving that's another concept
can you put multiple reports on one
sheet the answer is yes
how do you do that another concept
automation of all kinds of things once
you learn the basics then you can go on
to learn things like automation i'll
show you a quick example of that we of
course have auto report automation
classes actually a a class that's two
days long we learn how to create XL
controls like here's a command button
here are four option buttons
here's a check box here's what is called
a combo box which you might refer to as
a drop-down list but with this connect
button i'm asking the user for their
user ID and password
unfortunately i can't hide the password
here so everybody now knows my password
but now that they find on it and you'll
notice I have two reports here they they
both happen to be from the same database
but whether they're from the same
database or not
once the user presses actual
this radio button option button
I've find onto a space retrieved into
both of those reports whether they're
from the same database or not and find
off from essbase all-in-one automated
motion now when they click on budget
they get different data in both reports
when they check on profit instead of
profit percent they get different data
up here in this report when they select
instead of Illinois they select Oklahoma
they get different data in both reports
automatically through automation and
another method of automation here use
something like this i have a template
report which people want this report but
for many different selections instead of
just selecting the west market in
quarter to $TIME and forgetting what
they everyone is here with me on I this
i'm getting the actual data for quarter
to $TIME in the West in this report
these are my what are called three page
headers here i'll hide that again but
they want this report here and i put
actual west and quarter to $TIME in this
dynamic title here but your end user
might not know essbase at all but they
have a login and here i do hi their
password and when they click click on
create reports they can select as many
markets as they want Florida New
Hampshire california for whatever time
period they want let's see let's say
November here and what ever scenario
they want
let's say variance and once they press
build here notice that there's only two
sheets in this workbook main and
template here once they press build they
automatically get three more sheets
because now I have the variance
California october i guess i selected
instead of november i must move my mouse
variance for New Hampshire in October
and the Florida sheet says variance for
florida in October
so that's some of the things that you
can do with automation so that's in a
what's called the spreadsheet tool kit
class a two-day class using Excel
controls learning how to write
relatively simple visual basic for
applications macros which include inside
them a space functions like connecting
retrieving disconnecting and other
things like that so there's a lot to
learn and you just begun
so if you have questions feel free to
bring them up now I don't notice that
anybody has entered a question in the
chat area or in the questions area or
nobody spoken up vocally at this point
so i guess you don't have any questions
at this time I'll hang on for a while
but if you have for the questions feel
free to call me at the contact
information you see there or to email me
at my email address that you see there
and i do want to thank you all for
attending today and hope to see you
again in the future webinar or class
thanks again
ah you always somebody have a question
with their hand up in the chat area
nope let me see if I can attend the u.s.
jim has his hand up Jim as you can speak
over the phone I don't know how how to
unmute you I've unmuted you from my and
i'm not sure how to do it on your end
you want to type something in the
questions area or the chat area that's
fine thank you you lined up and you're
certainly welcome
uh oh oh let's see offense
thanks again you like that hope to see
you on a on a future course there
and thank you Jim Michael Susan Yolanda
for attending today there were some
others earlier unmuted