Supervision Server for Drilldown and Drillthrough

See also Supervision Server (SVS)

Drilldown: expenditure of a consolidated figure along the hierarchy definition to see additional details of the subsequent level (the children of the consolidated element). The opposite of this term is "Roll up".

Drillthrough: expenditure of a figure by querying another data source providing more details than the initially used data source.

The required files for both processes contain the phrase "drill through" in their names. Therefore you cannot deduce the running process from the file name. The same applies to the context menu command "drill-through".

If you have not checked "Enable DrillDown" during installation and you want to enable "Drill Through/DrillDown" later, you have to execute the following steps:

    Change the entry of the file <Install_path>svs\sep.inc.php to:
    <?php
    include './sample_scripts/sep.inc.drill_through.php';
    ?>     Add the following 3 lines to the palo.ini configuration:
    Line 1 on Windows OS: worker "<install_path>\svs\SupervisionServer.exe"
    Line 1 on 32-bit Linux OS : worker /svs-Linux-XXXX/SupervisionServer.exe (replace XXXX with i386, i486, i586 or i686 - see your svs directory name)
    Line 1 on 64-bit Linux OS: worker /svs-Linux-x86_64/SupervisionServer.exe
    Line 2 on Windows and Linux: workerlogin information
    Line 3 on Windows and Linux: enable-drillthrough

The workerlogin entry is required when configuring the drillthrough functionality. The JedoxSuiteMolapService must be restarted after the changes.

Drillthrough and drilldown functionality both fetch and process potentially large datasets, which raise the RAM usage of the Supervision Server process. The amount of RAM which Supervision Server is allowed to consume is limited to prevent exhaustion of the system's resources. It is recommended practice to include size limitations in drillthrough scripts, and prevent fetching of data if these size limits are surpassed. If necessary, the memory limit of Supervision Server can be increased by modifying the "memory_limit" parameter in the php.ini configuration file of Supervision Server.

Note that if SVS reaches the memory limit, this represents a fatal error. Execution of the script is then interrupted. Fatal errors in the PHP interpreter used by SVS are not recoverable, and the OLAP Server is shut down.

Drilldown functionality

The standard sample sep.inc.drill_through.php that is installed with Setup (with checkmark "Enable DrillDown") does drill down to base cells of the cube. This works for every cube, and Jedox Integrator (ETL) is not required. In SVS script, you can configure the maximum number of rows that are returned.

Drillthrough functionality

To perform drillthrough a user must have at least R rights on a specific cell. For more information about user rights, see Rights with Impact on Jedox Integrator.

The standard sample sep.inc.drill_through.php is installed with Setup (with checkmark "Enable DrillDown").

Note that the Supervision Server script can be adapted, such as to set a maximum number of returned rows for a drillthrough request or to disable drillthrough in some cases (e.g. for a particular cube or cube slice). The default upper maximum for returned rows defined in the shipped drillthrough scripts is 1000 rows.

The timeout for a drillthrough request is 60 s (the default socket timeout in PHP). The timeout can be increased by modifying the “default_socket_timeout” parameter in the php.ini configuration file of Supervision Server.

Example

For the following example, you will need Jedox Web and Jedox Excel Add-in.

Start Jedox Web and open the Integrator. Import the project <install_path>\tomcat\webapps\etlserver\data\samples\sampleDrillthrough.xml. Before you can start sampleDrillthrough, you must run the sampleBiker jobs "Initdata" and "default" (in this order) to create the necessary dimensions.

Next, execute the job "DrillthroughPersist" for the newly loaded project. This job will create the new Jedox database Biker_ETL with the cube Order2. The Order2 cube has drilldown functionality.

Afterwards, open the file <install_path>\svs\sample_scripts\sample_drill_through.xls. In this file, you will find a view of the new database Biker_ETL. Then, right-click on the shown cell and select “Drill Through” from the context menu. A new Excel worksheet will open, displaying the detailed source data for the cell you have selected.

It is worth mentioning that the example importBikerDrillthrough only covers a certain slice of the cube Biker_ETL; not all cells in this cube will have drilldown data.

Note

  • The sample drillthrough scripts for the Supervision Server have been changed to support usage of Virtual Dimensions; therefore, custom scripts for drillthrough may require adjustment.

Updated September 27, 2022