icCube

Documentation

Schema Management

Deployment

Schemas are created and edited via the builder application. Schemas being edited are not available yet for loading; they must first be deployed. Once deployed those schemas become available for load and are loaded by reading their XML definition file and loading the data from their data sources.

Deployed and builder schemas reside in two separate directories: respectively /cubes and /builder. The actual location (icCube.xml configuration) of these directories are available via the icCube IDE in Monitoring / Configuration / Directories section.

Graphical Interface

The icCube IDE the Monitoring / Schema section allows for managing deployed schema. You can (re)load, unload, undeploy schemas. Undeploy a schema means removing the schema definition from the deployed schema directory (the schema will not be available anymore for loading).

The icCube IDE the Monitoring / Schema / Offline and / Backup gives access to offline snapshots and schema backups .

The icCube IDE the Monitoring / Schema / Loaded section gives access to several load schema statistics: e.g., estimated memory footprint, dimension/facts information, sparsity, etc... Access to its XML definition is available from there as well.

Scheduler

icCube is embedding a scheduler allowing for loading schemas using several trigger rules (e.g., once, daily, fixed rates...). See the following page for more details.

XMLA Commands

Schemas can be managed using XMLA commands. For the sake of simplicity these commands are regular XMLA <Execute> statements. Unless specified otherwise, the returned dataset contains a single cell of type string containing the status of the operation. In case of error more details is available through the monitoring WEB interface.

The following commands are currently available:

  • FULL_LOAD_SCHEMA
  • LOAD_SCHEMA
  • LOAD_SCHEMA_FROM_XML
  • LOAD_PARTITIONS
  • LOAD_MEASURE_GROUP
  • UNLOAD_SCHEMA
  • UNLOAD_PARTITIONS
  • RESTORE_SCHEMA
  • LIST_SCHEMA
  • LIST_SCHEMA_EX
  • LIST_SCHEMA_BACKUP
  • DELETE_SCHEMA_BACKUP
  • LIST_SCHEMA_PARTITION

Parameters/Arguments: use the MDX notation [] for identifiers having spaces (or any other MDX separator) in their names. For example the following command allows for loading the schema called "Sales 2016":


    LOAD_SCHEMA [Sales 2016]
    

Response Format: LOAD / RESTORE / UNLOAD responses are as following (see each command for more details):


    /**
     * Unload successful.
     */
    UNLOADED,
    /**
     * Full load successful.
     */
    LOADED,
    /**
     * A load request has been CANCELLED while waiting for a duplicate loading.
     */
    DUPLICATED_LOADING_CANCELLED,
    /**
     * A blocked (due to simultaneous load limit reached) load request has been CANCELLED.
     */
    WAITING_LOADING_CANCELLED,
    /**
     * An error occurred during the load of the schema.
     */
    LOAD_ERROR,
    /**
     * Incremental load successful.
     */
    INCREMENTAL_LOADED,
    /**
     * Incremental load has been deactivated due to load error: this status means nothing has been done.
     */
    INCREMENTAL_LOAD_DEACTIVATED,
    /**
     * An error occurred during the incremental load of the schema: the state of the schema is uncertain.
     */
    INCREMENTAL_LOAD_ERROR,
    /**
     * Unload partitions successful.
     */
    PARTITIONS_UNLOADED,
    /**
     * Load partitions successful.
     */
    PARTITIONS_LOADED,
    /**
     * (Un)Load partitions on error: the schema state is still consistent (e.g., SELECT error, authorization error, etc...).
     */
    PARTITIONS_LOAD_FAILED,
    /**
     * (Un)Load partitions on error: the state of the schema is uncertain.
     */
    PARTITIONS_LOAD_ERROR
    

Note that when the schema has activated its backup, the status returned by the load commands (i.e., full load, incremental load, load partitions and measure group) contains the backup timestamp as well:


    LOADED:2016-04-27 11:50:10 863 UTC
    INCREMENTAL_LOADED:2016-04-27 11:55:10 833 UTC
    PARTITIONS_LOADED:2016-04-27 12:55:10 163 UTC
    

In case the backup could not be generated it contains a message starting with "err:". Note that a backup on error does not mean the schema has not been loaded:


    LOADED:err:backup error - check logs for more details
    

LOAD_SCHEMA

  • [waitS:n] (optional)
  • [cancelS:n] (optional)
  • keepMdxResultCache (optional)
  • schema_name
  • schema_definition_file_name (optional)

Load the schema. If the schema is already loaded, it is reloaded. Note that if the schema has activated the incremental load then the reload is actually an update of the schema. You'd have to use FULL_LOAD_SCHEMA to perform a full reload in that case.

Use the MDX notation [] for schemas having spaces (or any other MDX separator) in their names. For example the following command allows for loading the schema called "Sales 2016":


    LOAD_SCHEMA [Sales 2016]
    

To load the schema, icCube is scanning the content of the /cubes directory (i.e., deployed schemas) to located the XML definition file (.icc-schema file) containing the schema whose name is given by the command.

If the name of the schema definition file does not follow the icCube format (i.e., derived from the schema name) and having a very large number of schema definition files, it is recommended to pass the name of the schema definition file as parameter as well. This will avoid a scan of the /cubes directory.

The optional waitS and cancelS parameters allows for cancelling ongoing requests. A lengthy request might prevent the actual load (e.g. incremental load, re-load, etc..) of the schema. Unless specified otherwise loading a schema (or partitions) is performed in two steps. The first step is loading the required data and the second step is actually updating the already loaded schema. The first step can be performed in parallel of ongoing MDX requests but the second step requires no more requests are accessing the schema. Therefore, the load is stuck until all requests are completed. These two parameters allows for cancelling the requests before the second step is performed. Before the second step, icCube waits for waitS seconds, then if it cancels the ongoing requests (if any) and waits for cancelS seconds. Then if there is still some ongoing requests the load fails otherwise icCube proceed with the second step.

For example the following command incrementally loads the schema "Sales 2016" waiting 30 seconds that any ongoing requests are completed and cancel any remaining requests (with a timeout of 10s):


    LOAD_SCHEMA [waitS:30] [cancelS:10] [Sales 2016]
    

Note that the log file icCube-cancel-request.log contains the MDX requests that could not be properly cancelled. This file can be exported as the regular icCube log files (www).

The optional keepMdxResultCache parameter allows for restoring the MDX result cache once the incremental load has been performed if required. That is, existing cached requests are re-executed and cached.

LOAD_SCHEMA_FROM_XML

  • write:filename (optional)
  • schema_name
  • schema_definition

Similar to the LOAD_SCHEMA command it reads the schema XML definition from the second parameter instead of from an .icc-schema file. This command allows for loading a schema whose definition has not been deployed. This command will not deploy the schema definition. The second parameter should be exactly as the content of a .icc-schema file:


    LOAD_SCHEMA_FROM_XML sales <?xml version="1.0" encoding="UTF-8" standalone="yes"?><schemaFactory...
    

Depending on the actual client used to send the XMLA request, it might be required to escape the actual XML schema definition; but there is no specific requirement. E.g., using C#, the ' character must be escaped as the command is passed as a ' ... ' string.

The option write parameter allows for saving the XML definition within the cubes directory of icCube. Any existing file will be overwritten. The file name cannot have space.


    LOAD_SCHEMA_FROM_XML write:sales sales <?xml version="1.0" encoding="UTF-8" standalone="yes"?><schemaFactory...
    

LOAD_PARTITIONS

  • [waitS:n] (optional)
  • [cancelS:n] (optional)
  • [continueBackupOnError] (optional)
  • schema_name
  • datasource-name
  • datatable-name
  • partition (:partition) | (partition)*

Allows for loading (or reloading) a set of partitions.

The optional continueBackupOnError parameter allows for continuing the backup even if the load partitions command failed. It will be ignored when restoring the backup.

On completion this command returns the status PARTITIONS_LOADED or PARTITIONS_LOAD_FAILED or PARTITIONS_LOAD_ERROR. Use the MDX notation [] for identifiers having spaces in their names:


    LOAD_PARTITIONS [Sales Global] postgres sales 2010 2011.
    

A range of partitions can be specified as following:


    LOAD_PARTITIONS [Sales Global] postgres sales 2010 : 2014.
    

Please refer to the load command for details about the waitSand cancelS parameters.

UNLOAD_PARTITIONS

  • [waitS:n] (optional)
  • [cancelS:n] (optional)
  • schema_name
  • datasource-name
  • datatable-name
  • partition (partition)*

Allows for unloading a set of partitions. On completion this command returns the status PARTITIONS_UNLOADED or PARTITIONS_LOAD_FAILED or PARTITIONS_LOAD_ERROR. Use the MDX notation [] for identifiers having spaces in their names:


    UNLOAD_PARTITIONS [Sales Global] postgres sales 2010 2011.
    

Please refer to the load command for details about the waitSand cancelS parameters.

LOAD_MEASURE_GROUP

  • [waitS:n] (optional)
  • [cancelS:n] (optional)
  • schema_name
  • cube-name
  • measure-group-name (optional)

Allows for reloading a measure group (aka. facts). On completion this command returns the status PARTITIONS_LOADED or PARTITIONS_LOAD_FAILED or PARTITIONS_LOAD_ERROR. Note that the measure-group name is optional if the cube contains a single measure group (aka. facts). Use the MDX notation [] for identifiers having spaces in their names.


    LOAD_MEASURE_GROUP Sales Sales [Basics KPI]
    

Please refer to the load command for details about the waitSand cancelS parameters.

FULL_LOAD_SCHEMA

  • [waitS:n] (optional)
  • [cancelS:n] (optional)
  • [offline:snapshot] (optional)
  • backup (optional)
  • schema_name
  • schema_definition_file_name (optional)

Similar to LOAD_SCHEMA but explicitly performs a full load of the schema whether the schema has activated incremental load or not.

The optional offline parameter allows for reading/writing an offline snapshot. For generating a snapshot of the 'Sales' schema, use for example the following:


    FULL_LOAD_SCHEMA [offline:write] Sales
    

On success, the result will contains the name of the snapshot : e.g., LOADED:Sales.icc-schema.2015_01_08_08h46m04.1420677964775. The offline snapshot is generated while loading the schema. Loading the schema from this snapshot use the following:


    FULL_LOAD_SCHEMA [offline:Sales.icc-schema.2015_01_08_08h46m04.1420677964775] Sales
    

Please refer to the load command for details about the waitSand cancelS parameters.

The optional backup parameter forces the backup generation even if the schema definition does not mention it. Once loaded, the schema will continue to backup any subsequent activity (e.g., incremental load).

UNLOAD_SCHEMA

  • [waitS:n] (optional)
  • [cancelS:n] (optional)
  • schema_name

Unload the schema or does nothing if the schema is not loaded.

Please refer to the load command for details about the waitSand cancelS parameters.

RESTORE_SCHEMA

  • [waitS:n] (optional)
  • [cancelS:n] (optional)
  • schema_name
  • timestamp
  • mode (optional)

Restore a schema backup. The timestamp parameter corresponds to the timestamp of the backup. You can access the available timestamp using the LIST_SCHEMA_BACKUP command or the backup UI in the icCube Web interface or by looking at the /backup directory content directly. For example, the following command is restoring the [Sales] backup as generated on [2016-04-26 08:56:37 254 UTC]:


    RESTORE_SCHEMA [Sales] [2016-04-26 08:56:37 254 UTC]
    

The optional mode parameter can have the value: FULL, INITIAL_LOAD (default value: FULL). INITIAL_LOAD means only the initial full load is being restored; any subsequent activity (e.g., incr. load) is ignored:


    RESTORE_SCHEMA [Sales] [2016-04-26 08:56:37 254 UTC] INITIAL_LOAD
    

Please refer to the load command for details about the waitSand cancelS parameters.

The output of this command is similar to a load command.

LIST_SCHEMA

  • schema_name (optional)

Returns the list of all the schemas loaded (filtered by the optional schema name argument) in the server. For each schema, the following information is returned: the name, the XML definition file name (i.e., the .icc-schema file), the refresh time (UTC) and the update data time (UTC). The update data time corresponds to the last change of the schema data (e.g., incremental load). Note that an incremental load with no data loaded will not change this time. For example:


         | Name         | Factory                 | RefreshTimeUTC      | UpdateDataTimeUTC   |
     | 0 | Sales        | Sales.icc-schema        | 2016-04-28 11:47:34 | 2016-04-28 11:47:34 |
     | 1 | Sales (2016) | Sales (2016).icc-schema | 2016-04-28 11:47:38 | 2016-04-28 12:47:26 |
    

LIST_SCHEMA_EX

This command adds to the LIST_SCHEMA command the LOADING and UNLOADED schemas as well as the latest error available (in case of error, only one error is displayed, please consult the log files for detailed errors). Note that even a LOADED schema can have an error (e.g., incremental load error). For example:


         | Status   | Name         | Factory                 | RefreshTimeUTC      | UpdateDataTimeUTC   | ErrorTimeUTC        | ErrorMessage                                          |
     | 0 | LOADED   | Sales        | Sales.icc-schema        | 2016-04-28 11:47:34 | 2016-04-28 11:47:34 |                     |                                                       |
     | 1 | LOADING  | Stocks       | Stock.icc-schema        |                     |                     |                     |                                                       |
     | 2 | UNLOADED | Sales (2016) | Sales (2016).icc-schema |                     |                     | 2016-09-20 13:40:31 | The directory '/home/data/sales_2016' does not exist. |
    

LIST_SCHEMA_BACKUP

  • schema_name (optional)

Returns the list of backups available for the specified schema otherwise all the backups available in the server. For example:


         | schema                             | timestamp                   | status | activity-timestamp          | activity-type   | activity-status |
     | 0 | Sales                              | 2016-04-26 08:56:37 254 UTC | ok     | 2016-04-26 08:56:37 254 UTC | FULL_LOAD       | ok              |
     | 1 | Sales (Postgres) (load-partitions) | 2016-04-27 09:07:05 750 UTC | ok     | 2016-04-27 09:07:05 750 UTC | FULL_LOAD       | ok              |
     | 2 | Sales (Postgres) (load-partitions) | 2016-04-27 09:07:05 750 UTC | ok     | 2016-04-27 09:07:17 038 UTC | LOAD_PARTITIONS | ok              |
    

In case a schema contains several activity backups (e.g., incremental load, load partition, etc...) the result contains a line for each of this activity on top of the FULL_LOAD line. For example, in the previous result the schema "Sales (Postgres) (load-partitions)" contains a LOAD_PARTITIONS activity as well.

DELETE_SCHEMA_BACKUP

  • schema_name
  • timestamp

Delete the backup. Returns a status (true|false) about the deletion. For example:


    DELETE_SCHEMA_BACKUP [Sales (Postgres) (load-partitions)] [2016-04-27 09:07:05 750 UTC]
    

returns the following if the backup existed and has been deleted:


         | schema                             | timestamp                   | status
     | 0 | Sales (Postgres) (load-partitions) | 2016-04-27 09:07:05 750 UTC | true
    

Using "cleanup" as timestamp means to delete all backups but the last two valid. The response will contains a line for every backup timestamp available.

LIST_SCHEMA_PARTITION

  • schema_name

Returns the list of partitions available for the specified schema. For example:


         | cube    | facts    | partition |
     | 0 | Sales   | Revenues | 2015      |
     | 0 | Sales   | Revenues | 2016      |
     | 0 | Sales   | Revenues | 2017      |
    

Examples (JAVA)

The XMLA client library is containing several examples of these requests. For more details have a look to the class : XmlaSchemasClientExample.

Examples (PERL)

Here is a code extract derived from a bash implementation that used SOAP::Lite to execute a command :


    xmla () {
        local url="$1"
        local command="$2"

        local xml='<?xml version="1.0" encoding="UTF-8"?>
                   <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" soap:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
                      <soap:Body>
                         <Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
                            <Command>
                               <Statement xsi:type="xsd:string">'"$command"'</Statement>
                            </Command>
                         </Execute>
                      </soap:Body>
                  </soap:Envelope>'

        curl -qs -K - 4<<<"$xml" <<EOF
        url=$url
        header=content-type: text/xml; charset=utf-8
        header=soapaction: "urn:schemas-microsoft-com:xml-analysis#Execute"
        user=$(<$iccube_pw)
        data-binary=@/dev/fd/4
        EOF
    }
    

Next chapter: Schema Scheduler is giving an overview of the embedded scheduler features.