4 Replies Latest reply on Nov 20, 2012 6:12 PM by kodalitaruni

    Minimum How many dimension required for FDM integration Script

      Hi Gurus

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

      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?

        • 1. Re: Minimum How many dimension required for FDM integration Script
          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

            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)

            Import Screen Dimensions:
            3.Account Description

            In the integration script its taking the values as

            Account Description

            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.


            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

            • 3. Re: Minimum How many dimension required for FDM integration Script
              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

                Thanks for your clear explanation.