Comparing the current period to the prior period is relatively easy to accomplish in Essbase, and is often required when creating a Cash Flow hierarchy.  Assume the following scenario.

An outline exists that includes a Year and Time_Period dimension. The Year dimension includes 2007, 2008, and 2009.  The Time_Period dimension includes Full Year, Quarter 1 through 4, and all 12 months.  The dimension type for the Time_Period dimension has to be set to Time.  A dimension named COA (chart of accounts) holds the general ledger account structure. Below is an example of the Time_Period dimension

To enable a dynamic approach to solving this problem and minimizing the maintenance required as new years are added, an understanding of the following two functions is required.

@PRIOR
The PRIOR function provides a way to compare a member outside of the Time_Period dimension in multiple Time_Period members.  For example, accounts for July could be compared to June, or Quarter 2 could be compared to Quarter 1.  There are two parameters that this function accepts.  The first is the member to get the prior value for.  The second is the number of periods you want to shift the comparison.  If @PRIOR(“Asset123”,1) is used, it would provide the value for the period previous to what you had selected in the Time_Period member.  So, if June was selected, it would provide the value for May. If the formula was @PRIOR(“Asset123”,2) and June was selected, the result would be the value for April (2 periods back).  The function uses members at the same generation, so @PRIOR(“Asset123”,1) would provide the difference between Qtr2 and Qtr1 if Qtr2 was selected.

So, what happens if January or Qtr1 is selected?  There is no previous member for these.  This is where the second function comes in to play.

@MDSHIFT
MDSHIFT is similar to PRIOR, but it lets the calculation reference members across dimensions.  Where PRIOR only allows references on one dimension, MDSHIFT allows references to move across multiple dimensions.  If the user expects to see the different between Jan and Dec of the prior year or Qtr1 to Qtr4 of the prior year, MDSHIFT enables that to happen without hard coding the script.  Again, the goal is to have a script that doesn’t need to be maintained.

MDSHIFT accepts a set of parameters.  If your shift needs to occur along one dimension, it requires one set of parameters.  If your shift needs to occur along more than one dimension, it will accept multiple sets.  The function’s first parameter is the member you are evaluating, just like the PRIOR function.  The next set of parameters is what can exist multiple times if you are shifting along multiple dimensions.  The set consists of three parameters, of which the first two are required.  The first of the set is the number of positions to shift.  The second is the dimension to shift on.  The third is a range of member to use to shift along.  If this is left blank, Essbase uses level 0 members. 

To get the prior value for Jan, or Dec of the previous year, it would be MDSHIFT("Asset123",-1,"Year",,11,"Time_Period",).  The first parameter is the member to evaluate.  The next two parameters are used to reference the previous member (-1) in the Year dimension.  Since the Year dimension members are level 0, the fourth parameter is not required.  The next series, or set, references Dec (+11, or move forward 11 from Jan) of the Time_Period dimension.  The last parameter is not required since we only want to reference level 0 members again.

Putting it all together
If we put these two functions together with a basic if/then/else statement, we get a dynamic formula that won’t need to be updated as we progress through time.  It would look something like this:

If(@ISMBR("Jan"))
     /* if Jan, then we have to compare Jan in the current year to Dec [shift +11] in the prior year
         [shift -1]  */
     "Asset123" - @MDSHIFT("Asset123",-1,"Year",,11,"Time_Period",);
ELSEIF(@ISMBR("Qtr1"))
     /* if Qtr1, then we have to compare Qtr1 in the current year to Qtr4 [shift +3] in the prior year
         [shift -1]
         The last parameter includes a range since we are not using level 0, which is the default  */
     " Asset123" - @MDSHIFT("Asset123",-1,"Year",,3,"Time_Period",("Qtr1","Qtr2","Qtr3","Qtr4"));
ELSEIF(@ISMBR("YearTotal"))
     /* if Year, then we have to compare to last year [shift -1] */
     " Asset123" - @MDSHIFT("Asset123",-1,"Year",);
ELSE
     /* all other members, which would include Feb through Dec */
     " Asset123" - @PRIOR("Asset123",1);
ENDIF;

Hyperion Enterprise Performance Management System Installation and Configuration Guide Release 11.1.1.2 (252 Pages) and Oracle Hyperion Enterprise Performance Management System Manual Deployment Guide Release 11.1.1.2 (160 Pages) are a wealth of knowledge.  I know because I have read a great deal of it and keep them quite close during each engagement.  Most of what you need to know is available in the documentation and a good deal of additional knowledge can be found on the support forums.  I do not want to rehash the specifics of the installation and configuration.  There are just too many variables to cover everything without reposting most of the already prepared material.  What I hope to provide here is just a primer on architecture considerations and general installation activities with this release.

The Hyperion architecture establishment and installation activities in our organization cover the following five areas.

1. Defining an Architecture – Work with the client to define the hardware, software, and the distribution of Hyperion components
2. Provide Pre-Installation Requirements – Provide the client with a detailed list of activities prior to the installation
3. Installation – Running the installation and configuration utilities
4. Validation – Perform all functional activities necessary to validate the environment readiness
5. Documentation – Provide the client with all the details of the environment as it is configured.

In this post, I will go through step 1 that you, as the Hyperion architect, should deliver.  Steps 2-5 will be available in the coming weeks.  For the sake of simplicity I will be using the example of a common installation, primarily Hyperion Planning, Hyperion Financial Management (HFM), and the core BI applications.

Defining the architecture is somewhat difficult, especially if you do not have a good understanding of your organizations’ usage patterns or platform preferences.  Below are the core questions that need to be answered to define the necessary architecture.

How many servers will be needed?

The number of servers depends on a lot of things.  Below is a sample of some of the questions I typically ask when recommending the server infrastructure.

• How many users do you have?
• Will you be leveraging an existing Relational Database Platform?
• What products did you purchase?
• What products do your users use most frequently?
• Will you be using loadbalancing?

Let’s assume the answer is 100 users, the client has an existing Oracle database I can leverage, the client purchased HFM and Planning, and the client uses a lot of Financial Reports.

With these assumptions, you will want to make sure the Workspace and Reporting platforms are a decent size and the reporting application services reside on a separate server from the application servers.  In addition, since you are going to leverage an existing relational database, you can plan on having a stand-alone data tier for Planning and Essbase.  Just to further simplify the matter, let’s assume there is no load balancing and clustering.  For our example discussion, I will assume 3 servers will be included in the architecture.

What would be the system specifications and Operating System?

Once again, the answer to this question can vary widely based on the answers below.

• What operating systems does your organization support?
• How long do you intend to use this hardware?
• Will you be using virtual machines?
• Will you be using shared or local disks?
• How much data do you have now and what does your growth look like?

Keep in mind that HFM requires a Windows Server so I will need at least one of those.  It may be simpler from an administrative perspective to have a homogeneous OS infrastructure but it is not necessary.  If you have no preference, this is what I would typically go with.  If you have a propensity for some flavor of UNIX, then I would go that route where applicable.  Many organizations keep hardware in place for up to 3 years.  With that in mind, I think the best approach is to buy the most robust and powerful hardware you can afford.  The cost of migrating in 24 months will often be greater than the hardware spend.  VMware is quickly growing in popularity and I see deployments in virtual environments quite often with success.  Although Essbase does not require a physical server, I recommend that it not reside on virtual machines due to higher disk I/O.  It is also the application that is expecting the most growth.

What Web Application Server should we use?  Should it be Oracle Application Server 10g, WebSphere, WebLogic, or the Embedded Java container (Apache Tomcat 5.5.17)?

• What does your organization support?
• Do you have a license for anything?
• Will you need clustering?

First, some comments in favor of the Apache Tomcat server.  It is free and Hyperion does do some development on that platform.  So far, I have found it to be stable in most situations.  If your organization has a Web Application group, it may not be standardized on a version that is required by Hyperion.  In these instances, it may make sense to deploy Tomcat as opposed to a version of WebLogic or WebSphere that your organization does not support.  Additionally, if the organization is upgrading its application server farm in the future, Hyperion may be an outlier.

Now for the downside, there isn’t really any help desk you can call for Tomcat outside of Hyperion.  Unless your organization has a few other Tomcat servers, there is likely to be a learning curve in the administration and support.  Then, there is always the question of scalability.  Clustering with Tomcat is quite a bit more complex.  Our common recommendation on application servers is that you should use what you know.  If you do not know anything about application server administration and do not have a huge user base, Tomcat is probably your best choice due to its ease of installation.

One rule of thumb is that you can expect a single Tomcat instance to handle 25-30 concurrent threads comfortably.  The practical maximum is 50 per instance.  If you are using other sorts of Java servers, it is a good idea to review with those administrators their experience with free servers.  In a development environment, one could benchmark how Hyperion EAR and WAR files behave in comparison to those of other Java apps.

With all these assumptions, I deliver a Viso diagram detailing which components go on which servers with general recommendations for server sizing and operating systems. 

In our 2^nd installment, I will review the pre-installation activates that need to occur before the installation begins.