Uncategories Essbase basic interview questions

Essbase basic interview questions

Published on 11:42 PM By Koti Reddy Battula
1 comments
Hyperion Essbase Interview Questions
1. What is Hyperion Essbase?
A multidimensional database supports multiple views of data sets for users who need to analyze the relationships between data categories.
2. What is Hyperion Essbase architecture?
Hyperion Essbase 7.1.2 follows 3-tier architecture.
 Database tier – Hyperion Essbase server (Analytic Server)
One or more analytic servers store and process multidimensional database information.
 Middle tier – EAS Server EAS: Essbase Administration Services
This server maintains communication, session and security info for analytic servers.
 Client tier – EAS Console
Console provides a user interface to manage the analytic services environment.
EAS server, Essbase Server and EAS Console can be on a single machine or they can be on separate machine. EAS server/Essbase can be installed on windows/Unix platform.
Note: Console is a tool where end user/ client will work, where as server is tool where data will be stored.
3. What are dimension, parent, child, level, generation and sibling?
 Dimension is the highest level of consolidation in an outline.
 A parent is member that has a branch below it. For example profit is a parent member for sales and cost of products sold.
 A child is member that has a parent above it. For example sales and cost of products sold are children of the parent profit.
 Level refers to a branch within a dimension. The member that doesn’t have any children is at level ‘0’. Also called leaf members. The parent of level ‘0’ member will be at level ‘1’. Levels will increase from bottom to top where as generations will increase from top to bottom.
 Generation refers to a consolidation level within a dimension. When a database is created an empty outline gets created, outline will be at generation ‘0’. All the dimensions will be at generation ‘1’ and members created under dimensions will be G2, the members created under G2 will be at G3 and so on.
 Siblings are child members of the same intermediate parent, at the same generation. For example sales and cost of products sold are siblings (they both have the parent profit).
4. What is outline?
 Outline defines the structural relationships between members in an analytic services database.
 It defines consolidation and mathematical relationships between members.
 Organizes all the data in the database.
 Outline files are stored under ARBORPATH\ APP\ app_name\ DB_name\ with a file extension of “.otl”. To see the arbor path click on the properties under Essbase server. Example: ARBORPATH= C:\Hyperion\Essbase
5. What is rules file?
Rules files are used to build dimensions and to load data. Rules files are saved with “.rul” in the path, ARBORPATH \ APP \ app_name \ DB_name \
6. How to build dimensions using rules file?
STEP 1.Make sure the source file is ready. (The source file could be text file\ excel file\ csv file etc.). The default delimiter is “TAB”.
STEP 2.Right click on the rules file,  “create rules file”, and this will opens the data prep Editor.
STEP 3.Go to file menu and  “open data file”, select the source file from file system tab.
STEP 4.Select dimension build field button and select field properties button, select dimension build properties tab.
STEP 5.Make sure you are in field no 1.
STEP 6.Double click on dimension which you add to outline and double click on generations under filed type and enter number in number files: 2 and  next.
STEP 7.You are in field number 2. Repeat the above process until all the fields are mapped. Finally  OK.
STEP 8.Validates and save the rule file on analytic server.
STEP 9.Double click on the outline, go to outline menu and  update outline. Find data file from file system and select the source file OK.  Find rules file and select the rules file  OK. Select the overwrite check box and OK.
STEP 10.Save the outline.
7. How to handle headers in source file?
The source file may contain header records. In that case we need to skip the header records.
Open data prep editor (Right click on the rules file, click on “create rules file”), go to options menu and  data source properties and  header tab, and enter a number in “Number of lines to skip: 1”  OK.
8. How to handle delimiter in the source file?
Delimiter is one which separates the columns/fields in database file/source file/data file. Open data prep editor (Right click on the rules file, click on “create rules file”), go to options menu and  data source properties and  Delimiter tab, Select the required delimiter and  OK. Note: Default delimiter is “TAB”. In SQL, the default delimiter is “TAB”.
9. What are all the different methods to load data?
Data can be loaded using
I. Rules file
II. Through lock and send from excel file / right back option in analyzer
III. Free form loading (without rules files)
10. How to load data using rules file?
STEP 1.Open the data prep editor. Make sure data load fields button selected.
STEP 2.File  open data file  select source file
STEP 3. Filed properties,  Data load properties tab. Make sure you are in field number 1.
STEP 4. Double click on dimension and click next repeat the process until all the dimensions are selected. At the final you have to select data field check box. ( No need to select any dimension)  OK.
STEP 5.Validate and save rules files.
STEP 6.Right click on the database name (Ex: sales)  load data; make sure the type is “data file”.  Find data file, select source file,  find rules file, select the rules file and  overwrite check box  OK.
STEP 7. Right click on the database name (Ex: sales)  execute calculations.
STEP 8.Right click on the database name (Ex: sales)  Preview data.
Note:
I. Always try to load data for level ‘0’ members’ combination.
II. At least one member should present from each dimension.
11. What are the different steps in database design process?
The database design process includes the following basic steps
1. Analyze business needs and design a plan:
2. Draft a database outline.
3. Check system requirements.
4. Load test data into the database.
5. Define calculations.
6. Define reports.
7. Verify with users.
8. Repeat the process.
12. How to handle duplicate records?
 Open Data Prep Editor  Options (from menu bar)  data load settings  data load values (tab) under data values Select
o Overwrite existing value (to overwrite duplicate records)
o Add to existing values (to add the duplicate records)
13. How to solve the records unique problem?
Take an example of time dimension

Field1 Field2 Field3
2007 Qtr1 Jan
2007 Qtr1 Feb
2007 Qtr1 Mar
2006 Qtr1 Jan
2006 Qtr1 Feb
2006 Qtr1 Mar
Above data is your source file data.txt,
 Open data prep editor  file Open data file, go to  field(menu)  Move Filed1 to last position go to fields  Create using join select Field2 & field1 OK it will create one column with Qtr12007 Repeat the above process up to your requirement. Finally in the first field You want to add some prefix like Year 2007 or Year 2006 go to filed properties  Global properties  prefix Year  OK.
14. What are all the different build methods?
Select the build method to use for the selected dimension:
 Use generation references: Builds dimensions from top-down data sources. Generations are numbered hierarchically from the top down, with the dimension as generation 1 and its immediate children as generation 2, and so on.
 Use level references: Builds dimensions from bottom-up data sources. Levels are numbered hierarchically from the bottom up, with the lowest level at level 0.
 Use parent/child references: Builds the dimension using parent/child references. Parent/child references refer to the parent/child relationship among members in an outline.
 Add as sibling of member with matching string: Builds the dimension by matching new members to similar members in the outline.
 Add as sibling of lowest level: Builds dimensions by adding new members to the lowest level in the outline.
 Add as child of: Builds dimensions by adding each new member as a child of the dimension specified in the text box or selected from the dimension node. Expand this node to specify or select the dimension.
 Process null values: Select to perform certain processing if a null value is encountered in the data source. Analytic Services promotes primary fields (such as member names) that occur after a null field and ignores secondary fields (such as member properties and aliases) that occur after a null field. This option is only available when you use the generation references or level references build methods.
15. What are Consolidation operators?
Operator Description
+ Adds the member to the result of previous calculations performed on other members. + is the default operator.
- Multiplies the member by -1 and then adds it to the sum of previous calculations performed on other members.
* Multiplies the member by the result of previous calculations performed on other members.
/ Divides the member into the result of previous calculations performed on other members.
% Divides the member into the sum of previous calculations performed on other members. The result is multiplied by 100 to yield a percentage value.
~ Does not use the member in the consolidation to its parent.

Sample Roll up Example:
Parent1
Member1 (+) 10
Member2 (+) 20
Member3 (-) 25
Member4 (*) 40
Member5 (%) 50
Member6 (/) 60
Member7 (~) 70
Sample Roll Up for Members 1 through 4
(((Member1 + Member2) + (-1) Member3) * Member4) = X
(((10 + 20) + (-25)) * 40) = 200
Sample Roll Up for Member 5
(X/Member5) * 100 = Y
(200/50) * 100 = 400
Sample Roll Up for Member 6
Y/Member6 = Z
400/60 = 66.67
Because it is set to No Consolidation (~), Analytic Services ignores Member7 in the consolidation.
16. What is the use of ‘~’ (tilt) operator?
When a member has ‘~’ operator, it is not rolled upto parent.
Year
Qtr1
Jan (+) Qtr1=Jan + Feb
Feb (+)
Mar (~)
17. When you save the application in analytics server what are all the backup files will create?
Let us take an application Product, 2 files created.
1. Prodcut.app (Application file, defining the name and location of the application and other application settings)
2. Product.txt (Text file, such as a data file to load or a text document to link as a linked reporting object).
If you create the data base file Product, 4 files created.
1. Product.dbb (Backup of database file)
2. Product.esm (Analytic Services database transaction control file that manages all commits of data and follows and maintains all transactions)
3. Product.tct (Analytic Services database transaction control file that manages all commits of data and follows and maintains all transactions)
4. Prodcut.otl (Outline)
Along with folder “Trig”
If you save the dimension it creates .ind file (Analytic Services index file)
If you save the data, it creates the .pag file (Analytic Services database data (page) file). The default size of page file is 2GB. The minimum size is 8192Kb (8Mb). In projects let us say your page file size is 12GB.
18. What is the use of LOCKING and UNLOCKING of outline?
An outline is always locked when it is opened in edit mode. Analytic Services unlocks the outline when the Close button is used to close the outline. In some circumstances, an outline may get closed without getting unlocked; for example, when a user has an outline open and is disconnected from the server because of a timeout. When an outline is locked to other users, Analytic Services does not allow them to save over, rename, delete, edit, or optimize the outline. When you attempt to edit a locked outline, you are given an option to view the outline in Outline Viewer. If you have Supervisor permissions, you can unlock a locked outline. Before you forcefully unlock a locked outline, make sure that no one else is working with it.
19. What are the different storage properties?
When a member is created, the default storage type is “STORE”. Available storage types are
1. Store member
Stored members contain calculated values that are stored with the member in the database after calculation. By default, members are set as stored.
2. Dynamic Calc member
When a member is dynamic calc, analytic services does not calculate the value until user requests it. Also the value will not be stored. Dynamic calc can be tagged on parent members. Dynamic calc can be tagged on level ‘0’ members provided a formula is attached to level ‘0’ members. Using dynamic calc we can save space and also we can save calc time. But the retrieval time will be the more.
3. Dynamic Calc and Store member
When we tag member as dynamic calc and store, Essbase will not calculate the data value until user requests it. When the member is retrieval for first time, Essbase calc the member and then store the data value. For subsequent retrieval, the property will be same as store data.
4. Implied sharing
Analytic services, assumes implied shared member relationship with child values,
• When a parent has only one child
• A parent has more than one child, but only one child is consolidating to the parent, remaining children has ‘~’ operator.
In the above two cases, Essbase need not calc the parent value. It simply refers the child values. There is no storage space reserved for the parent.
Examples of implied sharing:
Year 2007
 Qtr1
 Jan 2007 (+)
 Feb 2007 (~)
 Mar 2007 (~) Year 2007
 Qtr1
 APR 2007 (+)

5. Never share member
Never share will not allow members to implicitly shared. Tag the parent member as never share to avoid implicit sharing.
6. Label only member
Label only members have no data associated with them. Use them to group members or to ease navigate and reporting from any reporting tool (spread sheets, etc.).
Note: For measure dimension most of the applications make it as label only.
7. Shared member
Shared members help us to create alternate hierarchies, in the above example using same children members, we can get different roll ups.
Products
 Soda(+)
 Coke(+)
 Coke Diet(+)
 Coke Reg(+)
 Pepsi(+)
 Pepsi Diet(+)
 Pepsi Reg(+)
 Diet(~)
 Coke Diet (Shared members) (+)
 Pepsi Diet(Shared members) (+)
 Reg(~)
 Coke Reg (Shared members) (+)
 Pepsi Reg (Shared members) (+)
Coke= coke diet + coke Reg
Pepsi= Pepsi diet + Pepsi Reg
Diet= coke diet + Pepsi Diet
Reg= Coke Reg + Pepsi Reg.

The data values associated with the shared member comes from another member with same name.
 Shared member should exit in the same dimension as their base member.
 Shared member must come after the base member in the outline.
 Shared members cannot have the children.
 We can have unlimited number of shared members.
 We cannot assign attributes with shared members.
 We cannot assign UDA’s or formulas to shared members.
 We can give alias to a shared member.
20. How to set member properties using data source file?
Let us create a source file like this
Parent Property Child
Soda Coke
Soda Pepsi
Coke Coke Diet
Coke Coke Reg
Pepsi Pepsi-Diet
Pepsi Pepsi-Reg
Diet ~ Coke-Diet
Pepsi ~ Pepsi-Reg
Diet ~ Pepsi-Diet
Pepsi ~ Coke-Reg

Load the source file into data prep editor.  Dimension build fields  Fields properties
 Dimension build properties (tab)
 Products (dimension)  parent (field type)  next
 Products (dimension)  Properties (field type)  next
 Products (dimension)  Child (field type)  OK
Remaining procedure is same as above. The output has shown in question no 20.
21. What is duplicate generation field type?
The name of a member that has duplicate parents; that is, a member that is shared by more than one parent. Put DUPGEN fields immediately after GEN fields. For example let us take a source file like this.
Gen2 Gen3 DUPGEN3 Gen4
Soda Coke Diet Coke Diet
Soda Coke Reg Coke Reg
Soda Pepsi Diet Pepsi Diet
Soda Pepsi Reg Pepsi Reg
The output will be something like this
 Products
 Soda (+)
 Coke (+)
 Coke-diet (+)
 Coke-Reg (+)
 Diet (~)
 Coke-diet (+) (Shared Member)
 Pepsi-diet (+) (Shared Member)
 Reg (~)
 Coke-Reg (+) (Shared Member)
 Pepsi-Reg (+) (Shared Member)
 Pepsi (+)
 Pepsi-diet (+)
 Pepsi-Reg (+)
Observation: the DUPGEN 3 members will be created along with ‘~’ operator instead of ‘+’ operator.

22. What is alias and alias table? What are all the different ways to create aliases?
Alias is an attribute name for a member. Any member in the outline can have alias. We can have more than one alias for a member. Alias information is stored in alias table. When you create a database, an empty alias table called default will get created.
If we want more than one alias for a member, we need to create a new alias table. The name of the alias table can be a max of 30 characters. An alias name can be a max of 80 characters. A member can have 10 aliases. That means we can create 9 more tables only. We can export the alias table information and at later point of time we can import the alias table.
Creating alias
Right click on the member  Edit member properties
Aliases  Aliases name.
Creating alias table using rules file:
Gen 2 Gen 3 Gen 4 Alias Prop
India SI AP Telugu
India SI TN Tamil
Open the data prep editor
 Map all the generation columns, and for the alias column, select dimension under field type. Select alias, the generation number will be the same as the previous column.
 Options (menu)
 Dimension build settings
 Dimension build settings (Tab)
Select the dimension
 Allow property changes
Validate and save the rules file.
Creating alias table:
In the outline editor:
 Properties (tab)
 Right click on the alias table name
 Create table name
 Right click on the alias table
 Set as active (It makes the alias table active)

23. What is the alias table format?
$ALT_NAME 'EMPLOYEE'/*Alias Table name*/
"E001" "Suresh"
"E002" "Pro_X"
$END /* End of table*/ Outline( Alias Table Employee)
 Employee
 E001 (Alias Suresh)
 E002 (Alias Pro_X)
24. What are the different types of dimensions?
Essbase has two types of dimensions.
1. Standard dimension
2. Attribute dimension
 Standard dimension: It again two types.
1. Dense dimension
2. Sparse dimension
Most multi dimensional databases are inherently sparse, that is they lack data values for majority of member combinations. A sparse dimension is a dimension with low percentage of available data. A dense dimension is a dimension with high probability that one or more points is occupied for the combination.
Data blocks and index system:
Analytic services uses 2 types of internal structures to store and access data. Those are data blocks and index system.
Data blocks:
An analytic service creates a data block for each unique combination of sparse members provided at least one data value exists for sparse member combination. Analytic service creates an index entry for each block.
No of indexes = No of Blocks.
= Multiply the no of members from one sparse dimension with the member from other sparse dimension and so on.
Block Size:
The data block represents all the dense dimension members for its combination of sparse dimension members. A block contains several cells.
No of cells =multiply the members from one dense dimension with the members from other dense dimension and so on.

Idle block size is between 8K and 100k. Each cell occupies 8 bytes.
Take an example:
Let us consider D: Dense dimension, S: Sparse dimension.
D1=10, D2=25, S1=10, S2=20, S3=10;
Potential no of indexes = S1 * S2 * S3
= 10 * 20 * 10 = 2000
No of cells = D1 * D2
= 10 * 25 = 250
Block size= No of cells * 8
= 250 * 8 = 2000 = 2K
Note: It is applicable only for storage type “Store”.
Example 2:
D1= 25, D2: Measure, D3= 10, S1=20, S2=100, S3=50;
Measure (Label only)
 Gross Sales(+)
 Net sales (+)
 Return sales(+)
 Gross Transactions (Dynamic Calc)
 Net Transaction (+)
 Return transaction (+)
 Quantity (~)
 Opening inventory(+)

Potential no of indexes = S1 * S2 * S3
= 20 * 100 * 50 = 1, 00,000
No of cells = D1 * D2 * D3
= 25 * 6 * 10 = 1500
In D2 (dense dimension) we count all the level ‘0’ members (5), Then we will count the parents, here we are having 3 parents, on that one parent (Gross transactions) is dynamic calc (It will calc the data at execution only, it will not store the data so we cannot count this member) another parent (Quantity) is having ~ operator so it is nothing but implied sharing. We are not count this parent also. The measure dimension that is also Label only data storage type, so this also not counted. Left is only one parent (Gross sales) we can count this parent. Total no of dense members are 6.
 Attribute Dimension:
Attribute describe characteristics of some other dimensions.
1. Attribute dimensions are always at the end of the outline.
2. Attribute dimensions are sparse in nature.
3. Attribute dimensions are dynamic calc data storage type.
4. We can tag attribute dimension only to sparse dimension.
5. A base dimension can have many no of attribute dimensions.
6. An attribute dimension must be associated with only one base dimension.
7. Only level “0” members of attribute dimension can be associated with level ‘0’ members of base dimension.
8. We cannot associate multiple members from the same attribute dimension with the same base member. Example: Coke-1 cannot have 1lit and 2lit at the same time.
9. There are 4 different attribute type available, ‘text’, ‘numeric’, ‘Boolean’, and ‘date’.
10. There are 5 types of calculations available, ‘sum’, ‘min’, ‘max’, ‘count’, and ‘avg’.
25. How to see/change the dimension is sparse or dense dimension?
You are in outline editor, go to properties (Tab), outline properties  data storage  Dimension storage types. If you want to change the dimension type you must change the data storage property Auto configure “False”.
26. Where you see the data block size and no of cells?
Double click on the data base properties, data base properties  go to statistics (Tab) there we will see the block size.
27. How to create attribute dimension using rules file?
1. Create a dimension at the end of the outline.
2. Tag the dimension as attribute.
3. Right click on standard sparse dimension and click edit member properties.
 Attribute(Tab)
 Select the attribute dimension
 Assign OK and save the outline.
Note: The above steps are manual.
4. Rules file
Emp Dept
E1 D1
E2 D1
E3 D2
Open data prep editor  open source file  dimension build properties  Field properties dimension build properties (Tab)  select the dimension  select the field type as generation  next. And again select the dimension and under field type select the attribute dimension. Generation will be same as previous column. Validate and save rules file.
28. What are the key futures of Essbase products?
Essbase products provide the analytic solution that integrates data from multiple sources and meets the needs of users across an enterprise.
1. Integration with existing infrastructure: Essbase products integrate with your existing business intelligence infrastructure.
2. Data Integration (Essbase integration services): It integrates data warehouses, legacy systems, OLTP, ERP systems, e-business systems, CRM applications, web log files, and other external data sources.
3. Ease of server and data base administration: Essbase products provide a cross-platform administration console. The console gives you detailed control over the Essbase environment:
• You can manage multiple servers and databases.
• You can use MaxL, a syntactical language command shell with a PERL extension module, to automate batch maintenance.
4. Mission critical applications in web based environment: A middle-tier framework extends the power of Essbase products by creating a Web-enabled, distributed platform for Essbase applications hence serving the analysis needs of large numbers of users in Web-based environments. Essbase Deployment Services provide connection pooling, clustering, and failover support, which extend the scalability and reliability of the platform and support mission-critical applications in a 24 x 7 environment.
5. Powerful querying
6. Calculations
7. Write back and security:
8. Easy development: Analytic Services offers many key advantages to help users develop effective multi-dimensional applications. Users can:
• Design and manage applications using a graphical interface to control most server functions.
• Quickly add dimensions, change calculations, and modify hierarchies to reflect new business developments. In addition, the dynamic dimension builder automatically defines and dynamically loads large amounts of data, including data from spreadsheets, flat files, and supported relational database tables directly into a database.
• Define key calculations without having to write a program
• Define security for individuals and groups and customize views and retrieval procedures for each user without writing a program.
29. What is User defined attributes (UDA’s)?
UDA’s can be associated with both sparse and dense dimensions also.
 Right lick on any member
 Edit member properties
 UDA’s (Tab)
 Enter a characteristic, for example small market
 Assign OK
1. UDA’s can be associated with any level member
2. Using UDA’s we cannot get the calculations automatically. For data analysis UDA’s are not as powerful as attributes.
Creating UDA’s using Rules file:
Source file:
India SI AP Small
India SI TN Medium
India NI UP Small
India NI Delhi Large
Everything is same, the difference is go to options Dimension build settings Dimension build settings (Tab) select the appropriate dimension, in existing members select allow property changes and allow UDA changes.  OK.
30. What is time dimension?
There are four types of inbuilt functions (dimension types) available in Essbase. Those are
1. Time
2. Accounts
3. Country
4. Currency
1. Time Dimension: we can assign any sparse or dense dimension as time. When dimension type is set as time, we get functions like dynamic time series. They are 8 inbuilt dynamic time series members are available.
H-T-D History – To – Date
Y-T-D Year – To – Date
S-T-D Season – To – Date
P-T-D Period – To – Date
Q-T-D Quarter – To – Date
M-T-D Month – To – Date
W-T-D Week – To – Date
D-T-D Day – To – Date
Note: if the lowest level is month M-T-D, W-T-D, D-T-D are not available. If the lowest level is daily, D-T-D is not available. To retrieve the dynamic time series members in report, we need to enter Y-T-D (level ‘0’ member’s name) Example: Y-T-D (jan 2007), Y-T-D (01/05/2007).
 Only one time dimension type can be there in an outline.
 It can work on only on base members, not on shared members/ attribute members etc.
31. How to add Essbase to your excel?
Open excel (if it is Excel 2003)
 Tools
 Add-ins (Browse)
 C:\Hyperion\Essbase\bin\ essexcln
 C:\Hyperion\Essbase\bin\ essxleqd
Open excel (If it is Excel 2007)
 Right click on any tool bar and select customize quick access toolbar, select Add-Ins
 Go
 Browse
 C:\Hyperion\Essbase\bin\ essexcln
 C:\Hyperion\Essbase\bin\ essxleqd
 OK
32. What is accounts dimension?
When we tag any dimension as accounts dimension, we will get additional functions such as time balance properties, two pass calculations etc. only one dimension can be tagged as accounts dimension. In order to have above features, a dimension tagged as time should also exist in the outline. We can tag accounts dimension to either sparse or dense dimension.
Time balance properties:
When a dimension is tagged as accounts, the members in the accounts dimension can be tagged with any of the following TB properties.
1. TB First
2. TB Last
3. TB Average
When a member is tagged as TB first consolidation of the parent in the time dimension will get effected.
Example: Qtr1 2007
 Jan 2007
 Feb 2007
 Mar 2007
Accounts
 Inventory(+)
 Opening Inventory(TB First)
 Ending Inventory(TB Last)
 Avg Inventory(TB Avg)
Now Qtr1 2007Inventory=Jan 2007+Feb 2007+ Mar 2007
Qtr1 2007Opening Inventory=Jan 2007
Qtr1 2007 Ending Inventory=Mar 2007
Qtr1 2007Avg Inventory = (Jan 2007+Feb 2007+Mar 2007)/3
Whatever be the value in Mar 2007 that value will be stored in Qtr2 2007. Sometimes there don’t be any data in Mar 2007 to get the current value of Qtr1 2007 Ending Inventory, there are skip missing & ‘0’ options are available.
Note: TB First & TB Last depends upon outline hierarchy.
Koti Reddy Battula

1 comment :

Subscribe to: Post Comments ( Atom )