Hyperion EPM System Installer Pre-configuration check failed – Hostname resolved to an IP address

Well, the good thing about errors in software is that it shows you the baling wire that is used to hold together our software. Hyperion EPM for example is now a bundle of Weblogic, JavaScript, Java and databases held together by a technology that was theoretically created for designing a fault tolerant system to handle a nuclear war. 

So, there I was trying to install Hyperion on my good old laptop when my install failed in the first step itself. Below are the snapshots of the install and where it failed.

 

The error is a pre-requisite check failure saying that “The hostname resolved to an IP address – x.x.x.x Please provide a host name”

Now, the error is good because of a few things. One, this is not my first Hyperion install and I never got this error before so learning opportunity rang out loud and clear.

Second, the error is generally a network error and this is something that is generally done by the networking guys and we just crack on with the Hyperion install. So, it reminded me that the next time I performed an install successfully, I should thank a faceless, unknown network guy for ensuring that my install went smoothly.

Now, the fix to ensure the error is as follows…

The hostname resolution is generally handled by a small file called hosts that is generally present at the following path:-

C:\Windows\System32\drivers\etc\

You just need to add an entry in the hosts file and give a name to the IP address as shown in the below screenshot.

So, if the error gave an IP address, say 10.25.24.205, I would create an entry in the hosts file that would look as follows:-

10.25.24.205 hyperionserver1

Once the hosts file is updated, all you need to do is save the hosts file and run the install again, and you will see the pre-requisite checker passing successfully as shown in the below snapshot.

Now generally this kind of error would not happen in the usual scenario since most of installs happen on well-managed server that will have the hostnames mapped to proper IP address. Another thing is that I was connected to a Wi-Fi network when I was running this install and it sure messed things up for me..

But, I managed to figure it out but more on this later…

Hierarchical Queries in Oracle – Bullet proofing your dimension builds in Essbase

In this blog, I would be talking about hierarchical queries in Oracle. Most of us, at some point or the other, might have used Oracle as a data source for building metadata. Now the most famous data extract format used for building dimension build rules file is always the parent-child format. Now suppose that I have a table that has metadata for thousands of rows. As a sanity check when building the rules file extract, I would want to ensure that my parent dimension member is built before any of its children dimension members are built. So the question now is how to achieve that…

I have created a table in Oracle called as “Period_metadata” whose structure is as shown in the below snapshot.

For sake of simplicity, it has three self-explanatory columns, Parent, Child and Alias.

Now I load the monthly values as shown in the below snapshot.

 

Once the monthly parent child references are inserted, I insert the quarterly parent child references as shown in the below snapshot.

The contents of the table is as shown in the below snapshot after the data inserts. As you can see, the monthly values come first and this is followed by the quarterly values.

I now want to write a query in such a way that my quarter values are selected first and this is followed by the monthly values… For doing this, I would be using the hierarchical query in Oracle.

Hierarchical queries are used for getting data in a hierarchical order (no surprises there…) Its output is similar to a tree structure and can be used as such…

The structure of the query is as shown in the below snapshot.

START with specifies the root node with which the query should start as a reference. In this case it is “Period”.

CONNECT BY specifies the relation between the parent rows and the child rows of the hierarchy.

PRIOR is a unary operator that evaluates immediately following expression for the parent row of the current row in a hierarchical query…

The query can be read as follows:-

Select those rows where the value of the parent first comes in the child column and start the selection from rows where parent=Period

The output of the query is as shown in the below snapshots.

The extract in a parent child format is as shown in the below snapshot.

Now, let us mess around a little with the hierarchical query…

If instead of using CHILD=PARENT, if I use the query the other way round as shown in the below example.

The next query shows the use of the Level pseudocolumn. For each row returned in a hierarchical query, level will return 1 for ROOT row, 2 for child of root and so on.

The concept of level is not to be confused with the Essbase concept of Level. In hierarchical query, Level is logically equivalent to the “Generation concept” in Essbase.

Output of the query with level is as shown in the below snapshot.

A cleaner output, where the data is ordered by level is as shown in the below snapshots.

Formatted Hyperion Essbase Level-0 extract - The sorcery of report scripts

In one of my previous blogs, I had talked about the structure of level 0 data format in ASO and BSO. While these are easy to export, it takes some help to get it in a neat report structure. Also, in another one of my blogs, I had spoken how to validate files against the data loaded in the system. Well, as it often happens in life, two different ideas came together and I got an idea on how to format level-0 data for export in a relational table format… This comes with a statutory disclosure as always… There is a reason why the level-0 data export is fast… It does not care if you can read the export or not…All that matters is that Essbase is able to write it quick and Essbase is able to read it quick… Now if you want a system that formats the data in a presentable format, you will have to compromise a bit on the performance… Report scripts are slower because each of them undergoes through a set of stages…If you try this on a massive system, chances are the efforts might not be worth it and the report may run forever, metaphorically speaking…

Below is the sample outline of the BSOSAMP application that I love to work on… The application has four dimensions, namely, Account, Period, Entity and Year.

 

 

Below is the step 1 of the report script that I have created… It suppresses missing rows and will produce a non-indented tab delimited file.

I want Account, Entity, Year and Period in a row…which is specified in the <ROW section.

I have chosen four level-0 members, one from each dimension… This is as follows:-

• ·         Representing Account I have “Sales” (I imagine I am introducing Miss Universe contestants when I am saying this)
• ·         “Jan” from Period dimension
• ·         “E1” from Entity dimension
• ·         “FY16” from the Year dimension.

Any Essbase cube will usually have a level-0 placeholder member (Planning folks will immediately relate BegBalance here). You can very well use this…

Before each of these member I use a member selection function <ONSAMELEVELAS as shown in the below snapshot…

 

 

The report script basically now reads as follows:-

Give me the non-missing data in a tab delimited format with all the members of Account dimension which are at a same level as Sales (which in turn is level 0) across members of Period dimension which are at same level as Jan across Level-0 entity dimension members across level-0 year dimension members… 

So by just some manipulations I now have the extract in a neat report format as shown in the below snapshot…

On a side, many a times you will have aggregate storage cubes that store the granular data and you can have a summary cube that has the high level members for dashboarding…If you are not in a mood for doing a partition to get data through, you can always use a structure similar like the above report script, replace the <ONSAMELEVELAS with <ONSAMEGENAS and voila, you have a summary extract…