Forum Stats

  • 3,838,074 Users
  • 2,262,326 Discussions
  • 7,900,495 Comments

Discussions

HFM RDBMS tables for Forms

Nikhil Deshpande
Nikhil Deshpande Member Posts: 6
edited Feb 12, 2019 1:26AM in Financial Consolidation

Hello All,

I want to create a custom report using the SQL query for the HFM application.

I did search for reference documents related to the same but to no avail.

It would be a great help if to know the specific tables where the data in the forms get stored.

Can anyone provide information related to the RDBMS (back-end tables) for the HFM application.

All of your inputs would be helpful and highly appreciated!

Thanks and Regards,

Nikhil Deshpande

Tagged:
Jim-DNikhil DeshpandeCBarbieri

Best Answer

  • CBarbieri
    CBarbieri Member Posts: 1,011 Gold Trophy
    edited Feb 5, 2019 9:02AM Answer ✓

    okay, I suggest you forget about accessing the database and investigate the in-product functionality HFM offers. You could write an on-demand rule within HFM to generate cell text that indicates the timestamp a form was filled in and then use that in a report. The only trick is that we can tell which entities and which form was calculated, but we can't tell which individual cells were updated. As Chandra pointed out, you can leverage HFM's data audit feature which captures data changes, but this is not form-specific.

    If you want the user who interacted with a form, that gets more complicated because that's not captured anywhere in HFM. You'd have to ask the user to fill in their name into a text field and leverage that.

    All of these ideas would be much easier than trying to decode the database and report on that.

    Nikhil DeshpandeNikhil Deshpande
«1

Answers

  • Jim-D
    Jim-D Member Posts: 584 Bronze Crown
    edited Feb 4, 2019 2:56PM

    Hi Nikhil,

    Welcome!  This space is for questions related to Oracle Enterprise Manager, and it appears you're asking a question about Hyperion.

    There are tips on how to find the correct space on the   page, which also has instructions on how to change your display name so you're not just a number.

    In the ACTIONS box on the top right of your post, you should be able to click on "Move", and then specify the correct space - such as   - so the right experts can see your question.

    Thanks and good luck!

    MOSC Move.png

  • Nikhil Deshpande
    Nikhil Deshpande Member Posts: 6
    edited Feb 4, 2019 3:08PM

    Thanks Jim!

    And apologies for posting it here, just getting used to Oracle Community.

    Jim-D
  • CBarbieri
    CBarbieri Member Posts: 1,011 Gold Trophy
    edited Feb 4, 2019 5:01PM

    Hi Nikhil,

    Since HFM's database is pretty cryptic, not documented as you might wish, and there are a lot of hoops to go through if you want to query its database directly, I will at first strongly recommend that you not do this. So, before we get into the mechanics, I'd like to ask what your use case is. Why do you want to access HFM's database directly, and what do you hope to do with the data? The reason for the question is to see if there is a more straightforward way to satisfy the use case.

    - Chris

    Nikhil DeshpandeNikhil Deshpande
  • CBarbieri
    CBarbieri Member Posts: 1,011 Gold Trophy
    edited Feb 4, 2019 5:32PM

    Keith Berry wrote a great presentation for KScope17 that explains the database tables. You can find it here:

    https://www.slideshare.net/aa026593/understanding-hfm-system-tables-85706049

    (Thanks Keith!)

    - Chris

    Nikhil Deshpande
  • Nikhil Deshpande
    Nikhil Deshpande Member Posts: 6
    edited Feb 5, 2019 4:52AM

    Hi Chris,

    Thanks for your response and sharing the link for HFM system tables!

    I'm trying to develop a custom report which enables me to know about the entities who have populated the data on the forms.

    Based on the last updated timestamp by a particular entity, the data will be populated in the report.

    Please do let me know if the description was helpful.

  • Chandra Bhojan-Oracle
    Chandra Bhojan-Oracle Posts: 198 Employee
    edited Feb 5, 2019 5:02AM

    APPNAME_DATA_AUDIT table contains the Data Entry task that will show you the Date and Time Stamp with user details. But it is not only specific to the form. It is for Data entry from all sources. Have you tried to use DATA_AUDIT reports in HFM?

    Thanks,

    Chandra

    Nikhil DeshpandeNikhil Deshpande
  • user6692921
    user6692921 Member Posts: 179 Silver Badge
    edited Feb 5, 2019 8:38AM

    I think you would be better to look at using cell text to capture the information you want rather than searching the data tables. It is easier to do and supported by Oracle at least in part. You can store things like the username, time, date, entity details... Accessing the data tables directly is not easy and you have a lot of fields to take into account as well as the difficulty in retrieving custom dimension information.

    Nikhil Deshpande
  • CBarbieri
    CBarbieri Member Posts: 1,011 Gold Trophy
    edited Feb 5, 2019 9:02AM Answer ✓

    okay, I suggest you forget about accessing the database and investigate the in-product functionality HFM offers. You could write an on-demand rule within HFM to generate cell text that indicates the timestamp a form was filled in and then use that in a report. The only trick is that we can tell which entities and which form was calculated, but we can't tell which individual cells were updated. As Chandra pointed out, you can leverage HFM's data audit feature which captures data changes, but this is not form-specific.

    If you want the user who interacted with a form, that gets more complicated because that's not captured anywhere in HFM. You'd have to ask the user to fill in their name into a text field and leverage that.

    All of these ideas would be much easier than trying to decode the database and report on that.

    Nikhil DeshpandeNikhil Deshpande
  • user6692921
    user6692921 Member Posts: 179 Silver Badge
    edited Feb 5, 2019 9:23AM

    HS.GetUser retrieves the current user.

    CBarbieri
  • CBarbieri
    CBarbieri Member Posts: 1,011 Gold Trophy
    edited Feb 5, 2019 12:00PM

    Very cool! I learned something new today! Thank you. This is an undocumented function, at least it's not in the admin guide, but it works very easily.

    - Chris