Mapping Metadata
Use
The OLE DB for OLAP specification defines a multi-dimensional schema. The BI OLE DB for OLAP providers support a subset of metadata objects that appear in this schema. Client applications can request metadata available in a system via the IDBSchemaRowset interface implemented by the OLE DB for OLAP providers. This metadata has the form of multi-dimensional schema rowsets. The MDDataProvider BW object provides these functions in OLAP BAPI.
For more information about special methods for browsing in the Business Information Warehouse metadata and master data, see OLAP BAPI and MDDataProvidersBW.
Features
The following graphic displays the model of BI metadata objects in the OLE DB for OLAP schemas:
The OLE DB for OLAP specification schema concept is not supported by BI.
The following sections describe the relationship between BI metadata objects and the OLE DB for OLAP specification schema in greater detail:
...
1. CATALOGS and CUBES
SAP BI terminology: InfoCube and Query
The OLE DB for OLAP specification catalog concept is supported by BI.
○ The schema rowset CATALOG is supported.
○ The CATALOG column, which is filled in all other schema rowsets, specifies the name of the InfoProvider whose data is accessed.
The current version of SAPs OLE DB for OLAP implementation allows for various options to access the data stored in an InfoProvider:
...
○ direct access without queries using the MDX command $InfoProvider (see variant1: Direct access to the data of an InfoProvider)
○ with queries, created with the BEx Query Designer (see variant2: Access to InfoProvider data using a query).
We recommend that you define BEx queries for the requested InfoProvider, and use them as sources (variant2).
Variant1: Direct access to the data of an InfoProvider
With certain restrictions, you can directly access data belonging to a BI InfoProvider, using the MDX command $InfoProvider. This option is available for the following objects:
○ for all InfoCubes having type standard InfoCube and for MultiProviders as Cubes
○ for all characteristics and key figures as Dimensions/Measures
Exceptions are navigation attributes and key figures that are neither restricted nor calculated.
The following code fragment shows an appropriate example:
SELECT
{
[Measures].[0D_DOCUMENT], [Measures].[0D_OORVALSC]
}
ON COLUMNS,
NON
EMPTY [0D_PLANT].MEMBERS ON ROWS
FROM [$0D_SD_C03]
WHERE (
[0CALMONTH].[200101], [0D_COUNTRY].[US] )
The following graphic shows the results table: The number of documents and the open orders are displayed in total and for individual US cities for January 2001.
Variant2: Access to InfoProvider data using a query
The modeling of BEx queries onto Cubes is based on significant common structural features. Characteristics, key figures and calculated or restricted key figures are selected from underlying InfoProviders for BEx queries. The multi-dimensional dataset of a BEx query is named QUERY_CUBE. No data is replicated when constructing a QUERY_CUBE. Only the query definition is permanently stored in the database. At query run time, the QUERY_CUBE is filled with the data required to generate the requested dataset.
Dataset
The OLE DB for OLAP interface Dataset is a multi-dimensional result quantity defined with a MDX command. For more information, see MDX as Basis for the Interfaces.
Note that you need to release queries for them to be used in OLE DB for OLAP providers. In the Query Properties dialog box, set the indicator for Allow External Access to This Query. For more information, see Query Properties (on the Specific tab page).
In the query definition, do not use formulas with the operators %GT, %RT, %CT, SUMGT, SUMRT, SUMCT or LEAF, as these are dependent on the list display in the BEx Analyzer and could return other unexpected values via OLE DB for OLAP or MDX. It is possible to achieve the desired result with constant selection. For more information, see Constant Selection.
Do not use variables with replacements from the result of a query which contains entry-ready variables. This is because these variables cannot be entered via MDX. You can use variables with type pre-calculated value set instead of a variable with the processing type replacement path. For more information, see Replacement Path: Replacement from Query and Details.
You can also use variables in BEx queries using SAP BI-specific OLE DB for OLAP providers enhancements. For more information, see SAP Variables.
The following graphic illustrates the data flow:
2. DIMENSIONS
BI terminology: Characteristics
A dimension rowset for a cube is filled with all free characteristics and the special MEASURES dimension. BI structures are modeled to dimensions. In the BEx Query Designer, you can set a technical name in the properties of structures or structural components. Otherwise, the UNIQUE-ID (UID) is used. For more information, see Structure Properties and Properties in the Selection/Formula.
Dimensions are constructed hierarchically (see HIERARCHIES and LEVELS below).
Note that in BI, a dimension relates to a group of related characteristics, for example, the characteristics relating to time dimension groups such as calendar year or fiscal year.
3. MEASURES
BI terminology: Key Figures
Measures are handled in the OLE DB for OLAP Specification as Members of a special Dimension, the dimension MEASURES.
The following graphic illustrates this:
Calculated and restricted key figures are modeled as Measures by default. They give OLE DB for OLAP interface consumers access to complex key figure definitions. Defining calculated and restricted key figures on the provider-end of the interface has the following advantages:
○ Performance is not compromised
○ There are fewer complexities for the customer
○ Consistent interpretation and use of derived key figures is guaranteed for various application areas.
At runtime, you can also request other calculations in addition to CALCULATED MEMBER using MDX syntax. For more information, see Examples for CALCULATED MEMBER.
4. HIERARCHIES and LEVELS
Every characteristic in a BI query is modeled to a dimension with a flat hierarchy in the OLE DB for OLAP interface. The UNIQUE NAME of this Hierarchy is the same as the UNIQUE NAME of the Dimension. This Hierarchy has the following levels:
○ Level 0 with ALL Members (all members are displayed irrespective of their position in the hierarchy).
○ Level 1 with a subset of master data table values.
BI hierarchies can be established as additional Hierarchies. For more information about maintaining BI hierarchies, see Hierarchies.
Time dependency in MDX
The term time dependency does not exist in Microsoft’s MDX specification. According to this specification, the same hierarchy, and therefore the same key date, has to be used in both MDX and in function BAPI_MDPROVIDER_GET_MEMBERS. Because the current date is always used when you call BAPI_MDPROVIDER_GET_MEMBERS, BI hierarchies with time-dependent names or time-dependent structures are also evaluated with the current key date. Key date variables are also ignored in MDX.
However, you can set a date other than the current date for the current session using function BAPI_MDPROVIDER_SET_KEY_DATE. For consistency reasons, the query key date of all subsequent MDX executions are also replaced by this date.
You use BAPI_MDPROVIDER_GET_KEY_DATE to get the value that you set using BAPI_MDPROVIDER_SET_KEY_DATE. If no value has been set, this function returns the current date.
You can use these two functions like you use other BAPIs delivered by SAP. Enter the date in the SAP-internal format YYYYMMDD.
The field Default Hierarchy for Dimension Rowset is filled with the presentation hierarchy of a BI query definition. For more information about selecting a hierarchy as a presentation hierarchy, see Selecting Hierarchies and, in particular, Characteristic Properties.
5. MEMBERS
BI terminology: Characteristic values (instances of characteristics)
6. PROPERTIES
PROPERTIES are the dimension properties (properties for each dimension level).
BI terminology: Attributes