This discussion is archived
11 Replies Latest reply: Sep 2, 2013 9:04 AM by atsoi RSS

Can we upload cell text via text file/excel file via FDM to HFM?

sayantan.mondal Newbie
Currently Being Moderated
Hi All,
Can we upload cell text into HFM via FDM from text/Excel files? My objective is to have a cell text column in the import text/Excel file and get it loaded into HFM.
Is it possible at all?

Thanks,
Sayantan
  • 1. Re: Can we upload cell text via text file/excel file via FDM to HFM?
    SH Guru
    Currently Being Moderated
    You can certainly do it from the FDM Excel templates using the memo fireld as one of the columns
  • 2. Re: Can we upload cell text via text file/excel file via FDM to HFM?
    sayantan.mondal Newbie
    Currently Being Moderated
    Thanks SH for your reply. Can we get cell text in when importing data from text file sources?
  • 3. Re: Can we upload cell text via text file/excel file via FDM to HFM?
    yFrechette Newbie
    Currently Being Moderated
    sayantan.mondal wrote:
    Thanks SH for your reply. Can we get cell text in when importing data from text file sources?
    Hi,

    It's not possible to load cell text with the source file. But we are trying to do the same thing...
    We decide to map the Description2 fields in FDM with the Cell text in the source file, than in a event script (like aftFileImport), to read the field from the table and add the text to a memo field in FDM.
    FDM provide a method to do this : API.ArchiveMgr.fMemoAddItem

    With this method the memo will be export to HFM as a celltext (you need to enable that in the Integration Settings).

    I tested the solution and it works, but we are in the process to implemented it !

    Hope this help.

    Yves
  • 4. Re: Can we upload cell text via text file/excel file via FDM to HFM?
    sayantan.mondal Newbie
    Currently Being Moderated
    Hi Yves,
    Great thinking! I think this is probably the only way to do it using as much out of the box functionality as possible. Thanks for the idea, and I am sure your implementation will work just fine. The only concern I have is any length restrictions on the description column.

    Thanks,
    Sayantan
  • 5. Re: Can we upload cell text via text file/excel file via FDM to HFM?
    yFrechette Newbie
    Currently Being Moderated
    HI,

    I have tested the solution and it worked perfectly !

    But, since de Desc2 field is limited to 75 characters, I have use the UD17 to UD20 dimensions to keep the data when importing the source file.
    If you need to do that, be aware that you need to create a "List" script in the adapter for the dimensions to be able to map it to [None].
    HFM does not support more that 4 custom dimensions, you need to trick FDM to be able to MAP other dimension.

    If you need more info on that, I can provide the detail of the script I used.

    Have a nice day.

    Yves
  • 6. Re: Can we upload cell text via text file/excel file via FDM to HFM?
    sayantan.mondal Newbie
    Currently Being Moderated
    Hi Yves,
    Awesome work! This is true out of the box thinking! It will be really helpful if you can please share the script.

    Thanks,
    Sayantan
  • 7. Re: Can we upload cell text via text file/excel file via FDM to HFM?
    yFrechette Newbie
    Currently Being Moderated
    sayantan.mondal wrote:
    Hi Yves,
    Awesome work! This is true out of the box thinking! It will be really helpful if you can please share the script.

    Thanks,
    Sayantan
    Hi,

    I found the script on Oracle support, you have to add it for each dimension you wish to use outside the first four.

    Function UD17List()
    '------------------------------------------------------------------
    'Oracle Hyperion Financial Data Quality Management
    '
    'Created By: Oracle Corporation
    'Date Created: 04/15/2008
    '
    'Purpose: Produce [None] Dimension Member for Drill Through
    '
    '------------------------------------------------------------------

    'Declare Local Variables
    Dim objR
    Dim vaUD17(1,0)

    '------------------------------------------------------------------------------
    'Intialize function return COM object
    '------------------------------------------------------------------------------
    Set objR = CreateObject("UpsOBJDMw.objScriptReturn")
    Set UD17List = objR
    objR.blnSortList = RES.FDMFalse

    vaUD17(0,0) = "[None]"
    vaUD17(1,0) = "Value for Drill Through"

    objR.varList = vaUD17
    objR.lngListCount = UBound(objR.varList,2)
    objR.lngListDimCount = 2
    objR.lngListDimActive = 2
    objR.lngErrCode = 0

    End Function

    The first thing I tried didn't use that, I was just unchecking the "Use List" option in the dimension property. That way FDM did not check the value against the HFM metadata, but when I drilldown from HFM to FDM, it did not return all the record because of the missing dimension.
    I found the solution in Oracle support, see "How to Leverage Additional Dimensions Inside of FDM That Are Not Exported But Maintain DrillBack [ID 1073258.1]" for more info.

    With that configuration, you need to add an import script to split the data into the number of dimension you want to use, an create a "Like" mapping that will map * to [None] !

    Next I use this script to combine all the data that was split into one memo after the file import :

    Function ChargementCellText(ByRef oErrText )

    On Error Resume Next

         'Get the variables from the POV for the Import
         strPartKey = API.POVMgr.fCheckLocProf(API.POVMgr.PPOVLocation).lngLocKey
         strCatKey = API.POVMgr.fCategoryKey(API.POVMgr.PPOVCategory)
         strPerKey = API.POVMgr.fPeriodKey(API.POVMgr.PPOVPeriod).strDateKey
         strTable = "tDataSeg" & RES.PlngSegKey
         
         'Recherche dans les données d'importation          
         strSQL = "SELECT t.dataKey, t.UD17||t.UD18||t.UD19||t.UD20 as CELLTEXT "
         strSQL = strSQL & " FROM " & strTable & " t"
         strSQL = strSQL & " WHERE t.partitionkey = " & strPartKey
         strSQL = strSQL & " AND t.catkey = " & strCatKey
         strSQL = strSQL & " AND t.periodkey = To_date('" & strPerKey & "','YYYYMMDD')"
         strSQL = strSQL & " AND t.UD17 is not null"
         
         
         Set rs = DW.DataAccess.farsSnap(strSQL)
         
         If Not rs.BOF And Not rs.EOF Then
              Do While Not rs.EOF
                   'Permet de créer un nouvel objet de type objMemoItem dans pObjmemoItem
                   API.ArchiveMgr.mIntializeMemoItemProperty
              
                   'Initialise les propriétés de l'objet
                   With API.ArchiveMgr.PobjMemoItem
                        .strAuthor = DW.Connection.PstrUserID               'Obligatoire
                        .dteMemoItemDate = Now
                        .lngDataKey = rs.fields("datakey").value
                        .lngDataTableID = RES.PlngSegKey
                        .strCategory = strCatKey
                        .strPeriod = strPerKey
                        .strShortText = "Mémo"
                        .strLongText = rs.fields("CELLTEXT").value
                        .lngPartitionKey = strPartKey
                        .lngMemoItemType = 1
                   End With
                   
                                                 
                   If API.ArchiveMgr.fMemoAddItem(API.ArchiveMgr.PobjMemoItem) Then
                        bOk = True
                   Else
                        bOk = False
                   End If
                   rs.movenext
              Loop
         End If
         
         If Err.number <> 0 Then
              oErrText = Err.Description
              ChargementCellText = False
         Else
              ChargementCellText = bOk
         End If
         Err.clear
         On Error GoTo 0
    End Function

    Voilà ! You got all the text load into the standard FDM Memo Field and ready to be export !
    The last thing I did was to modifiy the LoadB adapter script to remove the infomation that FDM add in the memo (Title, date, Author...) to keep just the text.
    Be aware that FDM will combine into the CellText, all Memos for an intersection, but will truncate it at 1950 chars.

    Hope this is understandable !

    Have a nice day

    Yves
  • 8. Re: Can we upload cell text via text file/excel file via FDM to HFM?
    sayantan.mondal Newbie
    Currently Being Moderated
    Hi Yves,
    Thanks a lot for sharing the script. I have not got a chance yet to try it out myself. It seems an advanced script and I will probably take some time to fully understand it. But you have researched and executed a great concept in FDM!

    Thanks,
    Sayantan

    PS: If I face any problems on this, I'll try to contact you once again! :)
  • 9. Re: Can we upload cell text via text file/excel file via FDM to HFM?
    877061 Explorer
    Currently Being Moderated
    Hi Yves, you mention the following:

    "The last thing I did was to modifiy the LoadB adapter script to remove the infomation that FDM add in the memo (Title, date, Author...) to keep just the text"

    What modifications did you make?

    Many thanks in advance!

    Kind regards,
    Jeroen
  • 10. Re: Can we upload cell text via text file/excel file via FDM to HFM?
    yFrechette Newbie
    Currently Being Moderated
    HI,

    I change this code :
    'Cell Text Memo                              
    strCleanMemo = rsCellText.Fields("MemoText").value                    
    strCleanMemo = Replace(strCleanMemo," [", vbcrlf & "[")
    strCleanMemo = Replace(strCleanMemo,"]","]" & vbTab)
    strCleanMemo = Replace(strCleanMemo,"** ", vbcrlf & vbcrlf)
                   
    If Len(strCleanMemo) > 1950 Then
    strCleanMemo = Left(strCleanMemo, 1950)
    End If

    For this code :

    strCleanMemo = TraiteMemo(rsCellText.Fields("MemoText").value, strTmpMemo)          
    If strTmpMemo <> "" Then
    bErrMemo = RES.FDMTrue
    strErrMemo = "Erreur Memo : " & strTmpMemo                    
    End If

    The function "TraiteMemo" remove all the informations add by FDM.

    '--[001]------------------------------------------------------------------------------
    ' Traitement des champs mémos
    ' Le champ mémo a la forme suivante :
    '     [Title :] aaaaaaaaa     [Author :] aaaaaaa     [Date :] yyyy-mm-dd     [Memo :] aaaaabbbbbabbbbbabbbba **
    ' Si plusieurs mémos sont présent, ils seront séparés par la chaîne suivante : " ** "
    '-------------------------------------------------------------------------------------
    Function TraiteMemo(ByVal iStrMemo, ByRef oErrDesc)
         On Error Resume Next
         i = 0 'Protection pour éviter une itération sans fin (avoid infinite loop, just in case)
         str = ""
         Err.Clear
         oErrDesc = ""
         Do While Len(iStrMemo) > 4 And i < 50
              
              posMemo = InStr(iStrMemo," ** ")
         
              If PosMemo > 3 Then
                   strtmp = Mid(iStrMemo,1, posMemo)     
                   str = str & Trim(DW.Utilities.fParseString(strTmp,5,5,"]")) & vbcrlf & vbcrlf          
                   iStrMemo = Right(iStrMemo,Len(iStrMemo)-(posMemo+3))
              Else
                   iStrMemo = ""
              End If
              i=i+1
         Loop     

         If Err.Number > 0 Then
              oErrDesc = Err.Description
         End If

         On Error GoTo 0          
         
         
         TraiteMemo = str

    End Function

    Hope this help.

    Yves Frechette
  • 11. Re: Can we upload cell text via text file/excel file via FDM to HFM?
    atsoi Newbie
    Currently Being Moderated

    Hi Yves,

     

    I was wondering if you could help me with an issue. I have followed the steps above for FDM and HFM 11.1.2.2, however our application is required to have CellText being loaded from FDM into calculated and parent level accounts in HFM. Is this possible?

     

    Best regards,

     

    Aaron

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points