Determine which Semantic Mapping and Local Transform objects should be views

Background and Strategy

For most objects in Ursa Studio, users can choose to save the object into the database as either a view or a physical table. During the development phase of an integration project there might have been good reasons for using tables (see Create and run Semantic Mapping objects), but it may be more appropriate to set those object to be views now that the integration is completed.

To review: the benefit of tables is that they can effectively save the results of computationally burdensome operations performed by an object, meaning those operations need only be run once regardless of times a downstream consumer invokes the object.

(Note that one such downstream consumer of an object is the Ursa Studio Case Review feature, which, naturally, queries the object's table or view to produce the case-level details. When the underlying object is a physical table, that query is computationally simple; when the object is a view, all the logic in the view runs each time a new case review is performed.)

Tables also offer some additional optimization configurations, like indexes (or index-like features such as Redshift sort keys) and partitions. Relatedly, databases can collect statistics on tables, which increases the chance of an efficient query plan when the table is invoked by downstream objects or measures.

The cost of using tables is that they occupy space and take more up-front time and database resources to populate.

In general, Semantic Mapping objects tend to be good candidates for using views. First, their record count can be quite high since they will contain exactly one record for each record in the source object they are mapping from, which are often large; this means the database resource burden of physically storing all those records might be significant. Second, Semantic Mapping objects are also often simple in terms of their joins or aggregation needs – indeed, most Semantic Mapping objects do not involve any joins or aggregation at all; this means the burden from repeated rerunning the view query might be modest.

The case for setting Local Transform objects as views is less straightforward, because they can take a much wider variety of forms compared to Semantic Mapping. In general, as the object becomes more complicated, the more the potential computational savings there will be by saving the results in a physical table if the object is referenced by two or more downstream objects. So Local Transform objects that are structurally simple or have only a single (direct) downstream object are theoretically good candidates to use views.

Ultimately, however, it can be hard to predict whether using tables or views will result in a net decrease in ELT time. Even the same objects run on different DBMS technologies (for example: Redshift vs. Snowflake) might have very different performance profiles. Simply testing both approaches and seeing which is faster is therefore a good exercise to consider.

Detailed Implementation Guidance

1. Semantic Mapping or Local Transform objects that are used by exactly one downstream object are often good candidates to be set as views.

2. Semantic Mapping or Local Transform objects that involve computationally intensive logic -- for example, patterns that invoke window functions or (in the case of Local Transform objects) involve stacks with a lot of linked objects -- are often good candidates to be set as views.

3. Note that changing the Database Object Type from a table to a view, or vice versa, will not immediately convert the object; you must ELT the object for that change to take effect.