04-03-2024 11:32 AM
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?
Solved! Go to Solution.
04-05-2024 06:12 PM - edited 04-05-2024 06:20 PM
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
04-03-2024 01:01 PM
@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.
04-03-2024 04:06 PM
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.
04-04-2024 01:51 PM
Are you currently importing the Cube Data into the Actual Table ?
04-05-2024 05:50 PM
Yes trying to load from the cube to the table.
04-03-2024 04:31 PM - edited 04-03-2024 04:33 PM
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.
04-05-2024 05:51 PM
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.
04-05-2024 05:52 PM - edited 04-05-2024 05:53 PM
Post your code so far. I can give you a hand with it. About how many data cells are you inserting?
04-05-2024 05:55 PM
Posted below to my reply to Jack. For this particular case should be 112. It seems if it's less records it's doing it correctly.
04-04-2024 11:36 AM
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:
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.
04-05-2024 05:53 PM
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.
04-05-2024 05:55 PM
Its a lot easier for me to edit,if you post the code as text vs a screen shot.
04-05-2024 06:05 PM
'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
04-05-2024 06:12 PM - edited 04-05-2024 06:20 PM
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
04-05-2024 06:21 PM - edited 04-05-2024 06:23 PM
A couple of the variables are declared and assigned out of frame so I can't test compile.
04-05-2024 06:57 PM
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.