Skip to main content

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 - Common 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 separate 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.

The SQL Query if required can be downloaded from my Skydrive.

File Name -  Get_Used_Deleted_Dimension_Values.rar

Hope you find the information useful.

Regards,
Saurav Dhyani
www.sauravdhyani.com

Comments

Popular posts from this blog

BC 21 and Higher - PowerShell Cmdlet (Replacement of Business Central Administration).

Hi Readers, As discussed in last article about deprecating of Business Central Administration, there are few common actions that we use in administration till Business Central 20. For our on-prem customers, we will still require doing activities. As Microsoft suggest we need to start using PowerShell cmdlet.    Let's see how to do those via PowerShell, or Administration Shell. I will be keep adding commands as you comment to this article.

Send Mail with Attachment From Navision.

Hi all, We have seen how to save a report into PDF and how to send mail to a customer. Let's link these two post in one i.e. Mailing statement to a customer into PDF Format. This article is part of the Series. Please Refer  Table of Content here . If you have the old objects set let me brief you what I will be changing - 

MSDYN365BC - Data Upgrade To Microsoft Dynamics 365 Business Central on premises.

Hi Readers, We have already talked about the number of steps for upgrading to Business Central on Premises from different NAV versions. After that article, I received multiple requests for an article which list down steps for Data Migration. In this article, we will discuss steps of data migration to MSDYN365BC (on-Prem) from NAV 2017. For this article, I am considering a Cronus Demo Database without any customization. For an actual upgrade project, we will have to complete object merge using compare and Merge process. After the Merge Process, the next step is data migration. Let's discuss those steps. Direct Upgrade to Microsoft Dynamics 365 Business Central (on-Prem) is from following versions - 1. NAV 2015. 2. NAV 2016. 3. NAV 2017. 4. NAV 2018.