Sunday, 27 July 2008

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:
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


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…

Tuesday, 22 July 2008

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

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 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 :-

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…