You can certainly do it from the FDM Excel templates using the memo fireld as one of the columns

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

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

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

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

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