6 Replies Latest reply on Sep 12, 2011 10:29 AM by 886365


      Can anyone help me regarding the different types of libraries available for VB scripting in FDM. We have many functions available for different operations.
      What type of library should be used to what type of function ?

      Please help me in this. Thanks in advance !!
        • 1. Re: libraries
          Your question is pretty vague and open ended; therefore, I apologize if my answer isn't entirely specific.

          You can make any regular VBScript call in your FDM scripts. Furthermore, using VBScript you can make calls to many Windows DLLs, COM Objects, etc. (i.e. File manipulation, Database Connectivity, Active Directory Interfacing, Network Packets, etc, etc.) A good starting point for reviewing VBScript would be from Microsoft Directly ( http://msdn.microsoft.com/en-us/library/t0aew7h6(v=VS.85).aspx )

          With that said, the FDM API provides you the means to perform FDM/Hyperion specific functions conveniently. For instance, the FDM API exposes some specific objects for database functionality within the scope of what you are doing in FDM. While you could also just wire up ADO through VBScript and accomplish the same thing, it's going to take a bit more effort on your part and if you have an API exposed for the specific purpose of what you're doing, why go thruogh the extra hassle! Additionally, the main point to the API is to provide you with a "black box" which is "future proof" from the consumer's perspective. (i.e. Even if a future release of FDM changes HOW the API works behind the scenes, as long as you are exposed to the same Interface <inputs and outputs>, it doesn't matter to you AND it doesn't break your script!)

          The 64K question is knowing what is provided in FDM vs. the unlimited features available to you through plain VBScript. This isn't as easy to answer as some of the more important references are not immediately obvious; however, the important documents to review can be found here :

          Basic FDM "API" Documentation (more a mix of VBSCript and a few FDM specific items) : http://download.oracle.com/docs/cd/E10530_01/doc/epm.931/fdm_admin_guide.pdf

          The "real" API doc : http://download.oracle.com/docs/cd/E12032_01/doc/epm.921/fdm_92000_api_object_guide.pdf

          (NOTE : That is 9.2.0; however, much of it is probably quite valid.)

          Hope that helps.
          1 person found this helpful
          • 2. Re: libraries
            Thanks for your reply !!

            I'm bigginer to VB. My question here is, as we can see in API document, there are many procedures (like fParseString, ActConfigue etc,.) available in FDM VB scipt. Can you assist me in using these procedures with the respective library functions.

            Ex: Function AccountParse(strField, strRecord)
            AccountParse = DW.Utilities.fParseString (strField, 3, 2, "_")

            Here "DW.Utilities" is used for "fParseString", in the same manner, How many differnet libraries are there for different procedures ? Hope you get my point.
            • 3. Re: libraries
              I'm not sure I understand what you are asking at this point.

              I'm bigginer to VB. My question here is, as we can see in API document, there are many procedures (like fParseString, ActConfigue etc,.) available in FDM VB scipt. Can you assist me in using these procedures with the respective library functions.

              You would use the library functions to solve problems or add functionality to your environment????

              fParseString is function of the Utilities class provided in the Data Window object/dll. The point of fParseString is to make it easier to get a reference to a value in a string that is delimitted.

              Hypothetical Scenario :
              - Location loads a CSV (delimitted file)
              - File contains 4 columns
              - Account Number is 2nd of the 4 columns
              - C2 should be derived from the first two characters of the Account Number

              - Update Import Format for location
              - Add C2 and specific that it will call a script called ParseC2.uss (i.e. script=ParseC2.uss)
              - Create ParseC2.uss file in <FDMAPPS Folder>\Data\Scripts\Import folder

              Option 1 - ParseC2.uss (Use API)
              Function ParseC2(strField, strRecord)
              'Verify that we have data in record
              If Trim(strRecord) = "" Then Exit Function

              'Create working variables
              Dim strAccount

              'Get reference to the Account Number field
              strAccount = DW.utilities.fparsestring(strRecord, 4, 2, ",")

              Select Case Left(strAccount,2)
              Case "10"
              ParseC2 = "A"
              Case "20"
              ParseC2 = "B"
              Case Else
              ParseC2 = "C"
              End Select

              End Function

              Here we use Parse string to get the 2nd delimitted column and then I used left to get the first two bytes of the result. I then compare that to determine my C2 value.

              In the next example, we'll use VB to find the second column and essentially recreate "fparsestring"

              Option 2 - ParseC2.uss (Do not use API)

              Function ParseC2(strField, strRecord)
              'Verify that we have data in record
              If Trim(strRecord) = "" Then Exit Function

              'Create working variables
              Dim strAccount

              'Get reference to the Account Number field
              'we are looking for the second field.
              Dim arrRecordColumns

              'Split the string based on the delimitter ","
              'arrRecordColumns becomes a one dimensional array where each column is an item in the array. Col 1 = index 0, etc.
              arrRecordColumns = Split(strRecord, ",", -1, 0)

              'confirm that array holds a reference for the column we want.
              If UBound(arrRecordColumns) >= 1 Then
              'We have at least 2 columns returned, process the second column.
              'NOTE : array indexes start at position 0; therefore, second column is array index 1

              Select Case Left(arrRecordColumns(1),2)
              Case "10"
              ParseC2 = "A"
              Case "20"
              ParseC2 = "B"
              Case Else
              ParseC2 = "C"
              End Select
              End If

              End Function


              Which one is right? Both. They both solve your problem. In this example, the use of VBScript items as opposed to built in FDM items didn't really matter much. We have about the same amount of code (if you ignore my additional comments) and neither is overly complicated. Other items are not so simple. If you want to manipulate data in one of the FDM tables, for example, using the API manipulation options will save you a ton of code AND it will abstract your from the lowest levels of the database which could change in the next release of the software. If you use VB to do it all, your script will break and you'll have to spend time diagnosing it whereas if you use API calls you should be insulated from that. (at least in theory)

              The rule of thumb for me is if it's something ridiculously basic, I'm going to stick with VB/VBSCript functions/methods as they are pretty well established. If it's something that is going to take me a large amount of VBScript to accomplish and API exists for it in FDM, I'm using API. I'm also always using API if it is somehow manipulating data in the FDM system unless there isn't an API call.

              Edited by: beyerch2 on Sep 7, 2011 10:51 AM
              • 4. Re: libraries
                Thanks for spending time on my question and for ur reply !!

                Can u check this script and correct me....Here, I want to import one Excel sheet (dataload.xls) to FDM through Integration Script.

                Function EXCELimport(strLoc, lngCatKey, dblPerKey, strWorkTableName)
                'Oracle Hyperion FDM Integration Import Script:
                'Created By:     ratna
                'Date Created:     9/7/2011 9:29:31 AM

                Dim rsAppend
                Dim rs

                Set rsAppend = DW.clsImpTemplateMgr.fImportExcelFile(strWorkTableName)

                Set objFileSys = CreateObject ("Scripting.FileSystemObject")

                objFilePath = "D:\work\satty\dataload.xls"

                Set rs = CreateObject("Source Recordset")

                IF objFileSys.FileExists(objFilePath) then

                Set objReadFile = DW.ImpExpressionM.fExcelOpen(objFilePath)

                Do While Not rs.eof
                rsAppend.Fields ("PartitionKey") = RES.PlngLocKey
                rsAppend.Fields ("CatKey") = lngCatKey
                rsAppend.Fields ("PeriodKey") = dblPerKey
                rsAppend.Fields ("Amount") = rs.fields("Freight").Value
                rsAppend.Fields ("Account") = rs.fields("CustomerID").Value
                rsAppend.Fields ("Entity") = rs.fields("ShipCountry").Value
                rsAppend.Fields ("Desc1") = rs.fields("ShipName").Value
                RES.PlngActionType = 2
                RES.PstrActionValue = "No records to load!"
                End If

                End Function

                I am unable to run this, Its giving error in this part "DW.clsImpTemplateMgr.fImportExcelFile(strWorkTableName)" and also, let me know the correct script. Check "Set rs = CreateObject("Source Recordset")" also. What I need to write in the place of "Source Recordset"

                Thanks in advance !!
                • 5. Re: libraries

                  It appears your use of fImportExcelFile is incorrect here is the info from the API guide :



                  Read the contents of a Microsoft Excel file using the Excel COM type libraries and load the data into a WORK table
                  with the specified POV. The Excel file is searched in order to find range names starting with the prefix "ups". The
                  data contained in the range name will be converted to a text file that is formatted in a Micrsoft SQL Server BCP
                  layout. The contents of the range name must have the following meta data layout: cells(1,N)=Field Name Tags,
                  Cells(2+,N)= Data values.

                  ByVal strLoadFile As String,
                  ByVal strLoc As String,
                  ByVal strCat As String,
                  ByVal strPer As String,
                  ByVal strWorkTableName As String,
                  Optional ByVal blnSQLInsert As Boolean

                  strLoadFile Full path of the Micrsoft Excel Workbook file to be loaded
                  strLoc Name of the Location to process (See Property: clsPOVMgr.PPOVLocation)
                  strCat Name of the Category to process (See Property: clsPOVMgr.PPOVCategory)
                  strPer Name of the Period to process (See Property: clsPOVMgr.PPOVPeriod)
                  strWorkTableName Name of the current work/temp table containing base data
                  blnSQLInsert Optional Switch used to control data load method (True=SQL Insert Statement,
                  False=RDMS Bulk Load)

                  Return Type:

                  Your line which looks like :
                  Set rsAppend = DW.clsImpTemplateMgr.fImportExcelFile(strWorkTableName)

                  Should probably be something like
                  Dim boolSuccess
                  boolSuccess = DW.clsImpTemplateMgr.fImportExcelFile("D:\work\satty\dataload.xls", strLoc, strCat, strWorkTableName)

                  ***strLoc and strCat you need to do some legwork on these as your function is passing in the numeric ID references and not the text names which you need for this function. You can use clsPOVMgr to get teh current Category and Location name) strWorkTable you have from the function as it was passed in **

                  The rest of the code, I'm not sure I know what to do with that; however, it looks like it might be unneeded mostly ?
                  1 person found this helpful
                  • 6. Re: libraries
                    Thank you for the answer, It was really helpful.