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	Name of the cube to extract; available cubes will appear in the dropdown list.
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, only cells where all elements are base elements are returned. Cells that with consolidated elements for other dimensions are not included. From the cube, only cells where ALL elements are base elements are returned. If all dimensions are filtered with mode "onlyBases", then setting "Base elements only" has no effect. But here it removes cells from the result that are on consolidated elements for other dimensions.
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.

Text and numeric values can be filtered.

Note: "Equal" is case-insensitive, while "Like" is case-sensitive. You can change the case-sensitivity by using a (?) modifier. Seehttps://www.regular-expressions.info/modifiers.html

Dimension

Set the dimension to be filtered here. Available dimensions are shown in a dropdown list in the input field.

Filter type

Various filter lines to the same input column are evaluated like a rule chain with the filter types "accept" and "deny". If only accept-filters are used, this behaves like a logical OR.

Operator

Comparison operators can be used on element names. The values for each expression depend on the operator used; specific values are noted when necessary in the descriptions below.

equal	The element entered here will be filtered. Filter behavior depends on the type of data: String: the filter must be exactly equal to the source value. Decimal number (floating or double): the filter value must be the most minimal representation of the value. For example, if the source value is 1.0, the filter value must be 1. If the source value is 1.5000, then the filter value must be 1.5. Non-decimal number (int or long): the filter value must be exactly the same as the number. Boolean (true/false): the filter must be "true" or "false" as appropriate.
inAlpharange	Alphanumerical values in a particular range are filtered. Example: [A100,D200]. The short GIF below shows how this functionality works with dates.
inRange	Numerical values in a particular range are filtered. Example: [1000,2000] Other examples: “[100,200]” - inclusive range: 100, 101.....199, 200 “(100,200)” - exclusive range: 101, 102, ... 198, 199 “[100,]” - inclusive half-open interval: numbers greater or equal to 100 “[,100)” - exclusive half-open interval: numbers lower than 100
isEmpty	Empty values (blank, space, or multiple space values). For this operator, the Value field should be left blank.
like	Filters the dimension elements according to regular expressions. This operator is case-sensitive. You can change that by using the (?i) modifier at the start of a regular expression. After using the regular expression, the data preview must look like in the example below:
subset	Filters the dimension elements based on any stored global Subsets for the dimension. The extract can filter from Subsets with one or more defined filter criteria or variables: Filtering from a stored Subset Select the stored Subset from the "Edit Subset" dialog: If the Subset uses variables, you must select them manually by adding as many rows as there are defined variables within the "Edit Subset" dialog. Filtering with an Integrator variable For more dynamic design, you can replace the value of the Subset variable with an Integrator variable, as shown in the screenshot below: Note: the value " has to be escaped as \"

Value

The element you want to filter for in the source data.

Mode

Defines a hierarchical filter for the dimension. If not set, the default mode "onlyNodes" is applied, returning only the filtered elements independently of the hierarchy. In case of a global Subset filter, a mode is in general not reasonable.

Mode	Example	Tree view
rootToBases	Selected value: Austria Based on the selected element, this element and the elements above and below are filtered.
rootToNodes (to top element and the nodes above)	Selected value: Basic Sports Equipment All parents and their respective parents are filtered, including the selected elements.
rootToConsolidates	Selected value: Basic Sports Equipment Only consolidated elements above the selected element are filtered.
nodesToBases	Selected value: Austria All bases under this value are filtered, including the parent element.
onlyChildren	Selected value: East Only the children are filtered based on the element selected.
onlyBases	Selected value: East After filtering the elements according to any of the operators, takes all base elements underneath them. Consolidated elements are not included. From the cube, only cells where ALL elements are base elements are returned. If you would filter all dimensions with mode "onlyBases" setting, "Base elements only" has no longer an effect. But here it removes cells from the result that are on consolidated elements for other dimensions. In the example on the right, the filter value "East" passes the operator, and the returned elements are the base elements.
onlyNodes	Only the selected element is filtered (nodal point).
onlyRoots	Selected value: Austria Only the element from the highest level of the hierarchy is filtered based on the selected element.

Filter condition for cube value

The result can be filtered on the cube value of the returned cells.

Operator

Both numeric and text values can be filtered. When using operators "Less than", "Less or equal", "Greater than", or "Greater or equal", strings are compared alphabetically.

For example, a filter for "Greater than" a value of "1000" will return results with cube cell values greater than 1000.

Logical operator

Multiple conditions can be 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.

Other settings

Rename value	Name of the last column containing the cube cell value.
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	If caching is activated, the complete output of the extract is temporarily stored during the first call of the extract, using an internal H2 database. Subsequent calls of the extract read directly from the cache without connecting to the underlying source system of the extract. If the extract or the underlying connection contains variables, a separate cache is build for different values of these variables. See Caching in Extracts and Transforms for more information.

Examples

Data source
Filter type “accept”
	Result with logical operator “and”
	Result with logical operator “or”
Filter type “deny”
	Result with logical operator “and”
	Result with logical operator “or”

What's in this article: