Sunday, September 14, 2008

Learn Hyperion Planning

Today we discuss something related to Hyperion Planning.
Hyperion Planning is a part of Hyperion Enterprise performance management applications.
Lets first understand the architecture of Hyperion planning. Hyperion Planning applications sits on top of hyperion planning foundation which uses Hyperion essbase calculation Engine.
This hyperion planning foundation takes care of all the planning and budgeting workflows, creating and applying the business rules, managing user forms, managing various business models for example P&L, Balance Sheet, Cash flow, Workforce planning and Capital Expenditure plans and also some operational planning models like sales and marketing plans etc. Now the interactive users can access and manage this using either Hyperion Workspace which is a web component or Microsoft Excel Add in.
If you ask me why we use hyperion planning, my answer would be to perform whatever I have mentioned in Planning foundation. Other than this the need of Hyperion planning comes in when you want to manage your planning, budgeting and forecasting workflows which generally are very complex.
Most of the companies use Excel to handle this but you know these excel sheets are just scattered peices of a book which are very difficult to manage and consume lot of our time.
Budget and forecasting are generally an iterative proess which means there are levels of hirarchies involved and the allocation of budget from the top, its distribution among the levels and again its consolidation from lower level to the top level is really a very complex task and should be managed using a workflow.

Some of the features are:
1. It uses multidimentional data structure which is powered by Essbase calculation Engine for data collection and Analysis.
2. Web based user data entry forms which users themselves can create.
3. Users can set their targets online and can do bottom-up planning.
4. Ability to create complex models using business rules.
5. Currency conversion feature for multi currency applications.
6. Ability to create complex models.
7. Users can create task lists which enables them to understand the status of their tasks.

Hyperion Planning uses a relational database to store its metadata and other details like cell annotations or text etc. Also it uses Essbase to store its cube dimensions and members. Hence to run Hyperion planning your database which you have configured for planning and Essbase both should be up and running.

You can create Planning applications using either EPM Architect or Classic Application wizard. In both the places the menus and the options available are different. Classic application wizard is only for creating Hyperion planning applications but EPMA can be used to create Planning as well as HFM applications. EPMA consists of Application library and Dimension library and Calculation manager and can be launched from Hyperion workspace.
Classic Application wizard is the only one which I tried and it is more easier to understand and it created the application alongwith the Essbase application and all the dataforms automatically.

Here I will be disucussing more on the concepts which are not there in Oracle by example document.

For the complete steps and procedure, you can refer Oracle By Example- Planning also.

The various steps involved in creating an Application using Classis application wizard are-
Go to planning administration to create an application.

1. Creating a datasourse- An Essbase database alongwith the outline will be created for this purpose and information related to this application will be stored in the relational database also.
To populate the data you need to manually upload data into the Essbase cube.

Hence as I said earlier, for your application to run, both the databases should be up and running.

2. Now we need to define the settings for the application.

This is the most important stage in terms of understanding what does an application contains and how does it works.
The Classic application wizard allows you to select the defaults and directly create an application but we will try to discover each option and try to understand its meaning.
(a) Calender-

Base Time period- This basically makes an application understand how an Year in our application is distributed. You can choose from options like 12 Months or Quarters. If you want to define a customized time period, you can do that as well. But once an option is selected, it cannot be changed once the application is created. Here the months or quarters automatically roll up to the year level.
Now for the base time period defined we need to select the starting month and fiscal start year which again cannot be changed. However more years can be added.
Under weekely distribution, you have options to define how one quarter is divided. We understand that a Quarter has 13 weeks(52/4) and the options we can choose for the quarter distribution are 4-4-5, 4-5-4 or 5-4-4.

(b) Plan Types- You want to have different plans for your application and you can define upto 3 plan types for your applications.
For each plan type you select, Hyperion creates an Essbase cube for that plan under the same application node and data stored in one plan type is shared with another plan type.

(c) Currencies- For applications which needs to have different currencies, you can select the currencies you may need to have. Hyperion creates an extra dimension with the name HSP_Rates in Essbase and currency calculation script which holds the conversion rates for those currencies. The application has one default currency and all other curriencies will be calculated based on the currency conversion rates values.

To Be continued...Will update it soon

Today Lets take a look at how Hyperion planning looks like and how to create an data entry form.
If you want to create data forms manually, you can do that.
Log on to Hyperion planning.

Navigate to the folder where you want to save your form.

above you will see a create button. click on create.

First tab will be gereral properties where you will type the form name.

After that the next column is Rows and columns.

Select your rows and columns from the select button after every row or column.
You will see a list and select from that.

Here we need to understand what are we selecting in our rows and columns. I did not do that before and hence adding that to make you understand the concepts.

There are six predefined dimensions available which are required to create any application-

1. Period and Year- Period dimension allows you to define your time periods like starting year and end year for the application. upto 100 calender years can be defined and once application is created you cannot delete the years or reduce them however you can add more years to it.
You also define the distribution of a year.

2. Scenario- It describes the type of plan you are creating like actual, budget or forecast.

3. Version- You may have a need to have different versions like Working and Final, best and worst case etc.

4. Entity- These are generally different regions, departments, cost centers etc of an organisation for which you want to create the plans.

5. Accounts- The account dimension defines the data to be collected from the entities. for example- Expense accounts, P&L accounts etc.

These dimensions are required to create any planning application.

Other than this there are 14 user defined dimensions like products, channels or customers which you can use in a planing application. There is a limit of 20 dimensions which a planning application can have.

will discuss other dimensions soon...

After you select them they will appear like this.

If you wish you can create POV (Point of Values) for your form, you can select any dimension which is not a part of row or column dimension. POV will basically allow you to select the dimension members from the list for example if you are creating a plan for best and worst scenario, you can select this in the POV and while entering the information in the data form you can select whether you want to enter the info for Best case or Worst case.

Just look at the options available in other options tab

Click on preview or finish. The form is ready and will look like this.

So its really simple and guided.

That's it for today. Lets see if I get some more things on this and i will post them as well.

Manohar Rana

Thursday, September 4, 2008

Hyperion Interactive Reporting Disadvantages


You may want to read my earlier post Qlikview Vs Others

You may also be interested in similar posts.
Hyperion Interactive Reporting Enhancements-Charts

Hyperion Interactive Reporting Enhancements- Live Charts

Hyperion Interactive Reporting Tutorial

Hyperion Interactive Reporting Disadvantages

There are some points which I found Interactive reporting lacks as compared to Qlikview.
This would help users who are evaluating IR and Qlikview.

1. To create a selection box (or LOV) and link it to report objects in the dashboard, we need to write some script and cannot be done with drag and drop features. You can do this using Dashboard studio using wizards but the way you do it in Qlikview is just amaging and even users without having any knowledge of the tool can do it in few seconds. Though in version 11, Oracle has introduced some charts and slider which can be interlinked to see the data changing but needs lots of improvements. Hyperion Web Analysis has this feature present.

2. The way we can do what-if analysis in qlikview with the help of slider and variables is really quick and effective. Nothing of that sort can be done in Interactive Reporting. But the concept is IR is not made for that purpose. The way to do this is Essbase to which we cannot just compare Qlikview.

3. To create pivot reports with member showing(+) sign to further drill down and (-) sign representing the last member is not possible in IR. Qlikview can do this very well.

Manohar Rana

Wednesday, September 3, 2008

Hyperion Essbase Interview Questions

You may also like to read Essbase Interview Questions.

I have got this request from someone to post some FAQ's on Hyperion Essbase. Please do not just rely on these questions only. I recommend you read the Essbase documentation and get some hands on to understand the basics. The questions below shold guide you to narrow down your search for the topics you should focus on.

Que 1: What are the two storage options available in Essbase and what are the differences?
Ans: ASO( Aggregate Storage Option) - Used for 1. If we have large number of dimensions(generally more than 10) 2. Most of the dimensions are sparse 3. We cannot write back in ASO. I heard recently that ASO will also have the writeback capability. I don't remember.
BSO (Block Storage Option)- 1. Dimensions are less and dense(recommended values for no of dim are between 4 and 8) . 2. We can write back hence much suited for planning applications.
Financial applications are generally dense structures and normal analytical cubes are sparse. Here we also need to understand what are these dense and sparse members. When the intersections or cells of two dimensions mostly contains a value it is dense. Say for example we have two dimensions period(Month or quarter) and region and we are calculation the sales amount. Generally maximum regions(countries, cities) will have some sales value for a particular month or quarter. Hence we can say that our period dimensions would be dense. But now instead of period, consider another dimension products. Now there are some products which are sold in only some particular regions hence for them there will be no values in other regions or will have very low percentage of data values hence the structure will become sparse.
Now the question arises what is the use of calling them dense or sparse. What difference does it make. Do some research and try to find the answer.

Que 2: Can we build dimensions directly from data sources without using rule files.
Ans: No.

Que 3: When do we generally use build rules files?
Ans: To automate the process of creating dimensions with thousands of members.

Que 4: What are the three primary build methods for building dimensions?
Ans: 1. Generation references
2. level references
3. Parent-Child references.

Que 5: In what case we can define generation 1 to a feild in generation build method.
Ans: We cannot define that as Generation 1 is not valid.

Que 6: Suppose we have assigned Generation 2 and Generation 4 as of now and think of adding generation 3 later some time. Can we build the dimension.
Ans: No. If gen 2 and gen 4 exists, we must assign gen 3.

Que 7: Can we create more than 2 dimensions using only one build rule file.
Ans: Yes, we can do that but it is recommended to use seperate rule file for each dimension.

Que 8: What is UDA( user defined attributes). How are they different than Aliases.
Ans: UDA represents the class of the members. Aliases are just another names of the members. both are different and has different usage.

Que 9: Can we query a member for its UDA in a calculation script.
Ans: Yes. You can query a member for its UDA in a calculation script.

Que 10: How does UDA's impact database size?
Ans: Absolutely no impact as UDA's do not require additional storage space.

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

Que 12: How does Attribute dimensions and UDA's impact batch calculation performance?
Ans: UDA's- No Impact as they do not perform any inherent calculations.
Attribute dim- No Impact as they perform only dynamic calculations.

Que 13: How can we display UDA's in reports? How do they impact report report performance.
Ans: UDA's values are never displayed in the reports and hence do not impact report performance.

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

Que 15: While loading the data, you have applied both the selection criteria as well as rejection criteria to a same record. What will be the outcome.
Ans: Thw record will be rejected.

Que 16: How is data stored in the Essbase database?
Ans: Essbase is an file based database where the data is stored in PAG files of 2 GB each and grows sequentially.

Que 17: Can we have multiple databases in one single application?
Ans: Yes. But only one database per application is recommended.

Que 18: Can we have one ASO database and one BSO database in one single application. If yes, how and If No, why.
Ans 18. No. Because we define ASO or BSO option while creating the appication and not database. Hence if the application is ASO, the databases it contains will be that type only.

Que 19: What are the file extensions for an outline, rule file and a calc script.
Ans: .OTL, .RUL and .CSC

Que 20: What is the role of provider services.
Ans: To communicate between Essbase and Microsoft office tools.

Que 21: What is an alternative to create metaoutline, rule file and load data.
Answer: Integration services and in version 11, we have Essbase studio.

Que 22: Can we start and stop an application individually. How ca this be used to increase performance.
Ans: Yes. We can manage our server resources by starting only the applications which receive heavy user traffic. When an application is started, all associated atabases are brought to the memory.

Que 23: We have created an application as unicode mode. Can we change it later to non-unicode mode.
Ans: No.

Que 24: How can I migrate an application from my test environment to the production environment. Can I still do this if my test environment is a windows one and my production env is UNIX one. Also can I still do it if my test env and prod env versions are different.
Ans: Hyperion Administrative services console provides a migration utility to do this but onluy the application, database objects are migrated and no data is transferred. the answers to other two questions are Yes.

Que 25: A customer wants to run two instances of an Essbase server on a same machine to have both test env and Development env on the same server. Can he do that?
Ans: Yes. We can have multiple instances of an Essbase server on a single machine and there will be dfferent sets of windows services for all these instances.

Que: 26 suppose I have a dimension A with members B and C and I do not want B and C to roll up to A. how can I do this.
Ans: Using (~) exclude from consolidation operator.

Que 27: What does never consolidate operator(^) do?
Ans: It prevents members from being consolidate across any dimension.

Que 28: Can we have multiple metaoutlines based on one OLAP model in Integration services?
Ans: Yes

Que 29: Can we have an metaoutline based on two different OLAp models.
Ans: No.

Que 30: What is hybrid analysis?
Ans: Lower level members and associated data remains in relational database where as upper level members and associated data resides in Essbase database.

Que 31: Why top-down calculation less efficient than a bottom-up calculation?Being less efficient, why do we use them.
Ans: In the process it calculates more blocks than is necessary. Sometimes it is necessary to perform top-down calculation to get the correct calculation results.

Que 32: On what basis you will decide to invoke a serial or parellel calculation method.
Ans: If we have a single processor, we will use serial calculation but if we have multiple processors we can break the task into threads and make them run on different processors.

Que 33: What is block locking system?
Ans: Analytic services(or Essbase Services) locks the block and all other blocks which contain the childs of that block while calculating this block is block locking system.

Que 34: What are the types of partitioning options available in Essbase?
Ans: 1. Replicated partition. 2. Transparent partition 3. Linked partition.

Que 35: Dynamic calc decreases the retreival time and increases batch database calculation time. How true is the statement?
Ans: The statement should be just opposite. As dynamic calc members are calculated when requested, the retreival time should increase.

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

If you have more questions with answers, please post them so that all of us can benefit.

Also let me know your views and suggestions.

Manohar Rana

Hyperion Essbase Migration


Check out my recent post where I have shown how we can Migrate Essbase Applications from one server to another using file system.

For past few days, I was getting too many queries for upgradation and migration from lower versions to system 9.
1. Brio version 5 to Interactive reporting system 9- This is the most difficult and time consuming migration. Brio customers having versions lower than 6.5 needs to first migrate to atleast 6.5 and then they can migrate to system 9. The BQY documents containing features not supported may not migrate correctly or fully.
2. Hyperion Essbase customers will not have that many problems. Using migration utility they can migrate an Essbase application from lower version to higher version on different server or on different platform.
Customers who are using version less than will have to first come to this version and then they can upgrade to 9.3.1 or 11.1.

if you have any specific questions relating to migration, please post them.