12 Replies Latest reply: May 16, 2011 9:58 AM by TonyScalese RSS

    How Can I Pull Target Entity Default Currency from HFM

    642338
      Hi All

      I have seen in FDM that there is the ability to populate a tDimEntity table that includes a field to hold the target entity currency so that I can perform a test that import data is in the correct currency for target entities but at the moment I cannot work out a 'dynamic' way to populate that table. The FDM script delivered to pull HFM metadata into the tDim tables in FDM only seems to pull member name and alias for entities (HFM_LoadDimTables).

      Regards
      Stuart
        • 1. Re: How Can I Pull Target Entity Default Currency from HFM
          TonyScalese
          Stuart,

          This posting is very similar to one in the HFM forum (Retrieve HFM Entities Default Currencies Cross posting is generally a frowned upon action in forums (any, not just OTN) because it creates disparate information and results in multiple people potentially investing their time to answer a question that has already been answered.

          I would suggest starting with trying to modify the script that populates the tDIMEntity table. Play with it a bit and see what information the API is returning. You can modify the script to write that additional information into the table.

          Tony
          • 2. Re: How Can I Pull Target Entity Default Currency from HFM
            642338
            Hi Tony

            Yes, apologies I have posted a similar question in the two forums, I know FDM and HFM are pretty inherently linked but I'm never quite sure whether people monitor across multiple forums so wanted to try to hit both aspects with a query angled at the HFM side and one from the FDM side.

            I very much appreciate the efforts of you and the other experts, I'm still discovering FDM and VB (my background is the Essbase, Planning, Reporting side of Hyperion).

            I did try modifying the HFM_LoadDimTables script yesterday but having reviewed the v11.1.2 API guide I couldn't see anything in there relating to fListEntities which seems to be the function that I assume retrieves the details from the HFM tables. I have spoken with a former Oracle employee who thought that there may be a host of functions, properties etc not detailed in the guide so I raised an SR with Oracle only to be pointed back to the API guide or to consultancy which isn't an option for me.

            Am I missing something obvious, or is it that I simply need to improve my VB knowledge?

            Regards
            Stuart
            • 3. Re: How Can I Pull Target Entity Default Currency from HFM
              TonyScalese
              No problem Stuart. I bring it up because we do see it a lot on the forums.

              I honestly haven't tried to bring back the currencies so I don't know which APIs can be used. I would try to add in logging to loop through the array that flistentities returns. That will show you what the function gives you.

              A lot of this is just learning the application and knowing VBScript well. It takes time. You could also consider a partner for assistance. I know you said it was not an option but it might be worth another look.
              • 4. Re: How Can I Pull Target Entity Default Currency from HFM
                beyerch2
                The data you need is in the HFM table <APP NAME>_CURRENCIES

                The data in the value table appears to correspond only to the currencies that you actually are loading in for translation and the ID numbers do not sync as you mentioned already.
                • 5. Re: How Can I Pull Target Entity Default Currency from HFM
                  642338
                  Hi Charles

                  In my version (11.1.2) the ItemID in the <APPNAME>CURRENCIES table does not correspond to the DefaultValueID in the <APPNAME>ENTITY_ITEM table.

                  Currencies table IDs range from 0 to 39, ID for GBP = 4
                  Entity Item table IDs go as high as 114 and entities that I know have GBP as default currency have ID = 29

                  That is where I got a bit confused as I couldn't see anywhere with a link that would join these two together.

                  Regards
                  Stuart
                  • 6. Re: How Can I Pull Target Entity Default Currency from HFM
                    beyerch2
                    Ahhhh in 9.3.1 they correspond. I had the impression this was a system 9 question. I'm not sure why those don't match then .. Odd
                    • 7. Re: How Can I Pull Target Entity Default Currency from HFM
                      SH
                      There is not a direct relationship between the IDs in the currency table and the Value IDs stored to represent the default currency. A calculation must be performed using the IDs in the <APPNAME>VALUEITEM table. This is because data can be stored against any one of the entiity currency value triplet. Therefore to derive your Default Currency ID from your Currency ID you must do the following caculation (Entity Currency Default ID - <ENITY CURRENCY> ID) / 3 = Currency ID
                      • 8. Re: How Can I Pull Target Entity Default Currency from HFM
                        642338
                        Hi SH

                        Thanks for the pointer, thought it must be some kind of relationship. Have just gone through and for me on v11.1.2 it seems to be:

                        ((DefaultValueID from <APPNAME>ENTITYITEM table - ItemID for <Entity Currency> from <APPNAME>VALUEITEM table) / 3) -1

                        e.g. ((29 - 14) / 3) - 1 = (15 / 3) - 1 = 4, so 29 converts to 4

                        I guess the -1 is required as my currency ID begins at 0 rather than 1 and agrees with the fact that my lowest DefaultValueID is 17 (other than 0 which is Entity member [NONE] so doesn't have a currency).

                        Thanks to all for your help, now I just need to finish how I pull this from HFM into FDM

                        Regards
                        Stuart
                        • 9. Re: How Can I Pull Target Entity Default Currency from HFM
                          TonyScalese
                          I would caution against trying to pull directly from the HFM database. There are certain data points that are not stored in HFM ever. You might want to consider running the data out to EA and then retrieving from there.
                          • 10. Re: How Can I Pull Target Entity Default Currency from HFM
                            TonyScalese
                            There is actually an FDM API function that will bring back the currency assigned in HFM metadata. It's in the HFM adaptor type library. Search through the object browser and I'm sure you'll find it.
                            • 11. Re: How Can I Pull Target Entity Default Currency from HFM
                              642338
                              Hi All

                              Just to let you know that with some help from you all I now have what I needed.

                              The key for me was seeing another post of Tony's where he mentioned importing the other Type Libraries (files that come with the adapter of file type .tlb), this uncovered a few extra API features that I had a feeling were there but previously didn't know how to get to (unfortunately they aren't documented either so I had to take a stab at what they would do / how to use them.

                              Anyway I found one called fGetDefCurrency and tried it out within the code of the HFM_LoadDimTables script and hey presto it pulled back the default entity currencies from HFM.

                              I can now check when importing data that the currency provided is the one I want in HFM, bingo! :-)

                              Thanks to all for their help (and sorry Tony had already marked another post correct so could only mark yours as helpful)

                              Regards
                              Stuart
                              • 12. Re: How Can I Pull Target Entity Default Currency from HFM
                                TonyScalese
                                No problem on the correct flag. The key is to get the right info out there.