This discussion is archived
4 Replies Latest reply: Nov 20, 2012 10:12 AM by kodalitaruni RSS

Minimum How many dimension required for FDM integration Script

kodalitaruni Newbie
Currently Being Moderated
Hi Gurus

I have only 2 dimensions in my SQL Table name dbo.ABC (Example: 1.Entity 2.Account and amount(data value)

Example:
USA, SALES, 50000

(Including value its total 3 dimensions)

How to export this data to Target HFM Application.

Integration Script got success when i click on validation it is shows only 2 dimension 1.Account 2.Entity. i have mapped correctly. but validation screen not showing anything. i got gold fish for validation button and Export is also showing success and got goldfish. but no data is exported to HFM application.
in FDM outbox its created a file which is containing only *!data* text. There is no record in this file.

I want to load the data with rest of the dimensions with [None] member combination as i don't have the additional dimensions in my source file.

Minimum how many dimension required to export the data from FDM to HFM?

regards
Taruni
  • 1. Re: Minimum How many dimension required for FDM integration Script
    SH Guru
    Currently Being Moderated
    Can you post your integration script? Can you see any data imported at the import stage?
  • 2. Re: Minimum How many dimension required for FDM integration Script
    kodalitaruni Newbie
    Currently Being Moderated
    Hi,

    I came to know, at least one member from the source file should be there in the integration script then only we can assign at least [None] member or any member for the target dimensions.

    My source file having only 3 dimensions ( USA,Sales,Amount)
    1.USA,2.Sales,3.$50000

    Import Screen Dimensions:
    1.Source-FM-Entity
    2.Source-FDM-Account
    3.Account Description
    4.SourceICP
    5.SourceCustom1
    6.SourceCustom2
    7.SourceCustom3
    8.SourceCustom4
    9.Amount

    In the integration script its taking the values as


    Source-FM-Entity(0)
    Source-FDM-Account(1)
    Account Description
    SourceICP
    SourceCustom1
    SourceCustom2
    SourceCustom3
    SourceCustom4
    Amount(2)

    above it shows only 0,1,2 numbers are assigned to source dimensions.

    As my source file having only 3 Dimension so it is taking only 3 dimensions shown below. rest of the dimensions it is not showing in the import screen.

    *0.Source-FM-Entity,1.Source-FDM-Account,2.Amount*

    If i assign any values(3-9) to next dimensions or if I left blank rs.fields("txtAcctDes") with its showing below error messages:

    Error: An error occurred importing the file.
    Detail: Item cannot be found in the collection corresponding to the requested name or ordinal.
    At line: (39 and 42-46)

    So i have assigned Source-FDM-Account Number<font color="Blue">(rs.fields(1) </font>Value to rest of the dimensions in my integration script.
    <font color="Blue">rsAppend.Fields("Account") = rs.fields(1).Value</font>

    rsAppend.Fields("Desc1") = rs.fields(1).Value
    rsAppend.Fields("ICP") = rs.fields(1).Value
    rsAppend.Fields("UD1") = rs.fields(1).Value
    rsAppend.Fields("UD2") = rs.fields(1).Value
    rsAppend.Fields("UD3") = rs.fields(1).Value
    rsAppend.Fields("UD4") = rs.fields(1).Value


    Now am able to import the data into import screen, And i found all the above member names as Sales as i assigned Account dimension number(1) to these members temporarily to succeed the import process . Then i have mapped to Target dimensions with [None] member combination as these members are not in original source file. Then rest of the process Export and Check is done perfectly.

    *<font color="red">1.Am i right?? Please suggest me the correct process?</font>*

    *<font color="red">2.Can we use blank values in Integration Script as mentioned below??</font>*

    rsAppend.Fields("Desc1") = rs.fields("txtAcctDes").Value
    rsAppend.Fields("Account") = rs.fields("txtAcct").Value
    rsAppend.Fields("Entity") = rs.fields("txtCenter").Value


    *1.Added value*
    Example: rsAppend.Fields("Desc1") = rs.fields("1").Value
    *2.Blank Value*
    rsAppend.Fields("Desc1") = rs.fields("txtAcctDes").Value

    *<font color="red">3.As per my observation system is not accepting blank values in integration script. Please correct me??</font>*


    Here is my Integration Script

    1     Function Integration(strLoc, lngCatKey, dblPerKey, strWorkTableName)
    2     '------------------------------------------------------------------
    3     'Oracle Hyperion FDM IMPORT Integration Script:
    4     Created By: admin
    5     Date Created: 2012-11-20-07:55:20
    6     'Purpose:
    7     '------------------------------------------------------------------
    8     Dim objSS 'ADODB.Connection
    9     Dim strSQL 'SQL String
    10     Dim rs 'Recordset
    11     Dim rsAppend 'tTB table append rs Object
    12     'Initialize objects
    13     Set cnSS = CreateObject("ADODB.Connection")
    14     Set rs = CreateObject("ADODB.Recordset")
    15     Set rsAppend = DW.DataAccess.farsTable(strWorkTableName)
    16     'Connect To SQL Server database
    17     cnss.open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TEST;Data Source=localhost;"
    18     strSQL = "Select * "
    19     strSQL = strSQL & "FROM ABC"
    20     'Get data
    21     rs.Open strSQL, cnSS
    22     'Check For data
    23     If rs.bof And rs.eof Then
    24     RES.PlngActionType = 2
    25     RES.PstrActionValue = "No Records To load!"
    26     FirstImportVB = False ' Assign return value of function
    27     Exit Function
    28     End If
    29     'Loop through records And append To tTB table In location’s DB
    30     If Not rs.bof And Not rs.eof Then
    31     Do While Not rs.eof
    32     rsAppend.AddNew
    33     rsAppend.Fields("PartitionKey") = RES.PlngLocKey
    34     rsAppend.Fields("catKey") = lngCatKey
    35     rsAppend.Fields("PeriodKey") =dblPerKey
    36     rsAppend.Fields("DataView") = "YTD"
    37     rsAppend.Fields("CalcAcctType") = 9
    38     rsAppend.Fields("Amount") = rs.fields(2).Value
    39     rsAppend.Fields("Desc1") = rs.fields(1).Value
    40     rsAppend.Fields("Account") = rs.fields(1).Value
    41     rsAppend.Fields("Entity") = rs.fields(0).Value
    42     rsAppend.Fields("ICP") = rs.fields(1).Value
    43     rsAppend.Fields("UD1") = rs.fields(1).Value
    44     rsAppend.Fields("UD2") = rs.fields(1).Value
    45     rsAppend.Fields("UD3") = rs.fields(1).Value
    46     rsAppend.Fields("UD4") = rs.fields(1).Value
    47     rsAppend.Update
    48     rs.movenext
    49     Loop
    50     End If
    51     'Records loaded
    52     RES.PlngActionType = 2
    53     RES.PstrActionValue = "SQL Import successful!"
    54     'Assign Return value
    55     Integration = True
    56     End Function

    Regards
    Taruni
  • 3. Re: Minimum How many dimension required for FDM integration Script
    SH Guru
    Currently Being Moderated
    You can assign blank values in your integration script e.g. rsAppend.Fields("Desc1") = "" will assign an empty string to the description. Personally I wouldn't assign an empty string to any of the active mappable dimensions as you would only be able to catch these with a wildcard map and they wouldn't be that informative. If you know they are goiung to be None then pass the string None to them and map that value e.g.
    rsAppend.Fields("ICP") = "ICP None"
    rsAppend.Fields("UD1") = "None"

    The reason your script has been failing is because the recodset you are using to assign the values from only has 3 fields. Therfore if you refence an oridinal in the range 3-9 it won't exsit
    i.e. rsAppend.Fields("ICP") = rs.fields(3).Value will generate the error message you get, as will rsAppend.Fields("Desc1") = rs.fields("txtAcctDes").Value because there is no field in the recordset named txtAcctDes
  • 4. Re: Minimum How many dimension required for FDM integration Script
    kodalitaruni Newbie
    Currently Being Moderated
    Hi,

    Thanks for your clear explanation.

    regards
    Taruni

Legend

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