Here are some interview questions collected from some of the Online hyperion bloggers.
Hail Hyperion Bloggers!!
Some of them were put down here. Some are being referenced by posting the links.
Links
Detailed answers
Converting an Esscmd Script to MaxLUnderstanding Intelligent Calculation
Data Block: The Building Block of Essbase(Illustrated Example)
Essbase Database Restructures
Sparse, Dense, and Blocks For Dummies
The Internal Workings of Essbase:BSO and ASO Secrets Revealed
MaxL Shell Commands
Hail Hyperion Bloggers!!
Some of them were put down here. Some are being referenced by posting the links.
Links
Detailed answers
Converting an Esscmd Script to MaxLUnderstanding Intelligent Calculation
Data Block: The Building Block of Essbase(Illustrated Example)
Essbase Database Restructures
Sparse, Dense, and Blocks For Dummies
The Internal Workings of Essbase:BSO and ASO Secrets Revealed
MaxL Shell Commands
Hyperion Essbase Questions Set One
1. can you tell me something about yourself and explain me about your project?
2. What is your role in ur current project?
3. What are the issues that you have faced while designing the cube and how did you resolve it?
4. What are different types of build methods?
5. How do you handle null values in essbase?
6. what is the maxl script to aggregate the values in ASO?
7. How are the values Aggregated in ASO? Is there any command that you run to aggregate the values in ASO?
8. What are different types of hierachies in ASO?
9. Say for example that i am building the dimensions and some members which are at gen4 are at gen3 how do build the dimension?
10. What will be there in .CFG file in essbase?
11. Tell me the end to end procedure how do you schedule the batch?
12. what do you understand by configuratation utility while installing the essbase system 9?
13. How do you capture errors while building the dimensions?
14. Tell about alternative hierachy?
15. HAve you ever worked on calculations Scripts?
16. Tell me steps to do migratation?
17. Difference between ASO & BSO?
18. What are the different types of LOG Files ?
19. What is the Extension of Cal Scripts and Rule File?
20. What is an Attribute dimension?
21. What are filters & Why are filters used?
22. what is dense dimension and sparse dimension?
23. What are different types of attribute dimension?
24. what is substitution Variables & Where do you use them?
25. What the storge type of essbase?
26. What are different types of built methods?
27. Tell me what do you understand by Two-pass Calculation?
28. What is the differene between Calc Dim and Calc All?
29. Tell me about TB First and TB Last?
30. How do you calculate the size of the DataBlock?
31. What is the differene between calc All and AGG?
32. What is the difference between Dynamic Calc and Dynamic Calc and Store?
33. How do you do the Data Load Optimization?
34. Different types of Data Load?
35. What is the difference between Fix and IF?
36. What is Intelligent Calculation?
37. what is Dirty Block and Clean Block?
38. If i delete a member from the dense dimension will that block be a clean block or dirty block?
39. What are the different types of Restructuring?
40. What will @ISMEM function will do?
41. What is the difference between Standard dimension and Attribute Dimension?
42. Difference between UDA and Attribute Dimension?
43. Tell me what do you understand by @XREF Function?
44. What is Data Cache?
45. What is the difference between Data Cache and Data File Cache?
46. What are the things that you take the backup?
47. How do you create users in Shared Services?
48. How and where do you configure LDAP?
49. Tell me about SSO?
50. How do you handle duplicate records while loading data into the essbase?
51. Can we give Formulas to Shared Members?
52. Can we give formulas to attribute dimension?
53. How do you optimize the Calclation?
54. What is the difference between ESSCMD and MAXL?
55. What is the difference between 7.x and System 9?
56. What is the the use of navigatation without data in Excel- add-ins?
57. What is cascade option in Excel-addins?
58. Can i give the same alias name to the different member?
59. What is TB First and TB Last?
60. What is the differene between FIX and IF Command?
61. What is MDALLOCATE Function do?
62. What @XREF Function do?
63. What is the difference between Data file cache and Data cache?
64. What will uncommit access do?
65. How does essbase consolidates the data?
66. Can you give the same name to different members in UDA?
67. What are different types of dimension building?
68. What do know about partition?
69. What are different types of partition? Have you ever worked on Partition?
70. what is the Maxl Command to rename the application or database?
71. What is the MAXL Command to bring the application into read only mode?
72. What are the steps to schedule the batch in Financial Reporting?
73. I have got 10 reports in my financial reporing and i want to send all those reports in one pdf how to you do that?
74. What is the difference between MOLAP and ROLAP?
75. What is fact table in your outline?
76. Can i have more then one fact table in my outline?
77. What is the calculation order of essbase?
78. How do you decide whether the file which you have received is in generation reference or level referene or parent & Child reference?
79. IS there any situation that i get more number of text files then the number of dimensions in my outline to build the dimensions?
80. Can i build the dimension and also the shared member with one text file if yes what is the procedure ?
81. Can i build the dimension and the data load with the same rule file if yes what is the procedure?
82. Can i set my cahces using MAXL Command if yes how do you do it?
83. Can i assign storage properties in my rule file if yes how should i do it?
84. If your end user complaint in report where should u check for finding error values? and what is the procedure that you follow to close it? Do you interact with user directly? if yes what is the mode of your interaction?
85. What are the different compression techniques?
86. How many dimensions a there in your project and what are they?
87. How many members are there in your time and account dimension & What is the hierachy of your time dimension?
88. What is rule file?
89. How many flat files for data loading you used in your project?
90. How many rule files you have used for data loading?
91. Tell me errors that you get while data loading and dimension building?
92. Where do you capture data laod errors and dimension build errors?
93. How much time it will take to load data in your project?
94. What is the size of your data file?
95. What is lock and send in your excel-addins
96. While loading the data if server hangs up what will happen?
97. When does restructuring happen?
98. What is your application size? and where do you check it?
99. How is data stored in ASO a and where is it saved?
100. If the data corrupts what will you do?
101. How do you remove defragmentation?
102. Is your current project and ASO or BSO and who will decide it and at which stage of SLDC?
103. What is your team size?
104. What are the differnent optimization techniques that you have followed to imporve the performance of the Essbase and how much percentage it has improved?
105. Can you tell me the number of users in your project?
106. How do you know if everything what you have done in your project is meeting the client needs?
107. Do you interact with client?
108. What type of testing is done in your project?
109. What is your project duration? How much time is spent for Dimension building and data loading?
110. Can you name some of your calculation scripts in your project?
111. How much time it will take when your run your calculation script?
112. Do you know what your calculation script does?
113. When and how do you take the back up of the cube?
114. What are the frequently used MAXL scripts in your project?
115. What will you do when you are not able to solve when problem occurs in your project?
2. What is your role in ur current project?
3. What are the issues that you have faced while designing the cube and how did you resolve it?
4. What are different types of build methods?
5. How do you handle null values in essbase?
6. what is the maxl script to aggregate the values in ASO?
7. How are the values Aggregated in ASO? Is there any command that you run to aggregate the values in ASO?
8. What are different types of hierachies in ASO?
9. Say for example that i am building the dimensions and some members which are at gen4 are at gen3 how do build the dimension?
10. What will be there in .CFG file in essbase?
11. Tell me the end to end procedure how do you schedule the batch?
12. what do you understand by configuratation utility while installing the essbase system 9?
13. How do you capture errors while building the dimensions?
14. Tell about alternative hierachy?
15. HAve you ever worked on calculations Scripts?
16. Tell me steps to do migratation?
17. Difference between ASO & BSO?
18. What are the different types of LOG Files ?
19. What is the Extension of Cal Scripts and Rule File?
20. What is an Attribute dimension?
21. What are filters & Why are filters used?
22. what is dense dimension and sparse dimension?
23. What are different types of attribute dimension?
24. what is substitution Variables & Where do you use them?
25. What the storge type of essbase?
26. What are different types of built methods?
27. Tell me what do you understand by Two-pass Calculation?
28. What is the differene between Calc Dim and Calc All?
29. Tell me about TB First and TB Last?
30. How do you calculate the size of the DataBlock?
31. What is the differene between calc All and AGG?
32. What is the difference between Dynamic Calc and Dynamic Calc and Store?
33. How do you do the Data Load Optimization?
34. Different types of Data Load?
35. What is the difference between Fix and IF?
36. What is Intelligent Calculation?
37. what is Dirty Block and Clean Block?
38. If i delete a member from the dense dimension will that block be a clean block or dirty block?
39. What are the different types of Restructuring?
40. What will @ISMEM function will do?
41. What is the difference between Standard dimension and Attribute Dimension?
42. Difference between UDA and Attribute Dimension?
43. Tell me what do you understand by @XREF Function?
44. What is Data Cache?
45. What is the difference between Data Cache and Data File Cache?
46. What are the things that you take the backup?
47. How do you create users in Shared Services?
48. How and where do you configure LDAP?
49. Tell me about SSO?
50. How do you handle duplicate records while loading data into the essbase?
51. Can we give Formulas to Shared Members?
52. Can we give formulas to attribute dimension?
53. How do you optimize the Calclation?
54. What is the difference between ESSCMD and MAXL?
55. What is the difference between 7.x and System 9?
56. What is the the use of navigatation without data in Excel- add-ins?
57. What is cascade option in Excel-addins?
58. Can i give the same alias name to the different member?
59. What is TB First and TB Last?
60. What is the differene between FIX and IF Command?
61. What is MDALLOCATE Function do?
62. What @XREF Function do?
63. What is the difference between Data file cache and Data cache?
64. What will uncommit access do?
65. How does essbase consolidates the data?
66. Can you give the same name to different members in UDA?
67. What are different types of dimension building?
68. What do know about partition?
69. What are different types of partition? Have you ever worked on Partition?
70. what is the Maxl Command to rename the application or database?
71. What is the MAXL Command to bring the application into read only mode?
72. What are the steps to schedule the batch in Financial Reporting?
73. I have got 10 reports in my financial reporing and i want to send all those reports in one pdf how to you do that?
74. What is the difference between MOLAP and ROLAP?
75. What is fact table in your outline?
76. Can i have more then one fact table in my outline?
77. What is the calculation order of essbase?
78. How do you decide whether the file which you have received is in generation reference or level referene or parent & Child reference?
79. IS there any situation that i get more number of text files then the number of dimensions in my outline to build the dimensions?
80. Can i build the dimension and also the shared member with one text file if yes what is the procedure ?
81. Can i build the dimension and the data load with the same rule file if yes what is the procedure?
82. Can i set my cahces using MAXL Command if yes how do you do it?
83. Can i assign storage properties in my rule file if yes how should i do it?
84. If your end user complaint in report where should u check for finding error values? and what is the procedure that you follow to close it? Do you interact with user directly? if yes what is the mode of your interaction?
85. What are the different compression techniques?
86. How many dimensions a there in your project and what are they?
87. How many members are there in your time and account dimension & What is the hierachy of your time dimension?
88. What is rule file?
89. How many flat files for data loading you used in your project?
90. How many rule files you have used for data loading?
91. Tell me errors that you get while data loading and dimension building?
92. Where do you capture data laod errors and dimension build errors?
93. How much time it will take to load data in your project?
94. What is the size of your data file?
95. What is lock and send in your excel-addins
96. While loading the data if server hangs up what will happen?
97. When does restructuring happen?
98. What is your application size? and where do you check it?
99. How is data stored in ASO a and where is it saved?
100. If the data corrupts what will you do?
101. How do you remove defragmentation?
102. Is your current project and ASO or BSO and who will decide it and at which stage of SLDC?
103. What is your team size?
104. What are the differnent optimization techniques that you have followed to imporve the performance of the Essbase and how much percentage it has improved?
105. Can you tell me the number of users in your project?
106. How do you know if everything what you have done in your project is meeting the client needs?
107. Do you interact with client?
108. What type of testing is done in your project?
109. What is your project duration? How much time is spent for Dimension building and data loading?
110. Can you name some of your calculation scripts in your project?
111. How much time it will take when your run your calculation script?
112. Do you know what your calculation script does?
113. When and how do you take the back up of the cube?
114. What are the frequently used MAXL scripts in your project?
115. What will you do when you are not able to solve when problem occurs in your project?
Hyperion Essbase Questions Set Two
1) Difference between ASO & BSO?
2) What is Incremental Dimensional Built and one time Dimension Built?
3) What are the different types of LOG Files?
4) What is the Extension of Cal Scripts and Rule File?
5) Why are Filters used?
6) What are dense and sparse dimensions?
7) What are Filters?
8) What are Attributes?
9) Different Types of Attributes?
10) What is Substitution Variable?
11) How is Data Stored in Essbase?
12) What is an hour glass modle?
13) Types of Build Methods?
14) What is Two Pass Calculation?
15) What is TB First and TB Last?
16) How do you calculate the Size of the datablock?
17) How many data blocks are there in your cube?
18) What is meant by Descendents and can you give me the best example to describe it?
19) How do you do the Data Load?
20) Where is IS Member Command used?
21) What is the difference between FIX and If Command?
22) What is intelligent Calcuation?
23) What is meant by Clean block and Dirty block?
24) Commands of Intelligent calcuation?
25) How do you calculate the subset of a cube?
26) Difference between standard dimension and attribute dimension?
27) Difference between UDA and Attribute Dimension?
28) What is meant by XREF Function?
29) Can you give the same name to different members in UDA?
30) What is Data Cahce ?
31) What is the Difference between the Data Cache and Data File Cache?
32) What is the Size of your cube?
33) What is Shared members? Can shared members can have children below it ?
34) What are the different storage properties in Essbase?
35) What is the difference between Dynamic calc and Dynamic Calc and Store?
36) How does Essbase consolidates the Data?
37) Different types of Dimension Building?
38) What is Label only? Give the the example of it?
39) Difference between calc all and Calc Dim?
40) Explain me about your project? And tell me any difficulities that you have faced and how did you resolve it?
41) What is Commit Block?
42) Tell me different steps so that your can optimze the performance your cube?
43) What is Partition?
44) What are different types of Partition? Have you ever worked on partation?
45) What is MAXL ? and where is it used?
46) What is Alias?
47) How many Alias Tables can be created?
48) How is Data Retrived from Essbase?
49) How is Data Stored in ASO?
50) Different types of Attributes in ASO?
51) Tell me about your project and what is your role in your project?
52) Did you write any MDX Member formulas in your Project?
53) What are different Types of Data Blocks?
54) Where do you check the data has been loaded properly or not?
55) What is Fact Table ? What are supporting Dimensions?
56) What is your Fact Table in Cube?
57) How do you create shared members in your cube?
58) Why do you use Fix Command?
59) Can you use the same logic which are used in calculation scripts in MDX formulas?
60) You have 5 dimensions in your cube and to load the data in the cube you have got only 4 columns what do you do?
61) Why do you use set update cal on and set update calc off?
62) What is mean by free form data load?
63) When do delete one member from the outline what will be status of the block?
64) What are different Types of blocks?
65) What is the difference between Attributes and UDA?
66) What is meant by location Alias?
2) What is Incremental Dimensional Built and one time Dimension Built?
3) What are the different types of LOG Files?
4) What is the Extension of Cal Scripts and Rule File?
5) Why are Filters used?
6) What are dense and sparse dimensions?
7) What are Filters?
8) What are Attributes?
9) Different Types of Attributes?
10) What is Substitution Variable?
11) How is Data Stored in Essbase?
12) What is an hour glass modle?
13) Types of Build Methods?
14) What is Two Pass Calculation?
15) What is TB First and TB Last?
16) How do you calculate the Size of the datablock?
17) How many data blocks are there in your cube?
18) What is meant by Descendents and can you give me the best example to describe it?
19) How do you do the Data Load?
20) Where is IS Member Command used?
21) What is the difference between FIX and If Command?
22) What is intelligent Calcuation?
23) What is meant by Clean block and Dirty block?
24) Commands of Intelligent calcuation?
25) How do you calculate the subset of a cube?
26) Difference between standard dimension and attribute dimension?
27) Difference between UDA and Attribute Dimension?
28) What is meant by XREF Function?
29) Can you give the same name to different members in UDA?
30) What is Data Cahce ?
31) What is the Difference between the Data Cache and Data File Cache?
32) What is the Size of your cube?
33) What is Shared members? Can shared members can have children below it ?
34) What are the different storage properties in Essbase?
35) What is the difference between Dynamic calc and Dynamic Calc and Store?
36) How does Essbase consolidates the Data?
37) Different types of Dimension Building?
38) What is Label only? Give the the example of it?
39) Difference between calc all and Calc Dim?
40) Explain me about your project? And tell me any difficulities that you have faced and how did you resolve it?
41) What is Commit Block?
42) Tell me different steps so that your can optimze the performance your cube?
43) What is Partition?
44) What are different types of Partition? Have you ever worked on partation?
45) What is MAXL ? and where is it used?
46) What is Alias?
47) How many Alias Tables can be created?
48) How is Data Retrived from Essbase?
49) How is Data Stored in ASO?
50) Different types of Attributes in ASO?
51) Tell me about your project and what is your role in your project?
52) Did you write any MDX Member formulas in your Project?
53) What are different Types of Data Blocks?
54) Where do you check the data has been loaded properly or not?
55) What is Fact Table ? What are supporting Dimensions?
56) What is your Fact Table in Cube?
57) How do you create shared members in your cube?
58) Why do you use Fix Command?
59) Can you use the same logic which are used in calculation scripts in MDX formulas?
60) You have 5 dimensions in your cube and to load the data in the cube you have got only 4 columns what do you do?
61) Why do you use set update cal on and set update calc off?
62) What is mean by free form data load?
63) When do delete one member from the outline what will be status of the block?
64) What are different Types of blocks?
65) What is the difference between Attributes and UDA?
66) What is meant by location Alias?
Hyperion Essbase Questions Set Three
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.
Hyperion Essbase Questions Set Four
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 2007Inventory=Jan 2007+Feb 2007+ Mar 2007
Qtr1 2007Opening Inventory=Jan 2007
Qtr1 2007 Ending Inventory=Mar 2007
Qtr1 2007Avg 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.
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 2007Inventory=Jan 2007+Feb 2007+ Mar 2007
Qtr1 2007Opening Inventory=Jan 2007
Qtr1 2007 Ending Inventory=Mar 2007
Qtr1 2007Avg 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.