In Dynamics 365 for Finance and Operations most of the data is represented in Entities which in turn are surfaced as OData feeds for used for integrations. If you need a raw look at the entity you can access the raw OData feed, unfortunately it is not easily readable for humans:
Excel to the rescue. In Excel go to Data – Get Data – From Other Sources – From OData Feed
A lot more readable… right 🙂
That’s it for today
On of my primary tasks at work every day is maintaining multiple Dynamics 365 for Operations environments for my customer. This means that I often juggle a couple of environments at the same time and often I have to double check the URL to make sure I am in the right place.
There is of course the possibility to set different banners on each environment to be able to differentiate between them.
Today I found a wonderful tool that can help me, a chrome addin that puts a border at the edge of the window, in different color, depending on the URL 🙂
You configure the addin with different keywords which triggers the color switch The keywords can be URLs:
Or you can for instance use name of the legal entity:
Note that the keyword is Case sensitive. The result looks like this:
Since the addin doesn’t car what you are running in the browser you can use it to differentiate between other similar sites as well… as long as the URL differs in a consistent way.
If you are running the new Microsoft Edge (nased on Chromium… It works there as well 🙂
Link:
https://chrome.google.com/webstore/detail/urlcolors/jjccpcminoppplpmcfghflolejbdkekm
The new Task Recorder in Dynamics 365 for Operations is a cool tool. It allows you to create a complete recording of a business process which then can be used for documentation, training and also for automated testing (I will revisit that in a later post). In this one we will take a closer look at documentation.
In AX 2012 the task recorder created nice screenshots in the word export but feature is no longer present in D365 for Operations. At least not in PU20 that my customer is running. There is however a solution.
If you use Google Chrome (which I do) there is actually a plugin that you can install and silently monitors you progress when you are creating you task recordings and takes a snapshot when you click on something. Once you save the recording as a Word Document these snapshots are inserted into the document.
The other day one of my colleagues needed a database copied from PROD to a DEV OneBox. Unfortunately there is no direct route from PROD to Onebox, you need to make a refresh to a Tier-2 environment and then move the database from the Tier-2 environment to LCS and then restore it to the OneBox environment. The issue I had was that I need to keep the data in the Tier-2 environment.
I used LCS to make an export to LCS, performed the entire process from above and it was finally time to restore the export… did not work… The issue I was getting was an error trying to create a trigger referring to a missing column. When I looked closer at this I found that the column was missing had been removed a long time ago and (in AX/D365 removed columns gets the prefix DEL_ and this column had that). Apparently the column had been removed before the export was done and for some reason the export was not consistent.
There are a couple of different solution to this problem…
In my case I opened up the bacpac file (it is apparently a zip file). Inside is all the data and the schema for the database. I opened the model.xml file and search for the T-SQL statement that created the trigger, I changed the statement to point to the DEL_ table, saved the file and put it back into the zip and renamed it back to bacpac.
When I tried to import it I got an error that said “mismatching checksum”. In the file called origin.xml there is a line containing the checksum for model.xml:
<Checksum Uri=”/model.xml”>8E0D68CEA76ACDB4DD4EE9853065E0E10D9C426F0CC880D18ABC19AAC64624C9</Checksum>
This prevents someone tampering with the model file. To generate a ned checksum for the model file, run the following PowerShell script and point to the model.xml file. Past the HASH in origin.xml
$modelXmlPath = Read-Host "model.xml file path"
$hasher = [System.Security.Cryptography.HashAlgorithm]::Create("System.Security.Cryptography.SHA256CryptoServiceProvider")
$fileStream = new-object System.IO.FileStream ` -ArgumentList @($modelXmlPath, [System.IO.FileMode]::Open)
$hash = $hasher.ComputeHash($fileStream)
$hashString = ""
Foreach ($b in $hash) { $hashString += $b.ToString("X2") }
$fileStream.Close()
$hashString
Put both Origin.xml and model.xml in the zipfil and change the extension to bacpac. Import it again.
The final takeaway: DO NOT RELY ON BACPAC EXPORTS for backup!!! Or at least test them in some way…
Note added after post:
A safer way to do this is probably (as long as we still have RDP access to t-2 environment):
Creating a new database as a copy of the original (since you are going to mess with it) by running this SQL query:
CREATE DATABASE MyNewCopy AS COPY OF axdb_mySourceDatabaseToCopy
Since this operation runs asynchronously in the background you will have to keep an eye on it before continuing:
SELECT * FROM sys.dm_database_copies
In Dynamics 365 for operations there is a very useful tool called Data Management Framework which is used for import and export of data…
A couple of month ago Microsoft introduced a new view called Enhanced View that I personally think is nice… but for some strange reason (maybe inertia) the Standard View (the old one) is still standard and every time you choose it the UI tells you that “Standard view has been deprecated, and will be removed. You can change your default view to Enhanced on the Data management framework parameters page.” which can be a bit annoying. Microsoft has promised that soon the Enhanced View will be standard… If it was only soon now 🙂
In the mean time you can set the default view.
Happy Days!!
One of my colleagues asked me why she was not able to create an alert for a batch job… Since I wrote a documentation on how to enable alerts some time ago I thought “this will be easy”… Ooooh I was wrong :-). When I looked in the environment the option was not even there:
After some research, looking into sysflighting and other settings I finally found a hint… Apparently there is a Configuration key called Event that controls wether alerts are visible or not. To fix this do the following:
Links:
https://community.dynamics.com/365/financeandoperations/f/765/t/283320
When upgrading, sorry updating, a Dynamics 365 for Operations environment the runbook will fail somewhere around step 57 (I am not sure if it will be 57 every time). I have had this in every environment I have run… it might have to do with the fact that we are using the Project module
The error message will look like this:
The step 23 failed with the following error:
The step started
04/26/2019 13:16:35: Managed Sync Table Worker encountered an exception, but is continuing because ContinueOnError is true. Table Sync Failed for Table: ProjHierarchySorting. Exception: System.InvalidOperationException: Database execution failed: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.PROJHIERARCHYSORTING’ and the index name ‘I_19940NODEREFERENCE’. The duplicate key value is (5637144576, usmf, 0, , 0). The statement has been terminated. CREATE UNIQUE INDEX I_19940NODEREFERENCE ON DBO.PROJHIERARCHYSORTING(PARTITION,DATAAREAID,TYPE,REFID,REFNODEID); —> System.Data.SqlClient.SqlException: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.PROJHIERARCHYSORTING’ and the index name ‘I_19940NODEREFERENCE’. The duplicate key value is (5637144576, usmf, 0, , 0). The statement has been terminated. 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__27_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.Sql.SqlDataAccessManager.ExecuteSql[T](SqlCommand cmd, Func`2 sqlFunc) at Microsoft.Dynamics.AX.Data.Management.DBExecute.ExecuteDDLWithNewTransaction(String commandText) — End of inner exception stack trace — at Microsoft.Dynamics.AX.Data.Management.DBExecute.ThrowOnSqlExecuteException(SqlException e, String commandText) at Microsoft.Dynamics.AX.Data.Management.DBExecute.ExecuteDDLWithNewTransaction(String commandText) at Microsoft.Dynamics.AX.Data.Management.DBExecute.ExecuteSqlBatch(TableSyncSqlBatch sqlBatch) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.ExecuteTableModificationDdl(TableSyncSqlBatch sqlBatch, String tableName, Boolean disableChangeTracking) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.ExecuteModifyTableViaAlterTable(AxTable newTableSchema, IndexListCache ignoreIndexListCache, IncrementalSyncTableSchemaBuilder tableSchemaBuilder) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.ExecuteModifyTableViaAlterViewAndHandlePossibleExceptionsFromSchemaBoundViews(AxTable newTableSchema, IndexListCache ignoreIndexListCache, IncrementalSyncTableSchemaBuilder tableSchemaBuilder) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.ExecuteModifyTable(AxTable newTableSchema, AxTable oldTableSchema, Int32 tableId) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.ModifyTable(AxTable newTableSchema, Dictionary`2 oldTableSchemaDictionary) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.TableSyncAction(AxTable axTable, Dictionary`2 oldTableSchemaDictionary, ConcurrentBag`1 exceptions) 04/26/2019 13:20:47: 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__DisplayClass15_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: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.PROJHIERARCHYSORTING’ and the index name ‘I_19940NODEREFERENCE’. The duplicate key value is (5637144576, usmf, 0, , 0). The statement has been terminated. CREATE UNIQUE INDEX I_19940NODEREFERENCE ON DBO.PROJHIERARCHYSORTING(PARTITION,DATAAREAID,TYPE,REFID,REFNODEID); at Microsoft.Dynamics.AX.Data.Management.DBExecute.ThrowOnSqlExecuteException(SqlException e, String commandText) at Microsoft.Dynamics.AX.Data.Management.DBExecute.ExecuteDDLWithNewTransaction(String commandText) at Microsoft.Dynamics.AX.Data.Management.DBExecute.ExecuteSqlBatch(TableSyncSqlBatch sqlBatch) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.ExecuteTableModificationDdl(TableSyncSqlBatch sqlBatch, String tableName, Boolean disableChangeTracking) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.ExecuteModifyTableViaAlterTable(AxTable newTableSchema, IndexListCache ignoreIndexListCache, IncrementalSyncTableSchemaBuilder tableSchemaBuilder) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.ExecuteModifyTableViaAlterViewAndHandlePossibleExceptionsFromSchemaBoundViews(AxTable newTableSchema, IndexListCache ignoreIndexListCache, IncrementalSyncTableSchemaBuilder tableSchemaBuilder) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.ExecuteModifyTable(AxTable newTableSchema, AxTable oldTableSchema, Int32 tableId) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.ModifyTable(AxTable newTableSchema, Dictionary`2 oldTableSchemaDictionary) at Microsoft.Dynamics.AX.Data.Management.ManagedSyncTableWorker.TableSyncAction(AxTable axTable, Dictionary`2 oldTableSchemaDictionary, ConcurrentBag`1 exceptions). at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.<>c.<RunFullTableSync>b__15_1(Tuple`2 result) at Microsoft.Dynamics.AX.Framework.Database.Tools.LegacyCodepath.ExecuteWithinAOS(SyncOptions syncOptions, String sqlConnectionString, IMetadataProvider metadataProvider, Func`1 func, Action`1 errorHandler) 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(SyncOptions options) at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.Run(String metadataDirectory, String sqlConnectionString, SyncOptions options) 04/26/2019 13:20:47: The operation failed.
The step failed
at Microsoft.Dynamics.AX.AXUpdateInstallerBase.AXUpdateInstallerBase.executeParallel(String runbookID, Boolean silent, String updatePackageFilePath, IRunbookExecutor runbookExecutor, Boolean versionCheck, Parameters param)
at Microsoft.Dynamics.AX.AXUpdateInstaller.Program.InstallUpdate(String[] args)
at Microsoft.Dynamics.AX.AXUpdateInstaller.Program.Main(String[] args
The issue is that the update adds an index to the table ProjHierarchySorting and if there are records in this table it will most likely fail since there will me posts that the index considers duplicates.
The solution is to empty this table before the update (this can be done safely since the table is auto populated). If you have already started the update you can empty the table and then resume the update. Simply log into the server, start Management Studio and run:
delete from ProjHierarchySorting
In production you will need to add a message in the service request telling the technician to run the above SQL line.
One of the crucial concepts in Dynamics 365 for Operations are Data Entities… you use them for basically everything. Data entities is an abstraction on top of the regular database table which aggregates multiple table into one object.
Today I had a problem with an entity missing som the entity list. I this happens the solution is usually to refresh the list by going to System administration – Workspaces – Data management – Data import/export framework parameters and clicking Refresh entity list under Entity Settings.
That did not do the trick today so I had to dig deeper… and found this SQL script that clears the entity list:
-- This source code or script is freeware and is provided on an “as is” basis without warranties of any kind, -- whether express or implied, including without limitation warranties that the code is free of defect, -- fit for a particular purpose or non-infringing. The entire risk as to the quality and performance of -- the code is with the end user. -- Create backups of the tables first SELECT * INTO DMFEntity_backup FROM DMFEntity GO SELECT * INTO DMFTargetXML_backup FROM DMFTargetXML GO SELECT * INTO DMFTargetXMLToEntityMap_backup FROM DMFTargetXMLToEntityMap GO SELECT * INTO DMFTargetEntityHierarchy_backup FROM DMFTargetEntityHierarchy GO -- Truncate the tables TRUNCATE TABLE DMFENTITY GO TRUNCATE TABLE DMFTargetXML GO TRUNCATE TABLE DMFTargetXMLToEntityMap GO TRUNCATE TABLE DMFTargetEntityHierarchy GO
Then I go back to Data import/export framework parameters and run Refresh entity list.
That seem to do the trick
NOTE: this procedure has one slight down side and that you are also deleting all mappings for all entities. The (rather tedious) solution is to go into each entity and regenerate mappings… for all 2800+ entities… Wohoo…
Fortunately a smart person on Yammer wrote this script that regenerates all mappings for all entities in one run.
Links:
https://cloudblogs.microsoft.com/dynamics365/no-audience/2016/07/20/data-management-missing-entities-2/?source=axsupport
One of the cool features in Dynamics 365 for Finance and Operation are Analytical workspaces which enables Dynamics to display PowerBI workspaces inside of the Dynamics UI which also means that you are able to use the included limited PowerBI license that comes with D365fO
To get this working you can either deploy the includes report packages which are downloadable from LCS or you can create your own and upload them to LCS. There is however some infrastructure required to get this up and running because the report get their data from aggregetad measurements, which you are required to create.
The measurements are syncronized to the AxDW database and the report uses Direct Query to read the data. Since the report reads its data from the AxDW database it does not have the ferformance implications that OData has.
The process for setting this up in a Tier-2 and up environment is pretty straight forward since these all include the specific service for hosting the PowerBI report but in Tier-1 (OneBox) this service is not present and this means that we will have to do a slight workaround.
First we have to create an Azure Service for hosting the report, you will find the detils in this link
The next step is to copy the AxDW database from the environment to Azure SQL. You find the information here and here.
And the last part is to reconfigure the OneBox environment to use the Azure SQL hosted entity store instead of the internal
I only had one minor issue diring the setup… the AxDW admin user was not able to log in to the AxDW database in Azure SQL resulting in this message:
I resolved it by deleting and recreating the user :
CREATE LOGIN [axdwadmin] WITH PASSWORD = 'axdwpassword' CREATE USER [axdwadmin] FOR LOGIN [axdwadmin] WITH DEFAULT_SCHEMA = dbo; ALTER ROLE db_datareader ADD MEMBER [axdwadmin]; ALTER ROLE db_datawriter ADD MEMBER [axdwadmin]; ALTER ROLE db_owner ADD MEMBER [axdwadmin];
Links:
https://blogs.msdn.microsoft.com/dynamicsaxbi/2017/07/29/deploy-azure-sql-server-for-hosting-entity-store-database/
https://blogs.msdn.microsoft.com/dynamicsaxbi/2017/07/29/publish-the-entity-store-database-on-azure-sql-server/
Some configurations in Dynamics requires you to be in Maintenance Mode. Some of these are:
The limitations of maintenance mode is that you will need to be Admin or maintenance mode user to log in.
Microsoft.Dynamics.AX.Deployment.Setup.exe --metadatadir K:\AosService\PackagesLocalDirectory --bindir K:\AosService\PackagesLocalDirectory\Bin --sqlserver . --sqldatabase axdb --sqluser --sqlpwd [sqlpassword] --setupmode maintenancemode --isinmaintenancemode true
To set it back run this
Microsoft.Dynamics.AX.Deployment.Setup.exe --metadatadir K:\AosService\PackagesLocalDirectory --bindir K:\AosService\PackagesLocalDirectory\Bin --sqlserver . --sqldatabase axdb --sqluser --sqlpwd [sqlpassword] --setupmode maintenancemode --isinmaintenancemode false
and then IISRESET
NOTE: Maintenance mode of a Production environment is set by a service request to DSE.
UPDATED NOTE: There is now a Deployable Package for enabling/disabling Maintenance Mode so you don´t have to bother DSE. It is in the Asset Library.
NOTE: On Sandbox Environments, do the above procedure on one of the AOSes. You might need to run IISreset on all AOSes.
Links:
https://community.dynamics.com/365/financeandoperations/b/ax2012codingaxclated/archive/2018/06/22/d365fo-maintenance-mode-importing-license-file-isv-var-add-on-license-import
https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/sysadmin/maintenance-mode