Converting the Dimension Set ID with DataMigrate Pro

Not all old NAV versions have the Dimension Set ID field. If you want to fill this field in the target system, a small entry in the migration configuration is enough.

The list of tables to be migrated contains the field Dimension Set ID Field No. If one is specified in the configuration, DataMigrate Pro adjusts the data query. Beforehand, however, a list of the dimension usages per table has to be determined. To do this you first open the form or page BC Dimensions and start the analysis which among other things Action

If we want the Dimension Set ID to be set for table 17, for example, the corresponding field number 480 must be entered in the overview.

This adjustment ensures that the correct Dimension Set ID is stored in Business Central.

For visitors interested in the technology – here is the generated SQL command:

;WITH T AS 
(
  SELECT ROW_NUMBER() OVER(ORDER BY T.[Entry No_]) [RowNumber]
       , T.[Entry No_]
	   , T.[G_L Account No_]
	   , ...
    FROM [Demo Database NAV (6-0)].dbo.[CRONUS AG$G_L Entry] T WITH (NOLOCK)
)
, D AS
(  
SELECT T.[Entry No_]       
      , MAX(COALESCE(CASE WHEN [Dimension Code]='BEREICH' THEN [Dimension Value Code] ELSE '' END,'')) [DimensionValue(BEREICH)]       
      , MAX(COALESCE(CASE WHEN [Dimension Code]='DEBITORENGRUPPE' THEN [Dimension Value Code] ELSE '' END,'')) [DimensionValue(DEBITORENGRUPPE)]       
      , MAX(COALESCE(CASE WHEN [Dimension Code]='EINKÄUFER' THEN [Dimension Value Code] ELSE '' END,'')) [DimensionValue(EINKÄUFER)]       
      , MAX(COALESCE(CASE WHEN [Dimension Code]='KOSTENSTELLE' THEN [Dimension Value Code] ELSE '' END,'')) [DimensionValue(KOSTENSTELLE)]       
      , MAX(COALESCE(CASE WHEN [Dimension Code]='KOSTENTRÄGER' THEN [Dimension Value Code] ELSE '' END,'')) [DimensionValue(KOSTENTRÄGER)]       
      , MAX(COALESCE(CASE WHEN [Dimension Code]='UNTERNEHMENSGRUPPE' THEN [Dimension Value Code] ELSE '' END,'')) [DimensionValue(UNTERNEHMENSGRUPPE)]       
      , MAX(COALESCE(CASE WHEN [Dimension Code]='VERKÄUFER' THEN [Dimension Value Code] ELSE '' END,'')) [DimensionValue(VERKÄUFER)]       
      , MAX(COALESCE(CASE WHEN [Dimension Code]='VERKAUFSKAMPAGNE' THEN [Dimension Value Code] ELSE '' END,'')) [DimensionValue(VERKAUFSKAMPAGNE)]    
   FROM [Demo Database NAV (6-0)].dbo.[CRONUS AG$Ledger Entry Dimension] TGROUP BY T.[Entry No_]
)
 SELECT T.*, [DimensionValue(BEREICH)]
       ,[DimensionValue(DEBITORENGRUPPE)]
       ,[DimensionValue(EINKÄUFER)]
       ,[DimensionValue(KOSTENSTELLE)]
       ,[DimensionValue(KOSTENTRÄGER)]
       ,[DimensionValue(UNTERNEHMENSGRUPPE)]
       ,[DimensionValue(VERKÄUFER)]
       ,[DimensionValue(VERKAUFSKAMPAGNE)]
       , 0 [Type_of_Change] 
    FROM T   
    JOIN D ON T.[Entry No_]=D.[Entry No_] 
   WHERE T.[RowNumb....