Forum Discussion

VENKAB05's avatar
VENKAB05
New Contributor III
6 months ago

Issues in Exporting Complex Dynamic Calc Accounts

Hello All,

We are having a requirement where we need to export Dynamic Calc members through FDX. We have few dynamic accounts which are complex and it refers to different time periods like (T#POVPrior, T#2023, T#2022). So whenever we run the export, it generates a very huge amount of error logs , 1 Million, referring to the T# 

Summary: Error processing script '((Divide(A#ABC:U8#Sample,A#DEF:U8#None))*100)'. Unable to execute formula for member 'ABC'. Invalid script 'A#DEF:I#Top:T#LastActualPeriod- A#IJK:V#YTD' near 'T#'

Here LastActualPeriod will be calculated based on current actual period, Example T#2022M1

Any idea why this happens and any alternate ideas to come out of this and export the Dyn Calc Accounts.

Thanks in advance for your help !

  • aformenti's avatar
    aformenti
    Contributor II

    Hi VENKAB05,

    Which type of FDX method are you using? Have you tried using  a Cube View to export it? I would expect you being able to get the data out by using it.; This is a detailed Example of how to run it:

    If args.DataSetName.XFEqualsIgnoreCase("GetFDXCubeView") Then
    
    							' Declare variables
    							' Workspace ID (we are using Default)
    							Dim workspaceID As Guid = BRApi.Dashboards.Workspaces.GetWorkspaceIDFromName(si, False, "FDXD")
    							' Enter Cube View Name
    							Dim cubeViewName As String = args.NameValuePairs.XFGetValue("CVName")
    							' Enter Entity Dimension Name (will use the Cube View values if nothing is provided)
    							Dim entityDimName As String = String.Empty
    							' Enter Entity Member Filter (will use the Cube View values if nothing is provided)
    							Dim entityMemFilter As String = String.Empty
    							' Enter Scenario Dimension Filter (will use the Cube View values if nothing is provided)
    							Dim scenarioDimName As String = String.Empty
    							' Enter Scenario Member Filter (will use the Cube View values if nothing is provided)
    							Dim scenarioMemFilter As String = String.Empty
    							' Enter Time Member Filter (will use the Cube View values if nothing is provided)
    							Dim timeMemFilter As String = String.Empty							
    							' A Name Value parameter object – this can be used if additional parameters are used in the Cube View
    							' Here we are passing in our KeyValuePair strings from parameter selection
    							Dim nvbParams As New NameValueFormatBuilder()
    							nvbParams.NameValuePairs.Add("prm_SelectEntity_FDX", args.NameValuePairs.XFGetValue("prm_SelectEntity_FDX"))
    							nvbParams.NameValuePairs.Add("prm_SelectTime_FDX", args.NameValuePairs.XFGetValue("prm_SelectTime_FDX"))
    							' Include Text columns – True/False (can be used to show the Cell-level annotation)
    							Dim includeCellTextCols As Boolean = False
    							' Use Standard Fact Table fields – True/False (can be used to use the Dimension Names to be in the columns)
    							Dim useStandardFactTableFields As Boolean = True
    							' Filter – Use SQL LIKE filters on the DataTable on the Dimension that is not part of the Data Unit
    							Dim filter As String = String.Empty
    							'  Parallel Query Count – An integer for parallel threads (cannot exceed 128)
    							Dim parallelQueryCount As Integer = 8
    							'  Log FDX Statistics – True/False
    							Dim logStatistics As Boolean = True
    							
    							' Return
    							Return BRApi.Import.Data.FdxExecuteCubeView(si, workspaceID, cubeViewName, entityDimName, entityMemFilter, scenarioDimName, scenarioMemFilter, timeMemFilter, nvbParams, includeCellTextCols, useStandardFactTableFields, filter, parallelQueryCount, logStatistics)
    

     

     

  • Henning's avatar
    Henning
    Valued Contributor II

    Hi, just to make sure, which FDX function are you using to do this? Is it FdxExecuteCubeView?

    BRApi.Import.Data.FdxExecuteCubeView()

     

  • aformenti's avatar
    aformenti
    Contributor II

    Hi VENKAB05 ,

    I was just talking to someone and the very same issue was mentioned. One option to solve the problem with overloading the error log is by modifying the Error Handler on those dynamic formulas:

    Try
    
    			Catch ex As Exception
    '				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
    				Return Nothing
    			End Try

    Unfortunately, apparently the FDX extracts does not work very well with Dynamic Formulas.  Not ideal but it will at least prevent blowing the Error Log.

    Hope that helps, 

    Albert