In Earlier Versions of HANA, to improve performance of the Model/Calculation view, it was suggested to stage the data in some physical table using a procedure and then create the calculation view on top of that staging table. This help us to fetch huge amount of data quickly as there are no calculations involved.To Avoid this manual work, from Q2/2022 SAP came up with SNAPSHOT option in Calculation view which will help us to speed up queries if the queries do not need to be based on the most current data.

Create a Snapshot table

We define a “snapshot query” directly in the model . To Define/enable snapshot in a model we need to follow below steps:

1. In the calculation view, select the semantics node.
2. On the View Properties tab, choose the Snapshots subtab.
3. Choose + (Add) to add a snapshot query.

A query is added to the list.

4. Select the query to edit the details.
   • In the Query Name field, enter a name for the snapshot query.
   • In the query section, overwrite the default query with the snapshot query.
5. Save and deploy the calculation view.

The snapshot table is created automatically.

6. In the SAP HANA database explorer catalog, find the newly created snapshot table with the following name: <calculation_view_name>/<query_name>/SNAP/SNAPSHOT
7. In the context menu of the snapshot table, choose Open Data.

The data shown corresponds to the result of the snapshot query.

In Above figure, we can see above sample snapshot scenario.Here Query_1 is the name of the query using which we want to insert data in snapshot table. Here we can add, remove columns as per our choice and we can also specify Input Parameters and Variables if we want to filter any type of records before inserting them in snapshot table. Once we edit the query we need to deploy the calculation view (CVR_ZSUM_M01_Q022 copy in our case).

Create an Interface View

Now we need to create a Interface View that is based on existing Calculation view and Snapshot table.

The generated calculation view makes use of union pruning so that when a query is run on the generated calculation view, either the original calculation view or the snapshot table is accessed depending on the value specified for the input parameter I_SOURCE.

To create an Interface view(Generated Calculation view) follow below steps:

1. In the calculation view (CVR_ZSUM_M01_Q022 copy in our case), select the semantics node.
2. On the View Properties tab, choose the Snapshots subtab.
3. Select the snapshot query.
4. In the Interface View Name field, enter the name of the calculation view to be generated.
5. Choose Generate interface calculation view.

For example:

The newly generated calculation view is listed in your workspace. It contains a union node with a union between the original calculation view (base view) and the snapshot table.The union node also has a column named SOURCE, which is used in the following filter expression: ‘$$I_SOURCE$$’=”SOURCE”. The input parameter I_SOURCE has the default value SNAPSHOT, so the snapshot table is accessed by default. It can be set to BASE (base view) to allow the original calculation view to be accessed.

6. Deploy the generated calculation view.
7. In the SAP HANA database explorer catalog, find the generated calculation view and execute a SELECT statement on it, specifying the input parameter as follows:

In Below Figure we can see, how interface view is generated

Here CVR_ZFIAP_M03_copy(Base View) is the original HANA view whose data we want to take snapshot. CVR_ZFIAP_M03_copy/Query_1/SNAP/SNAPSHOT(SnapShot) is the snapshot table which contains data inserted using query defined in Base View. Input Parameter of type Static List is automatically created which help us to do union pruning from Base View/Snapshot Table.

Procedures and Snapshot tables

During deployment, procedures are generated for creating, inserting data into, and dropping a “snapshot table” based on the defined snapshot query. The procedures and snapshot table are named according to the following naming conventions:

• Procedures: <calculation_view_name>/<query_name>/SNAP/SNAPSHOT/CREATE|DROP|INSERT
• Snapshot table: <calculation_view_name>/<query_name>/SNAP/SNAPSHOT

Note: Procedures and snapshot tables is coupled to the respective calculation view in which the snapshot query is defined. Thus, deleting the calculation view will also delete the corresponding tables and procedures.

Authorizations

The invoker mode is used for the procedures, which means that the authorizations of the caller are applied. Therefore, snapshots contain the data that is visible to the caller of the INSERT procedure. Several different snapshots can be generated based on an individual calculation view. These snapshots can be generated by different database users. In this way, data reflecting different privileges can be exposed.

To run SELECT statements on snapshot tables, you must have SELECT privileges for the respective snapshot tables.

Scheduling Inserts

You can schedule when the snapshot tables should be filled, for example, by using the job scheduler to execute the INSERT procedures on a regular basis. For more information about the job scheduler, see CREATE SCHEDULER JOB Statement (Data Definition).

For large data sets, that is, when particularly large queries lead to a huge number of records, it can be beneficial to schedule multiple inserts and to restrict each insert to a non-overlapping set of records based on an input parameter. For example, you could use an input parameter, named IP_year in the example below, to load individual years into the snapshot table:

CALL “snapshotCV/Query_1/SNAP/SNAPSHOT/INSERT”(IP_YEAR => ‘2022’)

The individual loads could then be scheduled using the job scheduler.

If you want to avoid being prompted for the “technical” input parameter when the calculation view is not being used to fill the snapshot, you can do so by adding a default value to the input parameter in the calculation view and then checking against this default value in a filter expression.

For example, if the input parameter IP_year has the default value ‘no influence’, you could use the following filter expression:

‘$$IP_year$$’=’no influence’ or “year”=’$$IP_year$$’

This would filter on the column “year” if IP_year differs from its default value. Otherwise, the filter would be ignored.

Monitoring Snapshots

You might want to find out how frequently snapshots are accessed and the approximate size of the snapshots.

You can execute the following statement, for example, to get the number of SELECT statements executed on all the snapshot tables whose metadata is visible to you (that is, the user who runs the query):

select SELECT_COUNT,* from

SYS.M_TABLE_STATISTICS

where TABLE_NAME like ‘%SNAPSHOT’

You can get an estimation of the size of a snapshot by running the following statement:

select MEMORY_SIZE_IN_TOTAL,* from

M_CS_TABLES

where TABLE_NAME like ‘%SNAPSHOT’

For the M_TABLE_STATISTICS monitoring view, the following parameters must be enabled in the sql section of the indexserver.ini file:

• table_statistics_modify_enabled
• table_statistics_select_enabled

With snapshots we have now an easy and flexible means to serve queries that do not need online data and by this reduce resource consumption.

References:

https://help.sap.com/docs/HANA_CLOUD_DATABASE/d625b46ef0b445abb2c2fd9ba008c265/d35f768ce50145d2ad0e5916898f004d.html

Limitations

• Snapshot cannot be implemented for Models/Calculation views having hierarchies
• Sometimes when we deploy interface view then for generated interface view, all columns turns into measures . We need to manually change them to attributes. Hope this will be fixed in future releases.

Hope this Article helps you answer question related to snapshot functionality in calculation views.