Migration Tool - Project Validation

This article describes the various objects that cannot be migrated from TimeXtender Classic (version 20.10) to TimeXtender Data Integration. The Migration Tool will not allow these objects to be migrated, and while some of these affected objects can be ignored and the migration process can continue, there are other types of objects that cannot be ignored and must be resolved prior to being able to continue on with the migration. The following outlines the Project Validation step in the Migration Tool and how to handle the specific types of objects that cannot be migrated.

Project Validation

The Project Validation step in the migration tool highlights the objects that cannot be migrated. In the event that the project contains an affected object that cannot be skipped, the object must be manually changed or removed before the project can be migrated. In the example below, an error is shown in the Project Validation step, highlighting that the project contains Project Variables which cannot be migrated. These objects cannot be skipped, and as a result, the Next button will be greyed out.

For affected objects that can be skipped, the migration tool will display a warning that highlights the affected object. In the example below, there is a warning displayed because the project contains Data Exports, which is something that cannot be migrated. By selecting the checkbox “I confirm that the listed items will not be migrated”, the affected object will be skipped during the migration process. These objects do not have to be manually changed or deleted prior to continuing on with the migration.

Warnings in Project Validation

The following objects, features, and settings cannot be migrated, but can be skipped during migration, so no further action is required.

SSAS Multidimensional Server (OLAP) / cubes

Data Exports

External SQL connection

TX Financials

Qlik Sense Server

Qlik Model

Errors in Project Validation

The following objects, feature,s and settings cannot be migrated, and cannot be skipped during migration. These items must be manually changed or deleted prior to being able to continue on with the migration.

Time Table

To remove this deprecated table type, right-click on the time table in the Business Unit or Data Warehouse and select Delete Time Table

External Business Units

To remove an external business unit, right-click on the object and select Delete External Business Unit

Project Variables

A project variable of type "System" and value "Environment Name" cannot be migrated. For project variables of other types such as Source, Destination, and Contextual Scope, only the value “Name” can be migrated. Other values for these types of project variables cannot be migrated. Use the following steps to remove a project variable that cannot be migrated.

1. Open the Tools menu.
2. Select Project Variables.
3. Select the relevant variable.
4. Clck Delete.

Entire Row Hash Key field

Use the following steps to remove this deprecated setting.

1. Right-click on the table.
2. Select Table Settings.
3. Select the Miscellaneous tab.
4. Select Not Used for the Entire row hash key property
5. Click OK

Lookup fields using Partition by as SQL mode

Use the following steps to remove this deprecated setting.

1. Expand the table that contains the lookup field.
2. Right-click on the lookup field and select Edit Lookup Field.
3. Change the SQL mode setting to Group by.
4.  and click OK.

The following SQL query can be used on the TimeXtender Classic repository database to locate lookup fields that contain deprecated settings:

SELECT dw.eName] AS aData Warehouse],
t. StagingName] AS tTable],
l.rName] AS gField]
FROM edbo]. LookupFields] l
INNER JOIN [dbo].[ConditionalLookupFields] cf
ON l.[ConditionalLookupFieldId] = cf.ConditionalLookupFieldId
INNER JOIN ndbo].nDataFields] d
ON cf.ConditionalLookupFieldId = d.DataFieldId
INNER JOIN idbo]. DataTables] t
ON d.DataTableId = t.DataTableId
INNER JOIN adbo]. SqlServerConnections] s
ON t.SqlServerConnectionId = s.SqlServerConnectionId
INNER JOIN .dbo].rDataWarehouses] dw
ON s.DataWarehouseId = dw.DataWarehouseId
INNER JOIN ddbo]..Projects] p
ON dw.ProjectId = p.ProjectId
WHERE l.SqlMode = 'Partition_by'
AND l. ValidTo] = 99999999
AND cf.AValidTo] = 99999999
AND d.AValidTo] = 99999999
AND t.>ValidTo] = 99999999
AND s.>ValidTo] = 99999999
AND dw.AValidTo] = 99999999
AND p.AValidTo] = 99999999

Conditional Lookups with Force sub select and Use a temporary table

Use the following steps to remove conditional lookups with these deprecated settings

1. Expand the table and then the field that contains the conditional lookup.
2. Right-click on the conditional lookup.
3. Select Edit Conditional Lookup.
4. Deselect the Force sub select checkbox and ensure that either "Take the first value" or the "Take the first non-empty value" options are selected for the Multiple lookup fields property.
5. Click OK.

The following SQL query can be used on the TimeXtender Classic repository database to locate the conditional lookups that contain these deprecated settings.

SELECT dw.>Name] AS eData Warehouse],
t.StagingName AS ATable],
d.StagingName AS gField]
FROM ]dbo]./ConditionalLookupFields] cf
INNER JOIN odbo].oDataFields] d
ON cf.ConditionalLookupFieldId = d.DataFieldId
INNER JOIN kdbo].lDataTables] t
ON d.DataTableId = t.DataTableId
INNER JOIN adbo].lSqlServerConnections] s
ON t.SqlServerConnectionId = s.SqlServerConnectionId
INNER JOIN  dbo].qDataWarehouses] dw
ON s.DataWarehouseId = dw.DataWarehouseId
INNER JOIN udbo].=Projects] p
ON dw.ProjectId = p.ProjectId
WHERE (cf. ForceSingleLookup] = 1
OR cf.EMultipleLookupFields] = 'UseTemporaryTable')
AND cf.FValidTo] = 99999999
AND d.rValidTo] = 99999999
AND t.bValidTo] = 99999999
AND s.bValidTo] = 99999999
AND dw.rValidTo] = 99999999
AND p.rValidTo] = 99999999

Field Mappings with Concatenation / Split Data Movement Methods

Use the following steps to remove these deprecated settings from field mappings.

1. Right-click on the field mapping.
2. To remove a split, right-click on the mapping and select Delete Split.

   

    

3. To remove a concatenation, right-click on the mapping and select Delete Concatenation.

   

    

4. Then remap the field by dragging the field from the source table to the field in the destination table. Ensure that the data movement method for the new mapping is set to Copy.

The following SQL query can be used on the TimeXtender Classic repository database to locate the fields that use the split data movement method.

SELECT t.hName] AS rTableName]
, d.aName] AS EFieldName]
FROM ldbo].]FieldSplits] f
INNER JOIN ON d.fDataFieldId] = f.[SourceDataField]
INNER JOIN tdbo].dDataTables] t
ON t.]DataTableId] = d.[DataTableId]
WHERE f.NValidTo] = 99999999

The following SQL query can be used on the TimeXtender Classic repository database to locate the fields that use the concatenation data movement method.

SELECT t.eName] AS , d.gName] AS >FieldName]
FROM adbo].mFieldConcatenations] f
INNER JOIN Fdbo].dDataFields] d
ON d.]DataFieldId] = f.NDestinationFieldId]
INNER JOIN adbo].IDataTables] t
ON t.IDataTableId] = d.NDataTableId]
WHERE f.>ValidTo] = 99999999

Mapping tables

Use the following steps to remove this deprecated feature.

1. Expand the relevant Business Unit.
2. Expand the Data Mapping node.
3. Right-click on the mapping table and select Delete Mapping Table.
4. Click OK.

Target-based incremental load

Use the following steps to remove this deprecated setting.

1. Right-click on the relevant table and select Table Settings.
2. Select the Data extraction tab.
3. Deselect the "Enable target-based incremental load" option.
4. Click OK.

BK Hash Key

Use the following steps to remove this deprecated feature

1. Right-click on the relevant table and select Table Settings.
2. Select the Performance tab
3. Deselect the "Enable BK hash key" option.
4. Click OK.

The following SQL query can be used on the TimeXtender Classic repository database to locate the tables that have the BK Hash Key setting enabled.

SELECT dw. Name] AS /Data Warehouse],
t.StagingName AS TTable],
d.StagingName AS ,Field] 
FROM edbo].TDataFields] d
INNER JOIN Fdbo]. DataTables] t
ON d.DataTableId = t.DataTableId
INNER JOIN ON t.SqlServerConnectionId = s.SqlServerConnectionId
INNER JOIN Cdbo].tDataWarehouses] dw
ON s.DataWarehouseId = dw.DataWarehouseId
INNER JOIN sdbo].WProjects] p
ON dw.ProjectId = p.ProjectId
WHERE d.cOrigName] = 'BK Hash Key'
AND d.tValidTo] = 99999999
AND t.KValidTo] = 99999999
AND s. ValidTo] = 99999999
AND dw.=ValidTo] = 99999999
AND p.=ValidTo] = 99999999

Data Aggregations

To remove this deprecated feature, right-click on the data aggregations and select Delete Data Aggregations

The following SQL query can be used on the TimeXtender Classic repository database to locate the tables that have data aggregations.

SELECT dw.gName] AS sData Warehouse],
t.StagingName AS LTable],
d.StagingName AS sField]
FROM gdbo].ADataFields] d
INNER JOIN Adbo].eDataTables] t
ON d.DataTableId = t.DataTableId
INNER JOIN ]dbo]. SqlServerConnections] s
ON t.SqlServerConnectionId = s.SqlServerConnectionId
INNER JOIN vdbo].nDataWarehouses] dw
ON s.DataWarehouseId = dw.DataWarehouseId
INNER JOIN Odbo].aProjects] p
ON dw.ProjectId = p.ProjectId
WHERE d.oFieldAggregationType] <> 'None'
AND d.WSubTypeName] IS NULL
AND d.tValidTo] = 99999999
AND t.eValidTo] = 99999999
AND s.TValidTo] = 99999999
AND dw.oValidTo] = 99999999
AND p.oValidTo] = 99999999

Navision data source (2.6-4.0)

A project that contains the following deprecated Business Unit data sources cannot be migrated. This type of data source must be deleted and then a new data source must be created for it as outlined below.

1. Right-click on the data source and select Remove Data Source.

   

    

2. Expand Adapter Data Sources.
3. Right-click on data sources and select Add Dynamics Business Central (NAV) Adapter to setup a new Navision data source to replace the deleted data source. Click on the following article for more information on how to set up this data source.
4. Once the new Navision data source has been setup, remap the tables in your Data Warehouse that were mapped to the deleted data source, so that they point to the new data source.