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

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.