11 Replies Latest reply: Sep 2, 2013 11:04 AM by atsoi RSS

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

    sayantan.mondal
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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

                              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