Cube Extract

image_pdfimage_print

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.

Main 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, all 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”.
Cell types Specifies whether numeric and/or text cells are extracted. Possible values are: “both”, “only_numeric”, and “only_string”.
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.

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

Advanced settings
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

Example:
– Operator: “Greater than”, Value: 1000
Return all paths with cube cell values > 1000.

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 allows to
– reorder the columns
– rename the columns
– omit 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 connection of types JedoxXMLA or Lucanet:
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.
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 PC’s

onlyBases

image_pdfimage_print
Was this post helpful?
NoYes (+1 rating, 3 votes)
Loading...