Forum Discussion

Kneelay's avatar
Kneelay
New Contributor II
9 months ago

SQL Table Insert Not adding all Records

HI All, 

I'm running into an issue when running a Finance Business Rule, essentially this rule creates a data buffer and loops through where U8 member is not 0. From here we have a SQL statement where we're inserting these dimensions and the Business Unit which is derived from the Workflow Text field. Whenever a end user presses the button it should trigger this DM job and Business Rule to delete an existing records for this BU in the table and then add all the records again. 

The issue is however that in this case that we're expecting to see 112 records but only seeing around 40. I wrote to the error log and see all the row records of 112, however when inserting into the table we only get around 40, sometimes even less. From what I compared looking at all 112 records vs the 40 in the table, I noticed that from the error most of the records that are the looping through last are being added to the table, but only a few from the beginning. It's almost as if these records are being over written. All these rows are unique, although some rows have everything the same besides a profit center (Entity field). Any idea of why this would be or what could be done?

 

Finance
  • RobbSalzmann's avatar
    RobbSalzmann
    9 months ago
    Dim valueList As List(Of String) = New List(Of String)
Dim x As Integer = 0

For Each Cell As DataBufferCell In SrcDataBuffer.DataBufferCells.Values
    ' Execute If Allocation percentage Is Not equal To 0
    If Cell.CellAmount <> 0 Then			            
        x += 1
        sAcct = Cell.DataBufferCellPk.GetAccountName(api)
        sUD1 = Cell.DataBufferCellPk.GetUD1Name(api)
        sFlow = Cell.DataBufferCellPk.GetFlowName(api)
        sUD2 = Cell.DataBufferCellPk.GetUD2Name(api)
        sUD4 = Cell.DataBufferCellPk.GetUD4Name(api)
		valueList.Add($"('{sqlentity}','{sEntitymember}', '{sUD4}', '{sFlow}', '{sUD2}', '{sUD1}', '{sAcct}', {Cell.CellAmount})")
    End If
Next

Dim insertValues = String.Join(", ", valueList)

Dim sql As String = $"INSERT INTO XFT_BSM_Mapping_Total$ 
                     ([BU], [Entity], [BSM_Account], [BSM_Measure], [Responsibility_Center], [Company_Code], [Account],[Allocation_Percentage])
                     VALUES {insertValues}"
brapi.ErrorLog.LogMessage(si,x)

Using dbConn As DBConnInfo = BRAPi.Database.CreateApplicationDBConnInfo(si)
	BRAPi.Database.ExecuteSql(dbConn , sql, True)
End Using

     

  • RobbSalzmann's avatar
    RobbSalzmann
    Valued Contributor II
    9 months ago

    You should be using BRApi.Database.ExecuteSql here, not ExecuteActionQuery.
    ExecuteActionQuery is for things like create and alter .

    Also, avoid running SQL queries inside of loops.

    • Kneelay's avatar
      Kneelay
      New Contributor II
      9 months ago

      Thanks, I changed it to ExecuteSql instead. I'm trying to think of the best way to do this besides running the SQL query inside the loop, but having some issues with that. 

      • RobbSalzmann's avatar
        RobbSalzmann
        Valued Contributor II
        9 months ago

        Post your code so far.  I can give you a hand with it.  About how many data cells are you inserting?

         

  • JackLacava's avatar
    JackLacava
    Honored Contributor
    9 months ago

    Is that chunk of code inside a cell loop? That's a bad idea - you're opening and closing a connection for each cell, I wouldn't be surprised if some were dropped.

    A better strategy would be:

    1. open the connection (the Using line)
    2. build your FULL sql statement by looping through cells. The syntax is simple, you can see it in this StackOverflow answer.
    3. once the loop is over, execute your SQL with ExecuteSql
    4. end using

    This makes it easier to also double-check all your records, by logging the sql query before step 3, to verify your assumptions about uniqueness.

    • Kneelay's avatar
      Kneelay
      New Contributor II
      9 months ago

      Yes that's correct, 

      I tried following what you showed but not sure if it's correct. Somehow I still see the 112 records being added to the error log. I created a test variable and was trying to see how many times it loops, seems like only 10, even though it shows up 112 times. 

       

  • Krishna's avatar
    Krishna
    Valued Contributor
    9 months ago

    Kneelay - Are you using Data Table ? or directly importing into the Actual Table (XFT_BSM_Mapping_Table). if not can you try copying the data into data table and perform a bulk copy which is always better in performance.

    • Kneelay's avatar
      Kneelay
      New Contributor II
      9 months ago

      Thanks for responding Krishna. We're just directly importing in the Actual Table (XFT_BSM_Mapping Table). Based on your response, just to confirm, could we load directly from the cube to a Data Table? The few examples I have seen in our BR's are all from tables that already exist or external table. 

      • Krishna's avatar
        Krishna
        Valued Contributor
        9 months ago

        Are you currently importing the Cube Data into the Actual Table ?

  • RobbSalzmann's avatar
    RobbSalzmann
    Valued Contributor II
    9 months ago

    Its a lot easier for me to edit,if you post the code as text vs a screen shot.

     

    • Kneelay's avatar
      Kneelay
      New Contributor II
      9 months ago
      'Create a databuffer for all combinations that would hold a value in U8#Alloc_Perc
Dim SrcDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("FilterMembers(V#Periodic:I#None:O#Top:U5#None:U6#None:U7#None:U8#Allocation_perc:U3#Primary,[U1#CO_20.Base],[A#GMACM.Base],[F#Total_BSM_Flow.Base],[U2#Total_RC.Base],[U4#New_Supertable.Base])")
							
Dim sAcct As String = String.Empty
Dim sUD1 As String = String.Empty
Dim sflow As String = String.Empty
Dim sUD2 As String = String.Empty
Dim sUD4 As String = String.Empty
								
								
	Using dbConnApp As DBConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
																	
		Dim sql As New Text.StringBuilder
		Dim x As Integer = 0
																		
		For Each Cell As DataBufferCell In SrcDataBuffer.DataBufferCells.Values
										
									
		'Execute If Allocation percentage Is Not equal To 0
		If Cell.CellAmount <> 0 Then 										
									
		'x = x + 1	
		'sEntity = Cell.DataBufferCellPk.GetEntityID(api)
		sAcct = Cell.DataBufferCellPk.GetAccountName(api)
		sUD1 = Cell.DataBufferCellPk.GetUD1Name(api)
		sflow = Cell.DataBufferCellPk.GetflowName(api)
		sUD2 = Cell.DataBufferCellPk.GetUD2Name(api)
		sUD4 = Cell.DataBufferCellPk.GetUD4Name(api)									
										
		'End If
									
'Next
																			
	sql.AppendLine($"INSERT Into XFT_BSM_Mapping_Total5")
	sql.Append($"([BU], [Entity], [BSM_Account], [BSM_Measure], [Responsibility_Center], [Company_Code], [Account],[Allocation_Percentage])")					
	sql.Append($"Values ('{sqlentity}','{sEntitymember}', '{sUD4}', '{sflow}', '{sUD2}', '{sUD1}', '{sAcct}', '{Cell2.CellAmount}')")
									
	End If
									
	brapi.ErrorLog.LogMessage(si,x)
									
	Next
									
	BRAPi.Database.ExecuteSql(dbConnApp,sql.ToString, True)
												
	End Using

       

      • RobbSalzmann's avatar
        RobbSalzmann
        Valued Contributor II
        9 months ago
        Dim valueList As List(Of String) = New List(Of String)
Dim x As Integer = 0

For Each Cell As DataBufferCell In SrcDataBuffer.DataBufferCells.Values
    ' Execute If Allocation percentage Is Not equal To 0
    If Cell.CellAmount <> 0 Then			            
        x += 1
        sAcct = Cell.DataBufferCellPk.GetAccountName(api)
        sUD1 = Cell.DataBufferCellPk.GetUD1Name(api)
        sFlow = Cell.DataBufferCellPk.GetFlowName(api)
        sUD2 = Cell.DataBufferCellPk.GetUD2Name(api)
        sUD4 = Cell.DataBufferCellPk.GetUD4Name(api)
		valueList.Add($"('{sqlentity}','{sEntitymember}', '{sUD4}', '{sFlow}', '{sUD2}', '{sUD1}', '{sAcct}', {Cell.CellAmount})")
    End If
Next

Dim insertValues = String.Join(", ", valueList)

Dim sql As String = $"INSERT INTO XFT_BSM_Mapping_Total$ 
                     ([BU], [Entity], [BSM_Account], [BSM_Measure], [Responsibility_Center], [Company_Code], [Account],[Allocation_Percentage])
                     VALUES {insertValues}"
brapi.ErrorLog.LogMessage(si,x)

Using dbConn As DBConnInfo = BRAPi.Database.CreateApplicationDBConnInfo(si)
	BRAPi.Database.ExecuteSql(dbConn , sql, True)
End Using

         

  • RobbSalzmann's avatar
    RobbSalzmann
    Valued Contributor II
    9 months ago

    A couple of the variables are declared and assigned out of frame so I can't test compile.

    • Kneelay's avatar
      Kneelay
      New Contributor II
      9 months ago

      Thanks Robb , I had to make a few edits, but this seemed to do the trick the first time. However, I refreshed a few times afterwards and the table count changed / deleted rows. Initially 112 was added which was expected. Then 112 again, but afterwards then it showed 40 and other smaller counts again. I created a new table with the same column names but even when executing then I only 28. This is similar to what I saw before, odd behavior why it works sometimes but not others and not sure what would be causing that.