Hi athlon007, are you storing the entity's default currency somewhere in FDMEE? If so, where?
Suggestion; Maybe you can incorporate the entity's currency in a lookup dimension and add it as a condition in your multi-dimensional mappings.
Here is where SQL mapping scripts using HFM database win :-)
Thanks for your reply. Unfortunately it is not stored anywhere in FDMEE.
So you mean that I could use for example in the Location under the Integration Options one of those 4 fields as a currency placeholder?
The problem with that is that we have Locations set up which contain multiple entities that are loaded into HFM.
I guess I must use a script instead of Multi Dimension mapping, too bad.
Sorry forgot to say that we actually have the Currency in the source file/DB, so I could use this as a Dimension for FDMEE.
EDIT: Currency info available in Source file/DB
If you have the currency (by Entity) in the source file then that value can be passed to a lookup dimension and the mapped target value used in a multi-dimension map. Just ensure that the lookup dimension is processed before the dimension where you are applying the multi-dimensional mapping.
That's what I will try and do, the only problem is that I actually want to have a different from USD, which is something you cannot to in the Multi Dimension mapping GUI, I think only via text import and I for sure don't want to do that.
If you can get the source file to include the default currency as a column of data, then I think you're good. Otherwise, you can try a custom solution to interrogate HFM, as suggested by Francisco Amores.
Thanks for the feedback. Unfortunately the source file contains multiple currencies for one entity (USD and EUR and NULL) This is creating an issue for me with mapping and querying this column.
I have since gone away from the Multi Dimension mapping and am trying an SQL. Would you know how to "interrogate HFM" for the entities' currency?
Thanks so much
Here is the query I use to get the entity currency for each entity in HFM (see below). Just substitute APPNAME with your application name. The way I worked it out was you had to count the number of members in the value dimension before you get to the actual currencies, starting at 0. Items 0 to 14 were used up by [None], [Contribution Total]....<Entity Currency>. The real currencies start after that and they are displayed in 3's ie USD, USD Adjs, USD Total. So when I incorporate the 14 and the 3 into my select statement, it returns the correct itemid's for the currencies that can be linked to the AppName_Entity_Item.defaultvalueid field.
Hope this helps.
WITH APPNAME_ENTITY_CURRENCY AS (SELECT ((ItemID+1)*3)+14 AS ITEMID, LABEL FROM APPNAME_CURRENCIES)
SELECT ent.Label as Entity, curr.label as Currency FROM APPNAME_ENTITY_ITEM ent, APPNAME_ENTITY_CURRENCY curr
WHERE ent.DefaultValueID = curr.itemid
--ent.FirstChildID = -1 use this to isolate just the base level entities if you like