Forum Stats

  • 3,838,795 Users
  • 2,262,400 Discussions
  • 7,900,758 Comments

Discussions

HFM Cannot extract data from journals

User_6GP2D
User_6GP2D Member Posts: 71 Red Ribbon
edited Jun 22, 2017 5:35PM in Financial Consolidation

Hi All,

I'm trying to extract the data that I loaded to the journals via FDMEE. I have seen the journals that I have loaded and they were all posted, also verified via Smartview that data is there.

However, I am getting a blank file when I extract those records. I'm using the Extract > Data option and I am using the POV and options below.

pastedImage_0.png

This was working well on the previous HFM version that the client is currently using. We have to use the specific POV above because we are trying to compare the HFM extract to the source data for validation. I have tried both Flat File and Database extract but still no luck getting any record.

Is there a reason as to why I can't extract journal data?

HFM Version: 11.1.2.4.204.5268

Thanks!

Tagged:
User_6GP2D

Answers

  • Jeo123
    Jeo123 Member Posts: 515 Gold Badge
    edited Jun 20, 2017 1:39PM

    Try changing entity from Base to Hierarchy.  I don't think the Base memberlist includes the Parent.Child relationship necessary to determine PCA.

    User_6GP2D
  • KostasN.
    KostasN. Member Posts: 547 Silver Badge
    edited Jun 20, 2017 2:08PM

    Hi,

    In addition to specifying the parent entity like {parentent.[Base]} also make sure that the [Active] flag is properly maintained in ownership management, for all levels in the entity hierarchy.

    Regards,

    Kostas

    User_6GP2D
  • Jeo123
    Jeo123 Member Posts: 515 Gold Badge
    edited Jun 20, 2017 3:51PM

    Specifying {ParentEnt.[Base]} instead of {[Base]} isn't going to help here.  I took it a step further to confirm my answer above through adhoc. 

    If you insert any [Base] List, you just get the MemberName without parents.  If you use one of the hierarchy based lists such as {Descendeants} you get the Parent.Child format.  A MemberName alone won't return journals for <Parent Curr Adjs> since it doesn't know the parent.

    It either needs to be either {Hierarchy} or {Descendants}.

    User_6GP2D
  • KostasN.
    KostasN. Member Posts: 547 Silver Badge
    edited Jun 21, 2017 9:58AM

    I did some testing as well, and found out that using {[Base]} as entity works very well with extracting from <Parent Curr Adjs>, without needing {Hierarchy} or {Descendants}. So this is not the problem. I also checked for dependency on owership, but also that's not a problem. In fact Ad Hoc is not a good simulation for data extraction. During journal input, the parent entity is specified mandatorily and therefore HFM saves the journal with the appropriate value member, -e.g. if the entity's parent is EUR and the entity default currency is USD, the journal is posted to the "EUR Adjs" value member. When extracting entity = {[Base]} all journals found for the entity (without any parent specification) are exported, with the appropriate specification of value member like below:

    Actual;2015;December;YTD;Acme;EUR Adjs;Account8020;[ICP None];[None];[None];[None];SB_ADJ;1000

    Therefore, we are looking in the wrong direction for solving the posted question. I would suggest to the user "3292348", to use {[Base]} for Custom3 and Custom4 and also make sure that there are posted journals on the scenario/year/period combination.

    Regards,

    Kostas

  • ChrisTurnbull
    ChrisTurnbull Member Posts: 29
    edited Jun 21, 2017 8:38PM

    Also to follow on from Kostas suggestion, I think the safest course is to post up here an example of the FDMEE output, or a simple posted Journal itself.  Can you see them when you run Extract Journals? We are assuming the Journals are even posted at <PCA> and not the node only [Parent Adjs] right now for instance.

  • User_6GP2D
    User_6GP2D Member Posts: 71 Red Ribbon
    edited Jun 22, 2017 7:06AM

    Thanks a lot, Kostas!

    I will try your suggestion. Although, I am kinda stuck with this POV because this is what they used to have in the previous HFM version (2.1) for extracting data and it's working perfectly fine. The extract is used to validate that the loaded data in HFM is the same as what is on the source table via a validation tool. 

    So the question would be, why is the POV not working now in 2.4? If I will change the Custom 3 and Custom 4 to {[Base]}, I need to make sure that my extract would be the same as their HFM 2.1 extract.

    Let me know your thoughts.

  • KostasN.
    KostasN. Member Posts: 547 Silver Badge
    edited Jun 22, 2017 7:46AM

    Hi,

    Ok, understood, changing Custom3 and Custom4, will not meet your need, however it might indicate where the problem is. Have you made sure that there exist posted journals to the scenario/year/period you're trying to extract? Have you seen those journals created and posted in the "Manage Journals" module? Have you seen the data you're looking for in a grid? Have you made sure that the problem is not with FDMEE not loading the data?

    I have tested with 11.1.2.3 (not 2.4) and I have no clue if there is any defect around data extraction with the newer version. Do you have access to Oracle's knowledge base?

    A final word, since your business need, may get you into trouble during reconciliation of what you have loaded: do you know that <Parent Curr Adjs> and <Entity Curr Adjs> mix up for entities that have the same default currency with their parent?

    Regards,

    Kostas

  • User_6GP2D
    User_6GP2D Member Posts: 71 Red Ribbon
    edited Jun 22, 2017 8:56AM

    Thanks Jeo123!

    This works. Although I need to confirm if it's the same output as the extract from HFM 2.1 using {[Base]} as Entity. I'm seeing that {[Descendants]} only gets the base members, but i thought it would also get some of the parents. So what's the difference between the two?

  • User_6GP2D
    User_6GP2D Member Posts: 71 Red Ribbon
    edited Jun 22, 2017 9:03AM

    Yes, I can see that the journals were loaded via "Manage Journals" and SmartView, verifying that data has been loaded through FDMEE. That's why, I'm not sure why the {[Base]} for Entity does not pull any value using the POV.

    I'm new in HFM but I will try to search more on the inputs you have provided. I think I'll open an SR for this one to know more about what I'm getting.

    Thank you very much!!

  • Jeo123
    Jeo123 Member Posts: 515 Gold Badge
    edited Jun 22, 2017 5:35PM

    @3292348
    You'll be fine with Descendants.

    The reason you don't have any values coming from the parents is because the value dimension collapses when you consolidate.  That means there's no data in the XXX Adjs values at the parent level entity because it all moved over to the EC value.  Additionally, parent level data is considered Calculated and you don't have that box checked.

    If you were pulling data at <Parent Curr Total> and checked the Calculated Data box, you would get the parent levels as well.

    @KostasN.

    This is 11.1.2.4 related, we're on that version and I see the same thing in smartview or data grids.  But to prove this out in the application, I went and found some of the PCA's in our application from years ago.  It's easy to replicate this issue.

    If you run an extract with [Base] and click the Info button on the Running Task Result  you can see the EA extract log.

    For [Base], all the entities are missing the parent.  Meanwhile when you use descendants, it does create the parent.child format for the entity list.

    And while I'm aware that when the journals are created, the journals are posted to the appropriate Value Adj member.  The reason this fails is that the EA extract doesn't know which parent currency you want.

    If Entity X has a USD parent and an EUR parent, it could be either.  Defining USA.EntityX is how the extract is able to determine USD Adjs vs EUR Adjs.  When no parent is specified, the look up fails.

    You get a similar problem if you go to a 2nd generation entity in a data grid and select [Base] with <Parent Currency>.  It prevents the entire grid from displaying.  If you do USD with base, you get a list of just the MemberNames(no parent).

    User_6GP2D
This discussion has been closed.