previous article
arcplan applications frequently offer the possibility for a user to rewrite by means of inputs into the application and/or the underlying database. This is particularly the case with forecasting applications, but in simplified form also with comment inputs.
Thus, in case of inputs, there always is a danger that two people are making an input regarding the same context (one input mask with input-relevant filter selection) at the same time and thus overwrite or unintentionally block one another. This is particularly difficult in case of input masks with many input fields as the input process may take slightly longer and/or in case of masks whose inputs build on one another (frequently in forecasting applications).
Using the example of the SQL server as backend, the following will illustrate how inputs in arcplan applications can be made transaction-secure in order to prevent simultaneous inputs within the same context.
Scenario
The scenario is based on an analysis services cube with adventure works demo data and shows a simple report with the temporal development of turnovers for different product categories. It is possible to select a month and/or a region by means of two filters.
In addition to the illustration of the values, a comment is shown depending on the filter selection. A separate comment can be entered for each month and/or region. The input is made via a separate report which is opened in dialogue mode via the opened report, just like a popup.
In this process, the comments are written into the SQL server database. This database is also used for the transaction security function.
The objective is that at any point in time only one person or session has the possibility to make an input regarding a combination of month and region (context). The input is thus intentionally blocked (Lock) if an input is made in a different session at the same time.
As soon as the month and/or the region differ from one another, the parallel input is uncritical, as the inputs are saved separately in this scenario and thus there cannot be an overwriting or unintentional blocking (different context).
Implementation
The implementation is made in three steps:
- Writing a lock when opening the input and deleting the lock upon closing
- Checking for locks prior to opening the input
- Removing locks if they have not been deleted correctly
Writing a lock when opening the input and deleting the lock upon closing
Firstly, a lock table is created in the SQL server. Here, a data set should be written at the time of the input which includes the person, the point in time, the filter combination and information about the session. Through the existence of such a data set a lock is marked. As soon as the input is closed, the data set is deleted.
If a logging of inputs is required or if such a function exists, it should be checked whether both functions can be combined.
In our scenario, the table has the following columns:
Commentary_lock_id: Unique ID (primary key and identity specification)
- region: filter value of region filter
- month: filter value of date filter
- set-up_user: the registered user in the arcplan application
- set-up _date: date of data set creation
- arcplan_sid: session ID by arcplan (information purpose)
- arcplan_process_id: process ID by arcplan (information purpose)
- spid: session ID by SQL-Server
- login_time: time stamp from login to spid
Filter values, spid and login_time are required in the table. The other columns are used for information purposes and/or output texts.
The table is empty at first and at most points in time. The data sets are written when the comment input is opened.
In the example, the input is made by means of a stored procedure. The required parameters such as filter values, user and the arcplan session and process ID are transferred thereto. In the secenario, the user is transferred from the arcplan application. This is required as in this scenario, as in many arcplan applications, a technical DB user is used for database access. Provided that each session has its own DB user, one could also use the DB internal methods for detecting the DB user. In the scenario, the user is transferred from the arcplan application, as access to the metadata database requires a technical user. This technical user is the same for all users provided the the SQL server functions are used to detect the user in advance.
Within the procedure, the SPID is supplemented and the data set is written into the table stating the current date. By changing the transaction isolation mode, the creation of the lock data set is transaction-secure even within the database.
After that, the input mask is opened in the dialogue mode. Due to the dialogue mode, no other document can be activated until the dialogue document has been closed via the function CLOSE. Thus, the input can only be quit by a close control panel and the process can be controlled in this way.
This close control panel includes the execution of a SQL function by which the lock data set is deleted. For this purpose, the current ID (description in the following step) is detected by means of a function and a dynamic DELETE statement is generated.
Checking for locks before opening the input
By means of the previous steps, it can be detected at any time whether an input regarding a particular context is active or not. In the following, this is used in order to only permit inputs if they are not locked by a different parallel session.
In the report, it is checked whether there already is a lock data set by clicking on the input control panel prior to opening the input mask. In this scenario, this is carried out by a user-defined function:
As the function is also used for the targeted deletion (see above), it returns the ID of the detected data set. If there is no data set available, `0` - and thus a numerical value - is returned in each case.
Depending on the result, the comment mask is either opened as described above or an error message is returned. The error message can also be enriched by further information from the lock data set. In the scenario, the user and the time of the set-up of the lock data set are also returned.
Removing locks which have not been deleted correctly
So far, the implementation is basically sufficient. However, it may happen that a session is terminated during the input for various reasons. In this case, the lock data set remains in place and can no longer be removed by the previous functionalities.
In order to automatically remove these data sets, a further procedure has been created which is always executed prior to opening the comment mask. As the table is usually very small, this has no substantial impact on performance.
In order to identify inactive lock data sets, the system view sys.dm_exec_sessions is used here. In addition to SPID (the SQL server session ID), the login time for the session is also stated here. This is required as the SPID is newly assigned very quickly and thus is not sufficient to identify inactive sessions by itself.
Conclusion
The described scenario illustrated how transaction-secure inputs can be created. The objective was to show the pertinent considerations and a basic illustration of the functionality.
In this example, SQL server functionalities have been used heavily. However, this is not mandatory. Most functions can also be implemented within arcplan or in other databases. The structure would be quite similar.
