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.project_id = c.project_id
and d.project_vrs_id = c.project_vrs_id
and d.dom_id = c.dom_id
where c.project_id = @project_id
and c.project_vrs_id = @project_vrs_id
and d.dttp like '%char%'
and d.length > 10
and d.length < 500
and d.control_id is null

The variables @project_id and @project_vrs_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.
caution

Make sure your update contains a project_id and project_vrs_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
(
project_id
,project_vrs_id
,dom_id
,dttp_id
,length
,control_id
)
select
@project_id as project_id
,@project_vrs_id as project_vrs_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.project_id = @project_id
and d.project_vrs_id = @project_vrs_id
and d.dom_id = ''gen_email'')

update col set dom_id = ''gen_email''
where project_id = @project_id
and project_vrs_id = @project_vrs_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.

caution

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

Available enrichments

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.

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 combined filter

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

  • The model update includes for each table the display column in the combined filter.

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.