Skip to Content
avatar image
Former Member

Querying Sys tables - Calculation Views

Soooo....

I've been playing around in the system views and found quite a bit of useful stuff for querying meta-data around base objects.

For Tables, column definitions, constraints etc ...

For Attribute views, column names, dependencies, joins & join conditions....

Yet for Calculation Views; I'm drawing a blank.

Ok, so I can list them (name, type) from sys.objects, but I've not been able to gain any further info.

Does anyone know (a) why this is (they they are not detailed in sys views ?) and (b) does the data I'm looking for exist in tables elsewhere ?

Just for illustration, a small example of the views I've been hitting so far ..

M_TABLES CS_VIEW_COLUMNS CS_BO_VIEWS CONSTRAINTS CS_JOIN_TABLES CS_JOIN_PATHS CS_JOIN_CONDITIONS CS_KEY_FIGURES OBJECTS OBJECT_DEPENDENCIES
Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

5 Answers

  • avatar image
    Former Member
    Nov 27, 2014 at 10:05 AM

    Hi Lars, Simon

    I'm relatively new to the HANA world, so forgive me if I am asking stupid questions, but I don't quite follow about the mappings.

    When I open up a Calculation View in HANA Studio, I can drill down to see which Tables (and Fields) it is based upon. When I create a new Calculation View that is based upon a SAP Table, for example, I can choose which Table Fields are to be included. Why can't I get to that in the metadata?

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Krishna Tangudu

      Thanks to both Krishna and Nicholas, for reassuring me that I am not after all mad nor alone in this thought.

      I have not yet looked into 'Ideas Place'; but it seems that I will have to go and explore.

      (thanks for the tip Krishna).


      Bear with me, once I find some quiet time to write all this up 'properly' I can drop a note on here with a link.

      (unless any of you are impatient and beat me to it 😉 )

  • avatar image
    Former Member
    Nov 26, 2014 at 03:28 PM

    I've got the same requirement. SYS.OBJECT_DEPENDENCIES is a good place to start, but that only goes so far. You can see which Calculation Views are based on which Tables, but I can't see how to get the next level of detail on how Calculation View attributes map to the individual Columns in those Tables. And for Calculated Fields I'd like to be able to extract the calculation itself.

    Any suggestions gratefully recieved

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Lars Breddemann

      Thanks Lars,

      This is what I expected, but it's nice to have it confirmed.

      I hear what you are saying about runtime mapping, (I think), but I'm not concerned with actual runtime in this case.

      For a simple scenario example ; say I want to list all views that (in design) use a certain table/field.
      Yes, there are some GUI options for this, but I will have more complex criteria, and/or product a more global report for analysis/reporting.

      A complicated option, would be to extract the xml behind, and parse that.
      But this is messy and vulnerable to changes come future HANA versions.

      Does anyone know if there is a way fro address the XML any other way than manually ?


  • Sep 19, 2014 at 07:40 AM

    What is it you want to find out about column views?

    - Lars

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      In the simplest case, I'd like to be able to see what tables/views it depends on.(and vice-versa)

      Example of what you can to explore for Attribute Views .... so something like this for CVs :

      Select * from SYS.CS_JOIN_TABLES WHERE VIEW_NAME = 'myView'

      SCHEMA_NAME VIEW_NAME TABLE_SCHEMA_NAME TABLE_NAME TABLE_TYPE _SYS_BIC myView_AT ABC Table1 none _SYS_BIC myView_AT ABC Table2 none _SYS_BIC myView_AT ABC Table3 none

      Select * from SYS.CS_JOIN_TABLES WHERE TABLE_NAME = 'myTable'

      SCHEMA_NAME VIEW_NAME TABLE_SCHEMA_NAME TABLE_NAME TABLE_TYPE _SYS_BIC ....../CUSTOMER_AT ABC myTable none _SYS_BIC ....../Vendor_AT ABC myTable none _SYS_BIC ...../PurchaseHistory_AT ABC myTable none

  • avatar image
    Former Member
    Nov 27, 2014 at 10:54 AM

    Lars

    Thank for the very valuable information.

    You clearly know a lot more about HANA than I do, but what you have described sounds not dissimilar in concept to Database Views based upon Database Views. Eventually you get down to Base Tables. I'm not clear why this is different. The reference to the next view/table you describe is effectively lineage information. If you track this back you eventually get to the base tables. Are you saying this lineage is not in an accessible metadata form?

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 27, 2014 at 05:48 PM


    Ok, so the idea submition was easier than I expected.

    <There is no longer an Idea Place for HANA Studio>

    Please vote if you are interested.

    Please also/instead drop me a comment if you feel that my argument could be better presented or strengthed in any way.

    Watch this space .....

    Add comment
    10|10000 characters needed characters exceeded