NAV 2013 & NAV 2013 R2 - Comman Issue Related to Dimension in Step 2 Continued.....

Hi all,

As discussed and Found the Cause of Issue related to Dimension During Upgrade, Lets' see how to Resolve Same Before Upgrading or During Step 2.

If you haven't Read the Earlier Post Use The Link.

NAV 2013 & NAV 2013 R2 - Comman Issue Related to Dimension in Step 2 

There are two points which make our life Easy -

1. In Earlier Version i.e 2009 / 2009 SP1 / 2009 R2, the Dimension Values were used to Store in a seprate Table.

2. The Upgrade Step Batch Job Commits the Transaction Before the Error Occurs, i.e If Table X have Issue in Data Everything done before Table X is Committed.

Why these two things help us?

POINT 1. If we are planning to Upgrade and want to be on Safe Side i can just do a check on all Tables related to Dimension in my Current Version i.e 2009.

-- Just Filter Tables as @*Dimension* and you get all the tables related to Dimension which need to be checked.

-- If you check the Above List of Tables we only need to do the Check for 12-14 Tables which are listed later in the Post.

-- Run the Batch and if required create the Necessary Entries and Start the Upgrade Process.

POINT 2. If you get Stuck during Upgrade Step 2 You can do same steps in NAV 2013 R2.

-- Before the Error Comes the Dimension & Dimension Values have been transferred to actual tables.

-- Run the Batch and create the Necessary Entries.

How to Check Wrong / Missing Dimension Value During Step 2 of Data Migration ?

1. If you a error related to a dimension, Just start the debugger and Re-run the Upgrade Step 2.

2. The Debugger will stop you somewhere in Table 480 where its checking Dimension Set ID.

3. Navigate some steps Back in Debugger as shown below, you can find the Table where the Problem IS.

As shown above i can see that there is an issue in Ledger Entry Dimension.

How to Resolve the Issue ?

Now we know the reason of error, and the table on which this error is coming, Lets Resolve the issue.

-- We just need to identify the Missing Dimension and we need to create it into Dimension Value Table with BLOCKED SET TO TRUE.

-- It may be the case that the error is coming due to Multiple Missing Dimension Value.

BELOW is a SQL Query which will help you to identify the Missing Dimension Value. As i am not good with SQL Queries you need to run it one by one for all the tables and take corrective actions.

--------  QUERY ------------------

-- Replace all <<>> parameters with actual values.
-- Parmeters Required.
-- <<Database Name>> - NAME OF DATABASE.
-- <<SQL Company Name>>$<<Issue Table SQL Name>>  - Complete Table Name, IN SQL Complete Table Name Contain Company Name Too.
--
USE
[<<Database Name>>]

Select Distinct UsedDIM.[Dimension Code], UsedDIM.[Dimension Value Code] from [<<SQL Company Name>>$<<Issue Table SQL Name>>] as UsedDIM
Left Join [<<SQL Company Name>>$Dimension Value] as DimVal ON
UsedDIM.[Dimension Code] = DimVal.[Dimension Code] AND UsedDIM.[Dimension Value Code] = DimVal.Code
Where DimVal.[Dimension Code] IS NULL

------------------------------------

The Above query will give you the Dimension Code and Dimension Value which is present in any transaction but the Value does not exist in Dimension Value Table.

Just create the Dimension Values in the Dimension Value with BLOCKED SET TO TRUE and we are good to go.

** The above step need to run on every company.

** Just Replace the Parameters and Run the Query, The Query Result will show the Missing Dimension Value.

** The above Step will be executed for Following Tables.
Default Dimension
Ledger Entry Dimension
Journal Line Dimension
Document Dimension
Production Document Dimension
Posted Document Dimension
G_L Budget Dimension
Service Contract Dimension
Job Task Dimension
Document Dimension Archive
FA Allocation Dimension
Item Budget Dimension

Let's try to execute the Query on Ledger Entry Table as we deleted one record in previous Post.

So now we know that dimension Value 30 for dimension Area does not exist in Dimension Value, so let's create it.

So Let's again run the Query and see Output.

So after repeating the steps for all tables in all the companies we are good to go ahead.

Hope you find the information useful.

Regards,
Sathish
http://sathish-nav.blogspot.com