Cube Load with Drillthrough

image_pdfimage_print

Related link: Cube Load

For huge data models, it may not be convenient to load the data at the most detailed level to an OLAP cube. However, access to this detailed data is required for the analysis. This is the scenario for drillthrough: the data are stored at an aggregated level in a cube. With a drillthrough request on a cube cell from a front end, the detailed data, including additional fields (e.g. Order number, Line item ID), can be displayed.

Definition of Annex Fields

The columns of the source that should  part of the detailed drillthrough data request (but not part of the cube) have to be defined. This can be done in the Dimension Mapping table by assigning the entry “” instead of a dimension name to the particular column. It is not necessary for these additional fields to be at the end of the source. The order of the columns in the source defines the display order.

Drillthrough Modes

In the cube load, two different variants of the drillthrough can be activated:

Direct

No additional persistence of the detailed drillthrough data is done during the cube load. The requirement for this is that all detailed data can be retrieved via one relational extract without any further transforms. Only the link from the OLAP cube to this relational extract is stored in Jedox Integrator.

Relation extract (for direct drillthrough only): all detailed data can be retrieved through a relational extract during a drillthrough request. It may contain an arbitrary SQL statement. The relational extract may have a field structure with a column mapping, but it must not contain default values for the columns. Note: the assignment of a field structure (with or without column mapping) increases the performance of the drillthrough request, especially in the case of complex SQL statements.

Persisted

During the cube load, the detailed drillthrough data, including the additional fields, is additionally persisted to a relational database. The link from the OLAP cube to this relational table is stored in Jedox Integrator.

Options for persisted drillthrough only:

Relational connection: A relational database connection where the detailed drillthrough data will be stored. They are recommended for very huge data volume. The same databases are supported as in Relational Load (e.g. Oracle or MS SQL Server) with the exception of MS Access. If no connection is indicated, the data are written to a H2-database which is used internally in Jedox Integrator.
Optional schema: The name of the schema in the Relational database. Default: The name of the OLAP database
Optional table: The name of the table in the Relational database where Drillthrough data will get stored. Default: The name of the OLAP cube.

Note: the cube load mode “insert” is not available in combination with drillthrough, as it might lead to inconsistencies between the cube and the detailed drillthrough data. Instead, the load mode “add” should be considered, eventually in combination with a preceding load mode “delete”. 

With a cube load without drillthrough and a separate relational load for the drillthrough data (using relational load mode “insert” with “aggregation” option activated), it would be possible to achieve this consistency but without linking the cube to the drillthrough table.

Differences between Persisted and Direct Drillthrough
    Persisted   Direct
Data transformations   Allowed   Not allowed (only in SQL query)
Performance   Slightly higher query performance (if direct drillthrough with complex SQL query)   Higher loading performance, as no loading to relational persistence
Data accuracy   Consistent with cube data   Always up to date but potentially different from cube data
Possible systems for source data   Any   One relational database system
Needed requirements  

With external database:
The Jedox Integrator project and the relational connection specified in the cube load must not be removed or renamed in order to find the correct data in a drillthrough request.

With internal database:
The Jedox Integrator project is not required.

  The Jedox Integrator project and the relational extract specified in cube load must not be removed or renamed in order to find the correct data in a drillthrough request.
Notes
  • Drillthrough requests are possible from Jedox Excel Add-in and Jedox Web.
  • For the configuration of the drillthrough, you need to install and configure Jedox Supervision Server.
  • For an example, see the Jedox Integrator Project sampleDrillthrough, which is included in every Jedox installation.
  • If “Parallel Load in Database for Drill Through” is set and “Splash mode” is set to anything but “disabled”, there will be a warning message, because no splashing can be done on the relational drillthrough data.
  • It is not possible to run several cube loads with drillthrough for the same cube in parallel jobs.
image_pdfimage_print
Was this post helpful?
NoYes (-2 rating, 8 votes)
Loading...