With SAP Predictive Analytics 2.x we have made some enhancements to better support the HANA Information views.
This workaround is no longer required when you want to consume HANA Information views to build models.
First some background about the issue:
InfiniteInsight (II) is not letting you use your analytical views, calculated views and so on in the user interface
In the background, II will use the capabilities of the ODBC driver to get the list of "data space" to be presented to the user using a standard ODBC function.
Unfortunately, the HANA ODBC driver is not currently including the names of the analytical views, calculated views.
However this ODBC driver behavior can easily be bypassed in two ways:
- simply type in the full name of the calculated view (including the catalog name) like "PUBLIC"."foodmart.foodmart::EXPENSES"
- configure II to use your own custom SQL that will list the item you want to display.
This feature is used in II to restrict the list of tables for example when your datawarehouse has hundreds of schemas.
One file needs to be change depending on if you are using a workstation version (KJWizard.cfg) or a client/server version (KxCORBA.cfg) by adding the following content:
ODBCStoreSQLMapper.MyDSN.SQLOnCatalog1=" SELECT * FROM ( "
ODBCStoreSQLMapper.MyDSN.SQLOnCatalog2=" SELECT '""' || SCHEMA_NAME || '""', '""' || OBJECT_NAME || '""', OBJECT_TYPE FROM SYS.OBJECTS WHERE OBJECT_TYPE IN ('TABLE', 'VIEW') AND SCHEMA_NAME NOT LIKE '%%SYS%%' "
ODBCStoreSQLMapper.MyDSN.SQLOnCatalog3=" UNION ALL "
ODBCStoreSQLMapper.MyDSN.SQLOnCatalog4=" SELECT '""' || SCHEMA_NAME || '""', '""' || VIEW_NAME || '""', VIEW_TYPE FROM SYS.VIEWS WHERE NOT EXISTS ( "
ODBCStoreSQLMapper.MyDSN.SQLOnCatalog5=" SELECT 1 FROM _SYS_BI.BIMC_VARIABLE_ASSIGNMENT A JOIN _SYS_BI.BIMC_VARIABLE v ON a.CATALOG_NAME = v.CATALOG_NAME AND a.CUBE_NAME = v.CUBE_NAME AND a.VARIABLE_NAME = v.VARIABLE_NAME "
ODBCStoreSQLMapper.MyDSN.SQLOnCatalog6=" WHERE SCHEMA_NAME = a.CATALOG_NAME AND VIEW_NAME = a.CUBE_NAME AND ( MANDATORY = 1 OR MODEL_ELEMENT_TYPE IN ('Measure', 'Hierarchy', 'Script') ) "
ODBCStoreSQLMapper.MyDSN.SQLOnCatalog7=" ) AND IS_VALID= 'TRUE' AND VIEW_TYPE IN ('CALC', 'JOIN') "
ODBCStoreSQLMapper.MyDSN.SQLOnCatalog8=" ) order by 1,2 "
The KxCORBA.cfg file (used in a client/server installation) itself is located on the InfiniteInsight server installation directory named:
C:\Program Files\SAP InfiniteInsight\InfiniteInsightVx.y.y\EXE\Servers\CORBA
where x.y.z is the version you have installed.
If you are using a standlaone (a.k.a. Workstation), then the file to modify is KJWizard.cfg which is located in:
C:\Program Files\SAP InfiniteInsight\InfiniteInsightVx.y.y\EXE\Clients\KJWizardJNI
where x.y.z is the version you have installed.
In this example I only include tables, views, calc and join views with no mandatory variables or 'Measure', 'Hierarchy', 'Script' variables at all.
You may need to adjust this configuration SQL if you want to list Smart Data Access objects.
You can notice here that we are changing the behavior for one ODBC DSN (MyDSN), so this value might need to be adjusted in your environment.
You can also replace it with a star (*), then this configuration will be applied to all ODBC DSN, which may not work on other databases.
Some functionalities in II may not work yet properly despite this workaround.
For example:
- data manipulations requires the configuration file change
- view placeholhers and in general views attributes are not properly supported
- some type of aggregates are not "selectable by name" which mean that if used in a select statement in HANA Studio it will not be returned (select * vs select cols).
Hope this will save you some time
