Querying Sys tables - Calculation Views

[Former Member]

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

[Former Member]

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 .....

[former_member229185]

I have a very similar need to determine object lineage, and can't seem to find your submitted idea.

Is there really no Idea Place for HANA/ HANA Studio? Here's an earth shattering idea to make one:

Idea Place for SAP HANA : View Idea

[Former Member]

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?

[lbreddemann]

Yes, that is what I am saying.

The lineage information is not available in an accessible metadata form for information models.

[Former Member]

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?

[lbreddemann]

A major theme in SAP HANA models is that you stack them massively.

It's not uncommon to have one calc view calling tens of other calc views, analytic views and tables just for one model.

Now imagine to know that you have a specific column and you want to change this column.

You would need to know all direct and indirect uses of that column in all models.

This would also include usage of calculated columns that are then further used.

This kind of information is not stored anywhere in the system with regards to the underlying tables, but always only as a reference to the next view/table.

So, yes, as long as you are in a very simple model, all is good and easy.

Unfortunately things are not that simple (yet... )

[Former Member]

I hear you, however.....

this is all we are aksing for :

"a reference to the next view/table"

Somewhere under the hood, HANA knows that (in my design);  table_1 field_1 is used in view_32 field_9.

And it will also know that view_32, field_9 is used in view_1032 field_3.

The quesion is, if HANA knows that, why can't we extract that information ?

There maybe a good and logic; reason why .... we just don't understand what that reason is !

[lbreddemann]

Well the "reason" here is a purely technical one.

The dependencies for the information models are stored in the definition of those models only and not in a central table.

For the processing of those views it's simply not necessary to have that on column level.

Another point here is that you'd also have to consider design time dependencies (e.g. non-active views) to be complete.

Anyhow, I'd say there is currently no architectural-special-HANA-related reason.

It's simply a functionality that is not there (yet?).

I'd assume if enough customer demand for something like this is present then product development will look into including that.

[former_member182302]

+1 for this Idea of including this feature. Even in the current assignment of mine where multiple teams work on the same hana box for different projects and documentation being one of the things often neglected it leads to creation of the as-of document for which we would need this kind of feature.

I think its time, we need to add it in the Idea Place and get votes there.

Regards,

Krishna Tangudu

[Former Member]

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   )

[Former Member]

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

[lbreddemann]

The mapping of source model/table columns to output columns is not stored anywhere except in the models. There is no central view to find this data.

With SPS9 the modeler will at least provide an option to review this mapping (which can be very complex) for the current model, but there won't be a centralized way to do this.

An important aspect to realize here is that the mapping and the actual use of source model/table columns is dynamic and decided upon during execution time.

So, even if there was a fixed view tracking the column mappings you would only ever get a "outer hull" of possible dependencies.

So, for dependency analysis, you're stuck with table/model level resolution.

As a side remark: never use the SYS. tables/views directly. There are public synonyms in place that should be used instead.

- Lars

[Former Member]

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 ?

[lbreddemann]

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

- Lars

[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