SAP HANA Views - Back End Tables & Where Used List Details

[former_member199945]

Hi All,

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. 

Introduction –

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.

Customer Table:

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.

Script:

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,

Seshu