Managing Virtual Machines

Many developers that work with Hyperion products, as well as many any other software product, use virtual machines. Virtual machines are an easy way to create multiple environments for testing and developing multiple product versions

The 3 main applications to create and use virtual machines are

Microsoft’s VirtualPC (version 2007 can be downloaded here)
Sun’s VirtualBox
VMWare

I have used all 3. Opinions can be found that promote all 3. Many IT professionals prefer VMWare. I have found it to be a little cumbersome to use, and find sharing virtual machines to be a frustrating experience. I am not a stereotypical IT professional, but rather a business person with an aptitide for technology. That said, I prefer VirtualBox. For me, VirtualBox is easier to install and manage the virtual machines. It is easy to move virtual hard drives to another computer, simple to duplicate a virtual hard drive and allows users to take snapshots, which allows, for lack of a better explanation, a huge undo if required.

Through my VirtualBox travels, I have found the following knowledge to be very valuable. Here are some HOW TOs that might be useful if you decide to use Sun’s VirtualBox.

How to reduce the size of a virtual machine
The use of virtual machines (just like any system) cause fragmentation and the size of the virtual hard drive to grow, sometimes substantially. Managing the size of the virtual machine is relatively easy, and is not time consuming. It involves 3 actions (defrag, delete free space, and compact the virtual hard drive). Here is one way to accomplish reducing the size of your virtual machine / virtual hard drive.

Open the virtual machine that needs compressed
Download sDelete, and extract the sDelete.exe to c:\
NOT REQUIRED: Download and install Smart Defrag – this is a free disk defrag tool that I have found to be more effective than the one that comes with the Windows OS.
Use the disk defrag tool that comes with Windows or the one above, and defragment the hard drive.
Go to Start/Run, and enter “c:\sdelete.exe -c”
Shut Down the OS on the virtual machine
On the host computer, open a command window (Start/Run, and enter cmd)
If VirtualBox was installed in the default location, change the directory to the VirtualBox directory by entering “cd C:\Program Files\Sun\xVM VirtualBox\”
Enter “VBoxManage modifyvdi HardDrivePathAndName compact” where HardDrivePathAndName is the full path to the hard drive the virtual machine is using.

If the virtual machine/hard drive has free space, this process will find it and reduce the overall size of your virtual machine/hard drive.

How to duplicating, or clone, a hard drive
Often times there is a need to replicate a virtual machine on the same host environment. Copying the file doesn’t do the trick, as every virtual machine’s hard disk must have a unique key. VirtualBox comes with a tool to duplicate the hard drive and assign it a new key. To accompolish, follow the following steps.

Reduce the hard drive size (see previous topic)
On the host computer, open a command window (Start/Run, and enter cmd)
If VirtualBox was installed in the default location, change the directory to the VirtualBox directory by entering “cd C:\Program Files\Sun\xVM VirtualBox\”
Enter “VBoxManage clonevdi Source Destination” where Source is the full path to the hard drive the virtual machine to duplicate and Destination is the location of the new virtual machine hard drive.
Open VirtualBox and create a new virtual machine that points to the newly created hard drive in the previous step.

Comparing current periods to prior periods

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;

The Good, The Bad, and The Dirty Calc

Executing calculations that only run on blocks that have changed is a great feature in Essbase. It enables administrators to calculate the database in a fraction of the time and is referred to as calculating “dirty” blocks, or an update calc. This is awesome. “Why shouldn’t I use it all the time?” you might ask. Understanding how the Essbase calc engine works is critical to answering this question.

The Essbase calc engine calculates each block in a specific order (see figure 1). The first block it calculates is the first level 0 block of the first sparse dimension. It then traverses to higher levels and moves through the dimension from top to bottom until the entire dimension is consolidated.

When a level 0 block is changed, it and all of its parents, are tagged as dirty (it needs to be calculated again). When a calculation is executed on just dirty blocks, the process is the same except that it skips all the “clean” blocks. Once the block is calculated the dirty tag is changed to clean. So far, so good!

Revisit figure 1, which is a very simple example. It shows a very simple hierarchy with the order in which the blocks are calculated, 1 through 10.

Figure 2 shows what happens if New York is updated. Blocks 5, 6, and 10 are tagged as dirty. The next calculation, if set to calculate only the dirty blocks, would only calculate blocks 5, 6, and 10, in that order.

Here is where things get a little ugly. When an application has write access, as a planning or forecasting application would, it is very possible that users are updating data DURING the calculation process. The timing of these events is critical to understand why calculating only dirty blocks can cause inconsistencies.

When a calculation has started, it identifies which blocks need calculated (5, 6, and 10 in this example). Immediately after that, it starts calculating block 5. If Texas is updated while block 5 is being calculated, what happens?

Figure 3 shows the state of the clean/dirty blocks when the calculation is finished with block 5. It is exactly what you might expect at this point. Blocks 6 and 10 are still dirty. The update of Texas caused Blocks 1, 3, and 10 to be tagged dirty.

This is the critical piece. Keep in mind how the calculation engine works. It will continue to calculate blocks 6 and 10. Also note that the calculation running does NOT reevaluate what needs calculated. It will not calculate blocks 1 and 3.

Figure 4 shows the state of the blocks after the calculation finishes. Only blocks 1 and 3 are dirty at this point because 10 was included in the calculation.

When the next calculation is executed, the only blocks that are dirty are 1 and 3. Can you see the problem now? After blocks 1 and 3 are calculated, is block 10 accurate? Does U.S. equal the total of South, East, and West? Unfortunately, it does not.

One could argue that it will get updated the next time data is changed. In a very simple example with 3 levels, this would probably correct itself rather quickly, if the problem happened at all. In a more realistic example where a company has 10 or 20 levels in their organization dimension, the problem is likely to be a reoccurring problem and may not be corrected until a full calculation is executed. In most situations, it is not acceptable to have a database where it consolidates correctly only some of the time without any warning that it is not accurate. Reporting can be incorrect, and bad management decisions can result.

Using the dirty calc feature is a great tool to have in your arsenal. It can save hours of processing time. It can make you look like a genius. Without understanding its pitfalls, it can be the source of countless wasted hours trying to figure out why a cube isn’t consolidating correctly. A worst case scenario is when a cost center manager updated their budget, it never gets consolidated correctly, and the problem isn’t identified until it is too late.

Hyperion Essbase Product Overview

Overview

Essbase is widely considered to be the industry leading OLAP (On-Line Analytical Processing) server. Built on a real-time analytic infrastructure, Essbase provides BI and EPM users the ability to quickly dissect and analyze data through an intuitive interface. Essbase end-users have multiple options for interacting with data including Microsoft Excel, Hyperion Financial Reporting and Hyperion Web Analysis. By bringing Essbase together with other Oracle applications, users will find broad support for demanding business requirements. Breaking down large datasets, working with best-case/worst-case scenarios, forecasting multi-line expenses, and reporting variance analysis is now at your fingertips. Essbase ultimately allows end-users to interact and analyze real-time data through an integrated, rapid-response, and secure environment.

Product History

Arbor Software Corporation, an OLAP software company that designed and marketed multidimensional database software for planning and analysis, developed Essbase (Extended Spreadsheet Database), first branded “Arbor Essbase” in 1992. This multidimensional database product was originally developed to address the scalability problems associated with spreadsheets like Microsoft Excel and Lotus 1-2-3.

In 1998, Hyperion Software Corporation, a company with packaged analytic applications specializing in financials, and Arbor, merged to become Hyperion Solutions Corporation. Their first assignment after the merger was obvious, integrating Essbase with Hyperion Pillar, Hyperion Enterprise, and Hyperion Reporting.

After the merger, Essbase became the backbone to a full suite of applications for planning & budgeting, financial consolidations, scorecards, reporting, and more. Hyperion Solutions become a leader in the world of performance management software with Essbase leading the way.