HFM Extended Analytics 101

One of my clients asked me to give them a quick write up of how Extended Analytics works in HFM since they've never used it before but are looking to explore more possibilities going forward.

Normal data extracts from HFM are limited in the point of view you get to select for the data being extracted.  You are limited to select on 6 of the 12 dimensions the remainder are defaulted, you can only select the following dimensions:

• Year
• Period
• Entity
• Account
• Scenario
• View

The remainder of the dimensions are defaulted for you: 

• ICP - Base ICP
• C1 - Base C1
• C2 - Base C2
• C3 - Base C3
• C4 - Base C4
• Value - Entity Currency

In addition to this limited POV you cannot extract 'Derived' data you only get real data that is stored in HFM. 

 

To access Extended Analytics, click on "Administration" then "Extended Analytics":

Extended Analytics gives you a lot more flexibility than the normal data extract because you can select all 12 dimensions and none of them are defaulted for you.  In addition, all amounts get extracted regardless of whether they are 'Derived' or not: 

For the "Standard" Extract Format, you need to add a Relational Table Prefix.  EA will automatically create the necessary tables in SQL Server based on your selected DSN:

This extract will create 13 tables in the relational database, one for each dimension and one 'Fact' table that contains all the data:

The Fact Table contains all the data but instead of seeing the real HFM Account, Entity, ICP, etc. you only see 'Index' numbers that you then have to look up in the respective dimension table:

So the only way to get data out of this Fact table is to write a query to join all the tables, which I have some examples.  If you've read this far and are interested, please contact me and I'll email it over.