How to use your HANA analytical/calculation views in SAP InfiniteInsight?
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
Hi Abdel Dadouche,
Really helpful blog.When we tired to follow the above steps we could not able to find the KxCORBA.cfg file.But there is a file available KxCOBRAShell-Authenticated.bat .
can you please suggest what we are missing and how to resolve this issue .
Thanks ,
Srikanth K
Hi Srikanth,
The modification of the KxCORBA.cfg file implies that you are using a client/server installation.
The file 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.
Hope this will help you solve the issue.
Regards
Abdel
Hi Dadouche,
We were able to implement the logic that you mentioned for server but we are unable to see the views in the front end.Can you please share the steps to follow the see the views.It would be great if you can share some screenshots on this.
Thanks and Regards,
Srikanth Konakandla.
Hi,
This seems strange...
Have you change the "MyDSN" token with your ODBC DSN entry name?
You also have to keep in mind that some of the Analytical views will not be displayed especially if they use placeholhers and in general views attributes, and also we do work only with calc and join view (not with measure, olad, hierachy and script views)
Can you run in HANA Studio the following SQL and tell me what it does return?
SELECT SCHEMA_NAME , VIEW_NAME, VIEW_TYPE FROM SYS.VIEWS WHERE NOT EXISTS (
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
WHERE SCHEMA_NAME = a.CATALOG_NAME AND VIEW_NAME = a.CUBE_NAME AND ( MANDATORY = 1 OR MODEL_ELEMENT_TYPE IN ('Measure', 'Hierarchy', 'Script') )
) AND IS_VALID= 'TRUE' AND VIEW_TYPE IN ('CALC', 'JOIN')
Regards
Abdel
Hi,
I just wanted to highlight that this workaround is no longer needed for some of the Automated Analytics components, starting with SAP Predictive Analytics 2.2.
It remains applicable only for Social/Recommandation or in case you want to access an attribute view (regardless of the component used).
Please refer to the following sources for more details:
http://help.sap.com/businessobject/product_guides/pa22/en/pa22_whatsnew_en.pdf
http://scn.sap.com/community/predictive-analytics/blog/2015/06/19/announcing-sap-predictive-analytics-22
https://css.wdf.sap.corp/sap/support/notes/2165858
https://css.wdf.sap.corp/sap/support/notes/2199585
Thanks & regards
Antoine
Hi Antoine,
we are using Predictive Analytics 3.1 and we found that we were not able to see HANA Calculation views in the Data Manager. We were able to see them in the Modeler.
However, after trying this workaround (also from OSS note 2199585 ) we were able to see the calculation views in the data manager.
Please can you confirm: are we supposed to be able to use HANA Calculation views in Data Manager without this workaround?
If yes, do you have any ideas as to why it would not work until we amended the .CFG files?
Hi Lance,
We do not currently support HANA views in Data Manager, even with the workaround.
Another possibility right now is to create SQL views on top of HANA views.
This is part of our short-term roadmap to deliver the "real" integration. I am interested to know more on your use case, feel free to direct message me.
Kind regards
Antoine
Hi Antoine,
I am not sure how to send a direct message to you so I will just post it here. We are just trying to create automated models based on HANA analytic views. Mostly these HANA views are auto-created by SAP BW, however we also have created some calculation views which enrich these analytical views with additional dimensions and measures. we want to use these to generate automated models in PA.
It's quite disappointing to find that we have to manually create database views in order to access these HANA Calc views in PA Data Manager because the SAP sales team was really pushing the tight integration with HANA as one of the major selling points of the tool. They certainly never indicated that we would have to create another layer of development artifacts in order to leverage our existing models.
Please can you give me a time estimation on when direct consumption of HANA calc/analytic views will be supported so that we can plan accordingly
sorry, just to add, the reason we want to use the data manager is to auto-generate hundreds of additional columns using the time period and dimension pivoting functionality of the data manager. this will prepare the data model for a churn prediction model.
Hi Lance, this feature is part of our short-term roadmap.
It's difficult to tell more at this stage around timelines & releases but our plans is to deliver this as soon as we possibly can.
Be sure that we identified the problem and wants to solve this at the earliest.
Kind regards,
Antoine
Good blog
Cheers
Sharma
Nice Blog..
Calculation Views are used to consume other Analytic, Attribute and other Calculation views and base column tables. These are used to perform complex calculations, which are not possible with other type of Views.Learn More… S/4 HANA Sourcing and procurement exam guide
Good Information…