Forum Discussion

tschilling's avatar
tschilling
New Contributor III
8 months ago

Extract Row count for Import only stage load

Hello all - Looking to capture the actual # of rows inserted into the StageSourceData, NOT just the # of rows that the api.Parser.ProcessDataTable(si, dt, True, api.ProcessInfo) command tries to insert.

For example, there are cases where rows are filtered out (not inserted) due to Zero Suppression, non numeric characters, etc...that are reported in the processing log, but don't actually make it into the StageSourceData table.  Looking for the # of rows that actually make into StageSourceData. 

Obviously, this can be querying by doing a count on that table with the proper wfk and wtk, but wondering if there is an API call that will tell me the same information that's in the processing log.  In the case below, I would want the lines valid, not the lines read (which is already available in the API)

----------- Line Counts -----------
Lines Read ........................ 42625

Lines Valid ....................... 42000

Data Integration
Transformation
  • sameburn's avatar
    sameburn
    Contributor II
    8 months ago

    Have you tried adding a logging line to your connector to log your source datatable e.g

    BRApi.ErrorLog.LogMessage(si, "dt row count", dt.Rows.Count.ToString)

  • franciscoamores's avatar
    franciscoamores
    Contributor II
    8 months ago

    Hi,

    the api (Transformer) object hast the Parser property (Type Parser) where you can get all those metrics.

    E.g.: api.Parser.LinesValid

    HTH

    • tschilling's avatar
      tschilling
      New Contributor III
      8 months ago

      I started down that path previously and it wasn't giving me what I was looking for.  In the example I am working on here, it breaks down like this:

      # of Source Rows - 42738 (when i query the external source)

      # of Lines Read - 42738

      #of Lines Valid - 42738

      However, in the processing log there are 42000+ rows that are showing with the 5 ZP (Zero Suppression).  I can confirm these are in fact being suppressed in the staging area because there are only 100 or so rows showing in the import screen.  

      So, I believe the import itself is working correctly by applying zero suppression, but the processing log and the API.Parser methods are not correctly capturing the # records that are being skipped.  If I can get the # of rows being skipped, then I can easily subtract that from the # of lines ready to accurately report how many rows actually got loaded.

      api.Parser.LinesSkippedZeroSuppress is returning zero, which I think it should be returning the 42k+ rows that are zero suppressed in the log.

       

  • Gaurav's avatar
    Gaurav
    New Contributor III
    7 months ago

    Hey,
    Try this, I'm quering StageSourceData at profilekey /scenario/time to count records:

    'Get Global Scenario
    Dim globalScenario As String = BRApi.Workflow.General.GetGlobalScenario(si)
    Dim globalScenarioID As Integer = BRApi.Finance.Members.GetMemberId(si,BRAPI.Finance.Dim.GetDim(si,"Scenarios").DimPk.DimTypeId,globalScenario)

    'Get Global Time
    Dim globalTime As String = BRApi.Workflow.General.GetGlobalTime(si)
    Dim globalTimeId As Integer = BRApi.Finance.Time.GetIdFromName(si,GlobalTime)

     

    'Get Import Profile Key
    Dim profileName As String = "xxxxxx.Import"
    Dim wfProfileKey As String = BRApi.Workflow.Metadata.GetProfile(si, profileName).ProfileKey.ToString
     
    'Count OS Imported Records
    Dim sql As New Text.StringBuilder
     
    sql.Append("select ISNULL((Select count(*) from StageSourceData ")
    sql.Append("Where wfk = '" & wfProfileKey & "' ")
    sql.Append("And wsk = '" & globalScenarioID & "' ")
    sql.Append("And wtk = '" & PeriodId & "'),0) As Count")
     
    BRAPI.ErrorLog.LogMessage(si,"QueryOS:" & sql.ToString)
     
    Using dbconn As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
     
    Using dt As DataTable = BRApi.Database.ExecuteSql(dbConn, sql.ToString, True)
     
    If (dt IsNot Nothing) AndAlso (dt.Rows.Count = 1) Then
    Dim dr As DataRow = dt.Rows(0)
    countOS = dr("Count")
    BRAPI.ErrorLog.LogMessage(si,"Count in OS Staging Table:" & dr("Count"))
    End If
     
    End Using
     
     End Using