Saturday 29 October 2011

EPM 11.1.2.1 – EAS and Studio console installers

When version 11 was released it seemed like Oracle didn’t really want to make life easier when it came to installing clients, to install the clients required a number of large foundation and product related files to be extracted.

At first there was only Smart View available as a standalone installer, no Excel add-in, Financial Reporting Studio, EAS or Essbase Studio console installers were available.

It wasn’t until a patch release in 11.1.1.3 that the Excel add-in was made available as a standalone installer.

From 11.1.2.1 the Financial Reporting Studio was finally available with its own installer.

Now I may be the last to know about this but the other day I noticed the following patch.


Initially viewing patch 12978472 doesn’t provide much information as it advises to consult the Read Me file.


If you have a look at the Read Me and defects fixed in the patch you will find that installers have been created for EAS and Essbase Studio consoles.


Download the patch and there you have it two installers are available.


Just like other EPM client installers it is just a matter of running the exe and following the wizard.

So if you are looking for an easier way of installing 11.1.2.1 EAS and Studio consoles then you now have this option.

Sunday 9 October 2011

Loading to EPMA planning applications using interface tables - Part 3

In the last part I went through interface tables and if you followed the blog you should now understand the requirements to load metadata to existing dimensions using them

I ended up with a set of tables ready to be populated with metadata though I am not going populate them just yet as I think it is worthwhile running through an interface import profile first.

An import profile provides all the required information about the dimensions and properties that are going to be imported, it defines the type of actions to take when loading members and their properties.

The easiest way to understand them is to go through the steps in creating one.


From within the dimension library go to File > Import > Create Profile


A profile name is entered and the Import Type is set to “Interface Tables”.

The application is set to “Shared Library” as the intention is to load to the already created Shared Dimensions, if you wanted to load directly to an application it can be selected from the dropdown.

The Data Source was created in the last part and holds the connection information to the database where the interface tables are located.


The Map Dimensions section defines the source dimensions from the interfaces tables and which dimension they will be mapped to in the Shared Library.


The source dimension information is picked up from the interface table IM_DIMENSIONS and column C_DIMENSION_NAME


There are a number of options under Process Type.

•    Merge as Primary—Merge as Primary processes all sections and adds new dimensions, members, relationships, properties and associations that exist in the source, but do not exist in the Shared Library or target application. No dimensions, members, relationships, properties, or associations are removed. If a dimension, relationship, member, or property specified in the source exists in the Shared Library or target application it is overwritten with the value specified in the source. Properties not included in the source are unchanged in the Shared Library or target application.

•    Merge as Move—This mode only processes members in the IsPrimary column that are set to “True” and ignores any others, so basically just looking at shared members.

•    Replace—All new elements are added and all property updates are made. Then, any members or member relationships that are not specified in the source are deleted from the Shared Library or target application. This option would be used if you wanted to rebuild the dimension hierarchy.



There are two options available for Reorder Type

•    Merge to Top —
Places new imported child members at the beginning of the child list under their parent.

•    Merge to Bottom—Places new imported child members at the end of the child list under their parent.


If Reorder Existing Members is selected then the action depends on what has been selected in Reorder Type

•    If Merge to Top is selected, the first child member in the import source becomes the first child under the parent, all imported child members are sorted to match the order in the import source, and any existing members not in the import source will be pushed to the end of the child list in their existing order.

•    If Merge to Bottom is selected, the last child member in the import source becomes the last child under the parent, all imported child members are sorted to match the order in the import source, and any existing members not in the import source will be pushed to the beginning of the child list in their existing order.

If you want a more detailed example have a look at the following section in the EPMA admin documentation – “Reordering Existing Member Examples”



The “Dimension Mapping” section provides the functionality to map each dimension source member property to a target member property in the Shared Library.


As I have created the source table columns with the same naming convention to the member properties in EPMA it is a direct mapping.


There is another parameter that can be set for each member that defines how existing property values are dealt with.

•    Clear Before Importing – if selected values will be cleared out, therefore making values match the import source exactly. If not selected, values will be merged in with existing values and all existing values will remain.

•    Allow Overwrites with Blank
–if the source value is blank the target property will be overwritten with the default property, so for instance if selected and there was no value in the DataStorage column the default value “StoreData” will be applied.
 

No need to execute the profile as there is no metadata in the hierarchy tables yet, I will be covering loading the metadata in the next instalment.

Sunday 2 October 2011

Loading to EPMA planning applications using interface tables – Part 2

In the first part I went through an introduction to what this series is about and covered getting the core planning application created in EPMA.

In this part I want to look at interface tables and end up with them built ready to load metadata into.

Interface tables are basically just a set of relational staging area tables that are populated before pushing into EPMA and these can be populated from any designated source, the ultimate end game in this series of blogs is to use ODI to populate the tables and then execute the process of moving them into EPMA.

The first step that needs to be undertaken is to create the interface data source and this is done in the EPM system configurator, I am not sure why this functionality is not yet available through workspace though I am sure it will be there one day.

Setting up the interface data source is basically configuring the connection to the RDBMS where the tables will be held, you will need to have created a schema/database first.


Start up the EPM System Configurator, uncheck all, expand Foundation > Performance Management Architect and select “Configure Interface Data Source


As this is the first time configuration “Create a New Datasource Link” is selected.


Select the RDBMS type that is going to be used for the interface tables.


A name for the data source is entered and this name that will be used in EPMA.
The connection details to database are entered.

Now there is the option to “Create interface tables”, I have selected this option and what it does is create a set of sample interface tables.

Once the interface tables have been created this is where the fear can set in.


As you can see a hell of a lot of tables have been created and you start to wonder whether this interface table route is going to be worth it, if you select one of the tables and view the amount of columns the fear gets worse but in the words of the great The Hitchhiker's Guide to the Galaxy “Don’t Panic!”

These are a set of sample tables and are not required, the columns in the tables relate to all the possible properties across Essbase, Planning and HFM.

The fact that I am initially only looking at planning and the approach I am taking is to only load metadata to dimensions which have been already created plus associations applied then honestly it is not as bad as it first looks.

The set of tables that are of more value are prefixed with IM_ which are not in the list above.

The rest of the tables that will be required are related to the metadata to be loaded for each dimension which I am going to manually create and will go through shortly.

IM_LOAD_INFO


This table basically allows grouping of metadata to be loaded, when importing dimensions in EPMA there is an option to select the interface load ID, the load ID is a numerical value.

Each dimension hierarchy table (not created yet) will have a column called LOADID that will relate back to the IM_LOAD_INFO table, it will become much clearer as we progress.


I have manually added a record to the table, it is not actually that important that the table keeps getting updated it could easily be set up once and then left alone.

I_LOAD_ID – I have started out with the value of 1

C_SOURCE_SYSTEM – Anything can be entered here but as I am initially going to be loading from files I have just set it to “FLAT_FILE”, it could be there are multiple metadata loads and you could have load ID 1 loading from flat files and Load ID 2 loading from a different source such as a warehouse.

C_USER_LAST_UPDATED – Once again anything can go here it all depends if you want to keep the user details.

D_DATE_LAST_UPDATED – Timestamp field.

C_LAST_UPDATE_LOGIN – Again anything can go here

IM_DIMENSION_ASSOCIATION – I am not going to be using this table as the dimension associations have already been applied, I may cover this at a later stage.

 

IM_DIMENSION

This table holds the information about the dimensions that are going to be loaded.

I_LOAD_ID – The load ID links back to the previous table IM_LOAD_INFO, so a corresponding value would be entered for the interface load.

C_DIMENSION_NAME – This is the name of the dimension in EPMA that is going to be loaded to.

C_DIMENSION_CLASS_NAME – This is the dimension type e.g. Account, Entity, Generic.

C_MEMBER_TABLE_NAME – This is the name of the table that will hold the member information. I am not going to be using this column as it can be all covered in the next column.

C_HIERARCHY_TABLE_NAME – This is the name of the table which will hold all the hierarchy and member information.

C_PROPERTY_ARRAY_TABLE_NAME – This is the name of the table which contains property information. I am not going to be using this column.

C_DIM_PROPERTY_TABLE_NAME – This is the name of the table containing all the dimension property information. I am not going to be using this column.

So out of all the columns I only need to populate four of them.


I have set the I_LOAD_ID to 1 to match what was set in the IM_LOAD_INFO table, so if a dimension import from interface tables is executed in EPMA against load ID 1 the required dimensions to be loaded and the tables holding the information are known.

For the records in column C_HIERARCHY_TABLE_NAME I have entered table names that will hold all the hierarchy/member properties, I am going to go through the process of creating them now.

It is possible to look at the HS_<DIMNAME>_HIERARCHY AND HS_<DIMNAME>_MEMBER sample tables and pick out the columns relating to Planning and then create a table based on them.

To make it easier I have gone through each dimension that I will be loading metadata to and generated the full list of columns that can be populated, many of them don’t have to be populated just like when using other methods to load metadata to a planning application

VERSION  

ENTITY

SCENARIO

ACCOUNT

SEGMENTS (custom dimension = Generic dimension type in EPMA terms)

The columns in each of the tables do not include any attribute related information yet, I will cover attributes and UDAs at a later stage.

In the next part I was going to look at introducing ODI to populate the interface tables but I think it is worth covering off import profiles first.

Loading to EPMA planning applications using interface tables - Part 1

In the mass of blogs I have written about using ODI with the Hyperion adaptors there is one area I have not touched upon and that is EPMA interface tables, it is true there are no knowledge modules available to make life easier when loading to EPMA type applications but by using interface tables it certainly is achievable.

In the past I have tried to steer clear of this area due to the numerous problems with EPMA but overtime many of the major flaws have been addressed and I thought it was about time to cover off ODI and interface tables, I will be perfectly honest and say using ODI with classic planning applications is still definitely my preferred option and it offers much more flexibility.

In this series of blogs I am going to basically try and replicate the sample planning application and use interface tables to populate metadata for a number of the dimensions, I am going to try and approach it from the angle of being used to loading to planning applications with the outline loader or ODI methods to make the transition over to EPMA a little bit smoother.  It is not going to be all about ODI in fact you could substitute the ODI part for another tool of your choice.

There are a few important points to highlight before I start.

•    It is going to be based on EPM 11.1.2.1, the concept should be similar for earlier versions but I can’t guarantee it will be exactly the same.

•    The version of ODI being used will be 11.1.1.5 but everything covered will be valid for earlier versions like 10g.

•    This is not a guide to EPMA and there will be an assumption you have used EPMA before or have a basic understanding of it but I will try and explain as much as possible as I go along.

•    There is going to be assumption that you are familiar with the basics of ODI.


My approach is going to be that the planning application is going to be built first and the dimensions and associations created, this is the same concept that would be undertaken if using the classic planning method as the core application would be available before loading to it. Using this approach I feel that it is much easier to grasp the concept of what is required to use interface tables.

I am not sure how many parts there will be to this blog as I am going to try and spread it out and not cram too much into each session, in this first part I am really just going to get the basic core application built in EPMA.

So to start with I am going to create the applications dimensions in the shared library, yes this is a manual process but it only needs to be done once and really does not take much time at all.

Remember it is going to be based on the sample planning application so the dimensions created will be Account, Year, Period, Entity, Currency, Version, Scenario, Segments, Alias.


I have suffixed each of the dimensions in the shared library with “_Shared”, just to point out that this is not a requirement it was just my way of highlighting they are shared dimensions. The dimensions have been simply created using File > New > Dimension and then entering the dimension name and type. 


Dimension        Type
Period_Shared        Period
Currency_Shared    Currency
Account_Shared    Account
Year_Shared        Year
Entity_Shared        Entity
Version_Shared        Version
Scenario_Shared    Scenario
Segments_Shared    Generic
Alias_Shared        Alias

I have cheated a little and manually populated the Period,Currency,Year and Alias dimensions. It is certainly possible to use interface tables to perform this step but due to the approach I am taking I need them to be available to be able to create the core sample application, also these dimensions are pretty much remain static.
The rest of the dimensions will be populated using interface tables.

The only additional steps was to create the associations (right click a dimension and select create association) for the currency dimension and between alias and all the dimensions


Hopefully this should all make sense as I did point out there was going to be an assumption of a basic understanding of EPMA.

On to creating the application, first of all make sure you have a relational schema/database created to hold the planning application.

File > New > Application




Basically I have replicated all the settings of the default sample application

All the dimensions from the Shared Library are associated with the dimensions in the application.


The properties of the sample application were replicated and then the application validated, any errors can easily to be resolved.



As “Deploy when finished” was enabled the Deploy window will display, if no Data Source has been created it can be generated at this point.



The application has been deployed successfully so in the next part I can move on to looking at interface tables.