Forum Discussion

dinishkrishna's avatar
dinishkrishna
New Contributor II
6 days ago
Solved

Fetching text property by time

  Hi,  I'm trying to display a time-variant text property of the UD1 dimension member in a report. The challenge is that the text value can exist in any period of the member, so I currently loop thr...
Application Properties
Cubes
Metadata
Planning
  • RobbSalzmann's avatar
    RobbSalzmann
    4 days ago

    Whilst the code you posted is a bit inefficient, I can't see a reason this loop would take 10 minutes unless you're running the insert query on every iteraton of the loop.   Here are some ways you might optimize this, including construction of a single query for all inserts that would run at the end of the loop as a batch insert using a DataTable object.  Also, added a convenience TimePeriod struct object for better readability and corrected the TimeDimHelper call & strDate part mentioned above.  Hopefully this can help you.

    Dim strStartPeriod As String = "2025M1"
Dim strEndPeriod As String = "2026M12"

Dim mbrId As Integer = 12345
Dim Mbr As String = "SalesGrowth"
Dim desc As String = "Sales Growth Trend (YoY)"

' Call the subroutine to build and run the SQL
RunInsert(si, mbrId, Mbr, desc, strStartPeriod, strEndPeriod)

Public Sub RunInsert(si As SessionInfo, mbrId As Integer, Mbr As String, desc As String, strStartPeriod As String, strEndPeriod As String)
	Dim startPeriod As New TimePeriod(strStartPeriod, si)
	Dim endPeriod As New TimePeriod(strEndPeriod, si)

	Dim timeIdCache As New Dictionary(Of String, Integer)
	Dim dt As New DataTable("MemberTextbyDate_Table")

	' Define columns expected by the destination table
	dt.Columns.Add("MemberId", GetType(Integer))
	dt.Columns.Add("MemberName", GetType(String))
	dt.Columns.Add("MemberDescription", GetType(String))
	dt.Columns.Add("Text", GetType(String))
	dt.Columns.Add("Date", GetType(String))

	For year As Integer = startPeriod.Year To endPeriod.Year
		Dim maxMonth As Integer = If(year = endPeriod.Year, endPeriod.Month, 12)
		Dim minMonth As Integer = If(year = startPeriod.Year, startPeriod.Month, 1)

		For month As Integer = minMonth To maxMonth
			Dim strDate As String = $"{year}M{month}"

			Dim timeID As Integer
			If Not timeIdCache.TryGetValue(strDate, timeID) Then
				timeID = TimeDimHelper.GetIdFromName(strDate)
				timeIdCache(strDate) = timeID
			End If

			Dim myVaryByTimeId As Integer = BRApi.Finance.Members.GetMemberId(si, DimType.Time.Id, strDate)
			Dim mbrText As String = BRApi.Finance.UD.Text(si, 9, mbrId, 2, -1, myVaryByTimeId)

			If Not String.IsNullOrWhiteSpace(mbrText) Then
				dt.Rows.Add(mbrId, Mbr, desc, mbrText, strDate)
			End If
		Next
	Next

	If dt.Rows.Count > 0 Then
		Dim dbLocation As String = "App:MemberTextbyDate_Table" ' or wherever your table is defined
		Dim useBulkInsert As Boolean = True

		Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
			BRApi.Database.SaveCustomDataTable(si, dbLocation, dbConnApp, dt, useBulkInsert)
		End Using
	End If
End Sub

' -------------------------------
'         TimePeriod struct
' -------------------------------
Public Structure TimePeriod
    Public Property Year As Integer
    Public Property Month As Integer
    Public Property Id As Integer

    Public Sub New(name As String, si As SessionInfo)
        If Not Regex.IsMatch(name, "^\d{4}M\d{1,2}$") Then
            Throw New ArgumentException("Invalid period format. Expected 'YYYYM#'.", NameOf(name))
        End If

        Year = Integer.Parse(name.Substring(0, 4))
        Month = Integer.Parse(name.Substring(5))
        Id = BRApi.Finance.Time.GetIdFromName(si, name)
    End Sub
End Structure

     

SimonHesford's avatar
SimonHesford
Contributor II
5 days ago

Hi,

When you say performance is not good, what do you mean can you quantify? Also can you paste the code snippet you are using and is proving to be inefficient?

 

dinishkrishna's avatar
dinishkrishna
New Contributor II
5 days ago

It takes around 10 minutes to loop through 2 years. I'm inserting the values into a table and displaying the results of that table in a dashboard. Here is my Dashboard Extender code:

    
Dim strStartPeriod As String = "2025M1"
Dim strEndPeriod As String = "2026M12"
Dim strStartPeriodId As Integer = BRApi.Finance.Time.GetIdFromName(si,strStartPeriod)
Dim strEndPeriodId As Integer = BRApi.Finance.Time.GetIdFromName(si,strEndPeriod)
Dim startYear As Integer = Integer.Parse(strStartPeriod.Substring(0, 4))
Dim startMonth As Integer = Integer.Parse(strStartPeriod.Substring(5))
Dim endYear As Integer = Integer.Parse(strEndPeriod.Substring(0, 4))
Dim endMonth As Integer = Integer.Parse(strEndPeriod.Substring(5))

For year As Integer = startYear To endYear
    Dim maxMonth As Integer = If(year = endYear, endMonth, 12) ' Stop at endMonth in the final year
    Dim minMonth As Integer = If(year = startYear, startMonth, 1) ' Start at startMonth in the initial year
    
For month As Integer = minMonth To maxMonth
Dim priorMonth As Integer = 0
Dim priorYear As Integer = 0

If month = 1 Then 
priorMonth = 12
priorYear = Year - 1
Else
priorMonth = month -1
priorYear = year
End If

        Dim Date As String = $"{year}M{month}"   
Dim timeID As Integer = timedimhelper.getidfromname("Date")
Dim myVaryByTimeId As Integer = BRApi.Finance.Members.GetMemberId(si,DimType.Time.Id, Date)
Dim mbrText As String = BRApi.Finance.UD.Text(si, 9, mbrId, 2, -1, myVaryByTimeId)

If Not String.IsNullOrWhiteSpace(mbrText) Then

Dim sqlQuery As New Text.StringBuilder
sqlQuery.AppendLine("INSERT INTO MemberTextbyDate_Table ")
sqlQuery.AppendLine("(MemberId , MemberName, MemberDescription, Text, Date) ")
sqlQuery.AppendLine($" VALUES ('{mbrId}', '{Mbr}', '{desc}', {mbrText}','{Date}'); ")
sqlUpdate.AppendLine(sqlQuery.ToString)

End If

Next
Next