Extract Row count for Import only stage load

[tschilling]

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

[sameburn]

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]

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]

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.

[franciscoamores]

Can you attach screenshot of your log?

[tschilling]

I had to edit all the other suppressed rows out of the picture to make it fit, but assume 40k+ were suppressed

[franciscoamores]

can i see the bottom of the log?

is that file loading to multiple periods?

[tschilling]

Here is the rest of the log, only loading to 1 period:

------------ Skipped --------------
Lines Blank ....................... 0
Lines Bypassed .................... 0
Lines Bypassed DynaCode ........... 0
Lines Non-Numeric ................. 0
Lines Zero Suppressed ............. 0
Lines Unmarked Value .............. 0
Lines Invalid DataKey.............. 0

----------- Duration --------------
1. Load Rule Cache ............... 44 ms
2. Parse + One-To-One Rules....... 31096 ms
3. Load Cache (Recalc/Append)..... 0 ms
4. Transformation Rules .......... 2 ms
5. Delete Existing Stage Data .... 15 ms
6. Delete Rule History ........... 1 ms
7. Write Data Pages To Stage ..... 166 ms
8. Post Summary Target Data ...... 18 ms
9. Post Rule History ............. 6 ms
10. Full Process .................. 31348 ms

----------- Data Keys ------------
(Global) - Model | (Global) - 2024M1

[Gaurav]

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