This document highlights some features and usage patterns we hope are useful in the development of your applications.
If you have an account with a dedicated database server please refer to the Dedicated Server Notes for additional documentation.
Accessing a large amount of federated data for analysis can be slow, especially where joins and large tables are involved. Often a better solution is to create a local copy of the data at periodic intervals to create a data warehouse of sorts. Although most of our Smart Connectors support automatic caching, sometimes creating an actual copy within the database itself is needed for complex joins or other reasons.
You can accomplish this with a Scheduled Event that runs periodically and refreshes the data (see Tools > Scheduled Events...). The simplest way, if the data isn't too large, is using a commands like the following within a Scheduled Event:
For the very advanced power user, you can use dynamic SQL within a stored procedure using the EXECUTE IMMEDIATE statement. See the EXECUTE IMMEDIATE documentation for details.
One potential use case is to create a stored procedure that is called via the API with dynamic values passed into the procedure as a JSON parameter.