The purpose of this blog post is to provide back end /physical tables source information of created SAP HANA Analytic & Calculation views during data modelling. This can be helpful for the developers to get an overall picture of created views with the where used list details and the back end logic’s behind at the time of creating views.
There are certain standard system views, which was provided by SAP by using these views we can get this information.
There are three types of views which we will create during data modelling in SAP HANA: attribute views, analytic views, and calculation views.
Image Source: SAP & help.sap.com
Coming to this blog post, it mainly provides below information .
List of Analytic & Calculation Views Created in the system.
Created Analytic & Calculation view details like who created & when it was created etc.
List of back end/Physical tables involved at the time of creating views.
Type of tables used at the time of creating Views (Dimension/Fact).
List of key figures and dimensions created & used in views.
Type of joins & Cardinality used at the time of creating view during between multiple tables.
Where used list of created Views.
For the demonstration purpose , I have created two sample Tables Customer & Order ,
created AV – ZAV_TEST and applied inner join between two tables using CID column.
Order Table :
Output of Analytic view :
- To get a list of Analytic & Calculation views created in the system by who and when details etc:
SELECT * FROM “_SYS_BI”.”BIMC_CUBES
- To get a list of views created under particular catalog and specific view detail :
. SELECT * FROM “_SYS_BI”.”BIMC_CUBES” where CATALOG_NAME=’ZTEST’
SELECT * FROM “_SYS_BI”.”BIMC_CUBES” where CUBE_NAME=’ZAV_TEST’
SYS_BI.BIMC_CUBES view will contains below details of created views. We can customize the sql
script in where clause by applying conditions using below columns.
- To get list of physical /back end tables used at the time of creating AV & CV and type of table (dim/fact) used :
SELECT * FROM “SYS”.”CS_JOIN_TABLES” where view_name=’ZTEST/ZAV_TEST’
- To get type of join condition and Cardinality used in views :
SELECT * FROM “SYS”.”CS_JOIN_CONDITIONS” where view_name=’ZTEST/ZAV_TEST’
- To get information about the key figures used in views :
SELECT * from “SYS”.”CS_KEY_FIGURES” where view_name=’ZTEST/ZAV_TEST’
- To get information about dependencies objects between created views (Where used list details) :
Ex: We have created an analytic view and it was used in multiple calculation views, we want to know the where used list of this view.
- By Using Where used list Option
- By Using sql script
Select a particular view and right click on it and we will get list of option as below
and now select where used option and it will display the where used details of created views.
SELECT * FROM “SYS”.”OBJECT_DEPENDENCIES” where BASE_OBJECT_NAME=’ZTEST/ZAV_TEST’ and BASE_OBJECT_TYPE=’VIEW’ and DEPENDENCY_TYPE=’1′
Please note that we can customize the scripts based on our requirements.
Hope this blog post can be helpful.
Any Suggestions/comments welcome.
Thanks & Regards,