Skip to main content

Tips & Tricks: Individual Selection as Performance Optimizer or Killer

In arcplan, the individual selection constitutes a fixed list of elements which were selected by the developer. Up to arcplan Enterprise 6, a database query for an individual selection did not take place. Thus, this could be used for performance optimizations or for a pre-selection. However, customers frequently requested an add-on for updating the displayed texts.  As a database query did not take place, there was no update here, either. For example, a typo corrected in the database was not updated in a performance indicator as it was not screened against the database. From a performance perspective, this was optimal, as well.

Since arcplan 7, this behavior was changed due to the customer requests. Unfortunately, this may lead to severe performance issues in case of MDX based connectors (e.g. SAP BW or SQL Server Analysis Services). The MDX query which is generated in order to update the texts now screens the entire cube almost without any limitation. The query may be viewed in the database dialogue. 

Example 1

An individual selection for definition of a date requests the related data.  The MDX shows that all performance indicators of the third quarter 2007 are identified just to update this one text. 

 

 

 

Here the results of the query in the SQL Server Management Studio.

 

 

Example 2

The performance indicator dimension was selected and limited. The system now identifies the performance indicator values and names without any limitations. 

 

 

 

As a result, this report would accordingly contain two additional database queries as from arcplan 7. Depending on the complexity, this may, of course, grow indefinitely.

 

 

 

As mentioned in the title, it is unclear, however, whether the individual selection will become a performance killer or whether it may be possible to use it for optimization, after all. By an adjustment of the arcplan settings file, a function can be activated which is able to restore the old behavior, if desired per object. In this context, it is important to note in case of an upgrade from an old version that this is a manual process and must be carried out for each relevant individual selection.                                                                 

How to restore the Behavior of arcplan 6 or older in newer Versions

1. Activation of the functionality in settingsDesigner.xml and settingsServer.xml
In order to activate the functionality, the following command must be added in the settings:

<option optionType="bool">
      <key>USEAUTOUPDATEPROPERTYFORINDIVIDUALSELECTION</key>
      <value>1</value>
 </option>

2. Restart arcplan Designer and the server

3. Set the database properties of the object with the individual selection to "no automatic update". 

 

 

After restarting the application, verify that a query is no longer triggered. (Note: if the object was already queried, the query does not disappear. The file must be re-opened for this purpose. As a precaution, the application should be completely shut down.) 

 

 

Thus, the old behavior is available if desired which provides more flexibility during development. However, it is essential to remember this for developing applications and in particular for upgrades from an older version.