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…