Forum Discussion
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...
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
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.