Screenshots in Task Recordings ni Dynamics 365 for Operations

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.

Beware of the BacPac

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

Tip of the week: Getting rid of Standard View

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.

  1. Go to the Data Management Workspace
  2. Go to Framework Parameters
  3. Change View Defaults to Enhanced

Happy Days!!

Where are my alerts???

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:

  1. Put the environment in Maintenance Mode
  2. Go to System Administration – License configuration
  3. Check “Event”
  4. Disable Maintenance mode

 

Links:
https://community.dynamics.com/365/financeandoperations/f/765/t/283320

Updating Dynamics 365 FO from 8.0 to 8.1 fails

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.

My entities are missing…

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

Setting up PowerBI embedded and Analytical workspaces in Dynamics 365 for Finance and Operations in a OneBox Environment

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-power-bi-embedded-service-for-dynamics-365-for-operations/

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/

https://blogs.msdn.microsoft.com/dynamicsaxbi/2017/07/29/configuring-1box-to-enable-analytical-workspaces-and-reports/

Setting a Dynamic 365 for Operations cloud hosted environment in Maintenance mode

Some configurations in Dynamics requires you to be in Maintenance Mode. Some of these are:

  • Activating dimensions
  • Changing license keys

The limitations of maintenance mode is that you will need to be Admin or maintenance mode user to log in.

  1. Log into the VM using RDP
  2. Start an elevated command prompt
  3. Go to K:\AosService\PackagesLocalDirectory\Bin\ (in this case K is the service volume)
  4. För följande:
    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
  5. Run IISRESET

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

Adding users WITHOUT an Azure AD Accounts to Dynamics 365 for Finance and Operations

Normally when using Dynamics 365 for Finance and Operations all you users are part of an Azure AD. This is created when ordering your license and is used for authentication. This AD is either stand-alone or synced with you OnPrem AD.

There might be situations where you need to add external users to your Dynamics installation and if these are part of an Azure AD you just use this guide but if the external part does not have Azure AD then it is a bit more problematic… You do not want to add them to your organization since this might add security issues and sometimes licensing costs, and you might not be able to force them to get their own Azure AD tenant.

There is however another way to do this. It will require the user to get a Microsoft Account, that should not be an issue since it is a free account.

  1. Log into the azure portal and go to Azure AD – All Users
  2. Click New Guest User
  3. Add the users Microsoft account (eg *@hotmail.com, *@outlook.com)
  4. Go to D365FO and choose Import users
  5. Select the new account, import it and give it the correct user role.

Now the user can log in using their hotmail adress

That’s all for today

/Johan

Force running distribution jobs in Dynamics 365 for Operations Retail

When working with retain in Dynamics 365 for Finance and Operations, distribution jobs are used for moving data between Channel Database and the “AX” transaction database.

The jobs with the prefix P are inbound to AX and the other once are for outgoing sync. In some cases we need to trigger these jobs manually. To do this go to Retail IT – Distribution Schedule. Select the job and click Run Now

When the job is run click the History button to verify that it ran OK.

/Johan