3 Replies Latest reply on Jan 29, 2013 6:01 AM by 967794

    Stat data load using FDM

    967794
      Hi,

      I have a requirement where statistical data is to be loaded to essbase using FDM. Below are few issues that I am facing.

      1) Is it possible to import only the first record when data is not unique? Any pointers to script would help.
      2) Can the format for amount dimension be changed? I am looking to remove comma separator for the amount.


      Thanks in advance.
        • 1. Re: Stat data load using FDM
          967794
          Additional information :

          Source file -

          Account, Entity, Amount
          *100,AB,300*
          *200,AB,300*

          FDM has mappings where both source account's 100 and 200 are mapped to Target 1000

          In the current scenario, FDM is showing the following data at Validate stage

          Account, Entity, Amount
          *1000,AB,600*
          I am looking to achieve something as given below :

          Account, Entity, Amount
          *1000,AB,300 (Only a single record and not add up the values)*

          Thanks in advance.
          • 2. Re: Stat data load using FDM
            beyerch2
            In regards to question #1 - Your best bet would be to eliminate the 'duplicate' rows in the AftValidate stage. You can query the tDataSeg table, find the duplicates, and then delete the "newer" records.

            NOTE : I've done minimal testing on this, but it should work fine. You may want to limit this to only execute for specific locations, etc, etc, etc. Use at your own risk. (i.e. don't just paste and run in production! :) )

            Sub AftValidate(strLoc, strCat, strPer, lngProcState)
            '------------------------------------------------------------------
            'Hyperion FDM EVENT Script:
            '
            'Created By:       cbeyer
            'Date Created:       2013-01-28-11:39:00
            '
            'Purpose:   This will remove duplicate rows from validated data.  The "original" row will be the one with the oldest timestamp (i.e. first one to be loaded)
            '                (you may want to lock this down to specific locations as this will execute for all locations out of the box)
            '                NOTE : This is a SQL SERVER specific solution.  If you are using Oracle DB, this will not work out of the box
            '------------------------------------------------------------------
            
            'Define Variables
            dtePerKey = RES.PdtePerKey     'Period Key
            strCatKey = RES.PlngCatKey     'Category Key
            strLocKey=RES.PlngLocKey     'Location Key
            strTableName = "tDataSeg" & API.DataWindow.Reports.PlngSegKey     'Location Segment Number (tDataSegxx)
            
            'Create SQL to clear duplications
            strSQL = "delete x from "
            strSQL = strSQL &  "( select *, rn=row_number() over (partition by AccountX order by DataKey) from " & strTableName & " where 
            strSQL = strSQL &  "   PartitionKey = " & strLocKey & " and CatKey = " & strCatKey & " and PeriodKey = '" & dtePerKey & "' ) x"
            strSQL = strSQL & " where rn > 1;"
            
            'Run the SQL statement 
            DW.DataManipulation.fExecuteDML(strSQL)
            
            'Return True (theoretically, you might have some error check / here before you just return true but ..... this is an example)
            RES.PlngActionType = 2
            RES.PstrActionValue = "Duplicate Data Clear Complete!"
            
            End Sub
            #2 - In regards to the format, are you talking about on the screen when the validation stage completes? I think your only shot here is to look at the file : 1033_WLBaseLayout_ValidateExportPageGridMetaData.xml which resides with your FDM web page files.

            It looks like the amount column is defined here. Unfortunately, I don't see anything that immediately jumps out as far as getting rid of the ,'s and .'s. Perhaps the "formatter" reference types more parameters which allow you to clean this up.
            -<column visible="true" dataType="CurrencyValue" horizontalAlignment="right" isAutoIncrement="false" isReadOnly="true" isSortable="true" autoSizeDisplayWidthUnit="1" minimumDisplayWidth="200" defaultValue="" imageFile="" description="~{Amount}~" databaseFieldName="Amt" isFilterable="false"> <renderer type="Hyperion.FDM.Controls.GridRenderer.HyperLinkRenderer" clientSideCallBackFunctionName="DM_ValidateExportPage_ShowDrillDown" toolTip="~{Click here to drill-down}~"/> <formatter type="Hyperion.FDM.Controls.GridFormatter.CurrencyFormatter"/> <validators/> </column> </columns>
            Hope that helps...
            • 3. Re: Stat data load using FDM
              967794
              Thank you very much.

              I have a start point to work on now.