Cube Extract

Data from a Jedox OLAP cube can be read using a Cube extract. The results of the extract are the paths of the cube cells in the first columns and the related cube cell value in the last column.

Settings

Connection Connection name
Query cube Cube name
Empty cells This setting defines the behavior in respect to cells that are not filled or that contain zero values "0" or "".
The following options are available:
  • excludeEmpty: empty cube cells are not returned from the extract. This corresponds to behavior in 6.0 when when option "ignore empty cells" was turned off.
  • excludeEmptyAndZero: if the option "store zero values" is set for the cube, no "0" (for numerical cells) and "" (for string cells) values are returned. Otherwise the option is identical to "excludeEmpty".
  • includeEmpty: the extract returns a NULL in the value column for empty cube cells.
  • includeEmptyDefaultMapping: the extract returns "0" (for numerical cells) and "" (for string cells) for empty cube cells. This corresponds to behavior in Jedox 6.0 when option "ignore empty cells" was turned off.
Base elements only If set, the numeric cube cells on consolidated cells are excluded from the extract. However, consolidated text cells are extracted in this case.
Read rule-based values If set, rule-based cell values are extracted. If the rule value has the result 0, it is extracted if "empty cells" is set to "excludeEmpty" but not if it's set to "excludeEmptyAndZero". Rule-based cell values of numeric type are extracted, even if the Cell Types setting is set to “only_string”. Rule-based cell values of string type are extracted, even if the Cell Types setting is set to “only_numeric”.
Cell types Specifies whether numeric and/or text cells are extracted. Possible values are: “both” (default), “only_numeric”, and “only_string”.

Tip: if your data requires both numeric and string cell types, you may see improved performance by creating separate extracts for the numeric and string types, rather than one extract with both.
Query filter on dimensions A query filter on the dimension allows filtering according to specified elements or values. The filter type can be set to "accept" or "deny" as required. This type of filter allows particular cube areas and/or cube cells to be filtered out. Only cube cells that meet the filter conditions for all dimensions are extracted.

Note: "Equals" is case-insensitive. "Like" (see Query filter for dimensions) is case-sensitive. You can change the case-sensitivity by using a (?) modifier: https://www.regular-expressions.info/modifiers.html

After using the regular expression, the data preview must look like in the example below:
Filter condition for cube value The result can be filtered on the cube value of the returned cells:

- Operator:

possible values:
<enumeration value="Equals" />
<enumeration value="Not equals" />
<enumeration value="Less than" />
<enumeration value="Less or equal" />
<enumeration value="Greater than" />
<enumeration value="Greater or equal" />

- Value (must be numeric value)

Example:
- Operator: "Greater than", Value: 1000
Returns all paths with cube cell values > 1000.
Logical operator Multiple conditions are connected with one of these logical operators: "or", "and", "xor".
Cube output columns The cube output columns of the extract can be specified by defining for each column:
  • The name of the column (default: Name of the dimension)
  • The cube dimension

In most cases this manual definition is not necessary: by default, all cube dimensions are output columns of the Cube extract with the dimension name as column name and with the order as defined in the cube.

The manual definition offers the options of
- reordering columns
- renaming columns
- omitting some of the cube dimensions.

When a cube dimension is omitted from the output columns, the cube values are extracted on the default read element of this dimension. This default read element has to be specified for the dimension in the Modeler. Additionally, no filter condition can be applied on this dimension.
Rename value Name of the last column containing the cube cell value. Default.
Retrieve drillthrough data Only relevant if the cube has been loaded with Drillthrough. If set, the underlying detailed relational drillthrough data of the cube is retrieved. All the defined filters are applied. This option is mainly relevant to verify the correctness of the Drillthrough data of a cube.
Block size The maximal number of cells to export from a cube in a single request (default value: 100.000).

For Lucanet connections:
The blocks can be defined by so-called slicer dimensions which split the XMLA request to a series of block requests. For each element of each slicer dimension a separate XMLA request is sent.
Format: #<Dimension>,[<Dimension>,....]
As alternative, the block size can be defined by a numerical value which leads to an automated determination of suitable slicer dimensions.
Example: #Year,Company
For each year and each company, a separate XMLA request is sent.
Use caching Memory, disk or none (default). See article Caching in Extracts.

The filter options are the same as for the Dimension Extract.

Example

Extract_Name Sales_Extract
Extract_Type Cube
Connection Conn_name
Query Cube Sales
Ignore Empty Cells yes
Base Elements Only yes
Read Rule-based Values no
Cell Types both

Query Filter on Dimensions

Dimension Filter Type Operator Value Mode
Years deny inRange [2002,2005] rootToBases
Products accept equal Stationary PCs onlyBases

Updated June 5, 2023