If you are a fan of the HSGetValue and HSSetValue, you probably are using a private connection. As you know, anybody that uses the template has to either change the connection string to their own predefined private connection, or set up a private connection with the same name. When dealing with inexperience users, both methods can be problematic.

You may be surprised to know that the Get and Set Value functions can use a shared connection. Read more

 

I recently learned the importance of encrypted passwords in batch files. Without a password file, the scripts will still run, but the user is prompted to input a password in the command prompt after initialization. Encrypted passwords allow for the automation of these scripts. Shout out to Sumit Deo for his patience in guiding me through the initial process and helping me with my batch scripting skills along the way. Read more

 

This post looks at the “Allow Expansion” functionality in Financial Reporting Studio. “Allow Expansion” is a great feature, as it gives the user more flexibility when running and displaying reports. However, it can also require more setup work as it makes the report more complex. Two situations that I encountered recently:

  1. Adding a custom heading using conditional formatting
  2. Calculating formulas on expanded members Read more
 

OK, I have opened up a ticket with Oracle.  Now, they want my logs.  Frustration sets in, and I wonder…how long is it going to take to figure out where they all are and which ones are important?  If I miss one, my resolution will surely get delayed. This is all I need!

In newer versions of the Hyperion environment, Oracle has made some huge improvements. Read more

 

All developers understand the power of using objects during development activities, a concept that can be leveraged in the development of Oracle/Hyperion Financial Reports. Utilizing saved objects allows the development team to deliver a product in less time and provides the ability to quickly react to future report modifications. The information below (1) provides common saved object examples and (2) displays how saved objects are created and used.

Read more

 

 

We will always be asked to do more with less. Finance is asked to produce more and better analytics with less people. Sales people are asked to produce more in a weakening economy with less marketing dollars, and yes, groups that manage Essbase environments are asked to produce and manage more data/applications with shrinking resources.

Back in the Day

In a prior life, I used to manage a group responsible for managing the Essbase environment used to produce all the reporting for the group. It generated about 70% of the revenue for Bank One (now Chase). We delivered all the reporting, budgeting, and forecasting applications. It included nearly 2 TB of data (pre ASO) on four servers that included more than 50 databases. All the typical technologies were employed. A large number of filters existed to maintain security. Many of the applications were linked together with several types of partitions. Data was loaded daily, weekly, and monthly. SQL Server was used for all the ETL processes, and we completed the development and performed all the maintenance with four people.

The only way the group could be effective in developing and enhancing applications, was to eliminate our effort spent on typical production activities. With the number of applications and the frequency they were updated (daily, weekly, or monthly), communicating this information to the more than 250 users was also a large time commitment.

The Solution

Read more

 

Think about how many emails you delete without reading. Your inbox gets cluttered with specials from your favorite stores that you requested to be sent.  You just but didn’t realize a new email would show up every other day. Everyone has the friend who sends emails that he or she thinks is so funny that aren’t. There’s also the person who seems to update his status multiple times a day and “checks in” at the grocery store, work, dinner, etc. Sometimes it’s your system administrator sending you updates about every aspect of the system that you delete. Then you think, Wait, I am the administrator. My emails are always read!  Right? Read more

 

There are times when planning and forecasting databases grow for apparently no reason at all. The static data (YTD actuals) that is loaded hasn’t changed and the users say they aren’t doing anything different.

If you load budgets or forecasts to Essbase, you probably do what I’m about to tell you. If you are a systems administrator and have never seen how finance does a budget or forecast, this might be an education.

The culprit? Read more

 

When developing it’s always best to avoid “recreating the wheel” and the same concept applies when building reports in Hyperion Financial Reporting. An important step in the design phase centers on report row and column sets; these are simply the groups of members which display in the rows and columns of each report. Any reports that “share” groups of members, whether in rows or columns, provide an excellent opportunity for creating Hyperion Row and/or Column Templates. These templates can then be referenced in additional reports, resulting in decreased development effort and less maintenance.

Benefits: When needing to create multiple reports which have similar or identical row and column sets, the user can save time by creating one standardized row and column set, saving it as an object in the repository, and reusing it for multiple reports.  The user also has the ability to modify a row/column template at any point and resave it into the repository which automatically updates all grids linked to the template.   Read more

 

 

Almost every planning or forecasting application will have some type of allocation based on a driver or rate that is loaded at a global level.  Sometimes these rates are a textbook example of moving data from one department to another based on a driver, and sometimes they are far more complicated. Many times, whether it is an allocation, or a calculation, rates are entered (or loaded) at a higher level than the data it is being applied to.

A very simple example of this would be a tax rate.  In most situations, the tax rate is loaded globally and applied to all the departments and business units (as well as level 0 members of the other dimensions).  It may be loaded to “No Department”, “No Business Unit”, and a generic member in the other custom dimensions that exist.

If a user needs the tax rate, in the example above, they have to pull “No Department” and “No Business Unit.”  Typically, users don’t want to take different members in the dimension to get a rate that corresponds to the data (Total Department for taxes, and No Department for the rate).  They want to see the tax rate at Total Department, Total Business Unit, and everywhere in-between.

There are a number of ways to improve the experience for the user.  An effective solution is to have two members for each rate.  One is stored and one is dynamic.  There is no adverse effect on the number of blocks, or the block size.  The input members can be grouped in a hierarchy that is rarely accessed, and the dynamic member can be housed in a statistics hierarchy.

Using tax rate in the example above, create a “Tax Rate Input” member.  Add this to a hierarchy called “Rate Input Members”.  Any time data is loaded for the tax rate; it is loaded to Tax Rate Input, No Department, No Business Unit, etc.  Under the statistics/memo hierarchy, create a dynamic member called “Tax Rate”.  “Tax Rate” would be the member referenced in reports.  The formula for this includes a cross-dimensional reference to the “Tax Rate Input” member, and would look something like this.

“No Department”->”No Business Unit”->”Tax Rate Input”;

When a user retrieves “Tax Rate”, it always returns the rate that is loaded to “No Department,” “No Business Unit,” and “Tax Rate Input,” no matter what department or business unit the report is set to.  The effort involved in creating reports in Financial Reporting or Smart View now becomes easier!

There is an added bonus for the system administrators.  Any calculation that uses the rate (you know, the ones with multi-line cross-dimensional references to the rates) is a whole lot easier to write, and a whole lot easier to read because the cross-dimensional references no longer exist.

Before you move the application to production, make sure to set the input rates consolidation method to “Never.”  Don’t expect this change to make great improvements in performance, but it will cause the aggregations to ignore these members when consolidating the hierarchies.  A more important benefit is that users won’t be confused if they ever do look at the input rates at a rolled up level.  The ONLY time they would see the rate would be at level 0, and would be an accurate reflection of the rate.

Note:  It is recommended to create member names without spaces.  The examples above ignored this rule in an effort to create an article that is more readable.