When creating views in the semantic layer, Zenlytic creates a file with definitions on top of the underlying table that accomplishes several things:
1. Assigns a name to the view (can be different from the underlying table)
2. Specifies which model_name to use (and therefore the database connection)
3. Identifies the database table name (and schema if needed)
3. Defines a default date for aggregating metrics
4. Defines the primary key for the table
version: 1
type: view
name: customers
model_name: pure_organics
sql_table_name: schema_name.table_name
default_date: first_order
fields:
- name: customer_id
field_type: dimension
type: string
primary_key: true
description: The id of the customer
sql: ${TABLE}.customer_id
etc, etc.
When the view is first created by Zoe, a dimension is created from each column in the underlying database table.
The developer (or Zoe) can then add further custom dimensions and/or measures to the view.
These dimensions and measures are part of the semantic layer, irrespective of the the underlying database columns.
What happens when the underlying table is changed?
When the underlying database table changes, either by deleting, adding or updating existing table columns, the view will still point to that table, however the semantic layer definitions may or may not still be valid. The developer must then go through and update the semantic layer to align with the new table structure.
Note: any dimensions or measures defined in the semantic layer that no longer exist in the underlying database table will not cause errors or warnings in the semantic layer complier, these errors will be seen at runtime if a dimension or measure that is referenced is no longer part of a valid SQL statement.
There are a few best practices to follow when updating the underlying database tables
1. In the view file itself, place all custom defined dimensions and measures at the bottom of the view, clearly separated from the rest of the definitions. Copy this part of the code to a text editor and save. Also copy the first few lines of the view that define the name, model_name, default_date and any description that have been added.
2. Create a backup of the master branch.
3. Create a developer branch off of the master branch and make any updates in this developer branch before pushing to the master branch
4. When the underlying database table is updated, delete the existing view, create the new view and then copy the saved custom definitions back to the new view. This way you won’t have to recreate any custom content.
Note: the semantic layer compiler will throw errors for any dimension or measure names that have changed or no longer exist, and can easily be cleaned up.