Sometimes, storing calculation view results isn't just for performance. You could need to take pictures of your data at different points in time (snapshots). This could be done to store historical values. Or you could need performance but not on real-time data, just on a latest snapshot of the system taken in the morning, for example.
The Snapshot Process
Let's look at how the snapshot feature works.
The calculation view snapshot process is as follows:
- First, you need to deploy your calculation view to generate the snapshot table.
- Procedures are also generated to truncate the snapshot table or to trigger insertion of data.
- You can execute the snapshot manually or schedule it to get new data regularly.
- You can then query the snapshot table to get historical data.
- You can always continue using the calculation view as usual to get real-time data.
Generating Snapshot Artifacts
First, if you want to use this feature, you need to setup your calculation view to create a snapshot.
To setup a snapshot you need to go to the View Properties tab of your calculation view and then to the Snapshots tab. Here you can create a query on top of your calculation view. It's the result of this query that will be stored in the generated snapshot table.
Procedures are also generated to create or drop the snapshot table and to insert or truncate data. You can trigger the procedures whenever a change in the snapshot data is needed. You need to define the security mode for the generated procedures. If you choose DEFINER, any user with the right to execute those procedures will be able to use them. If you use INVOKER, you'll have to give access to the snapshot table.
Watch this video to learn how to setup snapshots for a calculation view.
Executing Snapshots
The INSERT procedure can be used at any time to execute the query and store the results into the snapshot table.
Caution
If you call the procedure to insert data twice, without truncating data, you'll have duplicates.You could, of course, schedule execution of those procedures to get regular updates of the snapshot data.
You can also decide to provision the snapshot table when deploying the calculation view. For this, you need to select the Create Snapshot After Deployment Automatically option. By default, data will be truncated and potentially inserted again each time you deploy your calculation view. If you don't want to delete data between two deployments, you can use the Keep Snapshot During Re-deployment option.
Watch this video to learn how to provision the snapshot table.
Using Snapshots
In order to report on snapshot data, you just need to select from the snapshot table. If you need real-time data, you can still use your calculation view as usual.
If you want to be able to get both possibilities with one view, you can define an interface view. This generated view does a union with both the snapshot table and the calculation view and uses an input parameter to filter data from one source or the other.
Watch this video to learn how to generate and use the interface view.