1. Analytical Application Infrastructure User Guide
  2. Unified Analytical Metadata
  3. Derived Entity
  4. Creating Derived Entity

Creating Derived Entity

This feature allows you to create a Derived Entity based on a Dataset, an Entity or a union of Derived Entities. For Union and Union All options, the metadata used in the participating Derived Entities determines the columns of the physicalized materialized view. For Union based Derived Entity, even if the participating derived entities have metadata in common, the resultant materialized view in database will ensure unique columns.

The same is explained in a tabular format:

Table 8-1 Derived Entity based on the Dataset

Union Based DE Participating DEs Metadata present in participating DEs Final physicalized materialized view for union based DE
UN001 DE001 MSR001 MSR002 MSR003 MSR001 MSR002 MSR003 MSR004 MSR005
DE002 MSR001 MSR004 MSR005

In case of Union All based definition, the resultant materialized view in database may have repetition of data based on data present in the participating Derived Entities.

Note:

To define a Derived Entity based on an Entity in a Data Source, you should have defined permissions for the particular Data Source in the Atomic schema.

You can approve a Derived Entity created by other users if you have the authorizer rights. You need to be mapped to the role Derived Entity Write to add or create a Derived Entity.

Partitioning is supported for Dataset based Derived Entities which have partitions enabled on the FACT table.

To create a Derived Entity:

  1. Click Add from the Derived Entity toolbar. The Derived Entity Details window is displayed.

    Figure 8-4 Derived Entity Details window


    This illustration shows the Derived Entity Details window. The window has the Derived Entity Details and Metadata Tree panes. In the Derived Entity Details pane, you can specify the details of the entity. In the Metadata Tree pane, you have to select the Available Values field to the Selected Values field and Save the details. The fields in these panes are described in the table in this topic.

  2. Enter the details as tabulated.
    The following table describes the fields in the Derived Entity window.

    Table 8-2 Fields in the Derived Entity window Descriptions

    Field Description
    Code

    Enter a distinct code to identify the Derived Entity. Ensure that the code is alphanumeric with a maximum of 8 characters in length and there are no special characters except underscore “_”.

    Note the following:

    The code can be indicative of the type of Derived Entity being created.

    A pre-defined Code and Short Description cannot be changed.

    Same Code or Short Description cannot be used for Essbase installation: “$$$UNIVERSE$$$”, “#MISSING”, “#MI”, “CALC”, “DIM”, “ALL”, “FIX”, “ENDFIX”, “HISTORY”, “YEAR”, “SEASON”, “PERIOD”, “QUARTER”, “MONTH”, “WEEK”, “DAY”.
    Short Description Enter a Short Description based on the defined code. Ensure that the description is of a maximum of 80 characters in length and does not contain any special characters except “_, ( ), -, $”.
    Long Description Enter the Long Description if you are creating subject-oriented Derived Entity to help users for whom the Derived Entity is being created or other details about the type/subject. Ensure that the description is of a maximum of 100 characters in length.
    Source Type Select the source type from the drop-down list. The options are Dataset, Entity, Union, and UnionAll. The Union and UnionAll options are used to create a Derived Entity by combining 2 or more existing Derived Entities.
    Aggregate

    This field is enabled only if Source Type is selected as Dataset.

    Turn ON the Aggregate toggle button to collate the information for the Derived Entity.

    Materialize View

    Turn ON the Materialize View toggle button if you are using Oracle database to create a Materialized View with the Derived Entity Name and short description.

    Note: You cannot enable the Materialize View option if you are using IBM DB2 database.

    Dataset Name

    This field is enabled only if the Source Type is selected as Dataset.

    Select the Dataset Name from the drop-down list. The Short Description for the Datasets is available in the drop-down list to select.
    Source Name

    This field is enabled only if the Source Type is selected as Entity.

    Select the Source Name from the drop-down list.

    Refresh Interval

    This field is enabled only if the Materialize View checkbox is selected.

    Select the appropriate refresh interval from the drop-down list, The options are:

    None- Only materialized view will be created. If you select None for Refresh Interval, it is mandatory to select None for Refresh Method.

    Demand- The refresh of the Materialized View is initiated by a manual request or a scheduled task.

    Commit- The refresh is triggered by a committed data change in one of the dependent tables.
    Refresh Method

    This field is enabled only if the Materialize View checkbox is selected.

    Select the appropriate refresh method from the drop-down list, The options are:

    • None- Only materialized view will be created. If you have selected None for Refresh Interval, it is mandatory to select None for Refresh Method.
    • Complete- This recreates the materialized view replacing the existing data. This can be a very time-consuming process, especially if there are huge amounts of data to be read and processed.
    • Fast- Applies the incremental changes to refresh the materialized view. If materialized view logs are not present against the source tables in advance, the creation fails.
    • Force- A fast refresh is attempted. If it is not possible, it applies Complete refresh.

    Note: Refresh Methods Fast and Commit do not work if the query has some ANSI Join conditions.

    Enable Query Rewrite

    This toggle button is enabled only if the Materialize View toggle button is turned ON.

    Turn ON the toggle button if you want to create materialized view with the query rewrite option.
    Parallelism
    Hint

    Specify Hints (if any), for optimized execution of query. The specified hints are appended to the underlying query of the derived entity.

    Oracle hints follow (/*+ HINT */) format.

    For example, /*+ PARALLEL */.
    Prebuilt Table

    This toggle button is enabled only if the Materialize View toggle button is turned ON and Source Type is selected as Dataset.

    Turn ON the toggle button to enable partition for the Derived Entity.

    On selecting the Dataset Name or Source Application Name, the respective fields are displayed in the Metadata for Source Type list.

  3. Double-click Metadata for Source Type.
    • For Source Type selected as Dataset, the Metadata for Source Type displays all Hierarchies and Measures defined on the Entities that are part of the selected Dataset, and Business processors defined on the selected Datasets.
    • For Source Type selected as Entity, it displays all Entities in the selected DI Source.
    • For Source Type selected as Union or Union All, it displays all Derived Entities created with Source Type as Dataset. You can select maximum of 15 Derived Entities.
  4. Click the Select icon to expand the folders. Select the required metadata and click the Move button. Click Move All to select all metadata. You can select a metadata and click the Remove button to remove that metadata or click the Remove All button to remove all selected metadata.
  5. Select the hierarchy for which you want to add partition from the Partition drop-down list. This field is enabled only if the Materialize View toggle button is turned ON and Source Type is selected as Dataset. This drop-down lists the Hierarchies you selected as Metadata for Source Type.
  6. Generate Wrapper View.
  7. Retain History.
  8. Click Save.

    A confirmation dialog is displayed.

    The details are displayed in the Derived Entity Summary window.