This content has been marked as final.
Show 11 replies
-
1. Re: Can we upload cell text via text file/excel file via FDM to HFM?
SH_INT Jul 5, 2012 2:15 PM (in response to sayantan.mondal)You can certainly do it from the FDM Excel templates using the memo fireld as one of the columns1 person found this helpful -
2. Re: Can we upload cell text via text file/excel file via FDM to HFM?
sayantan.mondal Jul 5, 2012 4:43 PM (in response to SH_INT)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 Jul 6, 2012 5:07 PM (in response to sayantan.mondal)sayantan.mondal wrote:
Hi,
Thanks SH for your reply. Can we get cell text in when importing data from text file sources?
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 Jul 10, 2012 3:15 PM (in response to yFrechette)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 Jul 10, 2012 7:24 PM (in response to sayantan.mondal)HI,1 person found this helpful
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 Jul 11, 2012 6:30 AM (in response to yFrechette)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 Jul 11, 2012 12:00 PM (in response to sayantan.mondal)1 person found this helpfulsayantan.mondal wrote:
Hi,
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
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 Jul 16, 2012 2:06 PM (in response to yFrechette)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 Sep 6, 2012 10:54 AM (in response to yFrechette)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 Sep 19, 2012 8:04 PM (in response to 877061)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 Sep 2, 2013 4:04 PM (in response to yFrechette)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