Hyperion Essbase Interview Questions
Que 1: What are the two storage options available in Essbase and what are the differences?
Ans: ASO( Aggregate Storage Option) - Used for 1. If we have large number of dimensions(generally more than 10) 2. Most of the dimensions are sparse 3. We cannot write back in ASO. I heard recently that ASO will also have the writeback capability. I don't remember.
BSO (Block Storage Option)- 1. Dimensions are less and dense(recommended values for no of dim are between 4 and 8) . 2. We can write back hence much suited for planning applications.
Financial applications are generally dense structures and normal analytical cubes are sparse. Here we also need to understand what are these dense and sparse members. When the intersections or cells of two dimensions mostly contains a value it is dense. Say for example we have two dimensions period(Month or quarter) and region and we are calculation the sales amount. Generally maximum regions(countries, cities) will have some sales value for a particular month or quarter. Hence we can say that our period dimensions would be dense. But now instead of period, consider another dimension products. Now there are some products which are sold in only some particular regions hence for them there will be no values in other regions or will have very low percentage of data values hence the structure will become sparse.
Now the question arises what is the use of calling them dense or sparse. What difference does it make. Do some research and try to find the answer.
Que 2: Can we build dimensions directly from data sources without using rule files.
Ans: No.
Que 3: When do we generally use build rules files?
Ans: To automate the process of creating dimensions with thousands of members.
Que 4: What are the three primary build methods for building dimensions?
Ans: 1. Generation references
2. level references
3. Parent-Child references.
Que 5: In what case we can define generation 1 to a feild in generation build method.
Ans: We cannot define that as Generation 1 is not valid.
Que 6: Suppose we have assigned Generation 2 and Generation 4 as of now and think of adding generation 3 later some time. Can we build the dimension.
Ans: No. If gen 2 and gen 4 exists, we must assign gen 3.
Que 7: Can we create more than 2 dimensions using only one build rule file.
Ans: Yes, we can do that but it is recommended to use seperate rule file for each dimension.
Que 8: What is UDA( user defined attributes). How are they different than Aliases.
Ans: UDA represents the class of the members. Aliases are just another names of the members. both are different and has different usage.
Que 9: Can we query a member for its UDA in a calculation script.
Ans: Yes. You can query a member for its UDA in a calculation script.
Que 10: How does UDA's impact database size?
Ans: Absolutely no impact as UDA's do not require additional storage space.
Que 11: What is the difference between UDA's and Attribute dimensions?
Ans : Attribute dimensions provides more flexibility than UDA's. Attribute calculations dimensions which include five members with the default names sum, count, min, max and avg are automatically created for the attribute dimensions and are calculate dynamically.
Que 12: How does Attribute dimensions and UDA's impact batch calculation performance?
Ans: UDA's- No Impact as they do not perform any inherent calculations.
Attribute dim- No Impact as they perform only dynamic calculations.
Que 13: How can we display UDA's in reports? How do they impact report report performance.
Ans: UDA's values are never displayed in the reports and hence do not impact report performance.
Que 14: How does Attribute dim impact report performance?
Ans: They highly impact the report performance as the attributes are calculated dynamically when referenced in the report. For very large number of att dim displayed in the report, the performance could drastically reduce.
Que 15: While loading the data, you have applied both the selection criteria as well as rejection criteria to a same record. What will be the outcome.
Ans: Thw record will be rejected.
Que 16: How is data stored in the Essbase database?
Ans: Essbase is an file based database where the data is stored in PAG files of 2 GB each and grows sequentially.
Que 17: Can we have multiple databases in one single application?
Ans: Yes. But only one database per application is recommended.
Que 18: Can we have one ASO database and one BSO database in one single application. If yes, how and If No, why.
Ans 18. No. Because we define ASO or BSO option while creating the appication and not database. Hence if the application is ASO, the databases it contains will be that type only.
Que 19: What are the file extensions for an outline, rule file and a calc script.
Ans: .OTL, .RUL and .CSC
Que 20: What is the role of provider services.
Ans: To communicate between Essbase and Microsoft office tools.
Que 21: What is an alternative to create metaoutline, rule file and load data.
Answer: Integration services and in version 11, we have Essbase studio.
Que 22: Can we start and stop an application individually. How ca this be used to increase performance.
Ans: Yes. We can manage our server resources by starting only the applications which receive heavy user traffic. When an application is started, all associated atabases are brought to the memory.
Que 23: We have created an application as unicode mode. Can we change it later to non-unicode mode.
Ans: No.
Que 24: How can I migrate an application from my test environment to the production environment. Can I still do this if my test environment is a windows one and my production env is UNIX one. Also can I still do it if my test env and prod env versions are different.
Ans: Hyperion Administrative services console provides a migration utility to do this but onluy the application, database objects are migrated and no data is transferred. the answers to other two questions are Yes.
Que 25: A customer wants to run two instances of an Essbase server on a same machine to have both test env and Development env on the same server. Can he do that?
Ans: Yes. We can have multiple instances of an Essbase server on a single machine and there will be dfferent sets of windows services for all these instances.
Que: 26 suppose I have a dimension A with members B and C and I do not want B and C to roll up to A. how can I do this.
Ans: Using (~) exclude from consolidation operator.
Que 27: What does never consolidate operator(^) do?
Ans: It prevents members from being consolidate across any dimension.
Que 28: Can we have multiple metaoutlines based on one OLAP model in Integration services?
Ans: Yes
Que 29: Can we have an metaoutline based on two different OLAp models.
Ans: No.
Que 30: What is hybrid analysis?
Ans: Lower level members and associated data remains in relational database where as upper level members and associated data resides in Essbase database.
Que 31: Why top-down
calculation less efficient than a bottom-up calculation?Being less efficient,
why do we use them.
Ans: In the process it
calculates more blocks than is necessary. Sometimes it is necessary to perform
top-down calculation to get the correct calculation results.
Que 32: On what basis you
will decide to invoke a serial or parellel calculation method.
Ans: If we have a single
processor, we will use serial calculation but if we have multiple processors we
can break the task into threads and make them run on different processors.
Que 33: What is block
locking system?
Ans: Analytic services(or
Essbase Services) locks the block and all other blocks which contain the childs
of that block while calculating this block is block locking system.
Que 34: What are the types
of partitioning options available in Essbase?
Ans: 1. Replicated
partition. 2. Transparent partition 3. Linked partition.
Que 35: Dynamic calc
decreases the retreival time and increases batch database calculation time. How
true is the statement?
Ans: The statement should be
just opposite. As dynamic calc members are calculated when requested, the
retreival time should increase.
Que 36: A customer has a
cube with 13 dimensions and the performance was fine. Now they have added two
more dimensions in the existing cube and the performance becomes very very
slow. Customer said logically nothing wrong with the two new dimensions they
have added. What could be the reason for slowdown.
Ans: Even I dont have any
clue. I experienced this few days back with a customer. If you can throw some
light on this, please post your answer.
If you have more questions with answers, please post them so that all of us can benefit.
If you have more questions with answers, please post them so that all of us can benefit.
How
do you optimize outline?
Usually the outline is
optimized using the
hourglass design
for dimension ordering i.e,·
Dimension with Accounts tag· Dimension with Time tag· Largest Dense dimension·
Smallest dense dimension· Smallest Sparse dimension· Largest Sparse dimension
What
are the ways to improve performance during data loads?
There are several ways to
optimize load1.
Grouping of Sparse member
combinations2. Making the data source as small as possible3. Making source
fields as small as possible4. Positioning the data in the same order as the
outline5. Loading from Essbase Server 6. Managing parallel data load processing
What
are the design considerations for calculation optimization?
You can configure a
database to optimize calculation performance. The best configuration for
thesite depends on the nature and size of the database.· Block Size(8Kb to
100Kb) and Block Density· Order of Sparse Dimensions· Incremental Data Loading·
Database Outlines with Two or More Flat Dimensions· Formulas and Calculation
Scripts
When
does Fragmentation occur?
Fragmentation is likely to
occur with the following:
· Read/write databases
that users are constantly updating with data· Databases that execute
calculations around the clock · Databases that frequently update and
recalculate dense members· Data loads that are poorly designed· Databases that
contain a significant number of Dynamic Calc and Store members· Databases that
use an isolation level of uncommitted access with commit block set to zero
How
can you measure fragmentation?
You can measure
fragmentation using the average clustering ratio or average
fragmentationQuotient.
Using the average
fragmentation quotient
Any quotient above the
high end of the range indicates that reducing fragmentation may help
performanceSmall (up to 200 MB) 60% or higher Medium (up to 2 GB) 40% or higher
Large (greater than 2 GB) 30% or higher
Using the average
clustering ratio:
The average clustering
ratio database statistic indicates the fragmentation level of the data
(.pag)files. The maximum value, 1, indicates no fragmentation.
How
do you can prevent and remove fragmentation?
You can prevent and remove
fragmentation:· To prevent fragmentation, optimize data loads by sorting load
records based upon sparsedimension members. For a comprehensive discussion of
optimizing data load by grouping sparsemembers.· To remove fragmentation,
perform an export of the database, delete all data in the database
withCLEARDATA, and reload the export file.· To remove fragmentation, force a
dense restructure of the database.
Why
is database restructuring?
As your business changes,
you change the Essbase database outline to capture new product lines, provide
information on new scenarios, reflect new time periods, etc. Some changes to a
databaseoutline affect the data storage arrangement, forcing Essbase to
restructure the database.
What
are the types of database restructuring?
The two ways by which a
database restructure is triggered:
•
Implicit Restructures
•
Dense restructure
•
Sparse restructure
•
Outline-only restructure
•
Explicit Restructures
What
are the conditions affecting Database restructuring?
Intelligent Calculation,
name changes, and formula changes affect database restructuring:· If you use
Intelligent Calculation in the database, all restructured blocks are marked as
dirtywhenever data blocks are restructured. Marking the blocks as dirty forces
the next defaultIntelligent Calculation to be a full calculation.· If you
change a name or a formula, Essbase does not mark the affected blocks as
dirty.Therefore, you must use a method other than full calculation to
recalculate the member or thedatabase.
What
are the files used during Restructuring?
When Essbase restructures
both the data blocks and the index, it uses the files described
essxxxxx.pag
Essbase data file
essxxxxx.ind
Essbase index file
dbname.esm
Essbase kernel file that
contains control information used for db recovery
dbname.tct
Transaction control table
dbname.ind
Free fragment file for
data and index free fragments
dbname.otl
Outline file in which is
defined all metadata for a database and how data is stored
What
are the actions that improve performance for restructuring?
There are a number of
things you can do to improve performance related to databaserestructuring:· If
you change a dimension frequently, make it sparse. · Use incremental
restructuring to controlwhen Essbase performs a required database
restructuring. · Select options when you save amodified outline that reduce the
amount of restructuring required.
Which
restructure operations are faster?
These types of restructure
operations are listed from fastest to slowest:· Outline only (no index or data
files)· Sparse (only index files) · Dense (index files and datafiles) as a
result of adding, deleting, or moving members and other operations · Dense
(indexand data files) as a result of changing a dense dimension to sparse or
changing a sparsedimension to dense
What
is Implicit Restructures?
Essbase initiates an
implicit restructure of the database files after an outline is changed
usingOutline Editor or Dimension Build. The type of restructure that is
performed depends on the typeof changes made to the outline
What
is Explicit Restructures?
When you manually initiate
a database restructure, you perform an explicit restructure. Anexplicit
restructure forces a full restructure of the database. A full restructure
comprises a denserestructure plus removal of empty blocks.
What
is Dense restructure?
If a member of a dense
dimension is moved, deleted, or added, Essbase restructures the blocks inthe
data files and creates new data files. When Essbase restructures the data
blocks, it regeneratesthe index automatically so that index entries point to
the new data blocks.Empty blocks are not removed. Essbase marks all
restructured blocks as dirty, so after a denserestructure you need to
recalculate the database.
What
is Sparse restructure?
If a member of a sparse
dimension is moved, deleted, or added, Essbase restructures the indexand
creates new index files.Restructuring the index is relatively fast; the amount
of time required depends on the size of theindex.
What
is Outline-only restructure?
If a change affects only
the database outline, Essbase does not restructure the index or data
files.Member name changes, creation of aliases, and dynamic calculation formula
changes areexamples of changes that affect only the database outline.
Explain
the process of dense restructure?
To perform a dense
restructure, Essbase does the following:1. Creates temporary files that are
copies of the .ind, .pag, .otl, .esm, and .tct files. Eachtemporary file
substitutes either N or U for the last character of the file extension, so
thetemporary file names are .inn, essxxxxx.inn, essxxxxx.pan, dbname.otn,
dbname.esn, anddbname.tcu. 2. Reads the blocks from the database files copied
in step 1, restructures the blocks in memory, and then stores them in the new
temporary files. This step takes the mosttime. 3. Removes the database files
copied in step 1, including .ind, .pag, .otl, .esm, and .tctfiles. 4. Renames
the temporary files to the correct file names: .ind, .pag, .otl, .esm, and
.tct.
Explain
the process of sparse restructure?
When Essbase does a sparse
restructure (restructures just the index), it uses the following
files:·essxxxxx.ind· dbname.otl· dbname.esm
What
is data compression?
Essbase allows you to
choose whether data blocks that are stored on disk are compressed, as wellas
which compression scheme to use. When data compression is enabled, Essbase
compressesdata blocks when it writes them out to disk. Essbase fully expands
the compressed data blocks,including empty cells, when the blocks are swapped into
the data cache.Generally, data compression optimizes storage use. You can check
compression efficiency bychecking the compression ratio statistic.
What
are types of data compression?
Essbase provides several
options for data compression:
1.
Bitmap compression
, the default. Essbase
stores only non-missing values anduses a bitmapping scheme. A bitmap uses one
bit for each cell in the data block, whether the cellvalue is missing or
non-missing. When a data block is not compressed, Essbase uses 8 bytes tostore
every non-missing cell. In most cases, bitmap compression conserves disk space
moreefficiently. However, much depends on the configuration of the data.
2.
Run-length encoding (RLE)
. Essbase compresses
repetitive, consecutive values --any valuethat repeats three or more times
consecutively, including zeros and #MISSING values. Each datavalue that is
repeated three or more times uses 8 bytes plus a 16 byte repetition factor.
3.
zlib compression.
Essbase builds a data
dictionary based on the actual data being compressed.This method is used in
packages like PNG, Zip, and gzip. Generally, the more dense or heterogeneous
the data is, the better zlib will compress it in comparison to bitmap or
RLEcompression.
4.
Index Value Pair compression
. Essbase applies this
compression if the block density is lessthan 3%.Index Value Pair addresses
compression on databases with larger block sizes, where the blocks are highly
sparse. zlib does not use this.
5.
No compression
. Essbase does not
compress data blocks when they are written to disk
When
do you use RLE over Bitmap Compression?
Use RLE over Bitmap
When,Average block density very low (< 3%).Database has many consecutive
repeating Values.
When
do you disable compression?
You may want to disable
data compression if blocks have very high density (90% or greater) andhave few
consecutive, repeating data values. Under these conditions, enabling
compressionconsumes resources unnecessarily. Don't use compression if disc
space/memory is not an issuecompared to your application. It can become a drain
on the processor.
What
are data locks?
Essbase issues write
(exclusive) locks for blocks that are created, updated, or deleted, and
issuesread (shared) locks for blocks that should be accessed but not modified.
By issuing theappropriate locks, Essbase ensures that data changed by one
operation cannot be corrupted by aconcurrent update.
What
is a transaction?
When a database is in
read/write mode, Essbase considers every update request to the server (such as
a data load, a calculation, or a statement in a calculation script) as a
transaction.
What
is transaction control file?
Essbase tracks information
about transactions in a transaction control file (dbname.tct).The transaction
control file contains an entry for each transaction and tracks the current
state of each transaction (Active, Committed, or Aborted).
What
is isolation level and what are the types of isolation levels?
Isolation levels determine
how Essbase commits data to disk. Essbase offers two isolation levelsfor
transactions --
committed access
and
uncommitted access
(the default).
What
is commited access?
When data is committed, it
is taken from server memory and written to the database on disk.Essbase
automatically commits data to disk. There are no explicit commands that users
performto commit data blocks.
Talk
about committed and uncommitted access?Committed:
Committed at the end of a
transaction. Data retained till then.All blocks in question locked.Pre-Image
Access: If enabled, Read only access allowedWait Times:IndefiniteImmediate
Access or no Wait No. of Seconds Specified
Uncommitted:
Committed only at
synchronization points.Block by Block Locks.Commit Row: No of rows of data
loaded when Sync point occurs.Commit Block: No. of Blocks Modified when Sync
Point occurs.For Rollback, Commit Row=0 and Commit Block=0
What
are the advantages and disadvantages of using committed access?
You can optimize data
integrity by using committed access.Setting the isolation level to committed
access may increase memory and time requirements for database restructure.
Which
transaction is always in committed mode?
The Spreadsheet Add-in
lock and Send and the Grid API are always in CommittedAccess Mode
What are the memory caches
used by Essbase to coordinate memoryusage?
Essbase uses five memory
caches to coordinate memory usage 1. Index Cache 2. DataFile Cache 3. Data
Cache 4. Calculator Cache 5. Dynamic Calculator Cache
What
is Index cache?
The index cache is a
buffer in memory that holds index pages. How many index pages are inmemory at
one time depends upon the amount of memory allocated to the cache.
What
is Data file cache?
The data file cache is a
buffer in memory that holds compressed data files (.pag files).
Essbaseallocates memory to the data file cache during data load, calculation,
and retrieval operations, asneeded. The data file cache is used only when
direct I/O is in effect.
What
is Data cache?
The data cache is a buffer
in memory that holds uncompressed data blocks. Essbase allocatesmemory to the
data cache during data load, calculation, and retrieval operations, as needed.
What
is Calculator cache?
The calculator cache is a
buffer in memory that Essbase uses to create and track data blocksduring
calculation operations.
What
is Dynamic calculator cache?
The dynamic calculator
cache is a buffer in memory that Essbase uses to store all of the blocksneeded
for a calculation of a Dynamic Calc member in a dense dimension (for example,
for aquery).
What
are the memory caches used by Essbase to coordinate memory usage?
Essbase uses five memory
caches to coordinate memory usage
Index
Cache: Min
-
1024
KB (1048576 bytes)
Default
-
Buffered I/O : 1024 KB (1048576bytes);Direct I/O : 10240 KB (10485760 bytes)
Opt
-Combined
size of all
essn.ind
files,
if possible; as large as possible otherwise.Do not set this cache size higher
than the totalindex size, as no performance improvement results.
Data
File Cache: Min
-
Direct
I/O: 10240 KB(10485760 bytes)
Default
-Direct
I/O: 32768KB(33554432 bytes)
Opt
-Combined
size of all
essn.pag
files,
if possible; otherwise aslarge as possible.This cache setting not used if
Essbase is set to use buffered I/O.
Data Cache:
Min
-
3072
KB (3145728 bytes)
Default
-
3072 KB (3145728 bytes)
Opt
-0.125 * the value of data file cache size.
Calculator Cache:Min - 4
bytes Max:
200,000,000
bytes
Default
-
200,000 bytes
Opt
-Thebest
size for the calculator cache depends on the number and density of the
sparsedimensions in your outline. The optimum size of the calculator cache
depends on theamount of memory the system has available.
What
is the structure of currency applications?
In a business application
requiring currency conversion, the
main database
is divided into atleast
two slices. One slice handles input of the local data, and another slice holds
a copy of theinput data converted to a common currency.Essbase holds the
exchange rates required for currency conversion in a separate
currencydatabase
. The currency database
outline, which is automatically generated by Essbase from themain database
after you assign the necessary tags, typically maps a given conversion ratio
onto asection of the main database. After the currency database is generated,
it can be edited just likeany other Essbase database.
What
are the three dimension that should be present in main database of
currencyapplication?
The main database outline
can contain from 3 to
n
dimensions. At a minimum,
the main databasemust contain the following dimensions:· A dimension tagged as
time.· A dimension tagged as accounts.· A market-related dimension tagged as
country.
What
are the dimensions that should be present in currency database of
currencyapplication?
A currency database always
consists of the following three dimensions, with an optional fourthdimension:·
A dimension tagged as time, which is typically the same as the dimension tagged
as time inthe main database.
· A dimension tagged as
country, which contains the names of currencies relevant to themarkets (or
countries) defined in the main database.· A dimension tagged as accounts, which
enables the application of various rates to membersof the dimension tagged as
accounts in the main database.· A currency database, which typically includes
an optional currency type dimension, whichenables different scenarios for
currency conversion.
What
are the conversion methods supported by Essbase for currency applications?
Different currency
applications have different conversion requirements. Essbase supports
twoconversion methods:· Overwriting local values with converted values.·
Keeping local and converted values.Either of these two methods may require a
currency conversion to be applied at report time.Report time conversion enables
analysis of various exchange rate scenarios without actuallystoring data in the
database.
What
is the process to build a currency conversion application and perform
conversions?
To build a currency
conversion application and perform conversions, use the following process:1.
Create or open the main database outline. 2. Prepare the main database outline
for currencyconversion. 3. Generate the currency database outline. 4. Link the
main and currencydatabases. 5. Convert currency values. 6. Track currency
conversions. 7. If necessary,troubleshoot currency conversion.
What
is CCONV?
After you create a
currency conversion application, you convert data valuesfrom a local currency
to a common, converted currency by using the CCONV command incalculation
scripts Ex: CCONV USD;CALC ALL;
Can
we convert the converted currency back into its local currency?
You can convert thedata
values back to the original, local currencies by using the CCONV
TOLOCALRATEcommand.
When
you convert currencies using the CCONV command, are the resulting data blocksare
marked as dirty or clean?
When you convert
currencies using the CCONV command, theresulting data blocks are marked as
dirty for the purposes of Intelligent Calculation. Thus,Essbase recalculates
all converted blocks when you recalculate the database.
What
is CCTRACK?
You can use the CCTRACK
setting in the essbase.cfg file to controlwhether Essbase tracks the currency
partitions that have been converted and the exchange ratesthat have been used
for the conversions. By default CCTRACK is turned on.
What
are the reasons to turn off CCTRACK?
For increased efficiency
when convertingcurrency data between currency partitions, you may want to turn
off CCTRACK. For example,you load data for the current month into the local
partition, use the DATACOPY command tocopy the entire currency partition that
contains the updated data, and then run the conversion onthe currency
partition.
How
can you turn off CCTRACK?
You can turn off CCTRACK
in three ways: · Use the SETCCTRACKCALC ONOFF command in a calculation script to
turn off CCTRACK temporarily ·Use the CLEARCCTRACK calculation command to clear
the internal exchange rate tablescreated by CCTRACK.Set CCTRACK to FALSE in the
essbase.cfg file.
What
is LRO (Linked reporting objects)?
An LRO is an artifact
associated with a specificdata cell in an Essbase database. LROs can enhance
data analysis capabilities by providingadditional information on a cell.An LRO
can be any of the following:· A paragraph of descriptive text (a "cell
note")· A separate file that contains text, audio, video, or graphics· A
URL for a Web site· A link to data in another Essbase database
How
do you create LRO's?
Users create linked
objects through Essbase Spreadsheet Add-in for Excel by selecting a data
celland choosing a menu item. There is no limit to the number of objects you
can link to a cell. Theobjects are stored on the Essbase Server where they are
available to any user with the appropriateaccess permissions. Users retrieve
and edit the objects through the Essbase Spreadsheet Add-infor Excel Linked
Objects Browser feature, enabling them to view objects linked to the
selectedcell.
Does
adding or removing links to a cell does not affect the cell contents?
No.LROs are linked to data
cells --not to the data contained in the cells. The link is based on aspecific
member combination in the database.
Give
a few examples of LRO's?
Ex1: A sales manager may
attach cell notes to recently updated budget items. Ex2: A financemanager might
link a spreadsheet containing supporting data for this quarter's results. Ex3:
A product manager might link bitmap images of new products. Ex4: A sales
manager may link theURL of a company's Web site to quickly access the info on
the Web
How
does Essbase locate and retrieve linked objects?
Essbase uses the database
index to locate and retrieve linked objects. If you clear all data valuesfrom a
database, the index is deleted and so are the links to linked objects. If you
restructure adatabase, the index is preserved and so are the links to linked
objects.
Do
shared members share LRO's?
Shared members share data
values but do not share LROs. This is because LROs are linked tospecific member
combinations and shared members do not have identical member combinations.To
link a given object to shared members, link it to each shared member
individually.
Can
you change the member combination associated with any linked object?
You cannot change the
member combination associated with any linked object. To move anobject to
another member combination, first delete it, then use Essbase Spreadsheet Addin
for Excel to re-link the object to the desired member combination.
Why
do we need to limit the LRO file sizes for storage conversion?
Because Essbase stores
linked files in a repository on the server and, by default, the size
isunlimited. Limiting the file size prevents users from taking up too much of
the server resources by storing extremely large objects. You can set the
maximum linked file size for eachapplication. If a user attempts to link a file
that is larger than the limit, an error message displays.The maximum file size
setting applies only to linked files and does not affect cell notes or URLs.The
lengths of the cell note, URL string, and LRO descriptions are fixed.
What
is partitioning?
A partition is the piece
of a database that is shared with another database. An Essbase
partitionedapplication can span multiple servers, processors, or computers.
What
is Essbase Partitioning?
Essbase Partitioning is a
collection of features that makes it easy to design and administer databases
that span Essbase applications or servers. Partitioning is licensed separately
fromEssbase.
What
are the types of Partitions available in Essbase?
Three types of partitions
are there.1.
Transparent
partition:
A form of shared partition
that provides the ability to access and manipulate remote datatransparently as
though it is part of your local database. The remote data is retrieved from
thedata source each time you request it. Any updates made to the data are
written back to the datasource and become immediately accessible to both local
data target users and transparent datasource users2.
Replicated
Partition
:A portion of a database,
defined through Partition Manager, used to propagate an update to datamastered
at one site to a copy of data stored at another site. Users can access the data
as though itwere part of their local database.3.
Linked
Partition:
A shared partition that
enables you to use a data cell to link two databases. When a user clicks
alinked cell in a worksheet, Essbase opens a new sheet displaying the
dimensions in the linkeddatabase. The user can then drill down those
dimensions.
What
is the process for designing a partitioned database?
Here is the suggested
process for designing a partitioned database.1. Learn about partitions.2.
Determine whether the database can benefit from partitioning.3. Identify the
data to partition.4. Decide on the type of partition.5. Understand the security
issues related to partitions.
What
are the parts of partition?
Partitions contain the
following parts,
·
Type
of partition:
A flag indicating whether
the partition is replicated, transparent, or linke·
Data
source information:
The server, application,
and database name of the data source.·
Data
target information:
The server, application,
and database name of thedata target.·
Login
and password:
The login and password
information for the data source and the datatarget.·
Shared
areas:
A definition of one or
more areas, or sub cubes, shared between the data sourceand the data target.·
Member
mapping information:
A description of how the
members in the data source map tomembers in the data target.·
State
of the partition:
Information about whether
the partition is up-to-date and when the partition was last updated.
What
are benefits of partitioning?
Partitioning applications
can provide the following benefits:· Improved scalability, reliability,
availability, and performance of databases· Reduced database sizes· More
efficient use of resources· Data synchronization across multiple databases.·
Outline synchronization across multiple databases.· Ability for user navigation
between databases with differing dimensionality.
Can
you define different types of partitions between the same two databases?
No
Can
a single database serve as the data source or data target for multiple
partitions?
Yes
What
is overlapping partition?
An overlapping partition
occurs when similar data from two or more databases serve as the datasource for
a single data target in a partition.
Is
overlapping partition valid in all the partitions?
An overlapping partition
is allowed in linked partitions, but is invalid in replicated andtransparent
partitions and generates an error message during validation.
When
do you use substitution variables in partitions?
Using substitution
variables in partition definitions enables you to base the partition definition
ondifferent members at different times.
Can
we use attribute values to partition a database?Yes
,You can use attribute
functions for partitioning on attribute values. But you cannot partitionan
attribute dimension.
Can
we partition an attribute dimension?No
, we cannot partition an
attribute dimension.
What
is the limitation on version and mode during partition?
Both ends of a
transparent, replicated, or linked partition must be on the same release level
of Essbase Server. For example, if the source of a linked partition is on a
Release 7.1.2 server, thetarget must also beon a Release 7.1.2 server.
In addition, for
transparent and replicated (but not linked) partitions, the applicationmode of
both ends of the partitions must be the same--either Unicode mode or
non-Unicode mode.
What are the major difference
between ASO & BSO?
If we have more dimensions
(generally more than 10) then we will go for ASO that simply rollupIf we have
less dimensions then we will go for BSO We cannot write back in ASO we can
write back in BSO Most of the dimensions are sparse in ASO Most of the
dimensions are dense inBSO
What
is "Enterprise Analytics"?
ASO in System 9 is called
Enterprise Analytics.
Explain
in detail about the features of ASO?
· ASO databases are
created specifically to deal with the requirements of very large sparse
datasets with a high no of dimensions and potentially millions of members. ·
ASO
do
not haveindexes or data blocks.
· ASO
do
not use calculation scripts
. Bcoz calculations are
notcomplex. · ASO uses a
new
kind of storage mechanism
that allows improved
calculation timesfrom 10 to100 times faster than BSO. · ASO can store up to 252
dimensional combinations. · Thefront end tools usually do not care if the
database is ASO or BSO. Even Maxl sees minor differences. · We can have attribute
dimensions in ASO. · In ASO there is
no
concept as denseand sparse dimensions.
· We
do
not have two pass logic and built in time balancefunctionality.
( time balance
functionality is present from 9.3 version onwards). · Member formulas are not
supported in stored hierarchies. · Only non consolidation (~) and addition
(+)operators are supported in shared hierarchies. · We cannot create more than
1 database in ASO. ·ASO does not utilize procedural calculation scripts. ·
ASO
formulas are written in MDXsyntax.
· ASO has Accounts
dimension but it is completely different from the account dimensionof BSO. ·
ASO
is read-only
. You cannot write to ASO
databases, but there is a workaroundusing transparent partitions and pointing
to an attached BSO database for those duties. ·
You
canload data to level zero members only.
· The database must
restructure after any members in thestandard dimensions are added ,deleted or
moved. In fact most actions on an ASO outline willeither cause a loss of data
or restructure.
How
do you differentiate ASO applications?
You can easily
differentiate the ASO database in the Administrative Services Console by the
redstar beside the application name.
How
do you create an ASO application?
ASO has two types of
hierarchies: stored anddynamic. The dimension can contain both types of
hierarchies (if you enable multiplehierarchies).Other properties that need to
be set for dimensions and members include ·Dimension Type · Data Storage(store,
never share, label only) · Member solve order · AliasYou can add dimensions
using the visual editor or the rules files.Unlike in block storage ASO does not
allow you to preview the outline changes. If you areunsure of the build file,
make a backup of your outline before running the new build rule. For ASO
databases after the data values are loaded into the level 0 cells of an
outline, the databaserequires no separate calculation step. For retrieving from
the ASO database, retrieve and analyze just as in BSO database.
How
do you create an ASO database using ASO Outline Conversion Wizard ?
You can alsocreate an ASO
database using ASO Outline Conversion Wizard. This wizard uses the existingBSO
database to convert to an ASO database. This is advantageous because we do not
need tocreate an ASO database from the Scratch. However we need perform
reengineering of dimensions and hierarchies.
How
do you create ASO in the Automated Way?
The final way of creating
an ASOapplication is by using "Create Application" , "Create
Database" ,"Create Outline " commandsusing MaxL. Typically this
method is used when you are running the MaxL command as a partof the batch
job.**Unicode is supported for BSO databases only.**Data Mining is not
supported by ASO databases.**MDX is the only mechanism for defining member
calculations in databases.Unicode applications use UTF-8 encoding form to
interpret and store character text, providingsupport for multiple character
sets.To set up a Unicode application1. Setup a computer for Unicode support by
doing one of · Install the for that supports UTF-8 encoding· Install a Unicode
editor 2. Set the Essbase server to Unicode Mode via Administrative Services or
MaxL.3. Check the Unicode box when creating a new Unicode -mode application.4.
You can also migrate from non-Unicode applications to Unicode applications (but
not theother way round).Report Scripts are outdated but still can be helpful
when extracting subsets of data from Essbasefor online backups or feeding into
other systems.The Wizards Tab of Administrative Services Console menu has the
followingcomponents1. Migration2. Aggregate Storage Outline Conversion3.
Aggregate Storage Partition4. User Setup
5. Data Mining Wizard
Eventhough I increased
NetDelay and NetRetrycount in essbase.cfg, but that does notresolve this
problem. She was using IE7 on XP SP3.Here is the reason and resolution for the
issue:By design, Internet Explorer imposes a time-out limit for the server to return
data.The time-out limit is five minutes for versions 4.0 and 4.01 and is 60
minutes forversions 5.x and 6. Also, the time-out limit is 30 seconds for
Windows InternetExplorer 7. As a result, Internet Explorer does not wait
endlessly for the server tocome back with data when the server has a
problem.Apparently the query which the user was running takes around 45
seconds. The IE7time out setting was causing it to error out.we need to change
(add) three new registry keys to resolve the issue.1.Open the Registry (Start -> Run -> Regedit)Locate the
following
section:[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\InternetSettings]\2.Create the following new keys for dword with
decimal values:"ReceiveTimeout"=dword:00dbba00"KeepAliveTimeout"=dword:00180000"ServerInfoTimeout"=dword:001800003.Restart the machine for the new settings to take
effectIf the above settings does not make any difference and the version
of SmartView is9.3.1.2, it may need to upgrade SmartView 9.3.1.4 or higher
version.Here are the Screen shots for changing the registry settings.
Right click and select
DWORD (32-bit) valueRename to
KeepAliveTimeout
Right click and select
Modify
Select
Decimal
and enter the following
data
00180000
repeat the same process
for other 2 keys
Validate whether you have
right keys or not.
If you are planning admin
and had to restart planning services.here is the order you have to follow to
restart.
Stop OrderPlanning ServiceRMI registryAASStart
OrderAASRMI registryPlanning ServiceScreen shots of services shown below:
Posted by
Dornakal's-Hyperion-Blog at10:19 AM 1 comments Links to this postLabels: Order of restarting planning services
please find some Q&A,
which provide you with essbase overview.
What is Essbase OLAP
server?
An OLAP server is a
multidimensional database for storing data with an unlimitednumber of
dimensions such as time, region, accounts, channel or products. Essbaseserver
manages analytical data models, data storage, calculations, and data security.
What are the Essbase
Application tools?
Applications tools are
used for extending Essbase applicationsSpreadsheet Add-inSmart view for
Microsoft applicationsCurrency ConversionEssbase SQL interfaceEssbase
Application Programming Interface (API)
What is Partitioning?
Copying a slice of a large
database to work with it locally, or link from your databasedirectly to other
databases.
What is a Hybrid Analysis?
Hybrid analysis integrates
relational databases with Essbase databases to combine thesize and scalability
of the relational database with conceptual power and analyticalcapabilities of
the multidimensional Database.
What are administrative
requests and client requests?
Administrative requests
such as· Logging in and logging out· Starting and stopping applications and
databases,· Viewing users security informationare handled by the Essbase server
agentClient requests such asData loadsSpread sheet reportsData lock and
unlockare handled by the application server (ESSVR)
What is the use of
multidimensional database such as Essbase?
Multidimensional database
supports multiple views of data sets for users who need toanalyze the
relationships between data categories.For example marketing analyst needs
detailed information in different view than themanager.Multidimensional DB
consolidates and calculates data to provide different views. OnlyDatabase
outline, the structure that defines all elements of the DB, limits the numberof
viewsWith the multidimensional DB users can pivot the data to see information
fromdifferent view point, drill down to find more detailed information or drill
up to see anover view.Relational database have more data and have all
transactions information. Whereasthe Essbase has limitations on data which it
can hold. In Essbase the filters (security)can be given until the data cell
value. Whereas in RDBMS the security can be givenonly until table view. In
RDBMS we can see only one view.
What are Standard
dimensions?
Standard dimensions are
those which represent the core components of the businessplan an often relate
to the departmental functionsExamples of standard dimensions are as
followsTimeAccountsProductsMarketDimensionDimensions are static in most
databases. DB dimensions rarely change over the life of an application.
What is an Outline?
Outline is the structure
that defines all elements of the Database. It dictates howdata is to be stored
into the database. Outline defines the structural relationshipbetween the members
in DB. Organizes all data in the DBDefines consolidations and mathematical
relationships between membersIt defines type of dimensions. Aliases, member
formulas etc.The order of the outline is that how dimensions are arranged is as
follows:Dense dimensions followed by sparse dimensions followed by Attribute
dimensions.
How do you order the
Outline?
All the attribute
dimensions should be placed at the end of the outline. Densedimensions should
be placed in the first then followed by the sparse dimensionsThe order
determinesHow quickly calculations are runHow long it takes users to retrieve
informationThe order of the outline for query performance
· Dense· Most queried
Sparse· Least queried Sparse· Most queried Attribute· Least queried
AttributeThe order of Outline for Calculation time· Dense· Smallest Sparse
dimension· Largest Sparse· Smallest Attribute· Largest Attribute
What is the highest level
of consolidation in the outline?
Dimension
Is there any limitation on
number of members?
No. Essbase does not limit
the number of members within a dimension and allows youto add new members as
needed.Parent is a member, which has a branch below it.Child is a member, which
has a parent above it.Siblings are the child members of same immediate parent,
at the same generation.Descendants are all members in branches below a
parent.Ancestors are all members in above a member.Root is a top member in a
branch.Leaf member has no children.Generation number refers to the
consolidation levels within a dimension.Level also refers to a branch within a
dimension.
What is a cell/data value?
A data value is defined by
the intersection of all standard dimensions in the database.
What is the maximum number
of values in the database?
Product of members in each
dimension
Why do we need to have
classification as dense and sparse dimensions?
As the data is no smoothly
and uniformly distributed and data does not exist for themajority of members.
Essbase speeds up data retrieval while minimizing the memoryand disk
requirements.A sparse dimension is a dimension with a low percentage of
available data positionsfilled.A dense dimension is a dimension with a high
probability that one or more data pointsare occupied in every combination of
dimensions.
What are the two types of
internal structures in Essbase?
Data blocksIndex systemThe
two types of internal structures are to store data and access data.
What is a Data block?
Data block is a cube
created for each unique combination of sparse standarddimension members
(provided that atleast one data value exists for sparse membercombination).The
Data block represents all the dense dimension members for its combination of
sparse standard dimensions.Its size depends on number of dense dimensions.
How does Essbase calculate
the data?
Top down order
What is an Index entry?
The index entry provides a
pointer to the data blockEssbase creates Index entry for each data block. The
index entry represents thecombinations of sparse standard dimensions. It
contains an entry for each uniquecombination of sparse standard dimension
members for which atleast one data valueexists.
What happens if you make
all dimensions sparse?
Data blocks are created
for each unique combination of sparse standard dimensionmembers. Thus a large
number of dense blocks are created, and thus a huge indexcontaining pointers
(or addresses) for all those data blocks.Huge index consumes large amount of
memory. The more index entries the longerEssbase searches to find a specific
block.
What happens if you make
all dimensions dense?
Only one huge sparse block
is created with only one index entry. This block is verysparse. This
configuration requires thousands of times more storage than
otherconfigurations. Essbase need to load the entire data block into memory
when itsearches for a data value, which requires enormous amounts of memory.
What is the Design
Process?
Analyze business needs and
plan the databaseDefine the database outlineCheck the system requirementsLoad
the test data into the databaseDefine calculations
Define reportsVerify with
the usersRepeat the process
What are the different
types of data sources?
Flat files (column
formatted)Spreadsheet filesAny RDBMS files
What is a shared member?
The shared member concept
lets two members with the same name share data. Theshared member stores only
pointer to the data contained in the other member, so theEssbase only stores
the data once. Shared members should be in the same dimension.
What dimension can be
given time balance properties?
Only accounts dimensions
can be given time balance, expense reporting, and countryand currency
properties.
What is the use of variance
reporting?
Variance reporting
properties defines how Essbase calculates the difference betweenactual and
budget data in members with @VAR, @VARPER functions in their memberformula.·
Expense reporting (Budget-Actual)· Non-Expense reporting (Actual-Budget)What is
a Function?It is a predefined routine to carry on specific task or calculation.
What is a formula?
Formulas are used to
calculate relationships between members in the DB Outline.
What is a dynamic Calc?
When you tag a member as
dynamic calc, Essbase calculates the combinations of themember only when user
retrieves data, instead of pre calculating membercombinations during the
regular DB.Dynamic calc shorten the regular calc time, but may increase the retrieval
time fordynamically calculated data values.
What are the advantages of
Dynamic calc?
Low disk spaceReduced
database restructure timeReduced back up timeOptimum regular calculation time
What are the members for
which Two-pass calculations can be given?
Accounts
Dynamic calcDynamic calc
and store
How does a user can view
data?
Spread sheetPrinted
reportsReports published on webweb analysissmart view
Can you load data or
calculate data on client machine?
No.Applications and
Databases created on client machine are used only to store databaseobjects,
such as outlines and calc scripts. You cannot load or calculate data on aclient
machine.
What are Database Objects?
Files that are related to
databases are called Objects. Common types of objects are:Database Outlines
(.OTL)Report Scripts (.REP)Calculation Scripts (.CSC)Data Load rules and
Dimension build rules (.RUL)Data sourcesSecurity definitionsLRO’s (Linked
Reporting Objects)Partition definitions
What is a rule file?
Data load rules are set of
operations that Essbase performs on the data from externaldata source file as
it is loaded, or copied into the Essbase database.Specifying the data load
rules is the most common way to load data into theDatabase.Dimension build rule
files create or modify an outline dynamically based on the datain the external
source file.
What is a calculation
script?
Calc script is a text file
with set of instructions telling Essbase how to calculate datain the database.
It has “. csc” extension.What is a Report script?Report script is a text file
with a set of instructions on data retrieval, formatting andoutput to create a
report from the database.Report script has .REP extension.
What is a Linked Reporting
Object (LRO)?
A LRO is an object
associated with a specific data cell in the Essbase database. A LRO
can any of the following:A
paragraph of descriptive text (” cell note”).A separate file that contain text,
audio, video or graphics.An URL (Uniform Resource Locator).A link to data in
another Essbase database.
What are “.EQD” files?
Within spreads sheet
add-in, users can create queries using query designer (EQD).Users can save the
reports in the form of queries (.EQD files)
What are “.sel” files?
With the spreadsheet
add-in, users can define member retrievals with the memberselect feature. If
users want to save member select specification, they can do so witha “.sel”
file.
How can you create a
database?
Application manager
file>new>databaseEssbase administrative services consoleESSCMD (“CREATE
DB”)Maxl (“create database”)Application and database names should be less than
8 characters.create application------maxlCREATEAPP------------ESSCMD
What is annotating
database?
It is a database note that
can provide useful information in the situations where youneed to broadcast
messages to users about status of the database deadlines forupdates and so
onSelect database>set note
What are substitution
variables?
Substitution variables act
as global placeholders for the information that changesregularly.Each variable
has a value assigned to it. The value can be changed at any time by theDB
designer, thus manual changes are reduced. Ex :- currmnth.You cannot use the
substitution variables in formulae that are applied to the DBoutline. The value
of the substitution variable cannot be more than 256 characters.Substitution
variables can be used only inCalculation scriptsReport scripts
Spread sheet add-inSQL
interfaceServer>substitution variableMaxl (for
creating/deleting/updating)Alter systemAlter applicationAlter dbESSCMDCREATE
VARIABLEDELETE VARIABLEUPDATE VARIABLE
What is a location alias?
A location alias maps an
alias name for a DB to the location of that DB.You can use location aliases
only with the @XREF function.With @XREF function you can retrieve data value
from another database to include incalculation in the current database. In this
case, the location alias points to thedatabase from which the value to be
retrieved.Database>location aliasesCreateMaxl ----------------------create
location aliasESSCMD----------------CREATE LOCATIONEdit /DeleteMaxl display
location aliasdrop location aliasESSCMD LIST LOCATIONSDELETE LOCATIONWhat
happens if you open outlines in two instances?If you open same outline with two
instances of application manager using same loginid, each save will overwrite
the changes of the other instance.Copying databaseDatabase > copyMaxl create
database asESSCMD COPYDB
What are important points
while building an outline?
· All members and alias
names should be unique;· Only one dimension should be tagged as accounts, time,
currency type and country;· Level “0” members cannot be label only;· Level “0”
members cannot be assigned formulae but dynamic calc members of
standard dimensions may
have formula;· Dynamic calc should not have more than 100 children;· Boolean
attribute dimensions have only two members.
What are the restructuring
options in saving database?
· All data· Level 0 data
(when all data in the outline is at level 0)· Input data (when you load data
into non level 0 members)· Discard all data (when you expect to reload data or
when outline is radicallychanged that no existing data applies)
How do you set dense and
sparse settings?
Settings>data
storageData dictionary button· You must set the standard dimensions with which
you plan to associate attributedimension as sparse because attributes can only
be associated to sparse standarddimensions.· Application manager automatically
sets attribute dimensions as sparse.
How do you rename members?
· Data dictionary button·
Edit>properties· Manually
When does a DB
restructure?
When you add, delete, or
move non-attribute (standard) dimensions or members,Essbase restructure DB and
you must recalculate your data.
What is Metadata?
Metadata is data is data
about data. Metadata is the data contained in the databaseoutline that
describes the values within a DB.Ex:East>New
York>cola>sales>10000East>New York>cola>sales> is metadata
What are different types
of dimension tags?
·
Time· Accounts· Country·
Currency· Attribute
Can you add time members
that are not tagged as time?
Yes
When do you give an
accounts tag to a dimension?
You can tag a dimension as
accounts if it contains items that you want to measuresuch as profit or
inventory.Time dimension tag says how often you collect and update data. The
time dimensionenables several accounts dimension functions such as first and
last time balances.
What is the significance
of time balance properties?
When you set a time
balance property on a member in an accounts dimension, itaffects how Essbase
calculates the parent of that member in the time dimension.· TB FIRST (The
parent value is the value of the first member in the branch)· TB LAST (The
parent value is the value of the last member in the branch)· TB AVG (The parent
value represents the average value of the children)· TB NONE (default; rolls up
parents in the time dimension in the usual way)Skip Properties· None· Missing· Zeros· Missing and
zerosSkip properties, expense reporting, Time Balance properties are applicable
only tothe accounts dimension.
What is a Two-Pass
calculation?
By default Essbase
calculates outlines from the bottom up first calculating the valuesfor children
and then values for parent. Sometimes however the values of childrendepend may
be based on the values of parent or the values of other members in theoutline.
To obtain correct values for these members, Essbase must first calculate
theoutline and then recalculate the members that are dependent on the
calculatedvalues of the other members. The members that are calculated on the
second passthrough the outline are called Two-Pass Calculation.Only accounts,
dynamic calc, dynamic calc and store members can be given two
passcalculation.Edit>propertiesData dictionary button
What does the
consolidation properties do?
Member consolidation
determines how children roll up into their parents. Default (+)operator.Essbase
don’t use consolidation properties for attribute dimensions.Essbase
automatically tags members of the attribute dimensions as dynamic calc. you
cannot change this
setting.
When do you use label
only?
When no data is associated
with members we use label only. They are used only toease navigation and
reporting from the spread sheet add-in.You cannot associate attributes to label
only. If you tag label only to the basemember, which has, attributes associated
with it, Essbase removes attributes anddisplays a warning message
Posted by
Dornakal's-Hyperion-Blog at4:07 PM 1 comments Links to this postLabels: Essbase Overview,Hyperion Essbase BSO, Hyperion Essbase Interview questions,Important Points inHyperion Essbase
What
is Fragmentation?
Fragmentation is unused
disk space.
•
When
does Fragmentation occur?
Fragmentation is likely to
occur with the following:
Read/write databases that
users are constantly updating with dataDatabases that execute calculations
around the clock Databases that frequently update and recalculate dense
membersData loads that are poorly designedDatabases that contain a significant
number of Dynamic Calc and Store membersDatabases that use an isolation level
of uncommitted access with commit block set to zero
•
How
can you measure fragmentation?
You can measure
fragmentation using the average clustering ratio or average fragmentation
Quotient.
Using the average
fragmentation quotient
Any quotient above the
high end of the range indicates that reducingfragmentation may help
performance, with the following qualifications:
The reported value of the
Fragmentation Quotient is more accurate when there are no other
writetransactions running on the database.For databases less than 50 MB using
the Direct I/O access mode, the fragmentation quotient tends to be high. A high
fragmentation quotient does not necessarily indicate a need to
reducefragmentation, because the free space is created in 8 MB chunks and all
of it might not get usedright away.
Database
SizeFragmentation QuotientThreshold
Small (up to 200MB)60% or
higher Medium (up to 2GB)40% or higher Large (greater than2 GB)30% or higher
Using the average
clustering ratio:
The average clustering
ratio database statistic indicates thefragmentation level of the data (.pag)
files. The maximum value, 1,indicates no fragmentation.
•
How
do you can prevent and remove fragmentation?
You can prevent and remove
fragmentation:
To prevent fragmentation,
optimize data loads by sorting load records based upon sparse dimensionmembers.
For a comprehensive discussion of optimizing data load by grouping sparse
members.To remove fragmentation, perform an export of the database, delete all
data in the database withCLEARDATA, and reload the export file.To remove
fragmentation, force a dense restructure of the database.
You can use the
OUTLINECHANGELOG setting in the ESSBASE.CFG file to captureoutline changes.
This setting logs all outline changes into the database_name.OLGfile. The
OUTLINECHANGELOG setting allows database administrators to review theoutline
revision history and gather enough information to roll back changes if
needed.Example:OUTLINECHANGELOG TRUE FALSETRUE Essbase logs outline changes
into the file database_name.OLG.FALSE Essbase does not log outline changes. The
default is FALSE.
If you create a custom
dimension in planning and wish to delete it, here is how youmight remove it
from SQL manually. We recommend that only someone familiar with
relational databases
attempt this.Outlined below are the steps to remove a dimension from the Oracle
relational store.The directions are for SQL, but the table names should be the
same in Oracle.This procedure should only be performed by your SQL or Oracle
DBA.BE SURE YOU HAVE A BACKUP OF YOUR APPLICATION BEFORE ATTEMPTING
THISPROCEDURE.You will need to delete all the children first, then the
dimension.If you have the dimension in forms, you may also need to (after step
5) go into thehsp_formobj_def_mbr table and delete all references of that
object ID. Then deletethe dim id in the hsp_form_layout table.Recycle services
of Planning.The following assumptions are being made about the dimension:* No
forms have any references to the dimension.* Any and all attribute dimensions
have been removed from the dimension.* Any and all members have been removed
from the dimension.* All security access has been removed from dimension.* Any
Alias associated with the dimension has been removed.In this example we have
created a dimension called Dummy Dimension.1. Open the Enterprise Manager for
SQL server2. Open the database that has your planning application3. Open the
table HSP_OBJECT4. You need to find the row that has the dimension name you
want to delete:5. Take note of the OBJECT_ID in my example it is 50051, don't
delete it yet.6. Open the table HSP_MEMBER7. Find and delete any rows that have
a MEMBER_ID equal to the OBJECT_ID from step5.8. Close the Table HSP_MEMBER9.
Open the table HSP_MRU_MEMBERS.10. Find and delete any rows that have a DIM_ID
equal to the OBJECT_ID from step 5.11. Close the table HSP_MRU_MEMBERS.12. Open
the table HSP_DIMENSION13. Find and delete any rows that have a DIM_ID equal to
the OBJECT_ID from step 5.14. Close the table HSP_DIMENSION15. Also delete it's
reference from the HSP_UNIQUE_NAMES table.
16. Delete the row in the
HSP_OBJECT table that you found in step 4.17. Close the HSP_OBJECT table.18.
Open Essbase Application Manager for the application and remove the
dimensionfrom the necessary Essbase outlines.19. Restart the application
server.Now when you open the application in Planning the dimension will be
removed.The next step is to perform a refresh to make sure planning and Essbase
are in sync.
Posted by
Dornakal's-Hyperion-Blog at5:23 PM 0 comments Links to this postLabels: Delete Dimension in Hyperion Planning
This document demonstrates
step by step procedure to install Hyperion PlanningSoftware. This includes the
installation and configuration of Hyperion components andother relative
software.First step is to download hyperion planning and related software from
the followingwebsitehttp://edelivery.oracle.com/After entering the
details you will be directed to the page below. Select Hyperionperformance
management and BI.
Download the following
products:1. Hyperion Shared Services Release 9.3.1 Microsoft Windows x86
Installer English(V11229-01)2.
Hyperion Smart View for Office Release 9.3.1.2 Microsoft Windows x86 Installer( V12736-01)3. Hyperion Essbase - System 9
Release 9.3.1.3 Server Windows Installer (V14762-01)4. Hyperion Essbase
Administration Services - System 9 Release 9.3.1.3 WindowsInstaller--
(V14792-01)5. Hyperion Planning - System 9 Release 9.3.1.1 All Platforms Installer
English--V11378-01After downloading
the software. Extract the setup files from downloads and startinstallation.
Make sure that you
don't check
Launch configuration
utility in the last step in thefirst run.
How do you optimize
outline?
Usually the outline is optimized using the hourglass design for dimension ordering i.e,
· Dimension with Accounts tag
· Dimension with Time tag
· Largest Dense dimension
· Smallest dense dimension
· Smallest Sparse dimension
· Largest Sparse dimension
Usually the outline is optimized using the hourglass design for dimension ordering i.e,
· Dimension with Accounts tag
· Dimension with Time tag
· Largest Dense dimension
· Smallest dense dimension
· Smallest Sparse dimension
· Largest Sparse dimension
What are the ways to
improve performance during data loads?
There are several ways to optimize load
There are several ways to optimize load
1. Grouping of Sparse
member combinations
2. Making the data source as small as possible
3. Making source fields as small as possible
4. Positioning the data in the same order as the outline
5. Loading from Essbase Server
6. Managing parallel data load processing
2. Making the data source as small as possible
3. Making source fields as small as possible
4. Positioning the data in the same order as the outline
5. Loading from Essbase Server
6. Managing parallel data load processing
What are the design
considerations for calculation optimization?
You can configure a
database to optimize calculation performance. The best configuration for the
site depends on the nature and size of the database.
· Block Size(8Kb to 100Kb) and Block Density
· Order of Sparse Dimensions
· Incremental Data Loading
· Database Outlines with Two or More Flat Dimensions
· Formulas and Calculation Scripts
· Block Size(8Kb to 100Kb) and Block Density
· Order of Sparse Dimensions
· Incremental Data Loading
· Database Outlines with Two or More Flat Dimensions
· Formulas and Calculation Scripts
When does Fragmentation
occur?
Fragmentation is likely to
occur with the following:
· Read/write databases
that users are constantly updating with data
· Databases that execute
calculations around the clock
· Databases that
frequently update and recalculate dense members
· Data loads that are
poorly designed
· Databases that contain a
significant number of Dynamic Calc and Store members
· Databases that use an
isolation level of uncommitted access with commit block set to zero
How can you measure
fragmentation?
You can measure
fragmentation using the average clustering ratio or average fragmentation
Quotient.
Using the average
fragmentation quotient
Any quotient above the
high end of the range indicates that reducing fragmentation may help
performance
Small (up to 200 MB) 60%
or higher
Medium (up to 2 GB) 40% or
higher
Large (greater than 2 GB)
30% or higher
Using the average
clustering ratio:
The average clustering
ratio database statistic indicates the fragmentation level of the data (.pag)
files. The maximum value, 1, indicates no fragmentation.
How do you can prevent and
remove fragmentation?
You can prevent and remove
fragmentation:
· To prevent
fragmentation, optimize data loads by sorting load records based upon sparse
dimension members. For a comprehensive discussion of optimizing data load by
grouping sparse members.
· To remove fragmentation,
perform an export of the database, delete all data in the database with
CLEARDATA, and reload the export file.
· To remove fragmentation,
force a dense restructure of the database.
Why is database
restructuring?
As your business changes,
you change the Essbase database outline to capture new product lines, provide
information on new scenarios, reflect new time periods, etc. Some changes to a
database outline affect the data storage arrangement, forcing Essbase to
restructure the database.
What are the types of
database restructuring?
The two ways by which a
database restructure is triggered:
Implicit Restructures
Dense restructure
Sparse restructure
Outline-only restructure
Explicit Restructures
What are the conditions
affecting Database restructuring?
Intelligent Calculation,
name changes, and formula changes affect database restructuring:
· If you use Intelligent
Calculation in the database, all restructured blocks are marked as dirty
whenever data blocks are restructured. Marking the blocks as dirty forces the
next default Intelligent Calculation to be a full calculation.
· If you change a name or
a formula, Essbase does not mark the affected blocks as dirty. Therefore, you
must use a method other than full calculation to recalculate the member or the
database.
What are the files used
during Restructuring?
When Essbase restructures
both the data blocks and the index, it uses the files described
essxxxxx.pag
Essbase data fileessxxxxx.ind
Essbase index filedbname.esm
Essbase kernel file that
contains control information used for db recoverydbname.tct
Transaction control tabledbname.ind
Free fragment file for
data and index free fragmentsdbname.otl
Outline file in which is
defined all metadata for a database and how data is storedWhat are the
actions that improve performance for restructuring?
There are a number of
things you can do to improve performance related to database restructuring:
· If you change a
dimension frequently, make it sparse. · Use incremental restructuring to
control when Essbase performs a required database restructuring. · Select
options when you save a modified outline that reduce the amount of
restructuring required.
Hyperion Interview
questions
Can we have multiple
metaoutlines based on one OLAP model in Integration services?
Ans: Yes
What are LRO's( Linked Reporting Objects)?
Ans: They are specific objects like files, cell notes or URL's associated with specific data cells of Essbase database. You can link multiple objects to a single data cell. These linked objects are stored in the server. These LRO's can be exported or imported with the database for backup and migration activities.
What are the three primary build methods for building dimensions?
Ans:
1. Generation references
2. Level references
3. Parent-Child references
How does UDA's impact database size?
Ans: There will be no impact on the database as the UDA’s doesn’t store data in the database.
Can we have an metaoutline based on two different OLAp models.
Ans: No.
Can we create UDA’s and apply it to Dense as well as Sparse dimensions?
Ans: Yes
Types of Partitions available in Essbase?
Ans: Three types of partitions are there.
1. Transparent partition: A form of shared partition that provides the ability to access and manipulate remote data transparently as though it is part of your local database. The remote data is retrieved from the data source each time you request it. Any updates made to the data are written back to the data source and become immediately accessible to both local data target users and transparent data source users
2. Replicated Partition:
3. Linked Partition:
What is hybrid analysis?
Ans: Lower level members and associated data remains in relational database where as upper level members and associated data resides in Essbase database.
Why top-down calculation less efficient than a bottom-up calculation?Being less efficient, why do we use them.
Ans: In the process it calculates more blocks than is necessary. Sometimes it is necessary to perform top-down calculation to get the correct calculation results.
On what basis you will decide to invoke a serial or parellel calculation method.
Ans: If we have a single processor, we will use serial calculation but if we have multiple processors we can break the task into threads and make them run on different processors.
How can you display UDA’s in reports?
Ans: UDA's values are never displayed in the reports and hence do not impact report performance.
While loading the data, you have applied both the selection criteria as well as rejection criteria to a same record. What will be the outcome?
Ans: The record will be rejected.
What are the specified roles other than Aministrator to view sessions, disconnect sessions or kill users requests for a particular application?
Ans: You should have the role of Application manager for the specified application.
What is block locking system?
Ans: Analytic services(or Essbase Services) locks the block and all other blocks which contain the childs of that block while calculating this block is block locking system.
What are the three options specified in Username and Password management under security tab in Essbase server proprties.
Ans: 1. Login attempts allowed before username is disabled.
2. Number of inactive days before username is diabled.
3. Number of days before user must change password.
Can we have multiple databases in one single application?
Ans: Yes. But only one database per application is recommended.
Depend on which database that you are going to create. For Example: If you are creating ASO then we can’t create more that 1 db per application. If you are creating BSO then you can create more than 1 db per application
How is data stored in the Essbase database?
Ans: Essbase is an file based database where the data is stored in PAG files of 2 GB each and grows sequentially.
We have created an application as unicode mode. Can we change it later to non-unicode mode.
Ans: No
What are the types of partitioning options available in Essbase?
Ans: 1. Replicated partition. 2. Transparent partition 3. Linked partition.
Dynamic calc decreases the retreival time and increases batch database calculation time. How true is the statement?
Ans: The statement should be just opposite. As dynamic calc members are calculated when requested, the retreival time should increase.
What is the role of provider services.
Ans: To communicate between Essbase and Microsoft office tools.
A customer wants to run two instances of an Essbase server on a same machine to have both test environment and Development environment on the same server. Can he do that?
Ans: Yes. We can have multiple instances of an Essbase server on a single machine and there will be different sets of windows services for all these instances.
Why top-down calculation less efficient than a bottom-up calculation?Being less efficient, why do we use them.
Ans: In the process it calculates more blocks than is necessary. Sometimes it is necessary to perform top-down calculation to get the correct calculation results.
Ans: Yes
What are LRO's( Linked Reporting Objects)?
Ans: They are specific objects like files, cell notes or URL's associated with specific data cells of Essbase database. You can link multiple objects to a single data cell. These linked objects are stored in the server. These LRO's can be exported or imported with the database for backup and migration activities.
What are the three primary build methods for building dimensions?
Ans:
1. Generation references
2. Level references
3. Parent-Child references
How does UDA's impact database size?
Ans: There will be no impact on the database as the UDA’s doesn’t store data in the database.
Can we have an metaoutline based on two different OLAp models.
Ans: No.
Can we create UDA’s and apply it to Dense as well as Sparse dimensions?
Ans: Yes
Types of Partitions available in Essbase?
Ans: Three types of partitions are there.
1. Transparent partition: A form of shared partition that provides the ability to access and manipulate remote data transparently as though it is part of your local database. The remote data is retrieved from the data source each time you request it. Any updates made to the data are written back to the data source and become immediately accessible to both local data target users and transparent data source users
2. Replicated Partition:
3. Linked Partition:
What is hybrid analysis?
Ans: Lower level members and associated data remains in relational database where as upper level members and associated data resides in Essbase database.
Why top-down calculation less efficient than a bottom-up calculation?Being less efficient, why do we use them.
Ans: In the process it calculates more blocks than is necessary. Sometimes it is necessary to perform top-down calculation to get the correct calculation results.
On what basis you will decide to invoke a serial or parellel calculation method.
Ans: If we have a single processor, we will use serial calculation but if we have multiple processors we can break the task into threads and make them run on different processors.
How can you display UDA’s in reports?
Ans: UDA's values are never displayed in the reports and hence do not impact report performance.
While loading the data, you have applied both the selection criteria as well as rejection criteria to a same record. What will be the outcome?
Ans: The record will be rejected.
What are the specified roles other than Aministrator to view sessions, disconnect sessions or kill users requests for a particular application?
Ans: You should have the role of Application manager for the specified application.
What is block locking system?
Ans: Analytic services(or Essbase Services) locks the block and all other blocks which contain the childs of that block while calculating this block is block locking system.
What are the three options specified in Username and Password management under security tab in Essbase server proprties.
Ans: 1. Login attempts allowed before username is disabled.
2. Number of inactive days before username is diabled.
3. Number of days before user must change password.
Can we have multiple databases in one single application?
Ans: Yes. But only one database per application is recommended.
Depend on which database that you are going to create. For Example: If you are creating ASO then we can’t create more that 1 db per application. If you are creating BSO then you can create more than 1 db per application
How is data stored in the Essbase database?
Ans: Essbase is an file based database where the data is stored in PAG files of 2 GB each and grows sequentially.
We have created an application as unicode mode. Can we change it later to non-unicode mode.
Ans: No
What are the types of partitioning options available in Essbase?
Ans: 1. Replicated partition. 2. Transparent partition 3. Linked partition.
Dynamic calc decreases the retreival time and increases batch database calculation time. How true is the statement?
Ans: The statement should be just opposite. As dynamic calc members are calculated when requested, the retreival time should increase.
What is the role of provider services.
Ans: To communicate between Essbase and Microsoft office tools.
A customer wants to run two instances of an Essbase server on a same machine to have both test environment and Development environment on the same server. Can he do that?
Ans: Yes. We can have multiple instances of an Essbase server on a single machine and there will be different sets of windows services for all these instances.
Why top-down calculation less efficient than a bottom-up calculation?Being less efficient, why do we use them.
Ans: In the process it calculates more blocks than is necessary. Sometimes it is necessary to perform top-down calculation to get the correct calculation results.
