Creating a Multidimensional Base Views Over a Multidimensional Data Source

The following figure displays the different elements of a Multidimensional Database and how they are represented in Virtual DataPort.

Elements of a cube of a multidimensional data source (1)

Elements of a cube of a multidimensional data source (1)

To create a base view that retrieves data from a cube, open the “Multidimensional Data Source” dialog and select at least, a hierarchy and a measure of the cube. After selecting the appropriate elements, click Import. The Tool will create a base view for each group of elements of a cube (image1). Each view will have:

  • A field for each level (image2) of each selected hierarchy (image3)
  • A field for each selected measure (image4)
  • A field for each selected attribute (image5). Attributes are also known as “Dimensions properties”.
  • A field for each selected variable (image6) of each dimension (image7). The required variables are selected in the “Create base view” dialog and cannot be deselected.

For example, let us say that we have a cube called InfoCube (see Elements of a cube of a multidimensional data source (2)).

We are going to create a base by selecting the following elements of this cube:

  • The hierarchy IO_SREP hierarchy (Sales representative dimension), which has three levels.
  • The Sales revenue measure
  • The Sales quantity measure
Elements of a cube of a multidimensional data source (2)

Elements of a cube of a multidimensional data source (2)

If the Include leaf levels of hierarchies only check box is cleared, the schema of the base view will have five fields: one for each level of the hierarchy and one for each measure (see the following image). The section “Include Leaf Levels of Hierarchies Only” Option explains in detail the effect of selecting or clearing this check box. This option is only available for the multidimensional data sources whose adapter is Oracle Essbase, SAP BW or SAP BI.

Creating a multidimensional base view

Creating a multidimensional base view

The following figure displays the result of querying this view.

Result of querying a multidimensional base view

Result of querying a multidimensional base view

Note the hierarchical meaning of the values of the srep_level01, srep_level02 and srep_level03 fields. The elements of the srep_level01 level are regions like “East” or “Midwest”, which have cities like “Atlanta” or “New York” (srep_level02) and these cities have sales representatives like “Samuel Jackson” or “John Smith” (srep_level03).

“Include Empty Rows” Option

The queries to base views may return many empty rows when the data of the cube is sparse. You can avoid obtaining the combinations of members whose measures do not have value. To do this, clear the Include empty rows check box of the “Multidimensional Data Source” dialog. Do this when creating the base view, before clicking the button “Create selected base views”.

Note

This option is only available for the multidimensional data sources whose adapter is Essbase 9, Essbase 11, SAP BW (XMLA), SAP BI (XMLA), SAP BW (BAPI) and SAP BI (BAPI).

“Include Leaf Levels of Hierarchies Only” Option

If, when creating a SAP BW base view, the “Include leaf levels of hierarchies only” check box is selected, the base view, instead of having one field for each level of the each hierarchy, it will only have one field for the leaf level of each hierarchy. That is, the levels of the hierarchies that are not leaf will be ignored.

Note

This option is only available for the multidimensional data sources whose adapter is Essbase, SAP BW (XMLA), SAP BI (XMLA), SAP BW (BAPI) and SAP BI (BAPI).

For example, let us say that we have a cube called InfoCube (see Creating a SAP BW base view with “Include leaf levels of hierarchies only” enabled (1)) and we select the hierarchy IO_SREP hierarchy (Sales representative dimension) and the measures Sales revenue and Sales quantity.

If we select the Include leaf levels of hierarchies only check box, the base view will have three fields instead of five: one for the Level 03 of the hierarchy and one for each measure.

Creating a SAP BW base view with "Include leaf levels of hierarchies only" enabled (1)

Creating a SAP BW base view with “Include leaf levels of hierarchies only” enabled (1)

Creating a SAP BW base view with “Include leaf levels of hierarchies only” enabled (2)

Creating a SAP BW base view with “Include leaf levels of hierarchies only” enabled (2)

The benefits of creating a base view with the “Include leaf levels of hierarchies only” option enabled are the following:

  • The rows corresponding with the partial aggregates of non-leaf levels are removed from the result. In our example, the aggregate sales for all the sales representatives of the Midwest region, the aggregate sales for all the sales representatives in New York, etc. are removed from the result.

    This may be considered a more natural “relational view” of the data in the cube, since those aggregates can be seen as superfluous. I.e. they can be computed from the rows corresponding with the leaf levels. This is especially true when the origin data is relational in nature (e.g. ODS objects).

  • When this option is selected, Virtual DataPort can push down more complex conditions to SAP BW, without needing to use the “MDX Query” option.

“Include Member Keys” Option

If the Include member keys check box is selected when creating a Multidimensional base view, the schema of the new view will have an extra field for the leaf level of each hierarchy. In each row, the value of this extra-field will be the technical name of each member.

Note

This option is only available for the multidimensional data sources whose adapter is SAP BW (BAPI) and SAP BI (BAPI).

For example, let us say that we have a cube called InfoCube (see Creating a SAP BW (BAPI) base view with “Include Member Keys” selected (1)) and we select the hierarchy IO_SREP hierarchy (Sales representative dimension) and the measures Sales revenue and Sales quantity.

If we select the Include member keys check box and the Include leaf levels of hierarchies only, the base view will have four fields: one for the “Level 03” of the hierarchy (srep_level03), one for each measure (sales_quantity and sales_evenue) and srep_key, which will contain the technical name of the member.

Creating a SAP BW (BAPI) base view with "Include Member Keys" selected (1)

Creating a SAP BW (BAPI) base view with “Include Member Keys” selected (1)

Creating a SAP BW (BAPI) base view with "Include Member Keys" selected (2)

Creating a SAP BW (BAPI) base view with “Include Member Keys” selected (2)

The benefit of creating a base view with the “Include Member Keys” check box selected is that you will be able to filter not only by the business name, but also by the technical name of the members of dimension. In the view created in Creating a SAP BW (BAPI) base view with “Include Member Keys” selected (2), you can filter by these:

  • The business name: by adding a WHERE condition with the field srep_level03.
  • The technical name (attribute that has the same name as the level, but ending with “_key”): by adding a WHERE condition with the field srep_key.

Take into account that filtering by business name may return different results than filtering by technical name. The reason is that in a dimension, there may be several members with the same business name, but the technical name is always unique.

SAP Metadata Cache

Virtual DataPort caches the metadata of SAP to speed up the queries to multidimensional base views.

Note

This cache is only available for base views that are created with the option “Include leaf levels of hierarchies only” selected, over data sources with the adapter “SAP BI (BAPI)” or “SAP BW (BAPI) BAPI”.

The first time you query a SAP base view, Virtual DataPort caches the value (also called “caption”) and the technical name of all the members of the hierarchy that was selected when creating the view.

Later, when a query to this view filters by the caption of a member, the Server searches in the SAP Metadata Cache, the technical name of the members whose caption is the value provided in the query. Then, it will use this technical name to build the MDX query sent to SAP. The benefit of filtering by the technical name is that SAP processes these queries more efficiently than if the queries filter by the caption of the member.

Note that filtering by the technical name of a member may not lead to the same result as filtering by its caption. The reason is that technical names are unique, but captions are not. Therefore, there may be more than one member with the same caption.

This cache is automatically cleared when you restart the Virtual DataPort server. You can also delete the contents of this cache at any time, without restarting the Server. To do this, open the data source and click the button Clear metadata cache. Alternatively, you can invalidate the cache by executing a VQL command (see more about this last option in the section Invalidating the Metadata Cache of SAP BAPI Data Sources of the VQL Guide).