Sneak: How to manage Automatic Column Handling

How to manage Automatic Column Handling

Automatic Column Handling allows users to update chosen tables in the semantic model each time the model is processed by automatically adding new columns to the semantic model added physically to the underlying tables. Similarly, columns that have been removed from the physical tables will also be removed from the semantic model. This is especially useful for Direct Query models that need to be kept in sync with changes in the underlying database tables.

1. The Automatic Column Handling feature is added in the same tab as the Models Schedules.
   In the Job Details section, select SyncModelColumns. This is used to define the synchronization mode.
   There are three options:
   • None – No columns in the model are synced. This is the default value.
   • Sync All – Sync all the tables in the model.
   • Sync Selected Tables – Sync some, but not all, the tables available through the model

   

2. If the user selects Sync Selected Tables, they must select the tables that need to be synced. This is done directly in the model. Open the Model, then the Data Model tab, then the tables element.
   For each table that needs to be synced, click on the Table’s heading area (clicking on a column name will open the column feature, which has different options). In the Properties section, select Sync table columns.

   

3. Sync Column Settings
   This feature allows users to define how each data type in a model will be defined. In this case, when refreshing the columns, the sync defines how the new columns will be displayed in the database.

   

4. In the example on the right, if the new column is an Integer value, the column will be not displayed. If the column contains a Real value, it will be displayed, and any aggregate value for the column will display the column’s sum.
   It is possible to setup multiple different schedules for a Model. This could be utilized, for example, to run a model daily without column synchronization, then a different schedule running monthly that synchronizes the columns between the data source and model

   

Example demonstrating usage of the feature.

1. Discover in Pyramid using a direct query model, showing the PA_CUSTOMER table

1. A script is run on the database to update this table to include a First and Last name.

   alter table [dbo].[PA_CUSTOMER] add FIRST_ NAME varchar(255)
   alter table [dbo].[PA_CUSTOMER] add LAST_ NAME varchar(255)
   

   

   
    
2. Schedule is run set to ‘Sync Model Columns’ = Non

   

3. In the discover object the user clicks on “Refresh Metadata”. PA_CUSTOMER dimension remains unchanged

   

    

   

4. Schedule is run set to ‘Sync Model Columns’ = Sync Selected Tables

   

5. In the discover object the user clicks on “Refresh Metadata”. PA_CUSTOMER dimension has been updated

   

If you have questions about these tips (or improvements) please let us know in a comment.

P.S. We will post previous Tip&Tricks as well as all new ones to keep this information active and available to all users.