Skip to main content

Enrichment runs (Upcycler)

Overview of enrichment runs​

An enrichment run is one of the steps in the upcycle process. It is a way to automatically improve an upcycled application based on characteristics of the model and/or data.

Introduction to enrichment runs​

Unlike the upcycle and the data import, the enrichment run contains two separate phases:

  • First, the analysis run that produces a number of proposed enrichment actions.
  • Then, you select the actions you want to execute and start an enrichment action run.

Phase one: the Analysis run​

The Analysis run produces a number of proposed enrichment actions.

  • The analysis run needs to get information from both the model and the data. Information about the model is stored in the Software Factory database. The data is stored in the application database. Therefore, the enrichment analysis steps require two queries, one for the model and one for the data. Some enrichments need access to only one of the databases, so both queries are optional.
  • Like the upcycle and data import steps, the enrichment analysis steps are available on a generic, technology, and application level.

Phase two: the Action run​

The Action run executes the proposed actions you selected.

It needs to be able to change both the model and the data. Information about the model is stored in the Software Factory database. The data is stored in the application database. Therefore, the enrichments action steps have two updates, one for the model and one for the data. Some enrichments need access to only one of the databases, so both updates are optional.

Programming an enrichment​

It is easiest to understand how to program an enrichment by using an example.

In this example we have a large application with a huge database that uses a lot of email addresses.

Manually, it would take a lot of time to inspect the entire model for columns that might contain email addresses, check the data to see if they really do, and then change the columns by giving them a domain with an email control.

To save a lot of time and effort, we want to program an enrichment that does everything automatically.

Model query​

Since the database is huge, it would take a long time to check every column for email addresses. Many columns even cannot contain an email address, for example, because they are numeric. Other columns may already have an EMAIL control in their domain. So first, we use the model query to collect all columns we want to inspect in the database.

-- Select all columns which might be an e-mail address column

select c.tab_id,
       c.col_id,
       c.dom_id
from col c
     join dom d
       on d.model_id  = c.model_id
      and d.branch_id = c.branch_id
      and d.dom_id    = c.dom_id
where c.model_id  = @model_id
  and c.branch_id = @branch_id
  and d.dttp           like '%char%'
  and d.length         > 10
  and d.length         < 500
  and d.control_id     is null

The variables @model_id and @branch_id are made available by the Thinkwise Upcycler for use in a model query.

Data query​

The result of the model query is automatically made available to the data query. We want to check the database to see which of these columns contain only email addresses. For this smaller set of columns, we want to produce enrichment action steps that the user can select.

declare @sql             nvarchar(max),
        @score           bit,
        @error_number    int,
        @error_message   nvarchar(4000),
        @error_state     int,
        @tab_id          nvarchar(128),
        @col_id          nvarchar(128),
        @dom_id          nvarchar(128)

The result of the model query is passed in @json, in JSON format. In this example, we extract the information from the JSON and store it in a temporary table.

-- Store result of model query
select c.tab_id, 
       c.col_id, 
       c.dom_id
into #email_columns
from openjson(@json) a
     cross apply openjson(a.value) b
     cross apply openjson(b.value)
     with 
         (
             tab_id nvarchar(max),
             col_id nvarchar(max),
             dom_id nvarchar(max)
         ) c
where b.[key] = 'resultSet'

Then we prepare another temporary table to store the columns that actually contain email addresses.

-- List of found e-mail columns
create table #email_columns_data
(
   tab_id   nvarchar(200)
  ,col_id   nvarchar(200)
  ,dom_id   nvarchar(200)
)

Using a cursor, we loop through all the columns found in the model.

-- Go through all potential e-mail columns
declare email_columns cursor local static read_only forward_only for
select ec.tab_id,
       ec.col_id,
       ec.dom_id
from #email_columns ec
order by ec.tab_id, ec.col_id

open email_columns

-- Get the first 
fetch next from email_columns into @tab_id,
                                   @col_id, 
                                   @dom_id

-- As long as there are steps
while @@fetch_status = 0
begin

For each column found in the model, we construct an SQL statement that checks if the column contains only email addresses and then execute that SQL statement.

-- Create dynamic sql query to find e-mail addresses
    set @sql = 'declare @nr_values int, @nr_emails int
set @score = 0
select @nr_values = count(1) from ' + @tab_id + ' where ' + @col_id + ' is not null
if @nr_values > 0
begin
    select @nr_emails   = count(1) from ' + @tab_id + ' where ' + @col_id + ' like ''%@%.%''
    if @nr_emails = @nr_values
    begin
        set @score = 1
    end
end 
'
    -- Execute the SQL for the step
    begin try

        exec sp_executesql @sql, 
                           N'@score bit output',
                           @score = @score output

    end try
    begin catch 

        set @error_number  = error_number()
        set @error_message = error_message()

    end catch

If this column only contains email addresses, we add it to the temporary table.

    if @score = 1
    begin

        -- Add column to e-mail column list
        insert #email_columns_data 
        (
           tab_id, 
           col_id, 
           dom_id
        ) 
        values (@tab_id, @col_id, @dom_id)

    end 

    -- And the next one...
    fetch next from email_columns into @tab_id,
                                       @col_id, 
                                       @dom_id

end -- of while loop

close email_columns
deallocate email_columns

Now the loop has finished, all columns that we initially found in the model and turned out to contain only email addresses are in the temporary table.

Next, we do a query that produces output in the format of the enrichment actions:

  • order_no - The Thinkwise Upcycler will sort the enrichment actions in this field. In our example, the order is not important, so we fill every row with 1.
  • name - A name for the enrichment action. Usually, the name is the same for all enrichment actions produced by one enrichment analysis, but you can vary if you think that is useful.
  • description - The description should at least contain information on what the action will change, in our case which column of which table.
  • model_update - The update statement for the model. In this example, we insert a domain for email columns if it doesn’t already exist, and we update the column, so it uses the email domain. Note that this also standardizes the data type and length of the email columns.
warning

Make sure your update contains a model_id and branch_id.

  • data_update - The update statement for the data. In this example, we take the opportunity to make all email addresses lowercase.
  • selected - Use 1 if you want to select the action by default or 0 if you don’t.

The Thinkwise Upcycler will store the enrichment actions, linked to the current enrichment run. This ensures that they will show in the user interface of the Thinkwise Upcycler.

-- Produce action steps
-- Create needed domain
-- Add domain to columns
select  1                    as order_no,
        'Set Email controls' as name,
        'Table ' + tab_id + ', column ' + col_id + ', current domain ' + dom_id 
        as description,
        '
insert dom
(
    model_id    
   ,branch_id
   ,dom_id        
   ,dttp_id  
   ,length
   ,control_id
)
select
    @model_id    as model_id
   ,@branch_id   as branch_id
   ,''gen_email''    as dom_id
   ,''NVARCHAR''     as dttp_id
   ,1500             as length
   ,''URL''          as control_id
where not exists (select 1
                  from dom d
                  where d.model_id  = @model_id
                    and d.branch_id = @branch_id
                    and d.dom_id    = ''gen_email'')

update col set dom_id = ''gen_email'' 
where model_id  = @model_id
  and branch_id = @branch_id
  and tab_id = ''' + tab_id + '''
  and col_id = ''' + col_id + '''
        ' 
        as model_update,
        '
update ' + tab_id + '
set ' + col_id + ' = lower(' + col_id + ')
        ' 
        as data_update,
        1 as selected
from    #email_columns_data

More options​

The example covers a situation where you need to read and update both the model and the data. If that is not necessary, other options are available:

Only read model information​

In this case, only write a query in the model query field and leave the data query field empty. The output needs to be in the format of the enrichment actions.

Only read data information​

In this case, only write a query in the data query field and leave the model query field empty. The output needs to be in the format of the enrichment actions.

Only update the model​

In your enrichment actions, leave the data_update field empty.

Only update the data​

In your enrichment actions, leave the model_update field empty.

Be careful when testing enrichments​

Be careful when testing enrichments. A mistake in your updates can easily wreck your entire model and even other models in the same Software Factory. Also, your data can be damaged easily.

warning

Make sure to make backups of both your data and model databases. And create a new version of your model before running enrichments.

Availabe enrichments in the Software Factory​

tip

More enrichments are available in the Upcycler. See:

The following model enrichments are available in the Software Factory. You can run them as soon as your application is upcycled and available in the Software Factory. For more information, see Model enrichments.

NameDescription
Set controls (EMAIL, CHECKBOX, URL, CALCULATOR)Uses data from the product database and inspects the model to suggest controls to use.
Change Yes or No fields into BIT fieldsReads the contents of all tables and finds true/false data to convert into BIT.
Set Combo controls on foreign keysReads all tables equal to or below the given threshold. Then creates suggestions to change the foreign key lookup control and the default table lookup control to Combo (sorted).
Show row count in grids on first visible columnProvides the user with the number of records in a grid for the first visible column.
Set display column for every tableSets a display column for every table.
Include every lookup column in combined filterIncludes every column that is a lookup in the combined filter.
Set main screentypes of tablesSets the main screen type to master_detail, hierarchy, or master_detail_vertical based on the number of visible columns.
Hide technical primary key fieldsHides primary key columns when they are plain technical numbers without significant value for the user.
Set group labels in forms and gridsProvides structure and groups similar fields using grid group labels and form group labels.
Set column widths in gridsReads all tables that are not empty to determine suitable grid column widths.

Available AI enrichments in the Upcycler​

This chapter describes the AI enrichments that come with the Thinkwise Upcycler.

Translate objects using AI​

note

This enrichment only works if an AI provider is configured correctly in IAM (see Enable generative AI). During the first enrichment analysis, this provider is tested. If it does not work correctly, the steps are disabled in the application steps (Upcycle > Enrichment runs > Application > Application enrichment analysis steps).

Note that these steps may take a while if the legacy consists of a lot of code.

This enrichment exists for the following object types:

  • Views
  • Procedures
  • Functions
  • Triggers/Instead of triggers
  • Defaults
  • Layouts

With Translate objects, you can translate legacy code into T-SQL per code group, using AI. These enrichments are separated between code groups to be disabled if the legacy code does not support anything that can be transformed into that logic type.

  • The model query finds all control procedures that are not inactive and not generated (either by a dynamic model or from a base model) for that specific code group.
  • The model query uses the template code as a prompt for the AI to translate.
  • The AI instruction tells the OpenAI provider that it is a programmer who can translate code, which rules to follow, and what kind of code to return.
  • The data query produces a step per template with a valid AI response. During this step, the template code is overridden by the AI suggestion. You can choose which steps to execute in the action steps or execute them all and review the code in the Software Factory. There, you can view the old code in the history (Ctrl + H).

Create control procedure and template description using AI​

note

This enrichment only works if an AI provider is configured correctly in IAM (see Enable generative AI). During the first enrichment analysis, this provider is tested. If it does not work correctly, the steps are disabled in the application steps (Upcycle > Enrichment runs > Application > Application enrichment analysis steps).

Note that these steps may take a while if the legacy consists of a lot of code.

Create control procedure and template description uses template code to create a description that explains what the code does. This can be very helpful for legacy code that could not be translated, as well as for Thinkwise experts who are not fluent in the legacy language.

For this enrichment, we assume that every control procedure has precisely one template and one static assignment. It should be used before any changes have been made to upcycled code. If you have changed anything, check the model query, and exclude these changes in the model query if necessary.

  • The model query finds all control procedures that are not inactive and not generated (either by a dynamic model or from a base model).
  • The model query uses the template code as a prompt for the AI to summarize.
  • The AI instruction tells the OpenAI provider that it is a programmer who summarizes code, and what kind of response it must give.
  • The data query produces a step per template with a valid AI response. During this step, a description is added by the AI suggestion if the description was empty before.

Available enrichments in the Upcycler​

The Enrichment step in your upcycle process contains a large number of ready-made enrichments. This chapter describes the predefined enrichments that come with the Thinkwise Upcycler. The AI-driven enrichments are described in Available AI enrichments.

You also can make your own enrichments or tailor the predefined ones to your specific needs.

Set EMAIL controls​

This enrichment puts an EMAIL control on columns that contain email addresses.

  • The model query finds all columns with a character data type and a length between 10 and 500 characters.
  • The data query checks which columns contain only email addresses.
  • The model update adds a domain with an EMAIL control and applies it to all email columns. This also standardizes the datatype and length of the email columns.
  • The data update makes all email addresses lowercase.

One action step per column is produced.

Set URL controls​

This enrichment puts a URL control on columns that contain URLs.

  • The model query finds all columns with a character data type that contains at least 30 characters.
  • The data query checks which columns contain only URLs.
  • The model update adds a domain with a URL control and applies it to all URL columns. This also standardizes the datatype and length of the email columns.

One action step per column is produced.

Set CHECKBOX controls​

This enrichment puts a CHECKBOX control on columns with a BIT data type.

  • The model update adds a domain with a CHECKBOX control and applies it to all BIT columns.

One action step for the entire model is produced.

Change Yes or No fields into BIT fields​

This enrichment converts character columns that contain yes/no values to proper bit columns with a CHECKBOX control.

  • The model query finds all character columns with a length less than 20.
  • The data query checks which columns contain only values 'yes', 'no', 'y' or 'n'.
  • The model update creates a domain with a bit datatype and a CHECKBOX control and applies it to the columns. Values 'yes' and 'y' are replaced with '1' and values 'no' and 'n' with '0'.

One action step per column is produced.

Set CALCULATOR controls​

This enrichment adds a CALCULATOR control to all numeric domains that are not used in a primary or foreign key.

  • The model query finds all numeric domains that are not used on primary key and foreign key columns, and the columns that use them.
  • The model update creates a copy of these domains, adds a CALCULATOR control and applies that to the columns.

One action step per column is produced.

Set COMBO controls on foreign keys​

This enrichment presents foreign keys to tables with 10 or fewer records as a combo box.

  • The data query sets the lookup controls for references pointing to tables with 10 or fewer records to COMBO.

One action step per table is produced.

Add row count to first column in grid​

This enrichment adds a row count to the first column of each grid.

  • The model query finds all columns that are the first in a grid.
  • The model update adds settings Show aggregation and Aggregation type Count to the Subject.

One action step per table is produced.

Set display column for each table​

This enrichment tries to set a display column for each table.

  • The model query searches in each table the first column with a character data type and a certain naming convention.
  • The model update sets the display column for the table.

One action step per table is produced.

Include every display column in Search field​

This enrichment ensures that a table's display column is available in the Search field of that table.

  • The model update includes for each table the display column in the Search field.

One action step for the entire enrichment is produced.

Set main screen types for tables​

This enrichment determines the main screen type of the tables based on the number of columns.

  • The model query determines the screen type for each table.
    • Tables with more than 40 columns get the type ‘hierarchy’.
    • Tables with more than 12 but less than 40 columns get the type ‘master_detail’.
    • Tables with less than 12 columns get the type ‘master_detail_vertical’.
  • The model update applies the screen types to the tables.

One action step per table is produced.

Hide technical primary key fields​

This enrichment hides technical primary key fields.

  • The model query finds all primary key fields with a numeric datatype that are not used in a lookup reference.
  • The model update sets these columns to hidden.

One action step per column is produced.

Add group labels to forms and grids​

This enrichment adds group labels to forms and grids.

  • The model query tries to find suitable group labels based on the similarity of subsequent field names.
  • The model update applies the labels.

One action step per group label is produced.

Set column width in grids​

This enrichment sets the column width in grids.

  • The model query selects columns and the required model information to determine the field width.
  • The data query determines the display length in pixels based on the header length, column length, and a data sample.
  • The model update applies the width.

One action step per column is produced.

Remove empty tables​

This enrichment removes empty tables from the application.

  • The data query finds all empty tables.
  • The model update removes these tables from the application.

One action step per table is produced.

Was this article helpful?