hana - SQL Script to get Calculation View_name and its rowcount

Question

I am looking for a query where I can get all calculation view names and its Rowcount.

SELECT "TABLE_NAME","RECORD_COUNT"

FROM "SYS"."M_TABLES" WHERE SCHEMA_NAME = '';

This query works for tables.I need same kind of query for calculation views.

question from:https://stackoverflow.com/questions/65641457/sql-script-to-get-calculation-view-name-and-its-rowcount

Answer 1

Since calculation view is essentially a composition of multiple objects with some complex logic, there's no predefined rowcount per view:

• It should not be computed, because it depends on the underlying data, and there's no reason to recalculate it every time the data is changed.
• I do not know, how does HANA use that count for table, but for calculation view it has no practical meaning: calculation view result is always build from scratch at the execution time, where you'll have the rowcount at the final step.
• Because calculation view is not a "plain" object, but more like a table-valued function, which can have input parameters and produce the results based on that parameters, you have no ability to calculate the rowcount for parametrized view.

To get list of all calculation views with fully-qualified names you can use:

select
  '"' || schema_name || '"."'
    || catalog_name || '/'
    || cube_name || '"' as fullname
from _sys_bi.bimc_all_cubes

To get list of parametrized calculation views you can use:

select *
from _sys_bi.bimc_variable