HFM: How to quick sort for high-performing and user-friendly member lists

Do you have hundreds or even thousands of members in your dimensions? Users easily lose time looking for the single member that they need to complete their reporting. The most effective way to handle this is with Quick Sort.

In most dimensions in HFM, you can decide the sort order of the members by yourself and apply it by loading metadata in Replace mode.

There are some cases where this doesn’t fly:

  • In some dimensions, you can’t sort members. An example is ICP where the members always appear in the order that you have added them.
  • In some situations, you might be updating metadata in Merge mode.
  • Although there are ways to do it with tools, sorting manually can be time-consuming and error-prone.

One way to get around the sorting and to improve user experience in HFM is to show members in alphabetical order using member lists. However, especially with a higher number of members, the conventional methods for sorting are often slow.

Let’s pick an example: running an FR report. The following steps contribute to the time it takes for the report to run:

  1. sorting members
  2. fetching data from the database
  3. rendering the report, performing calculations on the report, etc.

Additionally, there are infrastructure, network, and other such elements having an impact on performance. In this blog I concentrate on the first point, so bear in mind that this might not solve all the problems related to report performance.

The best way to sort a member list is to first include the desired members into a temporary array. Secondly, you sort the array. Finally, you loop through the now sorted array and create the HFM member list.

Sorting the array takes the most time. The traditional sort method takes the next member and starts running through the array of members and places the member after the member where it fits in the alphabet. It’s highly inefficient.

Quick Sort is different. It splits the array into two and starts looking for which part the member belongs to and makes the swap. Watch this tutorial video about the principle:

To implement the Quick Sort method into member lists, you need to paste the following code block into your member list file and call the sort from the member list code in Sub EnumMembersInList(). In the call, you need to give the name of the temporary array, index number of the first and the last members. The code takes care of the rest.

Sub QuickSort(vArray, inLow, inHi)
      Dim pivot   'the pivot point to split the list into two parts
      Dim tmpSwap 'hold array content that will be swapped
      
      Dim tmpLow  'pivot's lower point
      Dim tmphi   'pivot's higher point
      
      'set initial values for the working variables are the Lbound and UBound from the list
      tmpLow = inLow
      tmpHi = inHi
      
      'split the array into two parts
      pivot = vArray((inLow + inHi) \ 2)
      
      'start comparing the first side of the list
      While (tmpLow <= tmpHi)
      
            'While tmpLow remains less than tmpHi
            While (vArray(tmpLow) < pivot And tmpLow < inHi)
                  tmpLow = tmpLow + 1
            Wend
            
            'Repeat the same for the array value at position tmpHi
            While (pivot < vArray(tmpHi) And tmpHi > inLow)
                  tmpHi = tmpHi - 1
            Wend
            
            'When the position of tmpHi exceeds or matches tmpLow swap the two items
            If (tmpLow <= tmpHi) Then
                  
                  'assign vArray(tmpLow) to tmpSwap
                  tmpSwap = vArray(tmpLow)
                  
                  'swap vArray(tmpHi) for vArray(tmpLow)
                  vArray(tmpLow) = vArray(tmpHi)
                  
                  'assign tmpSwap back to vArray(tmpHi)
                  vArray(tmpHi) = tmpSwap
                  
                  'adjust the new Hi and Low values
                  tmpLow = tmpLow + 1
                  tmpHi = tmpHi - 1
            End If
      Wend
      
      'If the original lower is less than tmpHi, call the routine again with inLow & tmpHi
      'as the pivot's lower and upper points
      If (inLow < tmpHi) Then QuickSort vArray, inLow, tmpHi
      
      'if the new tmpLow value lower is less than the original inHi, call the routine again with
      'tmpLow & inHi as the pivot's lower and upper points.
      If (tmpLow < inHi) Then QuickSort vArray, tmpLow, inHi      
End Sub

inlumi can help you with HFM member lists or any other consolidation issue with HFM, FCCS, OneStream or CCH Tagetik. Don’t hesitate to contact me or the inlumi experts near you.

 


About the author

Lauri Järvinen
Principal Consultant at inlumi
lauri.jarvinen@inlumi.com

Lauri is an experienced professional in building consolidation solutions. He has over ten years of experience in developing and maintaining Oracle Hyperion Financial Management solutions. Lauri has a keen eye for the links and dependencies between the technical solution and the process. He is development-driven and always eager to learn.