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 through the forecast periods (spanning two years) to retrieve the values. While the code works, its performance is not good.

Is there a way to fetch or look up the text properties by time (possibly from a specific table) without iterating through all periods? Any command or optimized approach to achieve this efficiently would be highly appreciated.

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, 1, -1, myVaryByTimeId)

Application Properties

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

M_L_Spencer
New Contributor II
4 days ago
Hi there, since this is just for reporting, have you tried to build a dynamic UD8 member to show this? If so, you should be able to do something along the following:
If api.View.IsAnnotationType() Then
Dim iUD1 As Integer = api.Pov.UD1.MemberId
Dim povUD1Text1 As String = api.UD1.Text(iUD1,1,api.Pov.Scenario.MemberId,api.Pov.Time.MemberId)
Return povUD1Text1
rhankey
Contributor II
4 days ago
The code fragment you show is full of inefficiencies. Having said that, for it to be taking 10 minutes to extract a single Text property for a set of members over 24 months, all I can imagine is you are running this for many thousands of members - perhaps 10's of thousands of members.
The most obvious inefficiency is the SQL INSERT statement. Inserting a large quantity of rows into a SQL table one row at a time is the most inefficient way to get the job done and will be a huge performance killer, potentially impacting performance to other users too. Assuming you are writing to an MS SQL Table, you would see a big improvement inserting multiple rows of values in a single INSERT statement, which will be bulk inserted as a set, which will improve performance. There are some non-MS SQL engines that will still split multiple rows of values into separate insert statements, resulting in no performance gain. If combining multiple rows into a single INSERT statement, you also have to be careful how big the text string becomes or how many rows you include, as there can be limits on that too,
However, if you are consuming this result set into a Dashboard Data Adapter, then why do you need to be outputting the data to an intermediary SQL table at all? A Dashboard Data Adapter ultimately consumes a DataSet or DataTable. Rather than writing to a SQL table, collect the results into a DataTable, keeping the result set fully in memory, and when done, simply return the DataTable to the Data Adapter. Without seeing the rest of your code, I'm guessing this one change will make a huge improvement.
If you are determined to write the data into a SQL Table, then collect the results into a DataTable, then at the end, use BRApi.Database.SaveCustomDataTable() to bulk insert the entire result set contained in the DataTable to the SQL table. That will bulk insert the rows into the SQL table in the most efficient manner. That will have a big performance gain.
And if you are absolutely determined to write the data into a SQL table, you really should be using the dbParamInfos parameter to minimally pass-in the Text property value to ExecuteSQL() or ExecuteActionQuery(). With the way you have written your code, you make it way too easy for someone to inject a SQL Statement in the Text property value that could do something very harmful, like wipe your entire database. Some would say that you should pass all parameters through via dbParamInfos, but the other parameters you are passing can't really be compromised for SQL Command Line Injection. This recomendation has nothing to do with performance, though it could if they wipe your DB out.
If keeping everything in an in-memory DataTable does not offer a big enough gain, then the next change would be perform this code in a Finance business rule where you can use api rather BRApi calls. The api calls can access all the metadata via in-memory cache, whereas BRApi read everything from underlying OS SQL tables. If you ultimately still need to be consume the result set into a Dashboard data adapter, you'd need the Data Adapter to call a Custom Calculate Data Management package for a single Data Unit. The invoked Finance Business rule would need to return the resulting DataTable via a Global variable so the calling Data Adapter can retrieve and surface it. This is a full in-memory option, and would likely bring the overall runtime down to seconds.
Alternatively, you could handle this in a Cube View, using a UD8 DynamicCalc member. But be very sure only to use api calls rather than BRApi calls. This ought to run fairly quickly, as it would be all in-memory, but with a bunch of overhead.

RobbSalzmann

Valued Contributor II

4 days ago

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

dinishkrishna
New Contributor II
4 days ago
Thanks for the responses! I realize I could have provided more context. This code snippet is part of a larger solution involving a dashboard where text properties can be updated by time. Once updated, the new values are displayed back at the same dashboard. This specific portion of the code handles displaying those updated values. I agree that the main inefficiency lies in the SQL INSERT statement. I'll update my code with this change to see if it improves performance and will keep you posted.
Also, just to clarify - I’m not using "Date" as an object type name in my code. It was originally DateStr2 (since I had DateStr1 elsewhere in the code), but I changed it to "Date" just for posting this snippet here.

RobbSalzmann
Valued Contributor II
4 days ago
The code in the green box should be (no quotes around date):
Dim strDate as String = $"{year}M{month}"
Dim timeID As Integer = TimeDimHelper.GetIdFromDate(strDate)

Also, you should not give a primitive another object type as its name. e.g. Date. Choose instead something like strDate.
Henning
Valued Contributor II
4 days ago
Hi, not sure what you are trying to achieve here exactly.

At first glance I would suggest a different approach. Create a cube view with Time in the columns and UD1 members in the rows.

Then, create a dynamic UD8 member that returns the string value of the UD1 text property as annotation (set the CV View to Annotation). Then you see each UD1 text attribute by period in your report.

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

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

Forum Discussion

Fetching text property by time

Related Content

Application Properties

Create a custom drop down list (Entity) of Text 2 Property

Filter IC Dimension by Entity Property

Extender: Auto Update Member Property

Cube Properties Best Practices!