web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Project Dimension Name...
Microsoft Dynamics AX (Archived)

Project Dimension Name Incorrectly Updated

(0) ShareShare
ReportReport
Posted on by Community Member

We have an issue reported where a user had accidentally changed the name of a dimension by:

- Clicking: General Ledger > Setup > Financial Dimensions > Financial Dimensions > (selected their legal entity's project dimension) > Financial dimension values

- Then amending the Dimension value of the first entry from 000000 to 000000X.

This issue was corrected by simply following the above steps and reverting back to the original value; but this did not undo all of the changes.  We now have issues where

- After entering a journal line with dimension 000000, on saving the line the dimension is displayed as 000000X.

- Data arriving from other systems via the AIF web services fails validation when the 000000 dimension is specified.

Looking at the database I could see that the DimensionFinancialTag's Value had been reverted to the original value, but the related DimensionAttributeValueSetItem's DisplayValue had not.  I tried amending the values again; first making the value incorrect, then again correcting it.  This seemed to resolve the issue in those tables; though did not resolve the above problems.

I also spotted that the code in the GeneralJournalAccountEntry's LedgerAccount was still incorrect (and in fact had become further corrupted; seemingly padding characters from my invalid values onto the end of this dimension's value so that it contained a dimension value which had never been used; made up of characters from this value's previous incarnations).

The below SQL has been used to help investigate the issues in our database.

set transaction isolation level read uncommitted
go
 
select dft.Value, davsi.DISPLAYVALUE
from DIMENSIONFINANCIALTAG dft
inner join DIMENSIONATTRIBUTEDIRCATEGORY dadc 
    on dadc.PARTITION = dft.PARTITION 
    and dadc.RECID = dft.FINANCIALTAGCATEGORY
inner join DimensionAttributeValue dav 
    on dav.PARTITION = dadc.PARTITION 
    and dav.DIMENSIONATTRIBUTE = dadc.DIMENSIONATTRIBUTE 
    and dav.ENTITYINSTANCE = dft.RECID
inner join DimensionAttributeValueSetItem davsi 
    on dav.PARTITION = dadc.PARTITION 
    and davsi.DIMENSIONATTRIBUTEVALUE = dav.RECID
where dft.Value != davsi.DISPLAYVALUE
order by dft.Value, davsi.DISPLAYVALUE 

select top 100 LedgerAccount 
from GeneralJournalAccountEntry 
where LedgerAccount like '%-%-%-%-%000000%-%-%-%' --hyphens are used to ensure we target the correct dimension

We've also tried flushing the caches and also restarting the AOS servers; but with no luck.

So far we haven't performed an SQL update to correct this data through the back end; though if we can't find a solution soon we'll likely opt for that workaround.

 

NB: this may be the same as the bug registered in LCS's Issue Search; sadly there's not enough information there yet to tell, and that issue relates to R3 whilst we're on R2: https://fix.lcs.dynamics.com/Issue/NotFixed/?bugId=3725505

 

Has anyone seen this before / have any advice on how best to resolve?

Thank-you in advance,

JB

NB: The issue occurred in production which is running AX2012 R2 CU7.

However I was able to reproduce in our test environment which is on CU9 Kernel + CU7 application.

*This post is locked for comments

I have the same question (0)
  • Verified answer
    Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at

    I recently did a blog post on detecting exactly these kinds of problems with SQL.

    community.dynamics.com/.../microsoft-dynamics-ax-2012-validate-dimension-attributes-with-sql

    Be sure to have good backups and don't do anything you can't easily undo.

  • Community Member Profile Picture
    Community Member on at

    That's fantastic; thank-you (again) for your help.

    In your post you've not mentioned how to resolve the issues when they're detected... My guess is that we should stop our AOSes, run a SQL update on the invalid entries, then start our AOSes again (i.e.. stopping the AOSes during the update to avoid issues with cached values not being updated where we bypass the application layer).

    Good point on backups; will do.

  • Verified answer
    Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at

    I can only say that I have had to fix such issues using SQL in the past because the UX provides no alternative.  You must do what you think is best for the integrity of your data.

    You'll want to check DimensionAttributeValueCombination records as well, since it has a DisplayValue field also.

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    Understood; thank-you.

    I suspect we'll do SQL this time too; but I'll speak with our developers on Monday to see if we can knock up a batch task to detect and (optionally) correct issues in a similar way to how the `Database Consistency Check` does for parent-child issues.  That way we can have the job run out of hours and alert us of any issues / can decide to run it in update mode if we determine it sensible to auto-resolve those.  If we develop something like that I'll try to get agreement to share it with the community so we give something back.

    Thanks again for all your help today,

    JB

    (Database Consistency Check: www.trideapartners.com/.../consistency-check-tool-in-dynamics-ax-2012)​

  • Community Member Profile Picture
    Community Member on at

    FYI: In trying to find the incorrect combinations in the database (i.e. where the values held in `GeneralJournalAccountEntry.LedgerAccount` and `DimensionAttributeValueCombination.DisplayValue` are incorrect, I've been writing some SQL to look at the normalised data and have attempted to generated the correct values for these entries so we can quickly find issues here and generate corrective statements.  So far I've got part way, but my current solution only finds the account number & the first 3 dimension values, rather than the full list.  Any thoughts on what I may be missing?

    SQL below:

    select gjae.ledgeraccount
, davc.DisplayValue -- should match gjae.ledgeraccount

--, cast(ma.MainAccountId as nvarchar(max)) + '-' --this data comes from davsi anyway 
, coalesce(max(case when dalv.ORDINAL = 1 then /*davsi*/ dalv.DISPLAYVALUE else null end),'') + '-'
+ coalesce(max(case when dalv.ORDINAL = 2 then /*davsi*/ dalv.DISPLAYVALUE else null end),'') + '-'
+ coalesce(max(case when dalv.ORDINAL = 3 then /*davsi*/ dalv.DISPLAYVALUE else null end),'') + '-'
+ coalesce(max(case when dalv.ORDINAL = 4 then /*davsi*/ dalv.DISPLAYVALUE else null end),'') + '-'
+ coalesce(max(case when dalv.ORDINAL = 5 then /*davsi*/ dalv.DISPLAYVALUE else null end),'') + '-'
+ coalesce(max(case when dalv.ORDINAL = 6 then /*davsi*/ dalv.DISPLAYVALUE else null end),'') + '-'
+ coalesce(max(case when dalv.ORDINAL = 7 then /*davsi*/ dalv.DISPLAYVALUE else null end),'') FromNormalisedDataDimensionAccount
/*
, dalv.ORDINAL
, dalv.DISPLAYVALUE
, davsi.DISPLAYVALUE
*/
from GeneralJournalAccountEntry gjae
inner join DimensionAttributeValueCombination davc
	on davc.[Partition] = gjae.[Partition] 
	and davc.RecId = gjae.LedgerDimension 
--inner join MainAccount ma
--	on ma.[Partition] = davc.[Partition] 
--	and ma.RecId = davc.MainAccount
inner join DimensionHierarchy dh
       on dh.[Partition] = davc.[Partition] 
       and dh.RECID = davc.AccountStructure
inner join DimensionAttributeValueGroupCombination davgc
       on davgc.[Partition] = davc.[Partition]
       and davgc.DimensionAttributeValueCombination = davc.RecId
inner join DimensionAttributeValueGroup davg
       on davg.[Partition] = davgc.[Partition]
       and davg.recid = davgc.DimensionAttributeValueGroup
       and davg.DimensionHierarchy = dh.RECID 
inner join DimensionAttributeLevelValue dalv
	on dalv.[Partition] = davgc.[Partition]
	and dalv.DimensionAttributeValueGroup = davgc.DimensionAttributeValueGroup
inner join DimensionAttributeValue dav
	on dav.[Partition] = dalv.[Partition]
	and dav.recid = dalv.DimensionAttributeValue
	/*
inner join DimensionAttributeValueSetItem davsi
	on davsi.[Partition] = dav.[Partition]
	and davsi.DimensionAttributeValue = dav.RecId
inner join DimensionAttributeValueSet davs
	on davs.[PARTITION] = dav.[Partition]
	and davs.RecId = davsi.DimensionAttributeValueSet 
	*/
where gjae.recid in (5639804335,5638020658,5639804230,5638018556) --a few example records of good data to check the query's validity
group by gjae.recid, gjae.LedgerAccount, davc.DisplayValue
order by gjae.recid


  • Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at

    Yea, DAVC is quite a bit tougher.  I haven't yet attacked that broadly with SQL, but it looks like you've got a good start.  ValueSet and ValueSetItem don't apply to DAVC records, as it seems you've figured out.

    I'll try to play with this some tonight.  I suspect we'll need to understand the Ordinal to make it work.

  • Brandon Wiese Profile Picture
    Brandon Wiese 17,788 on at

    Maybe a cursor based solution, where you loop through Ordinals from 1 to 10 (assumed reasonable maximum), doing an update and appending more dimensions with each pass, and then comparing the result at the end?

  • Community Member Profile Picture
    Community Member on at

    Thanks Brandon.  It seems the issue is the Ordinal only goes up to 4 (i.e. proven by select max(odinal) from DimensionAttributeLevelValue); so DimensionAttributeLevelValue doesn't contain all of the data (or I've misunderstood how to interpret it).

  • Community Member Profile Picture
    Community Member on at

    ps. I found a post here with similar SQL: www.atomicax.com/.../implementing-ledger-dimensions-flattened-view-your-financial-dimensions.  However this only pulls back MainAccount, InterCompany, Format and CostCenter dimensions.  With our setup these are the dimensions shared across all DataAreaIds; i.e. global dimensions.  The missing dimensions are the local ones (i.e. which are restricted to the set of DataAreaIds relevant to a particular country).

  • Verified answer
    Community Member Profile Picture
    Community Member on at

    NB: Resolved!

    I realised that the dimension hierarchy was restricting the values I could see in the the group.  Changing from using this and the ordinal to simply using the group and dimension names to dictate the order gave me the below SQL.  NB: Dimension names and number of dimensions returned would need to be amended per business needs:

    select gjae.ledgeraccount
, davc.DisplayValue -- should match gjae.ledgeraccount
--, cast(ma.MainAccountId as nvarchar(max)) + '-' --this data comes from davsi anyway 
, coalesce(max(case when da.Name = 'MainAccount' then dalv.DISPLAYVALUE else null end),'') + '-'
+ coalesce(max(case when da.Name = 'CostCenter' then dalv.DISPLAYVALUE else null end),'') + '-'
+ coalesce(max(case when da.Name = 'InterCompany' then dalv.DISPLAYVALUE else null end),'') + '-'
+ coalesce(max(case when da.Name = 'Format' then dalv.DISPLAYVALUE else null end),'') + '-'
+ coalesce(max(case when da.Name = 'SE_Project' then dalv.DISPLAYVALUE else null end),'') + '-' --todo: make generic for all companies
+ coalesce(max(case when da.Name = 'SE_Region' then dalv.DISPLAYVALUE else null end),'') + '-'
+ coalesce(max(case when da.Name = 'SE_Staff' then dalv.DISPLAYVALUE else null end),'') + '-'
+ coalesce(max(case when da.Name = 'SE_Local' then dalv.DISPLAYVALUE else null end),'') FromNormalisedDataDimensionAccount
from GeneralJournalAccountEntry gjae
inner join DimensionAttributeValueCombination davc
	on davc.[Partition] = gjae.[Partition] 
	and davc.RecId = gjae.LedgerDimension 
--inner join MainAccount ma
--	on ma.[Partition] = davc.[Partition] 
--	and ma.RecId = davc.MainAccount
--inner join DimensionHierarchy dh
--      on dh.[Partition] = davc.[Partition] 
--     and dh.RECID = davc.AccountStructure
inner join DimensionAttributeValueGroupCombination davgc
       on davgc.[Partition] = davc.[Partition]
       and davgc.DimensionAttributeValueCombination = davc.RecId
inner join DimensionAttributeValueGroup davg
       on davg.[Partition] = davgc.[Partition]
       and davg.recid = davgc.DimensionAttributeValueGroup
--       and davg.DimensionHierarchy = dh.RECID 
inner join DimensionAttributeLevelValue dalv
	on dalv.[Partition] = davgc.[Partition]
	and dalv.DimensionAttributeValueGroup = davgc.DimensionAttributeValueGroup
inner join DimensionAttributeValue dav
	on dav.[Partition] = dalv.[Partition]
	and dav.recid = dalv.DimensionAttributeValue
inner join DimensionAttribute da
	on da.[Partition] = dav.[Partition]
	and da.RecId = dav.DimensionAttribute
where gjae.recid in (5639804335,5638020658,5639804230,5638018556) --a few example records of good data to check the query's validity
group by gjae.recid, gjae.LedgerAccount, davc.DisplayValue
order by gjae.recid


    Thanks once again Brandon for your help; it's very much appreciated.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans