Planning V11 Drill back

Today I am going to look at the new drill back feature from Planning to FDQM (Financial Data Quality Management, rolls off the tongue). In my experience of FDM is that it was a product especially angled towards HFM, it used to be known as Upstream until Hyperion bought the company in 2006. If FDM is going to be used just for planning then you probably won’t use a lot of the functionality, I can see being more tightly integrated with Planning in the future though, the start of this the ability to drill back from planning to underlying data which has been loaded into FDM.

In previous versions it has had the ability to import an essbase adaptor, which was a cumbersome manual process so it will be good to see if that has been improved, the last version did not have inbuilt functionality to use with HSS though with a slight configuration change you could getting it talking with OpenLdap. It did feel like a product that had not yet been melded into the System 9 look and feel.

FDM has always been in the Microsoft corner of design primarily using the .Net framework, VB components and running on IIS so pretty much a windows based application, it also requires a copy of excel on the server machine which I find to be strange.

In quick summary FDM provides the ability to import excel files with various levels of mappings or manually configure them, load data driven excel templates using import formats and then apply validation to the loaded data against the mappings. The process from importing the data through to loading into essbase is split into 4 stages, Import, Validate, Export and Check

The export stage loads the validated data into the essbase cube; it basically creates a text data file and uses a load rule to fire it into essbase, you have to be careful though because if you choose merge when loading into essbase it uses a predefined calc script to clear the data which can end up clearing more than you bargained for, the script can be changed but you do need a little bit of vb scripting knowledge.

I am really just going to cover off creating a FDM application and get it hooked up to a planning (essbase) cube for this I going to stick using the trusty sample planning app.

One point to mention though when setting up FDM is that in this release it can interface with HSS


In case you are using FDQM System 9 and want to know how to link up with HSS (openLdap) then you will need firstly edit an OpenLdap configuration line this is because FDQM uses LdapV2 and HSS is using LdapV3.

Browse to the following location and edit the slapd.conf file:
\Hyperion\SharedServices\9.2\OpenLDAP\slapd.conf
In this file, simply add the line (without the quotes) near the top under the includes in the file:
"allow bind_v2"
Restart the Hyperion Shared Services OpenLDAP Windows service.


In the authentication providers configuration choose LDAP


Enter the following details



And that’s basically it.

Anyway back to Version 11, you can create a new application from the web front end or the workbench client. I am going to use the workbench because you require it to set up the essbase adaptor.



A new feature is that you set up against a HSS application group; I just used the default as I have not created a specific group in HSS.

The database set up is where I hit my first problem as I am using SQL server express (I knew it would bite me) the default details were not accepted with a warning that I had not supplied a username/password. As it uses OLEDB I just changed database server connection to \SQLEXPRESS and that did the trick


You do not need to create a SQL database as FDQM will create one for you, just be sure the db account you use has sufficient rights.

Once you have created the application the next step is to import the essbase adaptor, the adaptor consists of an executable (upsES9XG4B.exe) and an xml file (ES9x-G4-B.xml) I placed them in the \Hyperion\products\FinancialDataQuality\SharedComponents directory.

File > Import > Select the xml




I selected both Integration Adapters and Scripts to be imported

You then need to register the adapter, File > Register Adapter


Sometimes you will need to update DCOM settings for the adaptor, I didn’t need to but it is covered in the installation documentation.

The adaptor it still the same as in System 9 and is even named “S9 Hyperion Essbase”

You can rename the adaptor alias to suite your implementation


A new piece of functionality is the integration console, in previous versions you have to manually go and configure each part of the adapter.

Tools > Integration Settings

The first part is to set up Machine Profile, as I am only using one machine the source and target are the same.

The username/password sections can be left blank as I am going to use a global logon shown below



Fill in all the connection details the application database

I kept all the default settings in the rest of the tabs for Integration Setup, under the Load/Check tab you can specify your own essbase load rule to use.

The final tab is the dimensions, this is where you map all your essbase dimensions to the adaptor, I have found this to be very HFM orientated even though it is an essbase adaptor, I remember it being totally confusing the first time I used it in System 9.

This is how I usually set it up, there are probably different ways but that has always worked out to be suitable, apologies if others see this as being incorrect.

As I am using the planning sample application I need to map each of the following dimensions.
HSP_Rates, Account, Period, Year, Entity, Segments, Currency, Scenario, Version

Select Account, You can use the dropdown to map it to a dimension or manually type it in the Foreign name field.

I entered a value for the No Data Value that basically maps anything against that member if it is not included in the data; I also entered an Alias and a source and target alias that is displayed when you import data.

I repeated the process for Entity that was mapped against entity, UD1 against HSP_RATES, UD2 against Segments, UD3 against Currency and UD4 against Version.
I left ICP alone, you can probably map it against a dimension but I also see it being a part of HFM as Intercompany Partners.


Under the Global section, I mapped Category to Scenario, Period to Period, Year to Year. I removed VALACCOUNT,VALENTITY and VALORGANISTATION as being active.

I have never found a reason to use most of the dimension properties under Global I would interested know if anybody has used them for a planning implementation.

Once this is completed you are finished with the workbench everything else can be achieved through the web.
The address for the web piece is http://machinename/HyperionFDM/

Ok, I am just going to briefly go over what I did so I could load in data otherwise I will be here forever.

Control Tables, these are your mappings for the Global area which was set up above, so in this instance Scenario and Year/Period.

MetaData > Control Tables


I went through and added 12 months of information, if you need to delete the default period then add a new period, click the date on the lower toolbar to open up the POV and change it the period you just created, now you can go back into Control Tables and remove it.

You can manually enter the Month and Year Targets or select Browse for Target, which connects to essbase and retrieves the members.


The process was repeated for Scenario (Categories) removing the default WLCat


As this is a quick exercise I am not going to define really any mappings, I just want everything to map to exactly to the member name in the load file, to do this I create a rule for each dimension.

The mapping functionality can get quite detailed depending on your underlying data and the planning application, luckily it does let you import mapping excel templates.


Once I had created a rule for each dimension I was ready to get some data together.

I quickly created a quick csv file, in the csv file you don’t need to be concerned about the scenario and dates as they are chosen within the FDM POV.

Before you can load in the data you need to set up an import format, this is so FDQM understands which field in the load file maps with which dimension

MetaData > Import Formats

Enter the import group details



Map each field in the file to the dimensions

MetaData > Locations

Select the import format that was just created.

Now onto the import, browse to the file and click import


If the section is successful the fish will turn orange, why a fish? Maybe from the days when it was Upstream, you can change it if the fish doesn’t do it for you.

Next stage Validate

No problems with the validate otherwise it would display which records need fixing.

Export, the first screen you are greeted with is the option to Replace or Merge.

As I said earlier this where you need to careful as when you choose replace it will run a calc script which clears out data from the essbase cube.

Once you click OK in the background it will create the data file (consol01.dat) to load into essbase, if there are any issues you will be prompted with an essbase type error message.


You can the find dat file in the outbox directory where you set up the path when you created the application.

If you look at the essbase application log you will see the clear

Clearing data from [Jan] partition with fixed members [Entity(E01_0); Scenario(Actual)]

As you can see it doesn’t fix on many members and totally ignores the year.
If you want to update the script that generates the clear calc script, go back into the workbench.

Open the LOAD script under Actions

You will need to update the section shown above.

Now we can move onto to looking at drill back, in planning you have to add three properties :- FDM enabled – true/false, FDM Adaptor key and the FDM application name.

Administration > Manage Properties


Once the additions have been applied planning needs to be restarted.

Open up a form that displays the data that has been loaded in through FDM, right click a cell and select “Drill Back to Source”


If you don’t right click at the exact right point then you get the usual right click menu from the browser

I can see this annoying some users.


On selecting the drill back to source a pop up windows appears with the original information that was loaded into FDM.

If there is more than one record making up the Amount you can drill into the amount, I loaded an extra record with 3000 as the amount then clicked on Amount to drill down


If any memos were attached to the original record then these can also be viewed


There is also a summary section that gives you the ability to break out the FDM process into more detail, acting as a more detailed logging facility.




There are over 20 different available dropdown selections; I am not sure how relevant they would be to the everyday user, definitely looks to be more admin related.

Once Drill Back has been enabled for the planning application the functionality exists on all cells in a form, it is not clever enough to know whether the data in the form was loaded into FDM.

So you can right click back on any cell, if you select a cell that has no association with FDM then you get the following.


So what is it actually doing when you activate drill back, I had a quick look and it seems to be opening up a new window calling a FDM .net page called IntersectionSummaryByLocation.aspx , a number of parameters are passed into the page, it can be found at \Hyperion\products\FinancialDataQuality\WebServerComponents\Website\AuthorizedPages
The parameters that are being passed across are : SSO Token, FDM App name, Adaptor Key, FDM target product, FDM app name and all the intersection dimensions and members.

An example being

http://epmversion11/HyperionFDM/AuthorizedPages/?SSO_TOKEN=
NbD9qzBZXx81TZp0GpUu4qjE%2bL98cU4xDXOp3ExETu%2fSQot5HW2L9Kn99UfxrFsqNBzO
FysFZc%2fv%0ayY1QG8ORAxSa%2fXebSgfEHkMHhBlCw%2b769LO5gv9L7JEPig0mkXLanP1k
CidEIfeey4E5KzlRfpl5%0aMloJrh9CUvh1S%2fPmAbvns1ApGeUE5ZWkLOO77PpUyv8iPYpTYn
UJbseog9ha0dREhp%2fC00yI88SK%0aJ3AmWzNm2rudhAeWaQ%3d%3d&FDMAppName=
FDQMDEMO&FDMadapterKey=Es9x-G4-B&FDMTargetProdID=EssBase
&FDMTargetAppName=PLANSAMP&Segments=BAS&Year=FY08&Period=Jan
&Currency=Local&Scenario=Actual&Version=Working&Account=509110&Entity=E01_0
&HSP_Rates=HSP_InputValue

So if you ever want to use it in an application you have developed then in theory once you have obtained a SSO Token you can call this page from anywhere by applying the correct parameters.

One thing to watch out for is if you are using Firefox 2, which is supported for Planning but it is not supported for FDM so you end up with the following if you use drill back.



Well that’s it done for today, I think the next thing I am going to look at is Lifecycle Management for Planning, until then…

Planning 11 new features

Ok, with everything installed and configured I can now go through some of the new features, version 11 seems to be packed with new additions so it is going to take some time to go through all the product set.

Today I am going to start looking at what new gifts Planning has been blessed with.

First step is to make sure I can still access it directly, I always have this worrying feeling that they are going remove this and make you have to use the workspace, which is not really a problem but I think it hampers any trouble shooting plus I am not in the mood to blow lots of my memory starting up workspace. I will be looking at workspace new features in the future.

As I have not created any applications yet I won’t be able to hit the planning start page, the way to do this is point to
http://machinename:8300/HyperionPlanning/AppWizard.jsp

Good news looks like this has not been changed, though the noticeable difference straight away is the colour scheme, the drab colours have been funked up a little with a new aqua look.



New feature number one the ability to manage data sources directly from the web, I think this means all functionality now is available from the web front end which is definitely good news.

I am going to set up the sample application; I have already created a SQL database to hold it.


You can also validate the connection to the SQL database and the essbase connection.


Another new feature you notice from the data source set up is the support for Unicode mode, I can imagine this is going to be beneficial for so many.



The creation of the application is exactly the same as 9.3, still no HSS project for planning though so you need to create one or just use the default one.

Once the application is created you log into it directly from \HyperionPlanning\

The sample is initialized from the menu


You will also notice that there are short key routes now for all menu options, so to enter cell Text it would be alt > E > T

Anyway to complete the sample application build that you need to do is go to Administration > Manage Database > Create which will create the essbase database and set up the outline. Once this is done just unzip the data file sampApp_data.zip which is in \Hyperion\products\Planning\bin\sampleapp, open EAS and select the Consol db and load the data in.

The toolbar has some new additions

^ version 11 toolbar


^ 9.3.1 toolbar

Add/Edit Document is a new feature, it gives you the ability to attach documents like excel, word and pdf, link to a document on the web or directly to a document on workspace

To use this functionality you need to able it on the form setup.



If you are going to add a link to a workspace document you have to access planning through workspace.



Once a document is attached then you get a tiny little icon in the corner of the cell to let you know.

To access the document just click the open document icon

Yet another little funky addition is the open in smartview, this will open the current form you are viewing in excel using smartview.

There is no need to set up any connections in excel it will just do all the work for you.

I do like this feature and I did notice that the connection manager has gone and been replaced by a data source manager but that is for another day.


Next new addition is Display Member formula on a data form; you can enable this in the form set up either on the column,rows, pov or page.


Once it has been enabled any members with formulas will have a small formula icon in the member cell on the form.


Clicking the formula icon will open a window with read only formula information.


Users can now show/hide rows or columns that have no data or zero values.


Just right click over a column or row member and select

If a member has been set up as date type and a cell on a form is double clicked then it will open up a calendar.


There have been a number of times I have been asked why is the cell not displaying as text or percentage even though it has been set on the member properties. The reason for this is that you need to use the evaluation order in the dimension management section in planning, say you have an account members which are percentage and you want to display it on the form then choose select Account first in the Evaluation Order



The default message that is shown when there are no data values available on a form can be changed, this is done in the form set up in the Other Options section




It is now possible to clear cell details without having to resort to going into the SQL tables.



You can clear Account Annotation, Support Details, Cell Text and Cell-Level Document down to member level.


There is a new job console that enables users see the current status of Business Rules, Clearing cell details and data copies.



If there has been an error you can find out further information about it.



This information is stored in a table HSP_JOB_STATUS in the planning application repository.



Administrators can set the time before the job type starts running in the background, useful for longish running business rules.
This is done by adding new properties under Administration > Manage Properties, for more information about all the different settings you can apply go to :-
http://download.oracle.com/docs/cd/E12825_01/epm.111/hp_admin/ch02s07s05.html


Administrators can now jump straight from a member on a form to the dimensions page, just right click over a member on the form





The access permissions interface has split out users and groups making it easier to manage access.


You can also set how many users/groups will be displayed on the access page. This can be achieved by going to File > Preferences > Display Options



You can also now set permissions at form folder level



There have been a few additions to the auditing but no improved functionality.
You can now record:- clear cell details, copy data and task lists



Attributes now support Boolean, date and numeric


They also support hierarchies


There are a number of other features I want to cover such as the new command line utilities, migrations using Lifecycle Management and the drill back to FDM but I am going to leave that to the next exciting instalment

Signing off…

Version 11 Installation

I was going to start with going through the new functionality of Planning but before we can get to that stage we need to go through the installation.

I am going to be brave and pretty much install the full suite of products on one windows 2003 server vmware and on my trusty macbook, funnily enough vmware for the mac is called fusion, is this the word of the moment?

Please note that this just my experience of the installation and configuration, I never profess to speak the gospel word, please correct me if I am wrong.

First point to make is the installation has totally changed, gone are the days of just being able to double click an executable, now you have to download the installer (463MB) and the three parts of foundation services (just under 2.2gb), apparently on every machine you need these core elements, it seems
a bit crazy to me that you need to have 2.6gb of files just to install something like essbase server, I am not sure if this is totally correct as I am just going off the documentation.
Even the client essbase software is all stuck together in a whopping 312MB file, I am just hoping it does get split out in the future.
Inside most of the zipped files there is an assembly.dat file, looking quickly into the file I don't see much of existence of executables so looks like we are going down a totally new route.

If you are going to download version 11 from edelivery be aware that is now under Oracle Enterprise Performance Management and not in the Hyperion section.

All documentation for version 11 can be found online at :- http://download.oracle.com/docs/cd/E12825_01/index.htm


Enough waffle, onto the install. Everything has been unzipped into one folder. (if you don't put it all into one folder you will receive some java errors on startup I noticed somebody on the forums had already experienced this)

I think one of the many complaints in the past is that the Hyperion installers had too many parts to them and now it has all been simplified into 8 easy steps.

1. Welcome - Displays if the machine has passed the pre-requisites and the resolved hostname it will use (but will it use it throughout configuration?
   
   
   
2. Choose install directory
   
   
3. Installation type – New by tier (client,web application,service) or individually, upgrade or re-install by tier/individually
   
   
4. Select the products to install
   
   
   
5. Upgrade homes (fresh install this is missed out)
   
   
6. Confirmation screen of the products you are going to install
   
   
7. Displays progression of install
   
   
8. Summary of installation
   

And that’s it, couldn’t be simpler, quite impressive.

I have had a quick look at the uninstaller and it is based on the same methodology as the installer, it doesn’t seem to clean up the registry so well, it did remove all the services though but can you call that a full uninstall?

I must admit it was the fastest install I have ever done albeit to one machine, I am sure in the near future I will be trying a multiple server install just to try and break it and aren’t you lucky you will get to be involved.
I did notice that Application builder has made a comeback I honestly thought that was going to disappear; I know Hyperion produced a 9.2 version but not one for 9.3, you can use it with 9.3 with a few minor changes. I did test Application builder and it’s pretty decent if you want to develop .net apps for Essbase. I have ended up going down the JAVA API route as I feel it is far more powerful, I have even been pushing myself to learn JAVA which I am sure there will be more about in future blogs.

Right onto the configuration, I am not going to dwell too much on this only to try and point out the differences.

I have always wondered if you can configure the products using SQL server express, it is not down as a supported but it is free and looks very capable of doing the job Hyperion requires which is not much in the relational world. It uses JDBC most of the time so I thought I would give it a go, the thing to note with SQL server express is that it doesn’t automatically run on port 1433 so changed it so it did so just for ease.

All the products look to be in the same area on the start menu, which is now called Oracle EPM System, the configuration utility is called EPM System Configurator.


The configurator look and feel is pretty much the same as in 9.x. It uses the same type of xml files for the configuration settings which are located in E:\Hyperion\common\config\9.5.0.0\product

(Notice the 9.5.0.0 you will see that a lot if you delve into the file structures, there must not have been enough time to change between it being renamed to 11, all just to add to the confusion)

I must admit I have tried to configure a few times because I have hit a few oddities. I do recommend reading up on the configuration sequence in the install documentation.

In the documentation it recommends if you are installing on one machine you should configure all products at once, now if you do that it only lets you use one database and puts everything into there, I am not a big fan of this mainly for backup and recovery reasons plus I am sad and like to see which tables the product actually uses, comes in handy for diagnostics.


This is a new feature and I just have a feeling it is used to try and simplify things, it does also have advanced options, which let you enter the JDBC connection string directly and overrides the setting in the panel.

I went down the route of individually configuring the products so I could use a separate database for each one. This is where I came across the first issue if you try and configure Essbase on its own using HSS security then when it tries to convert to external authentication it fails, it does the conversion automatically now through the configurator, it just basically runs a maxl script in the background. It does say that the configuration was successful though. (always check out \Hyperion\logs\config\to be sure)

I think I found that if you configure Essbase and EAS at the same time it doesn’t hit this issue or if you configure all Essbase items at the same time.

If you do try and configure Essbase more than once it will not remove the previous windows service and create a new one with the number incremented by one.

While we are on that note I have been warned that if you don’t include the Hyperion and Reporting Migration utility assembly then workspace will not install fully, nice feature. ( I think it is the Workspace services and Common Libraries it doesn’t install which are definitely needed if you want to use workspace)

The default application server now known as the Embedded Java Container automatic deploys Tomcat 5.5.17

Shared Services now uses port 28080 instead of port 58080 as default, I am not sure why they have changed this (maybe a conflict with external products?), everything else looks to use the same port as before.

There is a validate option now which goes through and checks whether the product is all correctly configured and generates a nice little web page with the output.


This can also be accessed from the start menu > EPM System Diagnostic

I don’t think there are many more differences with the configuration but it does feel like there are fewer screens to through now.

The foundation element now uses something known as the Shared Services registry which on first inspection looks like a few tables in the HSS database holding configuration details which is definitely a step in the right direction



All services have finally been prefixed with Hyperion, Integration Services being the last one that needed updating.


Notable additions are Hyperion Annotation Server, Hyperion CALC Manager, Hyperion EPM Architect - .Net JNI Bridge.
BI+ Core services are now known as Hyperion Workspace – Agent Service

Logs – throughout versions in System 9 there has been a stepped change to try and put all logs files together which seems pretty logical. In 11 there are a number of extra product logs appeared in \Hyperion\Logs\ , the most noticeable is that Essbase has moved all its logs, it looks like there is no extra logging for planning, I suppose the auditing is considered to be the logging functionality.

There is \Logs\Services\ which logs all the web server activity for each product that is set up as a service, this is useful as you use to have to run the application server in the foreground or add extra information in the registry, had a quick look in the registry and that is how it has been done. I have always found this is the best method to troubleshoot planning.

I also noticed that there OPatch (Oracle patching utility) in the Hyperion directory so hopefully in the future patches will be much easier to apply.

One thing I have noticed though I am not sure if it is just my vm image is that if I don't shut down all the hyperion services then the image takes around 10 mins to shut down, not sure which service it is which is causing the problem.

I think that pretty much concludes the installation and configuration; everything looks to be up and running and even working with SQL server express (maybe it will come back and bite me)

Now for the product new features…..

It's a new dawn..

The time has finally arrived, the mounting pressure has paid its dividends and I have finally cracked, yes the blog has begun. I will take a lot of stick over this one because I said I would never do it, but when the nation calls you must answer.

So what is this blog going to contain, well unfortunately if you don't want to know about the Hyperion product set and were looking to the answer of life then it is time to leave, then again some people think it is the answer to life so it might be of some interest.

I just want to try and put down the things I have come across and some tricks and tips along the way, chance to answer most common questions on the forums, also it will give me the chance to showcase some of the applications I have developed not that I am going to spend most of the time plugging.

With the fresh arrival of the longest named suite Oracle Hyperion EPM 11.1.1.0.0.0.0.0 Fusion etc etc it is a good chance to go through some of the new features and what it has to offer, is all the excitement in the air wasted breath or can we believe the hype. As I am known to knock about on the Planning forum then this is going to be the first stop to look at the new functionality.

On with show..