Uncategories Hyperion Essbase Interview questions and answers

Hyperion Essbase Interview questions and answers

Published on 4:56 AM By Koti Reddy Battula
0 comments













Essbase Interview Questions and answers










What are the two storage options available in Essbase and what are the differences?




Ans: ASO (Aggregate Storage Option) - Used for 










1. If we have large number of dimensions (generally more than 10)




 2. Most of the dimensions are sparse 




3. We cannot write back in ASO. I heard recently that ASO will also have the write back capability. 



BSO (Block Storage Option)- 




1. Dimensions are less and dense (recommended values for no of dim are between 4 and 8) . 



2. We can write back hence much suited for planning applications.




Financial applications are generally dense structures and normal analytical cubes are sparse. Here we also need to understand what these dense and sparse members are. When the intersections or cells of two dimensions mostly contain a value it is dense. Say for example we have two dimensions period(Month or quarter) and region and we are calculation the sales amount. Generally maximum regions(countries, cities) will have some sales value for a particular month or quarter. Hence we can say that our period dimensions would be dense. But now instead of period, consider another dimension product. Now there are some products which are sold in only some particular regions hence for them there will be no values in other regions or will have very low percentage of data values hence the structure will become sparse.
Now the question arises what is the use of calling them dense or sparse. What difference does it make. Do some research and try to find the answer.



Can we build dimensions directly from data sources without using rule files.




Ans: No.










When do we generally use build rules files?




Ans: To automate the process of creating dimensions with thousands of members.










What are the three primary build methods for building dimensions?




Ans: 1. Generation references




2. level references




3. Parent-Child references.










In what case we can define generation 1 to a field in generation build method.




Ans: We cannot define that as Generation 1 is not valid.










Suppose we have assigned Generation 2 and Generation 4 as of now and think of adding generation 3 later some time. Can we build the dimension.



Ans: No. If gen 2 and gen 4 exists, we must assign gen 3.










Can we create more than 2 dimensions using only one build rule file.




Ans: Yes, we can do that but it is recommended to use separate rule file for each dimension.



What is UDA( user defined attributes). How are they different than Aliases.




Ans: UDA represents the class of the members. Aliases are just another names of the members. both are different and has different usage.









Can we query a member for its UDA in a calculation script.




Ans: Yes. You can query a member for its UDA in a calculation script.










How does UDA's impact database size?




Ans: Absolutely no impact as UDA’s does not require additional storage space.










What is the difference between UDA's and Attribute dimensions?




Ans : Attribute dimensions provides more flexibility than UDA's. Attribute calculations dimensions which include five members with the default names sum, count, min, max and avg are automatically created for the attribute dimensions and are calculated dynamically.








How does Attribute dimensions and UDA's impact batch calculation performance?




Ans: UDA's- No Impact as they do not perform any inherent calculations.




Attribute dim- No Impact as they perform only dynamic calculations.










How can we display UDA's in reports? How do they impact report report performance.




Ans: UDA's values are never displayed in the reports and hence do not impact report performance.









How does Attribute dim impact report performance?




Ans: They highly impact the report performance as the attributes are calculated dynamically when referenced in the report. For very large number of att dim displayed in the report, the performance could drastically reduce.








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.










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.









Can we have multiple databases in one single application?




Ans: Yes. But only one database per application is recommended.










Can we have one ASO database and one BSO database in one single application. If yes, how and If No, why.



No. Because we define ASO or BSO option while creating the application and not database. Hence if the application is ASO, the databases it contains will be that type only.









What are the file extensions for an outline, rule file and a calc script.




Ans: .OTL, .RUL and .CSC










What is the role of provider services.




Ans: To communicate between Essbase and Microsoft office tools.










What is an alternative to create meta outline, rule file and load data.




Answer: Integration services and in version 11, we have Essbase studio.










Can we start and stop an application individually. How can this be used to increase performance.



Ans: Yes. We can manage our server resources by starting only the applications which receive heavy user traffic. When an application is started, all associated databases are brought to the memory.








We have created an application as unicode mode. Can we change it later to non-unicode mode.



Ans: No.










How can I migrate an application from my test environment to the production environment. Can I still do this if my test environment is a windows one and my production env is UNIX one. Also can I still do it if my test env and prod env versions are different.


Ans: Hyperion Administrative services console provides a migration utility to do this but only the application, database objects are migrated and no data is transferred. the answers to other two questions are Yes.








A customer wants to run two instances of an Essbase server on a same machine to have both test env and Development env on the same server. Can he do that?



Ans: Yes. We can have multiple instances of an Essbase server on a single machine and there will be different sets of windows services for all these instances.









suppose I have a dimension A with members B and C and I do not want B and C to roll up to A. how can I do this.



Ans: Using (~) exclude from consolidation operator.










What does never consolidate operator (^) do?




Ans: It prevents members from being consolidate across any dimension.










Can we have multiple meta outlines based on one OLAP model in Integration services?




Ans: Yes










Can we have an meta outline based on two different OLAP models.




Ans: No.










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.









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 types of partitioning options available in Essbase?




Ans: 1. Replicated partition. 2. Transparent partition 3. Linked partition.




Dynamic calc decreases the retrieval 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 retrieval time should increase.









A customer has a cube with 13 dimensions and the performance was fine. Now they have added two more dimensions in the existing cube and the performance becomes very slow. Customer said logically nothing wrong with the two new dimensions they have added. What could be the reason for slow down.
Ans: Even I don’t have any clue. I experienced this few days back with a customer. If you can throw some light on this, please post your answer



Can we have multiple meta outlines 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 meta outline 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 parallel 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 Administrator 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 properties.



Ans: 1. Login attempts allowed before username is disabled.




2. Number of inactive days before username is disabled.




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 retrieval 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 retrieval 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.









Can we have multiple databases in one single application?




Ans: Yes. But only one database per application is recommended.










Can we have one ASO database and one BSO database in one single application. If yes, how and If No, why.



Ans: No. Because we define ASO or BSO option while creating the application and not database. Hence if the application is ASO, the databases it contains will be that type only.















What are the file extensions for an outline, rule file and a calc script.




Ans: .OTL, .RUL and .CSC










What is the role of provider services.




Ans: To communicate between Essbase and Microsoft office tools.










What is an alternative to create meta outline, rule file and load data.




Ans: Integration services and in version 11, we have Essbase studio.










What is data file cache?




Ans: A buffer in memory that holds compressed data (.PAG) files.










What is custom defined function?




Ans: Essbase calculation functions that you develop in the Java programming language and then add to the standard Essbase calculation scripting language by means of MaxL.









Can we start and stop an application individually. How can this be used to increase performance.



Ans: Yes. We can manage our server resources by starting only the applications which receive heavy user traffic. When an application is started, all associated databases are brought to the memory.








We have created an application as Unicode mode. Can we change it later to non-unicode mode.



Ans: No.










How can I migrate an application from my test environment to the production environment. Can I still do this if my test environment is a windows one and my production env is UNIX one. Also can I still do it if my test env and prod env versions are different.


Ans: Hyperion Administrative services console provides a migration utility to do this but only the application, database objects are migrated and no data is transferred. the answers to other two questions are Yes.








A customer wants to run two instances of an Essbase server on a same machine to have both test env and Development env on the same server. Can he do that?



Ans: Yes. We can have multiple instances of an Essbase server on a single machine and there will be different sets of windows services for all these instances.









Suppose I have a dimension A with members B and C and I do not want B and C to roll up to A. how can I do this.



Ans: Using (~) exclude from consolidation operator.










What is custom based macro?




Ans: Essbase macros that we write with Essbase calculator functions and special macro functions. Custom-defined macros use an internal essbase macro language that enables you to combine calculation function and operate on multiple input parameters.








What do you mean by dirty block?




Ans: A data block containing cells that have been changed since the last calculation. Upper level blocks are marked as dirty if their child blocks are dirty (that is, have been updated)









When do we generally use build rules files?




Ans: To automate the process of creating dimensions with more members, where it will take a lot of time in creating manually.









What does never consolidate operator(^) do?




Ans: It prevents members from being consolidate across any dimension.










What are the different types of LOG Files?




Ans: So many log files are there in essbase, but the important log files are




1. Application log




2. Essbase.log




3. Configtool.log




4. eas_install.log




5. essbaseserver-install.log










Suppose we have assigned Generation 2 and Generation 4 as of now and think of adding generation 3 later some time. Can we build the dimension.



Ans: No. If gen 2 and gen 4 exists, we must assign gen 3.










What are attributes?




Ans: A classification of a member in a dimension. You can select and group members based on their associated attributes. You can also specify an attribute when you perform calculations and use calculation functions. Eg: The database in Sample Basic which has product dimension has some attributes like size, package type, and flavor. We can add these attributes to the dimensions where we can retrieve the data like for example to retrieve “coke with 8 Oz with bottles”, this is useful for generating reports.






Why do objects gets locked and when does this happens?




Ans: Objects gets locked to prevent users to make simultaneous and conflicting changes to Essbase database objects. By default whenever an object is accessed through Administrative services console or Excel spreadsheet add-in, it gets locked.








What is the difference between UDA's and Attribute dimensions?




Ans : Attribute dimensions provides more flexibility than UDA's. Attribute calculations dimensions which include five members with the default names sum, count, min, max and avg are automatically created for the attribute dimensions and are calculate dynamically.








How does Attribute dimensions and UDA's impact batch calculation performance?




Ans: UDA's- No Impact as they do not perform any inherent calculations.




Attribute dim- No Impact as they perform only dynamic calculations.










What are different types of attributes?




Ans: Essbase supports two different types of attributes.




1. User-Defined attributes




2. Simple attributes




User-Defined attributes: The attributes that are defined by the user.




Simple attributes: Essbase supports some attributes, they are: Boolean, date, number, and string.









What are filters?




Ans: A method of controlling access to database cells in essbase. A filter is the most detailed level of security, allowing you to define varying access levels different users can have to individual database values.








What is TB First and TB Last?




Ans: TB First: in the Sample Basic database, the accounts member Opening Inventory is tagged as TB First. Opening Inventory consolidates the value of the first month in each quarter and uses that value for that month’s parent. For example, the value for Qtr1 is the same as the value for Jan.
TB Last: in the Sample Basic database, the accounts member Ending Inventory is tagged as TB Last. Ending Inventory consolidates the value for the last month in each quarter and uses that value for that month’s parent. For example, the value for Qtr1 is the same as the value for Mar.







How can we display UDA's in reports? How do they impact report report performance.




Ans: UDA's values are never displayed in the reports and hence do not impact report performance.









How does Attribute dim impact report performance?




Ans: They highly impact the report performance as the attributes are calculated dynamically when referenced in the report. For very large number of att dim displayed in the report, the performance could drastically reduce.








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.










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.









How do you optimize outline?




Usually the outline is optimized using the hourglass design for dimension ordering i.e,




· Dimension with Accounts tag




· Dimension with Time tag




· Largest Dense dimension




· Smallest dense dimension




· Smallest Sparse dimension




· Largest Sparse dimension










What are the ways to improve performance during data loads? 




There are several ways to optimize load










1. Grouping of Sparse member combinations




2. Making the data source as small as possible




3. Making source fields as small as possible




4. Positioning the data in the same order as the outline




5. Loading from Essbase Server




6. Managing parallel data load processing










What are the design considerations for calculation optimization?




You can configure a database to optimize calculation performance. The best configuration for the site depends on the nature and size of the database.



· Block Size(8Kb to 100Kb) and Block Density




· Order of Sparse Dimensions




· Incremental Data Loading




· Database Outlines with Two or More Flat Dimensions




· Formulas and Calculation Scripts




When does Fragmentation occur?




Fragmentation is likely to occur with the following:




· Read/write databases that users are constantly updating with data




· Databases that execute calculations around the clock




· Databases that frequently update and recalculate dense members




· Data loads that are poorly designed




· Databases that contain a significant number of Dynamic Calc and Store members




· Databases that use an isolation level of uncommitted access with commit block set to zero




How can you measure fragmentation?




 You can measure fragmentation using the average clustering ratio or average fragmentation Quotient.



Using the average fragmentation quotient




 Any quotient above the high end of the range indicates that reducing fragmentation may help performance



Small (up to 200 MB) 60% or higher




Medium (up to 2 GB) 40% or higher




Large (greater than 2 GB) 30% or higher




Using the average clustering ratio:




The average clustering ratio database statistic indicates the fragmentation level of the data (.pag) files. The maximum value, 1, indicates no fragmentation.



How do you can prevent and remove fragmentation?




You can prevent and remove fragmentation:




· To prevent fragmentation, optimize data loads by sorting load records based upon sparse dimension members. For a comprehensive discussion of optimizing data load by grouping sparse members.


· To remove fragmentation, perform an export of the database, delete all data in the database with CLEARDATA, and reload the export file.



· To remove fragmentation, force a dense restructure of the database.




Why is database restructuring?










As your business changes, you change the Essbase database outline to capture new product lines, provide information on new scenarios, reflect new time periods, etc. Some changes to a database outline affect the data storage arrangement, forcing Essbase to restructure the database.

What are the types of database restructuring?




 The two ways by which a database restructure is triggered:




Implicit Restructures




Dense restructure




Sparse restructure




Outline-only restructure




Explicit Restructures




What are the conditions affecting Database restructuring?




 Intelligent Calculation, name changes, and formula changes affect database restructuring:




· If you use Intelligent Calculation in the database, all restructured blocks are marked as dirty whenever data blocks are restructured. Marking the blocks as dirty forces the next default Intelligent Calculation to be a full calculation.


· If you change a name or a formula, Essbase does not mark the affected blocks as dirty. Therefore, you must use a method other than full calculation to recalculate the member or the database.


What are the files used during Restructuring?










When Essbase restructures both the data blocks and the index, it uses the files described




essxxxxx.pag




Essbase data fileessxxxxx.ind




Essbase index filedbname.esm




Essbase kernel file that contains control information used for db recoverydbname.tct




Transaction control tabledbname.ind




Free fragment file for data and index free fragmentsdbname.otl




Outline file in which is defined all metadata for a database and how data is stored 




What are the actions that improve performance for restructuring?




 There are a number of things you can do to improve performance related to database restructuring:



· If you change a dimension frequently, make it sparse. · Use incremental restructuring to control when Essbase performs a required database restructuring. · Select options when you save a modified outline that reduce the amount of restructuring required.


Que:1 What are LRO's( Linked Reporting Objects)?




Answer: 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 migtarion activities.






What are the specified roles other than Administrator to view sessions, disconnect sessions or kill users requests for a particular application?



Answer: You should have the role of Application manager for the specified application.










What are the three options specified in Username and Password management under security tab in Essbase server properties.



Answer: 1. Login attempts allowed before username is disabled.




2. Number of inactive days before username is disabled.




3. Number of days before user must change password.










Why do objects gets locked and when does this happens?




Answer: Objects gets locked to prevent users to make simultaneous and conflicting changes to Essbase database objects. By default whenever an object is accessed through Administrative services console or Excel spreadsheet add-in, it gets locked.








I have created an application with one database in Essbase at test environment and now needs to copy the entire application with all security permissions to the production server which is not connected to the test server. How can I do that?


Answer: You can achieve this using migration wizard to migrate an application to a text file which you can take physically to the production server.









From the above question, When I migrated the application to the production server I do not see any data in the Essbase database. What has gone wrong and how to correct this?



Answer: Nothing has gone wrong. Migration utility does not migrate the actual data. If you want to copy the exact data from the test machine, get it exported using export utility and import it on production machine.








Referring to the above question, Can I still do this if my production server and test server are two different operation systems. Explain.



Answer: While exporting the database, you can write the database data to an ASCII text file which enables you to load data on different platforms.









How can we backup an aggregate storage database?




Answer: To back up an aggregate storage database, copy the application directory ARBORPATH/app/appname.









How does Essbase recover from a database crash?




Answer: After a database crash or server interruption, Essbase automatically recovers a database after rolling back the transactions that were active at that time.









How can you make a Max L script run at a specified time?




Answer: MaxL scripts can be called from a BAT file which is used for scheduling.










Have multiple applications? Not sure how to transfer or import data to multiple applications that contain different members/dimensions?









If you need to map multiple members from a source application to an equivalent member in a target application, you must first define the source members.









 Define Source Members










Option 1 – Shared alternate hierarchy










       a. Create new parent member with the same member name as the target application and set property to  ignore to prevent duplication of data



       b. Add existing members as children of new parent member










- Requires additional maintenance of the dimension structure




- Does not compromise data retrieval




- May increase calculation time










We all want our end-users to be self efficient and I think they may be able to figure out the mapping once they drill-down the hiearchy. I have used alternate hierarchies in large applications and I have yet to compromise calculation time.








Option 2 – User Defined Attributes










       a. Assign UDA’s to source members




       b. Create a new member and set property to ignore to prevent duplication of data




       c. Develop a calc script fixed on the UDA and populate the Sum of the UDAs to “new member”









- Requires additional member property maintenance




- Does not compromise data retrieval




- Does not compromise calculation time










If data is constantly re-stated, re-classed or adjusted you must remember to re-calculate. Throw in a batch script and call it a day – I’m just saying.









Now, the sophisticated/advanced Essbase user will cleverly filter their retrievals on UDA’s and magically list the accounts that make up the mapping.









Option 3 – Attribute Dimension










        a. Create attribute dimension property










The attribute dimension gives you the same results as the alternate shared hierarchy, behaves similar to UDA’s. What is the difference? Attributes may significantly compromise report retrievals.








Which one is the best option?










Every environment is different. Performance is significantly dependent on the application configuration and I strongly recommend you test.









II. Data transfer and Data Export Options










Now, we are ready to explore options on how to populate data from the source application to the target application. The options are as follows:









Option 1 – @XREF built in function transfers data from source to target application via calc script









- Excellent option for consistent structured hierarchies




- Leverages built-in calculation functions




- Data flows seamlessly if the structures are one in the same




- Can be easily batched










If you fix at level 0 in a common dimension i.e. @LEVMBR(“ENTITY”,0) and it is not an exact mirror of the source application, duplication of data will result at the level where the dimension hierarchy differ. Therefore, the member names and levels must be identical.








The granular the fix statement, the quicker the performance.










Option 2 – Report Script Exports data to a flat file (see Simplified Essbase Administration Part II)









- Export data from specific Alias tables, UDA, levels




- Specify export column/row format




- Exclude members from a dimension




- Re-name members




- Can be easily batched










Report script performs efficiently when focused on smaller data sets. Larger data sets may take longer to process.









The rename built-in function works brilliantly if you need to map specific members to an additional dimension member in the target application i.e. source member 1234 must map specifically to target member 4567 and COLA {“RENAME” “1234”} “4567 COLA” . This option is best used if the mapping is consistent; however, unique to specific members in a different dimension. This data file can be easily parsed using the Split function within a data load rule.






Option 3 - DataExport – Exports data to a flat file










- Exports data to text file extremely fast




- Leverages built-in calculation functions




- Can be easily batched










DataExport outperforms report scripts. It is engineered to export data in the order that it is organized in the database. Whereas, report script performance is dependent on coding and may retrieve the same blocks multiple times.








DataExport does not support attributes, aliases, member re-name and data format options.










Every environment is different. Performance is significantly dependent on the application configuration and I strongly recommend you test.









This is the beauty of Essbase, the options are available at your disposal.






















Process for Creating Attributes




Understanding Attributes




Understanding Attribute Dimensions




Designing Attribute Dimensions




Building Attribute Dimensions




Setting Member Names in Attribute Dimensions




Calculating Attribute Data




Varying Attributes




Some information in this chapter applies only to block storage databases and is not relevant to aggregate storage databases.



Also see:




·         Comparison of Aggregate and Block Storage




·         Aggregate Storage Applications, Databases, and Outlines




For other information about using attributes, see:




·         Building Attribute Dimensions and Associating Attributes




·         Designing Partitioned Applications




·         Creating and Maintaining Partitions




·         Developing Report Scripts




Process for Creating Attributes




Attributes describe characteristics of data such as product size and color. Through attributes, you can group and analyze members of dimensions based on their characteristics. This chapter describes how to create and manage attributes in an Essbase Server outline.


When working with attributes in Outline Editor, perform the following tasks:




1.   Create a dimension.See Adding Dimensions and Members to an Outline. In the outline, position the attribute dimensions after all standard dimensions.



2.   Tag the dimension as an attribute dimension and set attribute dimension type as text, numeric, Boolean, or date.See Creating Attribute Dimensions.



3.   Add members to the attribute dimension.See Adding Dimensions and Members to an Outline.



4.   Associate a base dimension with the attribute dimension.See Understanding the Rules for Attribute Dimension Association.



5.   Associate members of the base dimension with members of the attribute dimension.See Understanding the Rules for Attribute Member Association.



6.   If necessary, set up the attribute calculations.See Calculating Attribute Data.




Understanding Attributes




You can use the Essbase attribute feature to retrieve and analyze data not only from the perspective of dimensions, but also in terms of characteristics, or attributes, of those dimensions. For example, you can analyze product profitability based on size or packaging, and you can make more effective conclusions by incorporating into the analysis market attributes such as the population of each market region.
Such an analysis could tell you that decaffeinated drinks sold in cans in small markets (populations less than 6,000,000) are less profitable than you anticipated. For more details, you can filter the analysis by specific attribute criteria, including minimum or maximum sales and profits of different products in similar market segments.
A few ways analysis by attribute provides depth and perspective, supporting better-informed decisions:



·         You can select, aggregate, and report on data based on common features (attributes).




·         By defining attributes as having a text, numeric, Boolean, or date type, you can filter (select) data using type-related functions such as AND, OR, and NOT operators and <, >, and = comparisons.


·         You can use the numeric attribute type to group statistical values by attribute ranges; for example, population groupings such as <500 500="" and="">1,000,000.



·         Through the Attribute Calculations dimension automatically created by Essbase, you can view sums, counts, minimum or maximum values, and average values of attribute data. For example, when you enter Avg and Bottle into a spreadsheet, Essbase retrieves calculated values for average sales in bottles for all the column and row intersections on the sheet.
·         You can perform calculations using numeric attribute values in calculation scripts and member formulas; for example, to determine profitability by ounce for products sized by the ounce.


·         You can create crosstabs of attribute data for the same dimension, and you can pivot and drill down for detail data in spreadsheets.An attribute crosstab is a report or spreadsheet showing data consolidations across attributes of the same dimension. The crosstab example below displays product packaging as columns and the product size in ounces as rows. At their intersections, you see the profit for each combination of package type and size.
From this information, you can see which size-packaging combinations were most profitable in the Florida market.



            Product Year Florida Profit Actual


















                 Bottle        Can           Pkg Type






                 =========     =========     =========








32 946 N/A             946







20 791 N/A             791







16 714 N/A             714







12 241 2,383 2,624






Ounces           2,692 2,383 5,075







Understanding Attribute Dimensions




In the Sample.Basic database, products have attributes that are characteristics of the products. For example, products have an attribute that describes their packaging. In the outline, you see these characteristics as two dimensions, the Products dimension, and the Pkg Type attribute dimension that is associated with it. An attribute dimension has the word Attribute next to its name in the outline.
Figure 39, Outline Showing Base and Attribute Dimensions shows part of the Sample.Basic outline featuring the Product dimension and three attribute dimensions, Caffeinated, Ounces, and Pkg Type.


Figure 39. Outline Showing Base and Attribute Dimensions




Outline Showing Base and Attribute Dimensions




In the outline, to the right of the Product dimension, the terms Caffeinated, Ounces, and Pkg Type show that these attribute dimensions are associated with the Product dimension.



A standard dimension is any dimension that is not an attribute dimension. When an attribute dimension is associated with a standard dimension, the standard dimension is the basedimension for that attribute dimension. In the outline in Figure 39, Outline Showing Base and Attribute Dimensions, the Product dimension is the base dimension for the Caffeinated, Ounces, and Pkg Type attribute dimensions.
Note:




Attribute dimensions and members are Dynamic Calc, so Essbase calculates attribute information at retrieval time. Attribute data is not stored in the database.



Understanding Members of Attribute Dimensions




Members of an attribute dimension are potential attributes of the members of the associated base dimension. After you associate a base dimension with an attribute dimension, you associate members of the base dimension with members of the associated attribute dimension. The Market dimension member Connecticut is associated with the 6000000 member of the Population attribute dimension. That makes 6000000 an attribute of Connecticut.
In the outline, the information next to a base dimension member shows the attributes of that member. Figure 39, Outline Showing Base and Attribute Dimensions, for example, shows that product 100-10 has three attributes—it has caffeine, is sold in 12-ounce containers, and is sold in cans.
Understanding the Rules for Base and Attribute Dimensions and Members




Rules regarding members of attribute dimensions and their base dimensions.




·         You can tag only sparse dimensions as attribute dimensions.




·         Before you can save an outline to the server, each attribute dimension must be associated with a standard, sparse dimension as its base dimension.



·         Attribute dimensions must be the last dimensions in the outline.




·         Attribute dimensions have a type setting—text, numeric, Boolean, or date. Text is the default setting. Although assigned at the dimension level, the type applies only to the level 0 members of the dimension. See Understanding Attribute Types.


·         If you remove the attribute tag from a dimension, Essbase removes prefixes or suffixes from its member names. Prefixes and suffixes are not visible in the outline. See Setting Prefix and Suffix Formats for Member Names of Attribute Dimensions.


·         A base dimension member can have many attributes, but only one attribute from each attribute dimension.For example, product 100-10 can have size and packaging attributes, but only one size and only one type of packaging.


·         You cannot associate an attribute with an implied shared member, the child of which is tagged as shared.



·         Essbase does not support attributes for Hybrid Analysis-enabled members.




You can use attribute values in calculations in the following comparisons:




·         > (greater than)




·          >= (greater than or equal to)




·         < (less than)




·         <= (less than or equal to)




·         = = (equal to)




·         <> or != (not equal to)




·         IN




Understanding the Rules for Attribute Dimension Association




When you associate an attribute dimension with a standard dimension, the standard dimension is the base dimension for that attribute dimension.



·         An attribute dimension must be associated with a sparse standard dimension.




·         A standard dimension can be a base dimension for more than one attribute dimension.



·         An attribute dimension can be associated with only one base dimension.For example, you might have a Size attribute dimension with members Small, Medium, and Large. If you associate the Size attribute dimension with the Product dimension, you cannot also associate the Size attribute dimension with the Market dimension. Tracking size-related information for the Market dimension requires another attribute dimension with a different name; for example, MarketSize, with the MarketSize attribute dimension associated with the Market dimension.
Understanding the Rules for Attribute Member Association




When you associate a member of an attribute dimension with a member of a base dimension, follow these rules:



·         You cannot associate multiple members from the same attribute dimension with the same base dimension member. For example, the Bottle and Can package types cannot both be associated with the product 100-30.


·         You can associate members from different attribute dimensions with the same member of a base dimension. For example, a decaffeinated cola product (100-30) sold in 16-ounce bottles has three attributes—Caffeinated:False; Ounces:16; and Pkg Type:Bottle.


·         After attributes are associated with base dimension members, if you cut or copy and paste base dimension members to another outline location, the attribute associations are lost.


·         Essbase does not require that each member of a base dimension be associated with a member of an attribute dimension.



·         All base dimension members associated with members of a particular attribute dimension must be at the same level.For example, in Figure 40, Association of Attributes with the Same Level Members of the Market Dimension, all Market dimension members that have Population attributes are at level 0. You cannot associate East, which is a level 1 member, with a Population attribute, because the other members of the Market dimension that have Population attributes are level 0 members.
Figure 40. Association of Attributes with the Same Level Members of the Market Dimension




Association of Attributes with the Same Level Members of the Market Dimension




·         The level 0 members of attribute dimensions are the only members that you can associate with base dimension members.For example, in the Population attribute dimension, you can associate only level 0 members such as 3000000, 6000000, and 9000000, with members of the Market dimension. You cannot associate a level 1 member such as Small.
The name of the level 0 member of an attribute dimension is the attribute value. The only members of attribute dimensions that have attribute values are level 0 members.



You can use the higher-level members of attribute dimensions to select and group data. For example, you can use Small, the level 1 member of the Population attribute dimension, to retrieve sales in both the 3000000 and 6000000 population categories.


Understanding Attribute Types




Attribute dimensions have a text, numeric, Boolean, or date type that enables different functions for grouping, selecting, or calculating data. Although assigned at the dimension level, the attribute type applies only to level 0 members of the attribute dimension.


·         The default attribute type is text. Text attributes enable the basic attribute member selection and attribute comparisons in calculations. When you perform such comparisons, Essbase compares characters. For example, the package type Bottle is less than the package type Can, because B precedes C in the alphabet. In Sample.Basic, Pkg Type is an example of a text attribute dimension.
·         The names of level 0 members of numeric attribute dimensions are numeric values. You can include the names (values) of numeric attribute dimension members in calculations. For example, you can use the number of ounces specified in the Ounces attribute to calculate profit per ounce for each product.You can also associate numeric attributes with ranges of base dimension values; for example, to analyze product sales by market population groupings—states with 3,000,000 population or less in one group, states with a population between 3,000,001 and 6 million in another group, and so on. See Setting Up Member Names Representing Ranges of Values.
·         All Boolean attribute dimensions in a database contain only two members. The member names must match the settings for the database; for example, True and False. If more than one Boolean attribute dimension exists, specify a prefix or suffix member name format to ensure unique member names; for example, Caffeinated_True and Caffeinated_False. For a discussion of how to change Boolean names, see Setting Boolean Attribute Member Names.
·         You can use date attributes to specify the date format—month-day-year or day-month-year—and to sequence information accordingly. For a discussion of how to change date formats, see Changing the Member Names in Date Attribute Dimensions. You can use date attributes in calculations. For example, you can compare dates in a calculation that selects product sales from markets established since 10-12-1999.Essbase supports date attributes from January 1, 1970, through January 1, 2038.
Comparing Attribute and Standard Dimensions




In general, attribute dimensions and their members are similar to standard dimensions and their members. You can provide aliases and member comments for attributes. Attribute dimensions can include hierarchies, and you can name generations and levels. You can perform the same spreadsheet operations on attribute dimensions and members as on standard dimensions and members; for example, to analyze data from different perspectives, you can retrieve, pivot, and drill down in the spreadsheet.
Table 18 describes major differences between attribute and standard dimensions and their members.



Table 18. Differences Between Attribute and Standard Dimensions




Functionality Attribute Dimensions Standard Dimensions


Storage Sparse. Their base dimensions also must be sparse. Can be dense or sparse


Storage property Dynamic Calc only, therefore not stored in the database. The outline does not display this property. Can be Store Data, Dynamic Calc and Store, Dynamic Calc, Never Share, or Label Only


Position in outline Must be the last dimensions in the outline Must be ahead of all attribute dimensions in the outline


Partitions Cannot be defined along attribute dimensions, but you can use attributes to define a partition on a base dimension. Can be defined along standard dimensions.


Formulas (on members) Cannot be associated Can be associated


Shared members Not allowed Allowed


Two-pass calculation member property Not available Available


Two-pass calculation with runtime formula If a member formula contains a runtime-dependent function associated with an attribute member name, and the member with the formula is tagged as two-pass, calculation skips the member and issues a warning message. Runtime-dependent functions include: @CURRMBR, @PARENT, @PARENTVAL, @SPARENTVAL, @MDPARENTVAL, @ANCEST, @ANCESTVAL, @SANCESTVAL, and @MDANCESTVAL. Calculation is performed on standard members with runtime formulas and tagged two-pass.

Two-pass, multiple dimensions: Calculation order Order of calculation of members tagged two-pass depends on order in outline. The last dimension is calculated last. Calculation result is not dependent on outline order for members tagged two-pass in more than one dimension.
Two-pass calculation with no member formula Calculation skipped, warning message issued. Therefore, member intersection of two-pass tagged members and upper-level members may return different results from calculation on standard dimensions. Available


Dense Dynamic Calc members in nonexisting stored blocks Calculations skip dense dimensions if they are on nonexisting stored blocks. To identify nonexisting stored blocks, export the database or run query to find out whether block has data. Available


UDAs on members Not allowed Allowed


Consolidations For all members, calculated through the Attribute Calculations dimension members: Sum, Count, Min, Max, and Avg. Consolidation operators in the outline are ignored during attribute calculations. Consolidation operation indicated by assigning the desired consolidation symbol to each member

Member selection facilitated by Level 0 member typing Available types include text, numeric, Boolean, and date. All members treated as text


Associations Must be associated with a base dimension N/A


Spreadsheet drill-downs List the base dimension data associated with the selected attribute. For example, drilling down on the attribute Glass displays sales for each product packaged in glass, where Product is the base dimension for the Pkg Type attribute dimension. List lower or sibling levels of detail in the standard dimensions. For example, drilling down on QTR1 displays a list of products and their sales for that quarter.
Comparing Attributes and UDAs




Attributes and UDAs enable analysis based on characteristics of the data. Attributes provide greater capability than UDAs. The tables in this topic describe the differences between attributes and UDAs in these areas of functionality:


·         Data storage (Table 19, Data Storage—Comparing Attributes and UDAs)




·         Data retrieval (Table 20, Data Retrieval—Comparing Attributes and UDAs)




·         Data conversion (Table 21, Data Conversion—Comparing Attributes and UDAs)




·         Calculation scripts (Table 22, Calculation Scripts—Comparing Attributes and UDAs)




Table 19. Data Storage—Comparing Attributes and UDAs




Data storage Attributes UDAs


You can associate with sparse dimensions. Supported Supported


You can associate with dense dimensions. Not supported Supported


Table 20. Data Retrieval—Comparing Attributes and UDAs




Data Retrieval Attributes UDAs


You can group and retrieve consolidated totals by attribute or UDA value. For example, associate the value High Focus Item to various members of the Product dimension and use that term to retrieve totals and details for only those members. Supported
Supported



Simple
More difficult to implement, requiring additional calculation scripts or commands


You can categorize attributes in a hierarchy and retrieve consolidated totals by higher levels in the attribute hierarchy; for example, if each product has a size attribute such as 8, 12, 16, or 32, and the sizes are categorized as small, medium, and large. You can view the total sales of small products.
Supported
Supported




More difficult to implement


You can create crosstab views displaying aggregate totals of attributes associated with the same base dimension.
Supported
Supported



You can show a crosstab of all values of each attribute dimension.
You can retrieve only totals based on specific UDA values.


You can use Boolean operators AND, OR, and NOT with attribute and UDA values to refine a query. For example, you can select decaffeinated drinks from the 100 product group.
Supported
Supported


Because attributes have a text, Boolean, date, or numeric type, you can use appropriate operators and functions to work with and display attribute data. For example, you can view sales totals of all products introduced after a specific date.
Supported
Not supported


You can group numeric attributes into ranges of values and let the dimension building process automatically associate the base member with the appropriate range. For example, you can group sales in various regions based on ranges of their populations—less than 3 million, between 3 million and 6 million, and so on.
Supported
Not supported


Through the Attribute Calculations dimension, you can view aggregations of attribute values as sums, counts, minimums, maximums, and averages.
Supported
Not supported


You can use an attribute in a calculation that defines a member. For example, you can use the weight of a product in ounces to define the profit per ounce member of the Measures dimension.
Supported
Not supported


You can retrieve specific base members using attribute-related information.
Supported
Supported



Powerful conditional and value-based selections
Limited to text string matches only


Table 21. Data Conversion—Comparing Attributes and UDAs




Data Conversion Attributes UDAs


Based on the value of a UDA, you can change the sign of the data as it is loaded into the database. For example, you can reverse the sign of all members with the UDA Debit. Not supported Supported


Table 22. Calculation Scripts—Comparing Attributes and UDAs




Calculation Scripts Attributes UDAs


You can perform calculations on a member if its attribute or UDA value matches a specific value. For example, you can increase the price by 10% of all products with the attribute or UDA of Bottle. Supported Supported


You can perform calculations on base members whose attribute value satisfies conditions that you specify. For example, you can calculate the Profit per Ounce of each base member. Supported Not supported


Designing Attribute Dimensions




Essbase provides more than one way to design attribute information into a database. Most often, defining characteristics of the data through attribute dimensions and their members is the best approach. The following sections discuss when to use attribute dimensions, when to use other features, and how to optimize performance when using attributes.
Using Attribute Dimensions




For the most flexibility and functionality, use attribute dimensions to define attribute data. Using attribute dimensions provides the following features:



·         Sophisticated, flexible data retrievalYou can view attribute data only when you want to; you can create meaningful summaries through crosstabs; and, using type-based comparisons, you can selectively view only the data that you want to see.


·         Additional calculation functionalityNot only can you perform calculations on the names of members of attribute dimensions to define members of standard dimensions, you can also access five types of consolidations of attribute data—sums, counts, averages, minimums, and maximums.
·         Economy and simplicityBecause attribute dimensions are sparse, Dynamic Calc, they are not stored as data. Compared to using shared members, outlines using attribute dimensions contain fewer members and are easier to read.


See Understanding Attributes.




Using Alternative Design Approaches




In some situations, consider one of the following approaches:




·         UDAs. Although UDAs provide less flexibility than attributes, you can use them to group and retrieve data based on its characteristics. See Comparing Attributes and UDAs.



·         Shared members. For example, to include a seasonal analysis in the Year dimension, repeat the months as shared members under the appropriate season; Winter: Jan (shared member), Feb (shared member), and so on. A major disadvantage of using shared members is that the outline becomes large if the categories repeat many members.
·         Standard dimensions and members. Additional standard dimensions provide flexibility but add storage requirements and complexity to a database. For guidelines on evaluating the impact of additional dimensions, see Analyzing and Planning.


Table 23 describes situations in which you might consider an alternative approach to managing attribute data in a database.



Table 23. Considering Alternatives to Attribute Dimensions




Situation Alternative



Analyze attributes of dense dimensions UDAs or shared members



Perform batch calculation of data Shared members or members of separate, standard dimensions



Define the name of a member of an attribute dimension as a value that results from a formula Shared members or members of separate, standard dimensions



Define attributes that vary over time Members of separate, standard dimensions. For example, to track product maintenance costs over time, the age of the product at the time of maintenance is important. However, using the attribute feature, you could associate only one age with the product. You need multiple members in a separate dimension for each time period that you want to track.
Minimize retrieval time with large numbers of base-dimension members Batch calculation with shared members or members of separate, standard dimensions.



Optimizing Outline Performance




Outline layout and content can affect attribute calculation and query performance. For general outline design guidelines, see Designing an Outline to Optimize Performance.



To optimize attribute query performance, consider the following design tips:




·         Ensure that attribute dimensions are the only sparse Dynamic Calc dimensions in the outline.



·         Locate sparse dimensions after dense dimensions in the outline. Place the most-queried dimensions at the beginning of the sparse dimensions and attribute dimensions at the end of the outline. In most situations, base dimensions are queried most.


See Optimizing Calculation and Retrieval Performance.




Building Attribute Dimensions




To build an attribute dimension, tag the dimension as an attribute and assign the dimension a type. Then associate the attribute dimension with a base dimension. Finally, associate each level 0 member of the attribute dimension with a member of the associated base dimension.

To build an attribute dimension, see “Defining Attributes” in the Oracle Essbase Administration Services Online Help.



To view the dimension, attribute value, and attribute type of a specific attribute member, use a tool:



Koti Reddy Battula

0 comments :

Subscribe to: Post Comments ( Atom )