Activate Financial Dimensions in Dynamics 365FO

 

Activate Financial Dimensions 

How to resolve the error when activating financial dimension in D365 FO – “The activation of dimensions is only allowed when the system is in maintenance mode. Maintenance mode can be enabled in this environment by running maintenance jobs from LCS or using the Deployment Setup tool locally.

So to fix this issue or to activate the financial dimension, you need to enable the maintenance mode on your targeted environment. only then you can activate the Financial Dimension.

There are 3 ways to enable maintenance mode.

• LCS
• Command Line
• Query

LCS

Login to LCS, Navigate to your environment, Go to Maintain menu and click on enable maintenance mode.

Command Line

Open the command windows as Systems Administrator and execute the below command.

K:\AOSService\PackagesLocalDirectory\Bin\Microsoft.Dynamics.AX.Deployment.Setup.exe –metadatadir K:\AOSService\PackagesLocalDirectory –bindir C:\AOSService\PackagesLocalDirectory –sqlserver . –sqldatabase axdb –sqluser AOSUser –sqlpwd AOSWebSite@123 –setupmode maintenancemode –isinmaintenancemode true

SQL Script

Please perform the following steps 

• Disable the IIS services.
• Disable the Batch job services
• Open SQL and Execute the below command on AxDB

Update SQLSystemvariables set Value=0 where parm='CONFIGURATIONMODE'

Support Faryal's Cusine

Database sync failed during Dimension Activation

 

Database sync failed during Dimension Activation

If you are facing the Database sync failure and below error while activating the Financial dimension in maintenance mode.

Please perform the following steps to resolve this issue

You need to fix your data. Go to the Visual studio find inventdim table then check what fields are in "DimIdx" index.

Then Open the SQL and execute the below query to confirm either you have duplicate Invent Dim or nor

select InventDimId from INVENTDIM

group by InventDimId

having count(*)>1

When records will be identified you need to make a decision on what to do with them. fix data or delete.  

Error Message

Managed Data Sync Entity Worker encountered an exception, but is continuing because ContinueOnError is true. View Name and ID: Managed Data Sync Entity Worker encountered an exception, but is continuing because ContinueOnError is true. View Name and ID: 'DimensionCombinationEntity(5502)' Exception: System.InvalidOperationException: Database execution failed: Incorrect syntax near ')'.

 Incorrect syntax near ')'.

 CREATE VIEW [DBO].[DIMENSIONCOMBINATIONENTITY] AS SELECT T1.RECID AS RECORDID, T1.MAINACCOUNTVALUE AS MAINACCOUNT, T1.MODIFIEDDATETIME AS MODIFIEDDATETIME, T1.MODIFIEDBY AS MODIFIEDBY, T1.CREATEDDATETIME AS CREATEDDATETIME, T1.CREATEDBY AS CREATEDBY, T1.RECVERSION AS RECVERSION, T1.PARTITION AS PARTITION, T1.RECID AS RECID, T2.NAME AS ACCOUNTSTRUCTURE, T2.RECVERSION AS RECVERSION#2, T2.PARTITION AS PARTITION#2, T2.RECID AS RECID#2, (CAST ((

                 SELECT (

                     REPLACE(

                         REPLACE(

                             REPLACE(

                                 CASE WHEN

                                     T1.LEDGERDIMENSIONTYPE != 1

                                 THEN

                                     (SELECT CASE WHEN 

                                         NonNullDisplayValue IS NOT NULL 

                                         THEN NonNullDisplayValue 

                                         ELSE 'No active format for data entities has been set up.

 Set up an active format for each dimension format type.' 

                                     END

                                     FROM

                                     (SELECT (

                                         -- Remove initial delimiter

                                         STUFF(

                                             (SELECT

                                                 -- Prepend with delimiter and escape internal delimiters

                                                 DIMENSIONSEGMENTDELIMITER + COALESCE(

                                                         REPLACE(REPLACE(DIMLIST.COLUMNVALUE, '\', '\\'),

                                                         DIMENSIONSEGMENTDELIMITER,

                                                         DIMENSIONSEGMENTDELIMITERESCAPED),

                                                     '')

                                                 FROM 

                                                     -- Get the dimension hierarchy

                                                     (SELECT TOP(1) DH.RECID

                                                         FROM DIMENSIONHIERARCHY DH

                                                         JOIN DIMENSIONHIERARCHYINTEGRATION DHI 

                                                                     ON DHI.DIMENSIONHIERARCHY = DH.RECID

                                                                     AND DHI.ISDEFAULT = 1

                                                                     AND DHI.PARTITION = T1.PARTITION

                                                         WHERE

                                                             DH.PARTITION = T1.PARTITION

                                                             AND DH.STRUCTURETYPE =

                                                                         CASE T1.LEDGERDIMENSIONTYPE

                                                                             WHEN 0 THEN 18

                                                                             WHEN 2 THEN 19

                                                                             WHEN 4 THEN 20

                                                                         END

                                                     ) AS DH

                                                 JOIN DIMENSIONHIERARCHYLEVEL DHL 

                                                     ON DHL.DIMENSIONHIERARCHY = DH.RECID AND DHL.PARTITION = T1.PARTITION

                                                 JOIN DIMENSIONATTRIBUTE DA 

                                                     ON DA.RECID = DHL.DIMENSIONATTRIBUTE AND DA.PARTITION = T1.PARTITION

                                                 LEFT JOIN

                                                         (SELECT DIMENSIONATTRIBUTE, COLUMNVALUE

                                                             FROM

                                                                 (SELECT * FROM DIMENSIONATTRIBUTEVALUECOMBINATION DAVC

                                                                     WHERE DAVC.PARTITION = T1.PARTITION AND DAVC.RECID = T1.RECID) P

                                                             UNPIVOT

                                                                 (COLUMNVALUE for DIMENSIONATTRIBUTE IN (MAINACCOUNTVALUE)) AS DIMENSIONVALUECOLUMNNAME

                                                         ) AS DIMLIST

                                                     ON

                                                         DIMLIST.DIMENSIONATTRIBUTE = REPLACE(DA.DIMENSIONVALUECOLUMNNAME COLLATE Database_Default, '.', '$')

                                                 ORDER BY DHL.DIMENSIONHIERARCHY, DHL.LEVEL_

                                                 FOR XML PATH('')

                                             ),

                                             1, LEN(DIMENSIONSEGMENTDELIMITER), '')

                                         ) AS NonNullDisplayValue)

                                     AS DisplayValueFormat)

                                 ELSE 

                                     -- Default or dynamic accounts

                                     (SELECT REPLACE(REPLACE(T1.DISPLAYVALUE, '\', '\\'),

                                                     DIMENSIONSEGMENTDELIMITER,

                                                     DIMENSIONSEGMENTDELIMITERESCAPED))

                                 END

                                 , '&', '&')

                             , '&lt;', '<')

                         , '&gt;', '>')

                     )

                 FROM

                     (SELECT TOP(1)

                             --Determine the segment delimiter

                             CASE DIMENSIONSEGMENTDELIMITER

                                 WHEN 0 THEN '-'

 WHEN 1 THEN '.'

 WHEN 2 THEN '_'

 WHEN 3 THEN '|'

 WHEN 7 THEN '||'

 WHEN 8 THEN '~'

 WHEN 4 THEN '--'

 WHEN 5 THEN '..'

 WHEN 6 THEN '__'

 WHEN 9 THEN '~~'

                             END AS DIMENSIONSEGMENTDELIMITER,

                             --Determine the account delimiter escaped

                                 WHEN 0 THEN '\-'

 WHEN 1 THEN '\.'

 WHEN 2 THEN '\_'

 WHEN 3 THEN '\|'

 WHEN 7 THEN '\|\|'

 WHEN 8 THEN '\~'

 WHEN 4 THEN '\-\-'

 WHEN 5 THEN '\.\.'

 WHEN 6 THEN '\_\_'

 WHEN 9 THEN '\~\~'

                             END AS DIMENSIONSEGMENTDELIMITERESCAPED

                         FROM DIMENSIONPARAMETERS

                         WHERE DIMENSIONPARAMETERS.PARTITION = T1.PARTITION

                     ) AS DIMENSIONPARAMETERS

                  ) AS NVARCHAR(500))) AS DISPLAYVALUE, (CAST ((T1.DISPLAYVALUE) AS NVARCHAR(30))) AS ACCOUNTVALUE, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS BANKACCOUNTID, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS CLIENTORGANIZATIONUNIT, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS DELIVERYCHANNEL, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS FINALCLIENT, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS INTERCOMPANY, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS LOCALSKILLSET, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS LOCALSUBINDUSTRY, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS LOCALSUBSERVICELINE, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS PL_PODATEK, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS PROGRAMWBS, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS VENDORLEGALENTITY, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS LOCALCOSTCENTER FROM DIMENSIONATTRIBUTEVALUECOMBINATION T1 LEFT OUTER JOIN DIMENSIONHIERARCHY T2 ON(( T1.ACCOUNTSTRUCTURE  =  T2.RECID)  AND ( T1.PARTITION  =  T2.PARTITION)) ---> System.Data.SqlClient.SqlException:                 ) AS NVARCHAR(500))) AS DISPLAYVALUE, (CAST ((T1.DISPLAYVALUE) AS NVARCHAR(30))) AS ACCOUNTVALUE, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS BANKACCOUNTID, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS CLIENTORGANIZATIONUNIT, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS DELIVERYCHANNEL, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS FINALCLIENT, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS INTERCOMPANY, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS LOCALSKILLSET, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS LOCALSUBINDUSTRY, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS LOCALSUBSERVICELINE, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS PL_PODATEK, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS PROGRAMWBS, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS VENDORLEGALENTITY, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS LOCALCOSTCENTER FROM DIMENSIONATTRIBUTEVALUECOMBINATION T1 LEFT OUTER JOIN DIMENSIONHIERARCHY T2 ON(( T1.ACCOUNTSTRUCTURE  =  T2.RECID)  AND ( T1.PARTITION  =  T2.PARTITION)) ---> System.Data.SqlClient.SqlException: Incorrect syntax near ')'.

    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

    at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)

    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)

    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

    at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.SqlCommandExtensions.<>c__DisplayClass1.<ExecuteNonQueryWithRetry>b__0()

    at Microsoft.Practices.EnterpriseLibrary.TransientFaultHandling.RetryPolicy.ExecuteAction[TResult](Func`1 func)

    at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.<ExecuteNonQuery>b__30_0(SqlCommand c)

    at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.ExecuteSql[T](SqlCommand cmd, Func`2 sqlFunc)

 --- End of stack trace from previous location where exception was thrown ---

    at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

    at Microsoft.Dynamics.AX.Data.Sql.SqlDataAccessManager.HandleException(ExceptionDispatchInfo edi, SqlExecutionInfo execInfo)

    at Microsoft.Dynamics.AX.Data.Management.DBExecute.ExecuteDDLWithNewTransaction(IEnumerable`1 commandTextInBatch)

    --- End of inner exception stack trace ---

    at Microsoft.Dynamics.AX.Data.Management.DBExecute.ThrowOnSqlExecuteException(SqlException e, String commandText)

    at Microsoft.Dynamics.AX.Data.Management.DataEntity.ManagedSyncDataEntityWorker.SyncAllAction[T](ViewSyncGraphNode viewToSync, Dictionary`2 oldViewDefinitions, StringCollection dataEntityViewList, StringCollection compositeEntityViewList, IProducerConsumerCollection`1 modifiedViewDependencies)

    at Microsoft.Dynamics.AX.Data.Management.DataEntity.ManagedSyncDataEntityWorker.DoSyncAll[T](IEnumerable`1 sortedViewList, Dictionary`2 oldViewDefinitions, IProducerConsumerCollection`1 modifiedViewDependencies)

  AOS database sync failed. Microsoft.Dynamics.AX.Framework.Database.TableSyncException: AOS database sync failed. Microsoft.Dynamics.AX.Framework.Database.TableSyncException: Full sync did not complete successfully. Error: AggregateException:One or more errors occurred.

    at Microsoft.Dynamics.AX.Data.Management.ManagedSyncWorkerManager.SyncAll()

    at Microsoft.Dynamics.AX.Data.Management.ManagedSync.<SyncAll>b__21_0()

    at Microsoft.Dynamics.AX.Data.Management.ManagedSync.EnsureMetadataProviderCacheCleared(Action action)

    at Microsoft.Dynamics.AX.Data.Management.ManagedSync.SyncAll()

    at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.RunFullManagedSync(SyncOptions options, String sqlConnectionString, IMetadataProvider metadataProvider)

    at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.<>c__DisplayClass20_0.<RunFullTableSync>b__0()

    at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.ExecuteWithinAOS(SyncOptions syncOptions, String sqlConnectionString, IMetadataProvider metadataProvider, Func`1 func, Action`1 errorHandler)

 Inner exceptions:

  Database execution failed: Database execution failed: Incorrect syntax near ')'.

                 ) AS NVARCHAR(500))) AS DISPLAYVALUE, (CAST ((T1.DISPLAYVALUE) AS NVARCHAR(30))) AS ACCOUNTVALUE, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS BANKACCOUNTID, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS CLIENTORGANIZATIONUNIT, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS DELIVERYCHANNEL, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS FINALCLIENT, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS INTERCOMPANY, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS LOCALSKILLSET, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS LOCALSUBINDUSTRY, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS LOCALSUBSERVICELINE, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS PL_PODATEK, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS PROGRAMWBS, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS VENDORLEGALENTITY, (CAST ((SELECT TOP 1 T1. ) AS NVARCHAR(30))) AS LOCALCOSTCENTER FROM DIMENSIONATTRIBUTEVALUECOMBINATION T1 LEFT OUTER JOIN DIMENSIONHIERARCHY T2 ON(( T1.ACCOUNTSTRUCTURE  =  T2.RECID)  AND ( T1.PARTITION  =  T2.PARTITION))

    at Microsoft.Dynamics.AX.Data.Management.DataEntity.ManagedSyncDataEntityWorker.DoSyncAll[T](IEnumerable`1 sortedViewList, Dictionary`2 oldViewDefinitions, IProducerConsumerCollection`1 modifiedViewDependencies).

    at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.<>c.<RunFullTableSync>b__20_1(Tuple`2 result)

    at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.RunFullTableSync(SyncOptions syncOptions, String sqlConnectionString, IMetadataProvider metadataProvider)

    at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.FullTableViewSync()

    at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.FullSync()

    at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.RunSync()

    at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.Run(String metadataDirectory, String sqlConnectionString, SyncOptions options)

  Process: syncengine.exe exited with code -1.

Support Faryal's Cusine

Dimension Entry Control D365FO

Dimension entry control subpattern

• 
  

SourceLink
This article provides information about the Dimension Entry Control sub pattern. This sub pattern is used when you have a group or tab page that uses the Dimension Entry control (DEC).

Usage

The Dimension Entry Control pattern is used when you have a group or tab page that uses the Dimension Entry control (DEC).

Wireframe

Lets Begin Example

First Create new project for demo purpose..

                                              

Step-1 - Create Custom Table

Step-2- Add Dimension Field and Extends with LedgerDefaultDimensionValueSet

Step-3- Make Relation with DimensionAttributeValueSet

Step-4-  Create New Form & Display Menu items and set the Form Name in the object property of the menu Item.

Step-5- Create New menu extension if you required and drag your Display menu item where you want to display.

Step-6-  Now Open the Form you created previously and drag the newly created table in the data source of your form.

Step-7- Now Add the Tab control and one Tab page with tab control and apply the Dimension Entry control pattern and set the properties like below screenshot.

Now Add two groups as per pattern requirement and set the properties 

First Group Properties

• Frame Type =None
• Hide if Empty =No

Second Group Properties

• Arrange Method=Vertical
• Auto Declaration=Yes
• Hide if Empty =No

Now perform the build & sync 

Now login to D365FO and Open the newly created Form

Now Create new Record and enjoy Dimension Entry Control :)

Please feel free to contact me, if you are facing any issue to implement the above blog.

Support Faryal's Cusine