sap hana analytics catalog (bimc views) reference · public sap hana platform 2.0 sps 00 document...
Post on 29-Aug-2018
318 Views
Preview:
TRANSCRIPT
PUBLIC
SAP HANA Platform 2.0 SPS 00Document Version: 1.0 – 2016-11-30
SAP HANA Analytics Catalog (BIMC Views) Reference
Content
1 Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
2 BIMC Tables and Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
2.1 View BIMC_CUBES. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4
2.2 Table BIMC_ALL_CUBES. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
2.3 View BIMC_ALL_AUTHORIZED_CUBES. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
2.4 View BIMC_REPORTABLE_VIEWS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
2.5 Table BIMC_DIMENSIONS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
2.6 Table BIMC_HIERARCHIES. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
2.7 Table BIMC_LEVELS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
2.8 View BIMC_MEASURES. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .14
2.9 Table BIMC_MEMBERS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
2.10 View BIMC_DIMENSION_VIEW. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .19
2.11 View BIMC_ATTRIBUTE_RELATIONS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
2.12 Table BIMC_VARIABLE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
2.13 View BIMC_ALL_VARIABLES_VIEW. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
2.14 Table BIMC_VARIABLE_RANGE_DEFAULTS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
2.15 Table BIMC_VARIABLE_ASSIGNMENT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .30
2.16 View BIMC_VARIABLE_VIEW. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
2.17 Table BIMC_VARIABLE_MAPPING. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
2.18 Table BIMC_VARIABLE_VALUE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
2.19 Table BIMC_VARIABLE_ODBO. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
2.20 BIMC_SOURCES. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
3 Consuming Views With Parameters. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
3.1 Main Variable and Parameter Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
3.2 SAP HANA SQL Syntax for Passing Parameters and Variables. . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
3.3 SAP HANA MDX Syntax for Passing Parameters and Variables. . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
3.4 Default Values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
3.5 Expressions as Special Default Values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .41
3.6 Value Help. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
3.7 Passing and Mapping of Variables and Parameters. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
2 P U B L I CSAP HANA Analytics Catalog (BIMC Views) Reference
Content
1 Overview
The SAP HANA Analytics Catalog consists of tables and views with the prefix BIMC located in the schema _SYS_BI. It contains metadata required by analytic clients such as Analysis Office and Business Objects Cloud. The metadata is also required for access via MDX.
The SAP HANA Analytics Catalog is populated with metadata when the following analytic models are deployed:
● Calculation views● Analytic views● Attribute views (the column CUBE_NAME is filled in with the '$ATTRIBUTE' value for these models)
This catalog can be seen as an analytic extension to the database catalog (schema SYS). However, it also contains some additional information, like the data type of attributes, so that analytic clients do not need to read the database catalog as well..
The structure of the analytic catalog is close to that of the ODBO schema rowset specification that can be found at:
● MDX standard Using Schema Rowsets in OLE DB for OLAP● SQL Server 2012 including specific extensions: OLE DB for OLAP Schema Rowsets
From SP11 onwards most of the views can be queried without the MDX prefix. In such cases, use the schema _SYS_BI, as shown in the following example:
Sample Code
SELECT * FROM _SYS_BI.BIMC_DIMENSIONS
The following tables still need to be queried with the MDX prefix because they depend not only on the metadata but on the data as well:
● BIMC_HIERARCHIES● BIMC_LEVELS● BIMC_MEMBERS
Sample Code
MDX SELECT * FROM BIMC_HIERARCHIES where CUBE_NAME = 'MyCube'
NoteWe recommend not to use the schema "_SYS_BI" when the statements are prefixed with MDX. From SPS12 onwards, the MDX prefix is required only for HIERARCHIES, LEVLES, and MEMBERS.
SAP HANA Analytics Catalog (BIMC Views) ReferenceOverview P U B L I C 3
2 BIMC Tables and Views
The following sections provide an overview of BIMC tables and views.
2.1 View BIMC_CUBES
A view of the BIMC_ALL_CUBES table with restriction on INTERNAL_CUBE = 0. Displaying only externally visible views (cubes). The BIMC_CUBES view lists only those views for which the user has select privileges, so the list of cubes displayed to the end user can be restricted by using SQL privileges. Relevant columns that need to be requested to get repository texts in column DESCRIPTION for versions below SPS10: CUBE_NAME and CATALOG_NAME
2.2 Table BIMC_ALL_CUBES
This table contains one header entry for each deployed view (a cube or a view enabled for multidimensional reporting). The columns CATALOG_NAME, SCHEMA_NAME and CUBE_NAME have the same semantics and are included in all the other tables. This table is currently not accessible with the MDX prefix. Attribute views do not appear in it.
NoteThis table is currently not accessible with the MDX prefix. Attribute views do not appear in it.
Table 1:
Column Description
CUBE_ID Unique identifier for the view (this ID is not stable and is only used for internal purposes)
CATALOG_NAME REPO 1: Package of the view. HDI: <runtime container>.<namespace>. For example, RTC1.sap.fi. In case of free-style names, just <runtime container>, for example RTC1.
SCHEMA_NAME Deployment schema. For XSC, it is be filled in with the deployment schema _SYS_BIC. For XSA or HDI, it contains the name of the runtime container.
CUBE_NAME Name of the view (analytic view, calculation view)
4 P U B L I CSAP HANA Analytics Catalog (BIMC Views) Reference
BIMC Tables and Views
Column Description
QUALIFIED_NAME Available from SP11 (for HDI). Contains the full qualified name (including namespace, if configured) of an HDI-based view - for example, sap.fi::CostCenter. Contains null for Repo 1 views.
CUBE_TYPE It is always 'CUBE', as required by MS Excel (ODBO clients). HDI: Contains the data category 'DIMENSION' for views of type DIMENSION, 'CUBE' for views of type CUBE, and 'DEFAULT' for views with an empty data category.
CUBE_GUID Always NULL (deprecated)
CREATED_ON Contains the same time stamp as LAST_SCHEMA_UPDATE. MDX standard rowset: Should contain the time stamp when the cube was created (activated for the first time)
LAST_SCHEMA_UPDATE Time stamp of the last activation of the cube
SCHEMA_UPDATED_BY Contains the user name that last activated the cube
LAST_DATA_UPDATE Contains the same time stamp as LAST_SCHEMA_UPDATE. MDX standard rowset: Should contain the time stamp when the low-level data was last updated or when the schema was last updated.
DATA_UPDATED_BY Currently contains the same user as SCHEMA_UPDATED_BY. MDX standard rowset: Should contain the user name that last changed the data.
DESCRIPTION Default description or label of the cube
ANNOTATIONS Any annotations for the cube. The default is NULL.
COLUMN_OBJECT Name of the catalog object (the generated column view or table) to read the data from. The default is the schema _SYS_BIC for this. So a cube "FM_SALES" in the package "my_package" would have the column view "_SYS_BIC"."my_package/FM_SALES".
COLUMN_OBJECT_TYPE Contains the type of the catalog column view:
● OLAP: OLAP View● CALC: Calculation View● JOIN: Join view
NoteIn case an analytic view is deployed as an OLAP View with a Calculation View on top, the type will be CALC. See "SYS"."VIEWS"."VIEW_TYPE"
DEFAULT_SCHEMA Default schema of the cube. It is used to find, for example, the TCUR* tables needed for currency conversion.
SAP HANA Analytics Catalog (BIMC Views) ReferenceBIMC Tables and Views P U B L I C 5
Column Description
INTERNAL_CUBE INT 0=false, 1=true. This is a flag indicating whether the cube is exposed through the MDX metadata interface. Models having this flag set to 1, are not to be consumed by multidimensional clients, that means that they have DataCategory set to <blank> or DIMENSION. These models still need to be added to the BIMC tables to enable the data preview in the studio, for example. With the following SQL (working from SPS11 onwards) you can only select CUBEs and DIMENSIONs (but not views with empty data category).
Sample Code
select c.*from _sys_bi.bimc_all_authorized_cubes as c left outer join _sys_bi.bimc_all_dimensions as d on c.catalog_name = d.catalog_name and c.catalog_name = d.dimension_catalog_name and c.cube_name = d.cube_name and c.cube_name = d.dimension_namewhere c.QUALIFIED_NAME is null and ( c.internal_cube = 0 or d.is_private_attribute = 0 )union all select * from _sys_bi.bimc_all_authorized_cubes where qualified_name is not null and cube_type in ('CUBE', 'DIMENSION');
HISTORY_ENABLED INT 0=false, 1=true. This is a flag indicating whether the cube is history enabled. If set to true it makes sense to select the data from a certain time stamp onwards by queries such as
SELECT ... FROM <view> AS OF UTCTIMESTAMP <timestamp>For more information, see the SQL and System View reference: http://help.sap.com/hana/sap_hana_sql_and_system_views_reference_en.pdf Chapter 1.8.1.14.1
CATALOG_DESCRIPTION Description of the cube package. Displays the description only if it differs from the package name. Not supported with HDI.
HIERARCHIES_SQL_ENABLED INT 0 = false, 1 = true. If this flag is true, it indicates on cube level whether all hierarchies are enabled for SQL filtering and aggregation.
6 P U B L I CSAP HANA Analytics Catalog (BIMC Views) Reference
BIMC Tables and Views
2.3 View BIMC_ALL_AUTHORIZED_CUBES
This view contains the same columns as BIMC_ALL_CUBES but lists only the views for which the user has SELECT privileges. It also shows views of the data category DIMENSION and DEFAULT, while BIMC_CUBES only shows views of data category CUBE.
2.4 View BIMC_REPORTABLE_VIEWS
This view is available from HANA 2.0 SPS00 onwards. Many clients need to offer cubes and dimensions (master data reporting) to their users. Retrieving the right rows is somewhat complicated and the view BIMC_REPORTABLE_VIEWS returns the correct views. The view has the same columns as BIMC_(ALL_AUTHOIRZED_)CUBES. As long as this view is not there, you can use the following select statement from SPS11 onwards:
Sample Code
select c.* from _sys_bi.bimc_all_authorized_cubes as c make sure REPO1 calc views of type dimension are addedleft outer join _sys_bi.bimc_all_dimensions as d on c.catalog_name = d.catalog_name and c.catalog_name = d.dimension_catalog_name and c.cube_name = d.cube_name and c.cube_name = d.dimension_namewhere c.qualified_name is null and ( c.internal_cube = 0 or d.is_private_attribute = 0 )HDI viewsunion all select * from _sys_bi.bimc_all_authorized_cubes where qualified_name is not null and cube_type in ('CUBE', 'DIMENSION') add attribute views from BIMC_DIMENSIONSunion allselect dimension_id as cube_id, catalog_name, schema_name, dimension_name as cube_name, qualified_name, 'DIMENSION' as cube_type, null as cube_guid, null as created_on, last_schema_update, null as schema_updated_by, null as last_data_update, null as data_updated_by, description, annotations, column_object, null as default_schema, 1 as internal_cube, concat( concat(CATALOG_NAME, '/'), DIMENSION_NAME) as view_name, 0 as history_enabled, null as catalog_description, 0 as hierarchies_sql_enabled from _sys_bi.bimc_dimensions where cube_name = '$ATTRIBUTE';
SAP HANA Analytics Catalog (BIMC Views) ReferenceBIMC Tables and Views P U B L I C 7
2.5 Table BIMC_DIMENSIONS
This table contains one entry for each dimension of the model, information about attribute views (in that case, the column CUBE_NAME is filled with '$ATTRIBUTE'), and one entry for the Measures dimension.
Table 2:
Column Description
DIMENSION_ID Unique identifier for the dimension (attribute view)
CATALOG_NAME Package of the cube
SCHEMA_NAME Deployment schema. For XSC: it will be filled with the deployment schema _SYS_BIC. For XSA or HDI: It contains the name of the runtime container.
CUBE_NAME Name of the view (analytic view, calculation view)
QUALIFIED_NAME Available from SP11 (for HDI). Contains the full qualified name (including a namespace, if configured) of an HDI-based view - for example, sap.fi::CostCenter. Contains null for Repo 1 views.
DIMENSION_NAME Name of the dimension
DIMENSION_UNIQUE_NAME Unique name of the dimension in MDX style, for example [Customer]
DIMENSION_GUID Always NULL (deprecated)
DIMENSION_CAPTION Taken over from the description or label in the modeler
DIMENSION_ORDINAL Ordinal number of the model dimension as defined by the modeler starting from 0. 0 is always [Measures].
DIMENSION_TYPE TIME = 1, MEASURES = 2, OTHER = 3
DIMENSION_CARDINALITY Number of distinct members
DEFAULT_HIERARCHY Unique name of the default hierarchy
DESCRIPTION Not used
ANNOTATIONS Not yet supported in the modeler
COLUMN_OBJECT Name of the catalog object (the generated column view or table) from where to read the dimension or master data. This is the dimension view in case of shared dimension or the column view in case of local dimensions or left outer joins. See also the comments for the field BIMC_ALL_CUBES.COLUMN_OBJECT.
DIMENSION_CATALOG_NAME Package of the dimension
8 P U B L I CSAP HANA Analytics Catalog (BIMC Views) Reference
BIMC Tables and Views
Column Description
IS_PRIVATE_ATTRIBUTE Contains 1 if the dimension consists just of a private attribute. This means that a local attribute is not joined to an attribute view and the dimension contains just one attribute and only the flat standard hierarchy exists.
Contains 0 if the dimension is explicitly modeled. This means that the model is either represented by a (shared) attribute view - the current view is joined to an attribute view and all the attributes and hierarchies defined in the attribute view are used for the dimension, or the hierarchy is explicitly modeled as part of the current view.
LAST_SCHEMA_UPDATE Timestamp from the last activation of the dimension (the activation of the corresponding attribute or dimension calc view). Note that the field may be left empty for private or local attributes.
DIMENSION_ORIGIN Describes the origin of the dimension:
● View: the dimension is defined by a shared view● Attribute: the dimension is defined by just one attribute
<=> IS_PRIVATE_ATTRIBUTE = 1● Hierarchy: the dimension is defined by a (local) hierar
chy
2.6 Table BIMC_HIERARCHIES
This virtual table contains the hierarchies. The modeled hierarchies are stored here and, in addition, one (attribute) hierarchy for each dimension attribute is created in this table automatically.
Table 3:
Column Description
HIERARCHY_ID Unique identifier for the hierarchy
CATALOG_NAME Package of the cube
SCHEMA_NAME Deployment schema. For XSC, it is be filled in with the deployment schema _SYS_BIC. For XSA or HDI, it contains the name of the runtime container.
CUBE_NAME Name of the view (analytic view, calculation view)
QUALIFIED_NAME Available from SPS11 onward (for HDI). Contains the full qualified name (including a namespace, if configured) of an HDI-based view - for example, sap.fi::CostCenter. Contains null for Repo 1 views.
DIMENSION_UNIQUE_NAME Unique name of the dimension the hierarchy belongs to
SAP HANA Analytics Catalog (BIMC Views) ReferenceBIMC Tables and Views P U B L I C 9
Column Description
HIERACHY_NAME Name of the hierarchy
HIERARCHY_UNIQUE_NAME Unique name of the hierarchy in MDX style, for example [Time].[Fiscal_Year]
HIERARCHY_GUID Always NULL (deprecated)
HIERARCHY_CAPTION Description or label of the hierarchy
DIMENSION_TYPE Type of the dimension - TIME = 1, MEASURES = 2, OTHER = 3
HIERARCHY_CARDINALITY Number of distinct members - not filled in because the hierarchy needs to be instantiated for this and the field is dependent on the data
DEFAULT_MEMBER A unique name for the default member
NoteSelect this column only if it is really needed. If DEFAULT_MEMBER is selected the hierarchies need to be instantiated first to fulfill the query
ALL_MEMBER A unique name for ALL_MEMEBERS or NULL if there is no name
DESCRIPTION Not used - always NULL
ANNOTATIONS Not used - always NULL
STRUCTURE Type of the hierarchy - 0 = Fully Balanced, 1 = Ragged Balanced, 2 = Unbalanced, 3 = Network.
MODELED Indicator whether the hierarchy is modeled in the studio - INT 0=false, 1=true.
HIERARCHY_VIEW Catalog name of the generated hierarchy view to read the hierarchy data from.
Use the following SQL statement to determine the catalog hierarchy view:
Sample Code
SELECT DISTINCT "COLUMNOBJECT_NAME" FROM "_SYS_BI"."BIMC_PROPERTIES" WHERE ( "HIERARCHY_UNIQUE_NAME" = '[COPY_AT_STUDENT].[STUDENT_HIER]' AND "CUBE_NAME" = 'AN_HIER_VI' AND "CATALOG_NAME" = 'hanae2e.rahulj.sp5' AND "DIMENSION_UNIQUE_NAME" = '[COPY_AT_STUDENT]' AND "PROPERTY_NAME" = 'HIERARCHY_VIEW' )
10 P U B L I CSAP HANA Analytics Catalog (BIMC Views) Reference
BIMC Tables and Views
Column Description
LEAF_ATTRIBUTE Contains the attribute representing the leaf level of the hierarchy.
● For level-based hierarchies: the attribute defining the most granular level (the level with the biggest number)
● For parent-child hierarchies: the child attribute of the hierarchy
● For compound hierarchies: a comma separated list of the quoted child attributes, for example "ControllingArea", "Cost center".
NODE_COLUMN_NAME Available from SPS10 onwards. Contains the view column representing the node column of the hierarchy.
EXPRESSION_PARAMETER_NAME Available from SPS10 onwards. Contains the expression parameter name of the hierarchy.
HIERARCHY_ORIGIN Available from HANA 2.0 SPS00 onwards. Possible values are:
● MD_USER_DEFINED = 0x0000001 - identifies user-defined hierarchies
● MD_SYSTEM_ENABLED = 0x0000002 - identifies attribute hierarchies
● MD_SYSTEM_INTERNAL = 0x0000004 - identifies attributes with no attribute hierarchies
● A parent/child attribute hierarchy is both MD_USER_DEFINED and MD_SYSTEM_ENABLED (=3)
ROOT_NODE_VISIBILITY Available from HANA 2.0 SPS00 onwards. Possible values are:
● False: do not add root node● True: add root node● Auto: add root node, if defined
HAS_NOT_ASSIGNED_MEMBERS Available from HANA 2.0 SPS00 onwards. Possible values are:
● 1: This is a hierarchy with the Not Assigned Member option.
● 0 or null: This is a hierarchy without the Not Assigned Member option.
NoteThe Not Assigned Member option in hierarchies in HANA models allows MDX/MDX/InA/SQL queries on HANA models to include booked values in facts that have no corresponding masterdata values in the respective hierarchies.
SAP HANA Analytics Catalog (BIMC Views) ReferenceBIMC Tables and Views P U B L I C 11
BIMC_HIERARCHIES table is a virtual table. It does not exist physically. The metadata containing the results from a "SELECT from BIMC_HIERARCHIES" statement is held inside internal data structures and a result set for the SELECT statement is constructed from those internal data structures and not from an SQL table. Any SQL statement issued against it goes through a minimal SQL parser inside the MDX engine, not through the regular SQL processor. The minimal parser can perform any request required by ODBO. The ODBO specification does not support wildcards, so the minimal SQL parser does not support them either.
The ODBO specification requires support for restrictions on the rows returned - CATALOG_NAME, SCHEMA_NAME, CUBE_NAME, DIMENSION_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME, HIERARCHY_NAME. You can use any predicates listed here in your WHERE clause (without wildcards).
NoteA single * wildcard for "all fields" is supported but no other wildcards; otherwise you must explicitly list the fields that you want to fetch.
Currently you must also specify a WHERE clause with at least one predicate in your SQL to constrain the results so that you do not request all hierarchies from all dimensions in all cubes from all catalogs.
A simple ORDER BY is also allowed. The requirement is that any fields in the ORDER BY statement must be present in the result set. For example, you cannot have the following statement because C will not be in the results set.
Sample Code
MDX SELECT a,b FROM <a BIMC table> ORDER BY c,a,b
Note that the ODBO specification specifies that the sort order should be by CATALOG_NAME, SCHEMA_NAME, CUBE_NAME, DIMENSION_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME. The code will return results in this order. You do not need to specify an ORDER clause unless you need any other ordering.
2.7 Table BIMC_LEVELS
This table contains the levels within the hierarchies.
Table 4:
Column Description
CATALOG_NAME Package of the cube
SCHEMA_NAME Deployment schema. For XSC, it is filled in with the deployment schema _SYS_BIC. For XSA or HDI, it contains the name of the runtime container.
CUBE_NAME Name of the view (analytic view, calculation view)
12 P U B L I CSAP HANA Analytics Catalog (BIMC Views) Reference
BIMC Tables and Views
Column Description
QUALIFIED_NAME Available from SPS11 onwards (for HDI). Contains the full qualified name (including a namespace, if configured) of an HDI-based view - for example, sap.fi::CostCenter. Contains null for Repo 1 views.
LEVEL_ID Unique identifier for the level. (No longer exists or not supported)
DIMENSION_UNIQUE_NAME Unique name of the dimension the hierarchy belongs to
HIERARCHY_UNIQUE_NAME Unique name of the hierarchy the level belongs to
LEVEL_NAME Name of the level
LEVEL_UNIQUE_NAME Unique name of the level in MDX style. For example, [Time].[Fiscal_Year].[Fiscal_Period]
LEVEL_GUID Always NULL (deprecated)
LEVEL_CAPTION Description of the level
LEVEL_NUMBER Number of parent levels. 0 if this is the top or 'all' level.
LEVEL_CARDINALITY Number of distinct members
LEVEL_TYPE Type of the level: 0 = Regular, 1 = All, 4 = General Time.
Time Dimension types: 20 = Year, 36 = Half-Year, 68 = Quarter, 132 = Month, 260 = Week, 516 = Day, 772 = Hour, 1028 = Minutes, 2052 = Seconds
For more information, see http://www.olap4j.org/2.0/api/org/olap4j/metadata/Level.Type.html. There the values are listed in hexadecimal code:
● MDLEVEL_TYPE_REGULAR (0x0000)● MDLEVEL_TYPE_ALL (0x0001)● MDLEVEL_TYPE_TIME_YEARS (0x0014)● MDLEVEL_TYPE_TIME_HALF_YEAR (0x0024)● MDLEVEL_TYPE_TIME_QUARTERS (0x0044)● MDLEVEL_TYPE_TIME_MONTHS (0x0084)● MDLEVEL_TYPE_TIME_WEEKS (0x0104)● MDLEVEL_TYPE_TIME_DAYS (0x0204)● MDLEVEL_TYPE_TIME_HOURS (0x0304)● MDLEVEL_TYPE_TIME_MINUTES (0x0404)● MDLEVEL_TYPE_TIME_SECONDS (0x0804)● MDLEVEL_TYPE_TIME_UNDEFINED (0x1004)
DESCRIPTION Not Used
The BIMC_LEVELS table is a virtual table. It does not exist physically. The metadata containing the results from a SELECT FROM BIMC_LEVELS statement is held inside internal data structures and a results set for the SELECT clause is constructed from those internal data structures and not from an SQL table. Any SQL query issued against it goes through a minimal SQL parser inside the MDX engine, not through the regular SQL
SAP HANA Analytics Catalog (BIMC Views) ReferenceBIMC Tables and Views P U B L I C 13
processor. The minimal parser can carry out any request required by ODBO. The ODBO specification does not support wildcards and the minimal SQL parser does not support them either.
The ODBO specification requires support for restrictions on the rows returned - CATALOG_NAME, SCHEMA_NAME, CUBE_NAME, DIMENSION_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME, LEVEL_NAME, LEVEL_UNIQUE_NAME. You can use any predicates listed here in your WHERE clause (without any wildcards).
NoteA single * wildcard for "all fields" is supported but no other wildcards. If you do not use that wildcard, you must explicitly list the fields that you want to fetch.
Currently you must also specify a WHERE clause with at least one predicate in your SQL too, so you cannot request all levels of all hierarchies of all dimensions of all cubes.
A simple ORDER BY is also allowed. The requirement is that any fields in the ORDER BY must be present in the results set. For example, you cannot have the following statement because "c" is not in the result set.
Sample Code
MDX SELECT a,b FROM <a BIMC table> ORDER BY c,a,b
According to the ODBO specification, the sorting order should be by CATALOG_NAME, SCHEMA_NAME, CUBE_NAME, DIMENSION_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME, LEVEL_NUMBER. The code returns results in this order. You do not need to specify an ORDER clause, unless you need another type of ordering.
2.8 View BIMC_MEASURES
Contains the information about the measures in a cube.
Table 5:
Column Description
CATALOG_NAME Package of the cube
SCHEMA_NAME Deployment schema. For XSC, it is be filled in with the deployment schema _SYS_BIC. For XSA or HDI, it contains the name of the runtime container.
CUBE_NAME Name of the view (analytic view, calculation view)
QUALIFIED_NAME Available from SPS11 (for HDI). Contains the full qualified name (including a namespace, if configured) of an HDI-based view - for example, sap.fi::CostCenter. Contains null for Repo 1 views.
14 P U B L I CSAP HANA Analytics Catalog (BIMC Views) Reference
BIMC Tables and Views
Column Description
MEASURE_ID Unique identifier for the measure. Note that this is not the same as the MEMBER_ID for the measure).
NoteThe field no longer exists and is not supported. Please do not request this field in your SQL statements.
MEASURE_NAME Name of the measure
MEASURE_UNIQUE_NAME Unique name of the measure in MDX style, for example [Measures].[store_sales]
MEASURE_CAPTION Description of the measure
MEASURE_GUID Always NULL (deprecated)
MEASURE_AGGREGATOR Describes the type of aggregation for the measure:
● 1 = SUM (= MDMEASURE_AGGR_SUM)● 2 = COUNT (= MDMEASURE_AGGR_COUNT),● 3 = MIN (= MDMEASURE_AGGR_MIN)● 4 = MAX (= MDMEASURE_AGGR_MAX)
NoteFor more information, see MEASURES Rowset
Clients need to use this aggregation function for the measure when querying the measure via SQL:
Select ATTRIBUTE1..., sum( MEASURE1), max(MEASURE2)... from CUBE group by ATTRIBUTE1...
NoteThis does not always mean that the client can use this aggregation function to further aggregate the measure because for calculated measures and for counters this usually results in wrong numbers. Client aggregation is only allowed if MEASURE_AGGREGATABLE = 1.
MEASURE_AGGREGATABLE INTEGER:
● 1: The measure may be aggregated further by the clients with the aggregation function listed in the previous column MEASURE_AGGREGATOR.
● 0:The measure may not be aggregated further by the clients because this would lead to wrong results.
DATA_TYPE OLEDB representation of the measure type. 3 = Int, 5 = Double, 131 = Numeric and so on
NUMERIC_PRECISION Maximum precision of the measure
SAP HANA Analytics Catalog (BIMC Views) ReferenceBIMC Tables and Views P U B L I C 15
Column Description
NUMERIC_SCALE NULL except for DATA_TYPE 131 where the scale of the measure is stored
MEASURE_UNITS Indicates what string should be appended by the clients to indicate the unit of measure (contains the same value as BIMC_MEASURES.UNIT_TYPE)
● If the measure has a fixed unit or currency assigned, it contains this unit (for example, EUR, KG)
● If the measure has a parameter or another view column assigned for determining the target currency or unit of measure, it contains an empty string.
DESCRIPTION Label of the measure
UNIT_TYPE Contains the currency code (for example, USD, EUR and so on), if the measure has a fixed unit or currency; has same value as BIMC_DIMENSION_VIEW-UNIT_TYPE.
UNIT_COLUMN_NAME Contains the name of the attribute/column containing the unit/currency in case the measure has a variable unit/currency. This happens in the following two cases:
● The target unit/currency is defined by another column of the view. In that case this column is contained in UNIT_COLUMN_NAME.
● The target unit/currency is determined by a parameter. In that case UNIT_COLUMN_NAME contains the generated target currency column which has the name <measure>.CURRENCY. This column is added to the view definition at deploy time. At run time the engine fills it in with the result unit/currency (null in case of errors or the value of the parameter). The column has the same value as BIMC_DIMENSION_VIEW-UNIT_COLUMN_NAME.
MEASURE_DISPLAY_FOLDER The path to be used when displaying the measure on the user interface. Folder names are separated by a semicolon. Nested folders are indicated by a backslash (\). For more information, see measure_display_folder in MDSCHEMA_MEASURES Rowset .
A measure can be assigned to several folders. For example, if a measure is assigned to the foder ABC and the subfolder XYZ of MNQ, the value of the property will be ABC;MNQ\XYZ.
The BIMC_MEASURES table is a virtual table. It does not exist physically. The metadata containing the results from a SELECT FROM BIMC_MEASURES statement is held inside internal data structures. A set of results for the SELECT clause is constructed from those internal data structures and not from an SQL table. Any SQL query issued against it goes through a minimal SQL parser inside the MDX engine, not through the regular SQL processor. The minimal parser can carry out any request required by ODBO. The ODBO specification does not support wildcards and the minimal SQL parser does not support them either.
16 P U B L I CSAP HANA Analytics Catalog (BIMC Views) Reference
BIMC Tables and Views
The ODBO specification requires support for restrictions on the rows returned - CATALOG_NAME, SCHEMA_NAME, CUBE_NAME, MEASURE_NAME, MEASURE_UNIQUE_NAME. You can use any predicates listed here in your WHERE clause (without any wildcards).
NoteA single * wildcard for "all fields" is supported but no other wildcards. If you do not use that wildcard, you must explicitly list the fields that you want to fetch.
Currently, you must also specify a WHERE clause with at least one predicate in your SQL too, so you cannot request all levels of all hierarchies of all dimensions of all cubes.
A simple ORDER BY statement is also allowed. The requirement is that any fields in the ORDER BY statement must be present in the results set. For example, you cannot have the following statement because "c" is not in the result set.
Sample Code
MDX SELECT a,b FROM <a BIMC table> ORDER BY c,a,b
According to the ODBO specification, the sorting order should be by CATALOG_NAME, SCHEMA_NAME, CUBE_NAME, and MEASURE_NAME. The code returns results in this order. You do not need to specify an ORDER clause, unless you need another type of ordering.
For releases below SPS10, the relevant columns that need to be requested to get repository texts in column MEASURE_CAPTION are CUBE_NAME, CATALOG_NAME, and MEASURE_NAME.
2.9 Table BIMC_MEMBERS
A virtual table containing details about the members of the levels within the cube.
Table 6:
Column Description
CATALOG_NAME Package of the cube
SCHEMA_NAME Deployment schema. For XSC, it is be filled in with the deployment schema _SYS_BIC. For XSA or HDI, it contains the name of the runtime container.
CUBE_NAME Name of the view (analytic view, calculation view)
QUALIFIED_NAME Available from SPS11 (for HDI). Contains the full qualified name (including a namespace, if configured) of an HDI-based view - for example, sap.fi::CostCenter. Contains null for Repo 1 views.
DIMENSION_UNIQUE_NAME Unique name of the dimension to which the hierarchy belongs.
SAP HANA Analytics Catalog (BIMC Views) ReferenceBIMC Tables and Views P U B L I C 17
Column Description
HIERARCHY_UNIQUE_NAME Unique name of the hierarchy that the member belongs to.
LEVEL_UNIQUE_NAME Unique name of the level the member belongs to
LEVEL_NUMBER Level number of the level the member belongs to
MEMBER_ORDINAL An incrementing number representing the position of the member in the hierarchy
MEMBER_UNIQUE_NAME Unique name of the member in MDX style. For example, [Measures].[Store_Sales].
MEMBER_TYPE Type of the member: 1 = Regular, 2 = All, 3 = Measure
MEMBER_GUID Always NULL - use is deprecated
MEMBER_CAPTION Description of the member
CHILDREN_CARDINALITY Number of children of the member. 0 means the member is a leaf item.
PARENT_LEVEL The level number of the member's parent member (1 if the member has no parent)
PARENT_UNIQUE_NAME The name of the member's parent or NULL if it does not have one
PARENT_COUNT The number of parents the member has. 1 row for the member will be present for each parent. Currently, only 0 or 1 parents are valid.
DESCRIPTION Not used
MEMBER_KEY For leaf children the key value of the underlying dimension table if it exists or NULL otherwise
BIMC_MEMBERS table is a virtual table and does not exist physically exist. The meta data comprising the results from a SELECT from BIMC_MEMBERS statement is held inside internal data structures. A result set for the SELECT statement is constructed from those internal data structures and not from an SQL table. Any SQL issued against it goes through a minimal SQL parser inside the MDX engine, not through the regular SQL processor. The minimal parser can carry out any request required by ODBO. The ODBO specification does not support wildcards and the minimal SQL parser does not support them either.
The ODBO specification requires support for restrictions on the rows returned. For more information, see Restrictions in the MEMBERS Rowset
You can use any predicates listed here in your WHERE clause. In that particular case, there is no wildcard support within SQL, so you need to explicitly list the fields that you want to fetch.
You must also specify a WHERE clause with at least one predicate in your SQL statement. A single * wildcard for "all fields" is supported.
A simple ORDER BY is also allowed. The requirement is that any fields in the ORDER BY clause must be present in the result set. For example, you cannot have the statement below because "c" will not be in the result set.
18 P U B L I CSAP HANA Analytics Catalog (BIMC Views) Reference
BIMC Tables and Views
Sample Code
MDX SELECT a,b FROM <a BIMC table> ORDER BY c,a,b
According to the ODBO specification, the order of sorting should be by CATALOG_NAME, SCHEMA_NAME, CUBE_NAME, DIMENSION_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME, LEVEL_UNIQUE_NAME, LEVEL_NUMBER, and MEMBER_ORDINAL. The code returns results in this order and you do not need to specify an ORDER clause, unless you need another type of sorting.
NoteIn order to calculate the members, you need to pass the parameter values entered by the user. For that purpose, use the following syntax:
mdx select * from BIMC_MEMEBERS ('PLACEHOLDER' = ('$$P1$$', 'Value1'), 'PLACEHOLDER' = ('$$P2$$', 'Value2'), 'PLACEHOLDER' = ('$$P_ROOT_NODE1$$', 'Value3') ) where CATALOG_NAME = 'mini' and CUBE_NAME = 'CV_SALES' and HIERARCHY_UNIQUE_NAME = '[H1].[H1]'
2.10 View BIMC_DIMENSION_VIEW
This is a virtual view of BIMC_PROPERTIES with the filter WHERE PROPERTY_TYPE = 4 and COLUMN_FLAG = 'Dimension Attribute'. It contains one row with the most prominent properties for each attribute and each measure of the cube. This view is not known to the database catalog, so you need to use the MDX prefix when querying it.
Table 7:
Column Description
CATALOG_NAME Package of the cube
CUBE_NAME Name of the cube
QUALIFIED_NAME Available from SPS11 (for HDI). Contains the full qualified name (including a namespace, if configured) of an HDI-based view - for example, sap.fi::CostCenter. Contains null for Repo 1 views.
DIMENSION_UNIQUE_NAME Unique name of the dimension
SAP HANA Analytics Catalog (BIMC Views) ReferenceBIMC Tables and Views P U B L I C 19
Column Description
COLUMNOBJECT_NAME The catalog name of the view to get the value help from. The format is "<schema>"."<name>".
This is the column view which is generated either for the dimension (attribute or calculation) view for attributes originating from the dimension, or for the cube (analytic or calculation) view for attributes coming from the data foundation or fact table.
However, if you have a left outer join to the dimension view, it also contains the column view generated for the analytic or calculation view.
The modeler can maintain an arbitrary value help view (table) for each attribute. In case such a view is maintained for the attribute, its catalog name is listed in this field.
DIMENSION_COLUMN_NAME The column name in the value help view stored in COLUMNOBJECT_NAME. Since aliasing dimension attributes inside the star join are allowed, this name might be different from COLUMN_NAME. The name is unique only within the value help view stored in COLUMNOBJECT_NAME.
If a separate value help view (table) is maintained for the attribute, the column name to get the value help from is listed in this field.
COLUMNOBJECT_REPOSITORY_FQN Contains the fully qualified name of the COLUMNOBJECT_NAME in case it is a repository object.
Use this name to query BIMC_DIMENSION_VIEW in order to find out the description field of DIMENSION_COLUMN_NAME in COLUMNOBJECT_NAME. If such a description field exists, it should be displayed in the value help in addition to the DIMENSION_COLUMN_NAME.
Contains null in case COLUMNOBJECT_NAME is a pure catalog object.
COLUMN_NAME The column name in the main view, that is the view name in BIMC_CUBES.COLUMN_OBJECT. This name is globally unique in the cube (might be aliased).
COLUMN_TYPE Either "NULL" or the column engine data type. For example 66 = fixed, 73 = int, 83 = string, and so on.
COLUMN_TYPE_D The (internal) column engine data type description (FIXED, INT, STRING, and so on). Please use the official COLUMN_SQL_TYPE wherever possible.
20 P U B L I CSAP HANA Analytics Catalog (BIMC Views) Reference
BIMC Tables and Views
Column Description
COLUMN_SQL_TYPE The SQL type of the column as in the official SQL data type reference.
In case you need to specify length for the data type, you need to add it in brackets without any spaces. For example, VARCHAR(12), FLOAT(2) or ALPHANUM(66). In case a precision and scale is specified, it is added in brackets without spaces separated by a comma, for example DECIMAL(12,2) where 12 is the precision and 2 the scale.
COLUMN_CAPTION The label (or caption) of the column. Use this (language-dependent) text to populate the caption or the label of the column or the attribute.
DESC_OBJECT_NAME The catalog name of the view to look up the dimension description attribute (format "<schema>"."<name>"). Not relevant for HANA views because it contains always the same view as COLUMNOBJECT_NAME. It is relevant for CDS analytics because texts for dimension attributes can be retrieved from separate text tables or views.
DESC_NAME The column name of the description attribute in the main view - that is, the view name in BIMC_CUBES.COLUMN_OBJECT. This name is globally unique in the cube (may be aliased).
In case a separate value help view (or table) is maintained, DESC_NAME contains the same column name as COLUMN_NAME.
DIMENSION_DESC_NAME The column name of the dimension description attribute in the value help view stored in COLUMNOBJECT_NAME. Since we allow aliasing dimension attributes inside a cube this name might be different from DESC_NAME. This name is only unique within the value help view stored in COLUMNOBJECT_NAME.
From SP09: In case a separate value help view (table) is maintained for the attribute the column name to get the descriptions for the values from will be listed in this field.
DESC_TYPE Internal attribute type of the dimension description attribute. For example, 66 = fixed, 73 = int, 83 = string.
DESC_TYPE_D See COLUMN_TYPE_D for the description column
DESC_COLUMN_SQL_TYPE See COLUMN_SQL_TYPE for the description column
KEY_COLUMN_NAME The value of the COLUMN_NAME if it is key of COLUMN_OBJECTNAME or NULL if not
SAP HANA Analytics Catalog (BIMC Views) ReferenceBIMC Tables and Views P U B L I C 21
Column Description
DISPLAY_COLUMN_NAME The value of the COLUMN_NAME if this column should not be used for drilling down or navigation in tools like AAO. The name of the column stems from the BW notion of a display-only attribute - it can only be displayed as a detail of its InfoObject or Dimension, but it cannot be used for drilling down (slicing and dicing).
DIMENSION_CAPTION Description of the dimension
DIMENSION_TYPE Dimension type: TIME = 1, MEASURES = 2, OTHER = 3
MEASURE_AGGREGATOR NULL if not a measure or how this measure should be aggregated
MEASURE_AGGREGATABLE INTEGER:
● NULL: if not a measure● 1: the measure may be aggregated further by the cli
ents with the aggregation function listed in the previous column MEASURE_AGGREGATOR
● 0: the measure may not be aggregated further by the clients because that would lead to wrong results. This is the case for calculated attributes (if calculated after the aggregation) and distinct counters.
SEMANTIC_TYPE Semantic type of the column:
● AmountValue (a measure with the semantic type amount)
● QuantityValue (a measure with the semantic type quantity)
● currencyCode● unitOfMeasure● date (can be used to indicate VARCHAR(8) fields carry
a data semantically)● date.businessDateFrom● date.businessDateTo● geoLocation.longitude● geoLocation.latitude● geoLocation.cartoId● geoLocation.normalizedName
MEASURE_TYPE Use is deprecated. Use column MEASURE_AGGREGATABLE instead to check whether a measure may be further aggregated in the client tools.
Describes how the measure is defined internally. This information may be needed by tools for performing calculations on top of the measure. This field contains the values restricted, base, calculated and aggregatableCalculated.
INFO_OBJECT The name of the InfoObject assigned to the attribute or the measure
22 P U B L I CSAP HANA Analytics Catalog (BIMC Views) Reference
BIMC Tables and Views
Column Description
UNIT_COLUMN_NAME Name of the attribute or column that contains the currency or unit in case the measure has a variable currency or unit. This happens in the following cases:
● The (target) currency orunit is defined by another column of the view. In that case this column is contained in UNIT_COLUMN_NAME.
● The target currency is determined by a parameter. In that case UNIT_COLUMN_NAME in general contains the generated target currency field. This column is added to the view definition at deploy time. At run time the engine fills it with the result currency unit (null in case of errors or the value of the parameter otherwise)
UNIT_TYPE Contains the currency code (for example, USD, EUR , and so on) in case the measure has a fixed unit or currency
CURRENCY_CONVERSION A flag indicating whether a currency conversion is defined for the measure (in that case this field is filled with 1). If not currency conversion is defined or if the column is an attribute the field is filled with null.
IS_PRIVATE_ATTRIBUTE A flag indicating whether the attribute is a private (local) attribute (in that case this field is filled with 1). If it is a measure or a shared attribute the field is filled with 0.
MEASURE_DISPLAY_FOLDER The name of the measure display folder. For more information, see BIMC_MEASURES.
DIMENSION_NAME Dimension name
DIMENSION_CATALOG_NAME Dimension catalog name
CONVERT_TO_EXTERNAL_FUNCTION Available from SPS12: Full qualified name of the scalar function for converting the values of this field into the external format
CONVERT_TO_INTERNAL_FUNCTION Available from SP12: Full qualified name of the scalar function for converting the values of this field into the internal format
CONVERSION_ORDER_PRESERVING Equals 1, if the internal or external conversion functions are order preserving and 0, if not.
IS_TEXT_SEARCHABLE INTEGER, available from SPS12 onwards, contains 1 (true), if the column is text searchable, that is the column is mapped to a table column, which is backed by a text index. It contains 0 (false), if the column is not text searchable. The column is a cache for SYS.CS_VIEW_COLUMNS.IS_TEXT_SEARCHABLE. The column contains NULL in case the view has not been reactivated after the upgrade to SPS12.
SAP HANA Analytics Catalog (BIMC Views) ReferenceBIMC Tables and Views P U B L I C 23
Column Description
IS_FUZZY_SEARCHABLE INTEGER, contains 1 (true), if the column is fuzzy searchable via the CONTAINS SQL clause. It contains 0 (false), if the column is not fuzzy searchable. The column is a cache for SYS.CS_VIEW_COLUMNS.IS_FUZZY_SEARCHABLE. The column contains NULL in case the view has not been reactivated after the upgrade to SPS12.
MODELED_KEY_COLUMN INTEGER; available from SPS12 onwards; contains 1 (true), if the column is explicitly modeled as a key column.
NoteThe column may be filled in with NULL, if the model was activated after upgrading to HANA 2.0 SPS01. This property cannot be derived from KEY_COLUMN_NAME because it is also filled in for the first attribute in each dimension. In a star join, this property is inherited from the dimension views.
2.11 View BIMC_ATTRIBUTE_RELATIONS
Contains the attribute relations defined in the models.
Table 8:
Column Key Description
CATALOG_NAME Yes Package of the cube
SCHEMA_NAME STRING
CUBE_NAME Yes Name of the cube
QUALIFIED_NAME Available from SPS11 (for HDI). Contains the full qualified name (including a namespace, if configured) of an HDI-based view - for example, sap.fi::CostCenter. Contains null for Repo 1 views.
COLUMN_NAME Yes The unique attribute or column name in the view (may be aliased and thus different from the column name in the dimension view)
PARENT_COLUMN_NAME
Yes The name of the parent attribute or column. This is an attribute or column that has an n:1 relationship with the current element. For example, if two records have the same value in COLUMN_NAME, they also have the same value for the column in PARENT_COLUMN_NAME. The example assumes that you have a geography dimension region, country, country_population and continent. Then you would define a relation from country to country_population (the data must fit to this setting). As a result the attribute country_population can be used as a hierarchy level attribute for the level country in case a leveled hierarchy is defined on the fields
24 P U B L I CSAP HANA Analytics Catalog (BIMC Views) Reference
BIMC Tables and Views
2.12 Table BIMC_VARIABLE
This tables contains the list of variables and parameters for each cube.
Table 9:
Column Key Type Description
CATALOG_NAME
Yes STRING Package of the cube
SCHEMA_NAME
STRING From SPS11 onwards: deployment schema or runtime container of the view
CUBE_NAME Yes STRING Name of the cube
QUALIFIED_NAME
STRING Available from SPS11 onwards (for HDI). Contains the full qualified name (including a namespace, if configured) of an HDI-based view - for example, sap.fi::CostCenter. Contains null for Repo 1 views.
VARIABLE_NAME
Yes STRING Name of the variable or parameter
COLUMN_TYPE
INT Either NULL or the column engine data type. For example, 66 = fixed, 73 = int, 83 = string, and so on.
COLUMN_TYPE_D
STRING The (internal) column engine data type description (FIXED, INT, STRING, and so on). Use the official COLUMN_SQL_TYPE wherever possible.
COLUMN_SQL_TYPE
STRING The SQL type of the parameter or variable as in the official SQL data type reference.
In case you need to specify length for the data type, it is added in brackets without any spaces. For example, VARCHAR(12), FLOAT(2) or ALPHANUM(66). In case precision and scale need to be specified, they are added in brackets without spaces and separated by a comma. For example, DECIMAL(12,2) where 12 is the precision and 2 the scale.
VALUE_TYPE STRING Specifies how the values for the variable are determined. Same values as the EMF enumeration: empty / Currency / UnitOfMeasure / Date / AttributeValue / StaticList
SEMANTIC_TYPE
STRING If the VALUE_TYPE is AttributeValue, in other words if the variable/parameter points to a column/attribute, this field should contain the same semantic type as the column/attribute (see BIMC_DIMENSION_VIEW.SEMANTIC_TYPE).
If you have other value types, the following mapping from VALUE_TYPE to SEMANTIC_TYPE exists.
● Currency -> currencyCode● UnitOfMeasure -> unitOfMeasure● Date -> date
SAP HANA Analytics Catalog (BIMC Views) ReferenceBIMC Tables and Views P U B L I C 25
Column Key Type Description
VALUE_ATTRIBUTE
STRING In case VALUE_TYPE = AttributeValue, this field together with BIMC_VARIABLE.VALUE_ENTITY defines how to populate the value help. The following cases are possible:
● Column BIMC_VARIABLE.VALUE_ENTITY does not exist or is not filled in. In this case the column contains the attribute name of the current view (CUBE_NAME) that determines the values and therefore also the value help. In case aliases are defined for the underlying attribute this field contains the alias. You have to read from BIMC_DIMENSION_VIEW where COLUMN_NAME = <VALUE_ATTRIBUTE>. In that record of BIMC_DIMENSION_VIEW the field COLUMNOBJECT_NAME gives you the view and the field DIMENSION_COLUMN_NAME the column to build the value help upon. (Depending on whether an attribute view is joined by inner or outer join, the name of the view and the column might be different).
● Column BIMC_VARIABLE.VALUE_ENTITY exists and is filled in. You simply build the value help by reading from the table or view that you find in column BIMC_VARIABLE.VALUE_ENTITY (the column to use is stored in BIMC_VARIABLE.VALUE_ATTRIBUTE).
VALUE_ENTITY STRING Contains the name of the catalog view or the catalog table for the value help of the variable. Only relevant if BIMC_VARIABLE.VALUE_TYPE = AttributeValue.
VALUE_ENTITY_REPOSI-TORY_FQN
STRING Use this name to query BIMC_DIMENSION_VIEW in order to find out the description field of VALUE_ATTRIBUTE. If such a description field exists, it should be displayed in the value help in addition to the VALUE_ATTRIBUTE. Contains null if the value entity is a pure catalog object.
DESC_ATTRIBUTE
STRING Only relevant if column BIMC_VARIABLE.VALUE_ENTITY is filled in. Contains the description for the column in VALUE_ATTRIBUTE. Tools should read both columns (VALUE_ATTRIBUTE and DESC_ATTRIBUTE) and show them to the end user in the value help. In case no description column is defined for VALUE_ATTRIBUTE, the column is null.
SELECTION_TYPE
STRING What kind of selections are possible - same values as the EMF enumeration:
● SingleValue: only a single value may be entered (multiple single values if MULTILINE=1 in addition)
● Interval: an interval with from (>=) and to (<=) may be entered (multiple intervals if MULTILINE=1 in addition)
● Range: the complete set of select options are supported: ( =, != , <, <=, >, >=, [] - interval, ![] - not in interval, (not) contains pattern/like)
MULTILINE INT 0=false; 1=true
MANDATORY INT 0=false; 1=true
ORDER INT Order in which to present the variables on the UI
DESCRIPTION STRING Description of the variable or parameter
26 P U B L I CSAP HANA Analytics Catalog (BIMC Views) Reference
BIMC Tables and Views
Column Key Type Description
DEFAULT_VALUE
STRING Default value of the variable. If you use an expression like "datae(now())" as a default value, the values will be calculated when the query "MDX select from BIMC_VARIABLES" is executed.
IS_EXPRESSION
INT 0=false: the default value is a constant
1=true: the default value is an expression
Clients do not need to read this field because a default expression is evaluated into a constant at the time when the query "MDX select from BIMC_VARIABLES" is executed.
RANGE_DEFAULTS_EXIST
INT 0=false: no range default values exist, only a single-valued default value in column DEFAULT_VALUE or no default value.
1=true: range default values exist in table BIMC_VARIABLE_RANGE_DEFAULTS. In this case DEFAULT_VALUE must contain the empty string.
HIERARCHY_VIEW
STRING Available from SPS10 onwards. If this field is filled in, a hierarchical value help must be provided. This field contains the name of the hierarchy view to be used for the value help.
NoteThe hierarchy view is based on the column view stored in VALUE_ENTITY. If a parameter mapping is defined from the current view to VALUE_ENTITY, this parameter mapping needs to be applied to the hierarchy view as well.
HIERARCHY_NAME
STRING Available from SPS11 onwards; contains the name of the hierarchy in addition to HIERARCHY_VIEW. Is filled in only if HIERARCHY_VIEW is filled in.
CONVERT_TO_EX-TERNAL_FUNC-TION
STRING Available from SPS12 onwards; contains the full qualified name of the scalar function for converting the values of this field into the external format.
CONVERT_TO_IN-TERNAL_FUNC-TION
STRING Available from SPS12 onwards; contains the full qualified name of the scalar function for converting the values of this field into the internal format.
CONVERSION_OR-DER_PRESERV-ING
INTEGER Equals 1, if the internal or external conversion functions are order preserving and 0, if not.
SAP HANA Analytics Catalog (BIMC Views) ReferenceBIMC Tables and Views P U B L I C 27
Column Key Type Description
HIERARCHY_NODE_STYLE
STRING Available from SPS11onwards; contains the node style to be used for the hierarchy assigned to the variable if you have hierarchy filter variables. It Is filled in with "null" in case the variable is not used as hierarchy filter variable. The possible values and implications for clients are:
Table 10:
Value Syntax for nodes (hierarchy members)
Value to pass to the hierarchy node column
PARENT_CHILD [Attribute].[Value], for example [Employee].[Smith]
Value [Smith]
LEVEL_NAME [Level Attribute].[Value], for example [Customer].[Country]
[Level Attribute].[Value], for example [Customer].[Country]
NAME_ONLY [Level Attribute].[Value], for example [Customer].[Country]
Value, for example Country
NAME_PATH Currently not applicable for hierarchy joins
N/A
NULL No node syntax is used, just single values.
N/A
HIERARCHY_LEVEL_NUMBER
INT Available from SPS11 onwards: If the value is a positive number, only nodes from this level may be used via the value help. If the value is NULL, all nodes can be selected via the value help.
2.13 View BIMC_ALL_VARIABLES_VIEW
This view fully corresponds to BIMC_VARIABLE and includes a privilege check. It can be used to securely read all variables, including the unassigned ones.
28 P U B L I CSAP HANA Analytics Catalog (BIMC Views) Reference
BIMC Tables and Views
2.14 Table BIMC_VARIABLE_RANGE_DEFAULTS
The table contains the range defaults for variables and parameters. The column BIMC_VARIABLE.RANGE_DEFAULTS_EXIST = 1 indicates that range defaults exist. In case expressions like "datae(now())" are used as a default value, the values (LOW and HIGH) are calculated when the query "MDX select from BIMC_VARIABLE_RANGE_DEFAULTS" is executed.
Table 11:
Column Key Type Description
CATALOG_NAME
Yes STRING REPO 1: Package of the view
HDI: <Runtime container>.<Namespace> for example, RTC1.sap.fi. In case of freestyle names just <Runtime container> for example, RTC1
SCHEMA_NAME
STRING Deployment schema. For XSC, it is be filled in with the deployment schema _SYS_BIC. For XSA or HDI, it contains the name of the runtime container.
CUBE_NAME Yes STRING Name of the cube or view
QUALIFIED_NAME
STRING Available from SPS11 (for HDI). Contains the full qualified name (including name space if configured) of an HDI based view - for example, sap.fi::CostCenter. Contains null for Repo 1 views.
VARIABLE_NAME
Yes STRING Name of the variable or parameter
LINE_NUMBER Yes INT Line number in case the value is multi-line enabled
LOW STRING Low value
LOW_IS_EXPRESSION
INT 0=false: the low value is a constant
1=true: the low value is an expression
Clients don not need to read this field, because a low expression is evaluated into a constant at the time of the "MDX select from BIMC_VARIABLE_RANGE_DEFAULTS " query.
HIGH STRING High value
HIGH_IS_EXPRESSION
INT 0=false: the high value is a constant
1=true: the high value is an expression
Clients do not need to read this field, because a high expression is evaluated into a constant at the time of the "MDX select from BIMC_VARIABLE_RANGE_DEFAULTS " query.
OPTION STRING Range option (EQ, BT, LE, GE, LT, GT, CP, NL [= IS NULL] )
SIGN STRING I for "including", E for "excluding". NULL is interpreted as including.
SAP HANA Analytics Catalog (BIMC Views) ReferenceBIMC Tables and Views P U B L I C 29
2.15 Table BIMC_VARIABLE_ASSIGNMENT
This tables contains the assignment of variables to Model elements.
Table 12:
Column Key Type Description
CATALOG_NAME
Yes STRING Package of the cube
CUBE_NAME Yes STRING Name of the cube
QUALIFIED_NAME
STRING Available from SPS11 onwards (for HDI). Contains the full qualified name (including name space if configured) of an HDI based view - for example, sap.fi::CostCenter. Contains null for Repo 1 views.
VARIABLE_NAME
Yes STRING Name of the variable or parameter
MODEL_ELEMENT_NAME
Yes STRING Name of the assigned model element:
● The unique name of the hierarchy (HIERARCHY_UNIQUE_NAME) in case the variable needs to be applied to the underlying hierarchy view.
● Attribute or alias name for attribute filter variables, including calculated attributes. This name has to be used as a column name in the WHERE clause for passing down the values to the engine. This case is relevant whenever PLACEHOLDER_NAME = null. In this case, MODEL_ELEMENT_TYPE = 'Attribute'
NoteIf you have CDS views, this column is filled in with the name of the hierarchy or attribute. Clients should not rely on other model element names because this is considered internal (private) information of the view. If you have hierarchies, the name needs to be interpreted because the clients have to access the corresponding hierarchy view which has its own set of parameters.
● Measure name for target currency and conversion date variables. In this case, MODEL_ELEMENT_TYPE = 'Measure'
● Measure name (of the calculated measure) for formula variables. In this case, MODEL_ELEMENT_TYPE = 'Measure'
● Calculated attribute name (for formula variables in calculated attributes). In this case, MODEL_ELEMENT_TYPE = 'CalculatedAttribute'
● Empty (SPACE) in case the variable is mapped to a script, or for filter and rank threshold parameter in calc views.
● Name of the variable in case the variable is mapped to a variable in a model on the lower level
30 P U B L I CSAP HANA Analytics Catalog (BIMC Views) Reference
BIMC Tables and Views
Column Key Type Description
MODEL_ELEMENT_TYPE
STRING Type of the assigned model element:
● Hierarchy● Attribute (Filter Variable)
NoteIn the case of CDS views, this column is filled in only for hierarchies and variables. Clients should not rely on other model element types because this is considered internal (private) information of the view. For hierarchies, the type needs to be interpreted because clients have to access the corresponding hierarchy view which has its own set of parameters.
● CalculatedAttribute = Formula variable● Measure = Formula or currency conversion variable● Script● Variable (mapped to parameters of underlying views)● Empty (SPACE) in case of filter and rank threshold parameters
in calc views.
SAP HANA Analytics Catalog (BIMC Views) ReferenceBIMC Tables and Views P U B L I C 31
Column Key Type Description
PLACEHOLDER_NAME
STRING If this field is filled in, the value for the parameter has to be passed to the view (or the hierarchy view in case MODEL_ELEMENT_TYPE = 'Hierarchy') by using of the PLACEHOLDER syntax. In that case, the value of the field contains the placeholder name. The SQL for accessing the (hierarchy) view will look like this:
SELECT ... FROM "myview"( PLACEHOLDER = ('<PLACEHOLDER_NAME_1>', 'D'), 'PLACEHOLDER' = ('<PLACEHOLDER_NAME_2>', '1')) WHERE ...Note from SP09 on we support multi-value parameters. For such a parameter, BIMC_VARIABLE-MULTILINE = 1 (true). Multiple values have to be put in the PLACEHOLDER clause as follows depending on the data type:
SELECT ... FROM "schema"."view" ('PLACEHOLDER' = ('$$MULTI_VALUE_PARAM_NUMERICAL$$', 'Value1, Value2'))
NoteUse the SELECT statement above for numerical typed parameters.
Use the following SELECT statement for string typed parameters (the string values need to be escaped by a double quotes (')):
SELECT ... FROM "schema"."view" ('PLACEHOLDER' = ('$$MULTI_VALUE_PARAM_STRING$$', ' ' 'Value1' ', ' 'Value2' ' ')) //no spaces between the apostrophes
NoteEven a single value has to be escaped with a triple apostrophe in case the parameter is multi-valued.
If the field PLACEHOLDER_NAME is empty, the values for the parameter have to be passed via the WHERE clause of the SELECT statement.
● If you have hierarchy views (MODEL_ELEMENT_TYPE = 'Hierarchy'), the parameters must be passed to the underlying hierarchy view
● If a placeholder appears twice in the assignment table, it may appear only once as placeholder in the SQL statement.
32 P U B L I CSAP HANA Analytics Catalog (BIMC Views) Reference
BIMC Tables and Views
Column Key Type Description
HISTORY_PARAMETER
INT 0=false, 1=true
1=true indicates the user input of this parameter must be used as a time stamp for the AS OF part of the query. Compose the query as follows:
Code Syntax
SELECT ... FROM <View> AS OF UTCTIMESTAMP <User Input for the History Parameter>
2.16 View BIMC_VARIABLE_VIEW
A view combining the fields from the variable tables BIMC_VARIABLE_RANGE_DEFAULTS and BIMC_VARIABLE_ASSIGNMENT.
Related Information
Table BIMC_VARIABLE_RANGE_DEFAULTS [page 29]Table BIMC_VARIABLE_ASSIGNMENT [page 30]
2.17 Table BIMC_VARIABLE_MAPPING
This table contains the mapping of variables, parameters, or attributes to their value help views. Clients read from this table to find out how parameters and variables have to be filled in when querying the value help (target) view.
Table 13:
Column Key Type Description
CATALOG_NAME
Yes STRING REPO 1: Package of the view; HDI: <Runtime container>.<Namespace>, for example, RTC1.sap.fi; In case of freestyle names, just <Runtime container>, for example, RTC1.
SCHEMA_NAME
STRING Deployment schema or runtime container of the view
CUBE_NAME Yes STRING Name of the cube
SAP HANA Analytics Catalog (BIMC Views) ReferenceBIMC Tables and Views P U B L I C 33
Column Key Type Description
QUALIFIED_NAME
STRING Available from SPS11 (for HDI). Contains the full qualified name (including name space if configured) of an HDI based view - for example, sap.fi::CostCenter. Contains null for Repo 1 views.
MAPPING_TYPE
Yes STRING Type of mapping:
● VALUE_ENTITY: the mapping target is the value (help) entity of a variable or parameter (from BIMC_VARIABLE.VALUE_ENTITY)
● COLUMNOBJECT_NAME: the target is the value (help) entity of an attribute (from BIMC_DIMENSION_VIEW.COLUMNOBJECT_NAME)
VARIABLE_NAME
Yes STRING ● If MAPPING_TYPE = 'VALUE_ENTITY': name of the variable or parameter for which the value help view is defined
● If there is another MAPPING_TYPE: empty string
ATTRIBUTE_NAME
Yes STRING ● If MAPPING_TYPE = 'COLUMNOBJECT_NAME': name of the attribute for which the value help view is defined
● If there is another MAPPING_TYPE: empty string
TARGET_VIEW Yes STRING Catalog name of the value help view
TARGET_VARIABLE_NAME
Yes STRING Name of the target variable or parameter of the value help view. It may be left empty (empty string) if the mapping points directly to an attribute name (instead of a variable).
TARGET_ATTRIB-UTE_NAME
Yes STRING ● Empty string, if TARGET_VARIABLE_NAME denotes a parameter.
● The target attribute or column in case TARGET_VARIABLE_NAME denotes a variable or the mapping is directly linked to an attribute name. You have to filter this attribute via the WHERE clause of the SELECT statement when accessing the target view.
TARGET_DEFAULT_VALUE
STRING Name of the default value of the target variable or parameter of the TARGET_VIEW. Null in case no default is defined or the mapping is directly linked to an attribute name (instead of a variable).
This value must only be used, if no CONSTANT_VALUE is defined and no value has yet been entered by the user for SOURCE_VARIABLE_NAME. Only in that case the TARGET_DEFAULT_VALUE has to be passed to the target view.
SOURCE_VARIABLE_NAME
STRING Name of the source variable or parameter of the cube or null in case of constant mapping, that means if CONSTANT_VALUE is filled in. If the value for this variable has already been entered by the user, the value has to be passed on to the target view.
CONSTANT_VALUE
STRING Constant value to be used for the target variable or parameter. Null in case SOURCE_VARIABLE_NAME is filled in - in that case, the constant value has to be passed on to the target view.
34 P U B L I CSAP HANA Analytics Catalog (BIMC Views) Reference
BIMC Tables and Views
Column Key Type Description
TARGET_PLACE-HOLDER_NAME
STRING ● Placeholder name to be used in the SQL statement for the TARGET_VIEW - see BIMC_VARIABLE_ASSIGNMENT.PLACEHOLDER_NAME in case TARGET_VARIABLE_NAME denotes a parameter.
● Null in case TARGET_VARIABLE_NAME denotes a variable or is empty.
IS_EXPRESSION
INT ● 0=false: the target default value is a constant● 1=true: the target default value is an expression
Clients do not need to read this field because a default expression is evaluated into a constant when the query MDX select from BIMC_VARIABLE_MAPPING is executed.
2.18 Table BIMC_VARIABLE_VALUE
A table containing the static values allowed for the variable, if BIMC_VARAIBLE.VALUE_TYPE = 'StaticList'. In the modeler, you can provide a list of names and descriptions for these types of variables.
Table 14:
Column Key Type Description
CATALOG_NAME
Yes STRING REPO 1: Package of the view; HDI: <Runtime container>.<Namespace>, for example, RTC1.sap.fi; In case of freestyle names, just <Runtime container>, for example, RTC1.
SCHEMA_NAME
STRING Deployment schema or runtime container of the view
CUBE_NAME Yes STRING Name of the cube
QUALIFIED_NAME
STRING Available from SPS11 onwards (for HDI). Contains the full qualified name (including name space if configured) of an HDI based view - for example, sap.fi::CostCenter. Contains null for Repo 1 views.
VARIABLE_NAME
Yes STRING Name of the variable
ORDER Yes INT Order in which to present the variables on the user interface
NAME STRING Technical name of the static list value
DESCRIPTION STRING Description of the static list value - the text IDs for these descriptions are defined by %V_<Variable Name>%<Static Value Name>.
The following columns need to be requested to get repository texts in column DESCRIPTION:
Table 15:
Column
CUBE_NAME
SAP HANA Analytics Catalog (BIMC Views) ReferenceBIMC Tables and Views P U B L I C 35
Column
CATALOG_NAME
VARIABLE_NAME
NAME
For example, if you have an attribute in the analytic view containing 'A' for actuals and 'P' for planning data, you could define a "ValueType" variable of type "Static List" and provide the following entries in the list:
Table 16:
Name Description
P Planned values
A Actual values
Then the clients should render a dropdown list for entering just one of these two values at query execution time. The Name value is passed on to the engine as filter value.
2.19 Table BIMC_VARIABLE_ODBO
This is a virtual table which retrieves information from the BIMC_VARIABLE_VIEW view. It is a schema rowset table containing metadata about given variables. The structure of the table closely corresponds to the
schema rowset table for variables in BW.
2.20 BIMC_SOURCES
This view is available only for HDI-based calc views and contains the XML source code of the view.
Table 17:
Column Key Type Description
CATALOG_NAME
STRING <Runtime container>.<Namespace>, for example, RTC1.sap.fi; In case of freestyle names just <Runtime container> for example, RTC1.
SCHEMA_NAME
Yes STRING Runtime container of the view
CUBE_NAME STRING Name of the view
QUALIFIED_NAME
Yes STRING Full qualified name (including namespace, if configured) of an HDI based view - for example, sap.fi::CostCenter.
36 P U B L I CSAP HANA Analytics Catalog (BIMC Views) Reference
BIMC Tables and Views
Column Key Type Description
SOURCE BLOB XML definition of the view
The view BIMC_SOURCES is based on the new table BIMC_ALL_SOURCES and filters the result according to the SQL privileges of the user.
SAP HANA Analytics Catalog (BIMC Views) ReferenceBIMC Tables and Views P U B L I C 37
3 Consuming Views With Parameters
3.1 Main Variable and Parameter Types
The objective of SAP HANA variables and input parameters is to make the HANA information models more dynamic by allowing parameter and column filtering via variables. The values for these variables are determined before executing the view - typically, by asking the user to enter the value manually or to choose one from a drop-down list. Alternatively, the SAP HANA engine can derive the variables by reading their values from a separate table.
Definitions
● Variable: a variable is a directive for view consumers to filter certain columns. For BI clients, such as Advanced Analytics for Office, this means to produce a variable user interface. On this user interface users can enter filter values they want to use for the current view execution.Variables define filters on an attribute of the view. Therefore they always have to be assigned to an attribute. The variable values are passed to the engine via the WHERE-clause of the SQL statement. Variables are not known to the engine but only to clients like Data Preview, MDX, Advanced Analysis for Office or BO Explorer. It is the task of the client to convert the user input into a WHERE-clause when accessing the data.
● Parameter (Input Parameter): It is used to parameterize the view execution - for example, as a part of filter or calculated column expressions or to specify the target currency of a conversion. It is handled by the view consumers in the same manner as variables, it is displayed on the variable UI.A parameter defines the internal parameterization of the view. This means that the engine needs to know it and uses the parameter value during the execution - for example, to calculate a formula for a calculated measure. The parameter value is passed to the engine via the PLACEHOLDER clause of the SQL statement. At runtime a parameter can be filled by one single value.
● Derived parameter: a parameter the value of which is derived automatically at run time without manual user input. For the end user there is no distinction between variables and parameters. Both appear as input fields on the variable pop up.
The distinction between variables and parameters exists only in the modeler. When it comes to consumption, there is no difference between the two variable types. In the next sections the term "variable" also means parameter, if not explicitly mentioned otherwise.
38 P U B L I CSAP HANA Analytics Catalog (BIMC Views) Reference
Consuming Views With Parameters
3.2 SAP HANA SQL Syntax for Passing Parameters and Variables
Client applications pass the entered values for variables and parameters to the engine as follows:
● Variables: the client converts them into fragments of the WHERE clause. Variables can be identified by BIMC_VARIABLE_VIEW table, column PLACEHOLDER_NAME = null.
● Parameters: the client converts them into fragments of the PLACEHOLDER clause. Parameters can be identified by BIMC_VARIABLE_VIEW table, column PLACEHOLDER_NAME != null
If a placeholder appears twice in the assignment table, it may occur only once as a placeholder in the SQL statement.
Sample Code
PLACEHOLDER clause syntax: ...SELECT ... FROM "schema"."view" ('PLACEHOLDER' = ('$$PARAMETER_1$$', 'Test'), 'PLACEHOLDER' = ('$$PARAMETER_2$$', '1'))
Use the following select statement for string-typed parameters (the string values need to be escaped by double apostrophe '). If the parameter is multi-valued then even a single value has to be escaped by a triple apostrophe....SELECT ... FROM "schema"."view" ('PLACEHOLDER' = ('$$MULTI_VALUE_PARAM_STRING$$', '''Value1'',''Value2'''))Multiple single values are supported for some parameters. They have to be put in the PLACEHOLDER clause as follows depending on the data type. Use the following SELECT statement for numerical typed parameters:
Sample Code
...SELECT ... FROM "schema"."view" ('PLACEHOLDER' = ('$$MULTI_VALUE_PARAM_NUMERICAL$$', 'Value1, Value2'))
3.3 SAP HANA MDX Syntax for Passing Parameters and Variables
SAP HANA supports the use of variables in MDX queries. The variables are an SAP-specific enhancement to standard MDX syntax.
You can specify values for all mandatory variables that are defined in SAP HANA studio for various modeling entities. The following example illustrates how to declare SAP HANA variables and their values:
Sample Code
MDX SelectFrom [SALES_DATA_VAR]
SAP HANA Analytics Catalog (BIMC Views) ReferenceConsuming Views With Parameters P U B L I C 39
Where [Measures].[M2_1_M3_CONV]SAP VARIABLES [VAR_VAT] including 10, [VAR_K2] including 112, [VAR_TARGET_CURRENCY] including 'EUR',
An MDX SELECT statement in SAP HANA enables you to send values for variables defined within modeling views.
Analytic and calculation views may contain variables bound to specific attributes. When calling the view, you can assign values to those variables and use them to filter results, for example.
SAP HANA supports an extension to MDX where you can assign values to variables defined in views by adding a SAP VARIABLES clause in your select statement. Here is the syntax for a SELECT statement:
3.4 Default Values
A variable or a parameter can have a default value which is used in the following cases:
● For prefilling the entry field in the variable UI (prompt UI)● If a parameter is not set in an SQL query, the default value is taken over by the engine at query runtime.
The mandatory flag can be set irrespective of the default value.
It can make sense to define a mandatory parameter which is prefilled in the variable UI. Mandatory in the view model means semantically mandatory - the data may be inconsistent, if the parameter is not provided because the data is then aggregated across SAP-client values (for example, multiple tenants). From the engine point of view, the parameter is technically mandatory if no default value is provided and the mandatory flag is set in the modeler.
In case the parameter is not mandatory and no default value is provided, the calc engine uses the empty string ('') as parameter (default) value.
The variable UI must enforce a user entry for a (semantically) mandatory variable even if a default value exists. For example, if the user deletes the proposal for a mandatory variable in the variable UI, the UI displays a warning message prompting the user to enter a value for the variable. In the end, the entered value is sent to the engine.
40 P U B L I CSAP HANA Analytics Catalog (BIMC Views) Reference
Consuming Views With Parameters
3.5 Expressions as Special Default Values
You can use expressions as special default values. Those expressions are specified in the variable or parameter wizard expression editor, for example date(Now()).
For a metadata request to the BIMC_VARIABLE table, the default value must be calculated and not read from the table. For example:
● allow "today" as default value for variables or parameters of type Date● allow a formula as default value (for example, $$CurrentYear$$ -2)
NoteIf both the default value and the default expression are filled in, which should not happen, the default value takes precedence. In case the default value is an expression, it will be calculated at the time the BIMC_VARIABLE_VIEW table is read and it will be returned in the field DEFAULT_VALUE. In that case the consumer still has as to use the MDX prefix.
3.6 Value Help
Value help consisting of a list of values is provided wherever possible. The way to retrieve this list depends on the value type of the variable.
Static List of Values
A static list of values can be read from table BIMC_VARIABLE_VALUE.
Currency Values
The currency codes and their texts can be read from the tables TCURC and TCURT. However, the database schema in which to look for the TCUR* tables is not fixed but chosen by the model developer and stored at the header level of the information model. There may be even several database schemas containing the TCUR* tables. During the deployment of the information models the default schema is stored in BIMC_CUBES.DEFAULT_SCHEMA. The clients need to read the schema from there to make sure they are accessing the right TCUR* tables.
To read all currencies and texts with the language, use the following SELECT statement:
Sample Code
SELECT TOP 50000 distinct M.WAERS as "CurrencyCode", T.LTEXT as "Description", T.KTEXT as "ShortDescription"
SAP HANA Analytics Catalog (BIMC Views) ReferenceConsuming Views With Parameters P U B L I C 41
FROM "SYSTEM"."TCURC" as M left outer join "SYSTEM"."TCURT" as Ton ( M.WAERS = T.WAERS and M.MANDT = T.MANDT and T.SPRAS = (SELECT SESSION_CONTEXT( 'LOCALE_SAP' ) FROM "DUMMY") )
NoteIt is recommended not to use the session client (mandt = (SELECT SESSION_CONTEXT( 'CLIENT' ) FROM "DUMMY") because you can define different clients for the currency conversions in a single view.
UnitOfMeasure Values
Unit of measures should be treated as currencies. The handling with respect to schema and client is exactly the same. (Tables T006, T006A).
To read all units and texts with the language, use the following SELECT statement:
Sample Code
SELECT TOP 50000 distinct M.MSEHI as "UnitOfMeasure", T.MSEH3 as "UnitOfMeasureCommercialName" , T.MSEHL as "Description" FROM "SYSTEM"."T006" as M left outer join "SYSTEM"."T006A" as Ton ( M.MSEHI = T.MSEHI and M.MANDT = T.MANDT and T.SPRAS = (SELECT SESSION_CONTEXT( 'LOCALE_SAP' ) FROM "DUMMY") )
Date Values
Date values display a date selection dialog (either pass as SQL date in the form YYYY-MM-DD or ERP CHAR 8 date).
Dynamic Attribute Value Lists
The attribute to generate the dynamic value help list can be derived from BIMC_VARIABLES.VALUE_ATTRIBUTE. As the value lists can be derived from an attribute of the model itself or a referenced model or table, the following cases need to be distinguished:
● BIMC_VARIABLES.VALUE_ENTITY is empty or does not exist.In this case, read the list of values from the column view of that attribute - get the column view name from BIMC_DIMENSION_VIEW.COLUMNOBJECT_NAME. The column view is the current view itself or the dimension view (reused attribute view or calculation view of type dimension) the attribute comes from.If you have a calc view, it may have parameters as well. In that case, you need to apply the parameter mapping, which is available in the table BIMC_VARIABLE_MAPPING.
● BIMC_VARIABLES.VALUE_ENTITY contains the name of a table or a view. In that case use the provided table and the column BIMC_VARIABLES.VALUE_ATTRIBUTEto query for the value help.
42 P U B L I CSAP HANA Analytics Catalog (BIMC Views) Reference
Consuming Views With Parameters
NoteIn case the view for the value help is the same view as the current one, pass all variables or parameters the user has already entered in the UI to it. This helps you avoid errors like "Mandatory parameter not set" when executing the value help select and ensures that fewer values are presented in the value help (only those that fit the parameter values that have already been entered). To avoid the error "Mandatory parameter not set", it is recommended to use default values for all parameters.
3.7 Passing and Mapping of Variables and Parameters
The most common scenario for passing and mapping of variables or parameters between view models is when a calculation view is built on top of other view models that have variables. You can pick variables from the underlying models and take them over to define variables on the calculation view. You can map one variable of the edited calc view to several variables of the underlying view models that are reused as data sources in the edited calc view.
Passing Variables
Parameter or placeholder variables: these variables can be passed on to the underlying views because they are mandatory in most cases.
Attribute or filter variables: these variables cannot be passed down because they are not known by the engine.
Parameter Mapping
To cover all use cases, it is possible to define a mapping from a parameter or variable of the main view to a parameter or variable of the external value help view. If the user has already filled in parameters or variables on the variable UI, the client will proceed as follows:
● Send the corresponding values to the mapped target parameters of the value help (or dimension) view if the mapping is to a parameter.
● Filter the attributes of the value help (or dimension) view accordingly if the mapping is to a variable (or an attribute).
All information needed for querying the external value help view is provided in the table BIMC_VARIABLE_MAPPING - the clients do not need to read the BIMC_VARIABLE table again for the external value help view.
SAP HANA Analytics Catalog (BIMC Views) ReferenceConsuming Views With Parameters P U B L I C 43
Important Disclaimers and Legal Information
Coding SamplesAny software coding and/or code lines / strings ("Code") included in this documentation are only examples and are not intended to be used in a productive system environment. The Code is only intended to better explain and visualize the syntax and phrasing rules of certain coding. SAP does not warrant the correctness and completeness of the Code given herein, and SAP shall not be liable for errors or damages caused by the usage of the Code, unless damages were caused by SAP intentionally or by SAP's gross negligence.
AccessibilityThe information contained in the SAP documentation represents SAP's current view of accessibility criteria as of the date of publication; it is in no way intended to be a binding guideline on how to ensure accessibility of software products. SAP in particular disclaims any liability in relation to this document. This disclaimer, however, does not apply in cases of willful misconduct or gross negligence of SAP. Furthermore, this document does not result in any direct or indirect contractual obligations of SAP.
Gender-Neutral LanguageAs far as possible, SAP documentation is gender neutral. Depending on the context, the reader is addressed directly with "you", or a gender-neutral noun (such as "sales person" or "working days") is used. If when referring to members of both sexes, however, the third-person singular cannot be avoided or a gender-neutral noun does not exist, SAP reserves the right to use the masculine form of the noun and pronoun. This is to ensure that the documentation remains comprehensible.
Internet HyperlinksThe SAP documentation may contain hyperlinks to the Internet. These hyperlinks are intended to serve as a hint about where to find related information. SAP does not warrant the availability and correctness of this related information or the ability of this information to serve a particular purpose. SAP shall not be liable for any damages caused by the use of related information unless damages have been caused by SAP's gross negligence or willful misconduct. All links are categorized for transparency (see: http://help.sap.com/disclaimer).
44 P U B L I CSAP HANA Analytics Catalog (BIMC Views) Reference
Important Disclaimers and Legal Information
SAP HANA Analytics Catalog (BIMC Views) ReferenceImportant Disclaimers and Legal Information P U B L I C 45
go.sap.com/registration/contact.html
© 2016 SAP SE or an SAP affiliate company. All rights reserved.No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP SE or an SAP affiliate company. The information contained herein may be changed without prior notice.Some software products marketed by SAP SE and its distributors contain proprietary software components of other software vendors. National product specifications may vary.These materials are provided by SAP SE or an SAP affiliate company for informational purposes only, without representation or warranty of any kind, and SAP or its affiliated companies shall not be liable for errors or omissions with respect to the materials. The only warranties for SAP or SAP affiliate company products and services are those that are set forth in the express warranty statements accompanying such products and services, if any. Nothing herein should be construed as constituting an additional warranty.SAP and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP SE (or an SAP affiliate company) in Germany and other countries. All other product and service names mentioned are the trademarks of their respective companies.Please see http://www.sap.com/corporate-en/legal/copyright/index.epx for additional trademark information and notices.
top related