Monday, April 26, 2021

MS D365 FinOps: How to create new LCS project and deploy Tier 1 (DEV) VM - Even you are not a MS customer or partner :) - Part I

Scenario:

You are willing to work on Microsoft Dynamics 365 Finance Operations product and want to get your hand dirty with some development. You are hearing so much about this product but never got a chance to work on this MS ERP. 

Andre wrote a detailed post on how you can setup a trial environment for MS D365 Finance Operations

Solution:

With this post I will explain a step-by-step guide on how;

  1. to create your own LCS project
  2. to deploy a new Tier 1 VM
  3. to log on to Azure Portal
  4. to access Power Platform environments
  5. to create your own power app and use other features
  6. to deploy solutions in Power Platform to integrate with Finance Operations

NOTE: You would need Azure Subscription to deploy VM

First create new domain to perform all above steps - its easy just follow following steps

1. Open this site Office 365 E3 in cognitive mode or as guest and go with Free Trial option









2. Fill in details - you can use your personal email or sign up for a new email account and use that one












3. Provide as much as information you can - it will be good for you :)














4. Choose verification method, I always select text me 















5. Provide Once verified enter your business details and check the availability
























6. Sign up and you are ready to use this account to perform all above steps mentioned under solution section











Manage your subscription option will take you to Microsoft 365 admin center where you will have 25 free user license for the whole month. You can use this account to sign up for teams and enjoy all features for the whole month FREE!!










Log on to LCS lcs.dynamics.com using above created account

Create new project by clicking on + sign and fill information - product name should Finance and Operations










Your LCS project is ready, click on hamburger sign and go to Project setting













Under Organization and ownership the type should be either customer or partner which will be one of them based on the account you have used to log on to LCS. If your account is linked to a partner organization then this will be partner and it will be customer if your account is of type customer.

Remember, the created account in post is not linked to either Partner or Customer so we cannot deploy any tier 1 environment in LCS as in order to connect to Azure portal with LCS the company account should be either customer or partner. 

So, we are blocked here :(


Here is the trick to convert this prospect account to customer account to unblock ourselves. 

Browse https://trials.dynamics.com/ and choose Finance and Operations, enter your new account and hit Get Started. This will deploy a new trial environment with demo data in next 30 minutes. Read Andre's post to find the downside of this environment.















After trial environment deployed, refresh project settings page (you can sign out and sign in again in) to see the changes where it has changed type from prospect to customer











Now you are the customer so let's continue our journey of completing our solution but this is it for this post and we will continue deploying cloud hosted environment through LCS in azure portal in next post.

MS D365 FinOps database sync failed with an error in SECURITYROLEPRIVILEGERESOURCELICENSEMAP

Scenario:

Database synchronization failed with following error message. 

It happened after I refreshed the tier 1 (DEV) environment of MS D365 FinOps from a sandbox environment database. The original database was named to AxDB_Orig as part of this refresh process.

Severity Code Description Project File Line Suppression State
Error ON JMAP.PRIVILEGEIDENTIFIER = SP.IDENTIFIER AND JMAP.ISUNIQUE = 1 0
Error LEFT JOIN SECURITYMENUITEMLICENSES RL 0
Error at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) 0
Error INSERT INTO SECURITYROLEPRIVILEGERESOURCELICENSEMAP 0
Error at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.Run(String metadataDirectory, String sqlConnectionString, SyncOptions options) 0
Error SP.RECID, 0
Error RP.AOTNAME, 0
Error at Microsoft.Dynamics.AX.Framework.Database.Synchronize.InitialSchemaSync.ScriptRegion.ExecuteCommand(SqlCommand cmd) 0
Error ON RL.IDENTIFIER = RP.AOTNAME 0
Error at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.RunSync() 0
Error LEFT JOIN SECURITYROLEPRIVILEGEEXPLODEDGRAPH PRMAP 0
Error END, 0
Error \n\nException message: System.Data.SqlClient.SqlException (0x80131904): Column name or number of supplied values does not match table definition. 0
Error FROM SECURITYPRIVILEGE SP 0
Error -- MAINTAINLICENSE 4 is Operations. We update MAINTAINLICENSE only if existing MAINTAINLICENSE is operations and set to Finance (8), SCM (9), Retail (10) 0
Error [MAINTAINLICENSE] = 0
Error ON PRMAP.SECURITYPRIVILEGE = SP.RECID 0
Error SP.IDENTIFIER, 0
Error --cleanup before populating data 0
Error \nCREATE PROCEDURE [DBO].[LICENSING_POPULATEROLEPRIVILEGELICENSEMAP] 0
Error --- End of inner exception stack trace --- 0
Error AS 0
Error at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.InitialSchemaSync() 0
Error at Microsoft.Dynamics.AX.Framework.Database.Tools.StaticSchema.RunStaticUpdate(String sqlConnectionString, String binDir, Boolean skipRegionHashing) 0
Error at Microsoft.Dynamics.AX.Framework.Database.Tools.SyncEngine.FullSync() 0
Error CASE WHEN JMAP.SKUNAME = 'Finance' AND RL.MAINTAINLICENSE = 4 THEN 8 0
Error TRUNCATE TABLE SECURITYROLEPRIVILEGERESOURCELICENSEMAP 0
Error LEFT JOIN LICENSINGSERVICEPLANSPRIVILEGE JMAP 0
Error RL.VIEWLICENSE 0
Error at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) 0
Error END; 0
Error BEGIN 0
Error at Microsoft.Dynamics.AX.Framework.Database.Synchronize.InitialSchemaSync.RunSync() 0
Error SELECT DISTINCT PRMAP.SECURITYROLE, 0
Error at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) 0
Error at Microsoft.Dynamics.AX.Framework.Database.Synchronize.InitialSchemaSync.ScriptRegion.Execute(SqlConnection connection, SqlTransaction transaction) 0
Error RL.MENUITEMTYPE, 0
Error Initialize schema failed. Microsoft.Dynamics.AX.Framework.Database.TableSyncException: Failed during InitialSchema at command: 0
Error ClientConnectionId:692f02f7-8690-4127-9da2-c59ead0da371 0
Error ON SP.IDENTIFIER = RP.PRIVILEGEIDENTIFIER AND SECURABLETYPE IN (1,2,3) 0
Error WHEN JMAP.SKUNAME = 'SCM' AND RL.MAINTAINLICENSE = 4 THEN 9 0
Error WHEN JMAP.SKUNAME = 'Retail' AND RL.MAINTAINLICENSE = 4 THEN 10 0
Error ELSE RL.MAINTAINLICENSE 0
Error at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() 0
Error -- While updating we also consider Privilege in LicensePrivileges.json is unique. 0
Error at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) 0
Error WHERE RL.MENUITEMTYPE IS NOT NULL 0
Error Error Number:213,State:1,Class:16 ---> System.Data.SqlClient.SqlException: Column name or number of supplied values does not match table definition. 0
Error JOIN SECURITYRESOURCEPRIVILEGEPERMISSIONS RP 0
Error at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) 0
Error syncengine.exe exited with code -1. 1   

Resolution:

It was the number of columns mismatch between the source and the target databases in SECURITYROLEPRIVILEGERESOURCELICENSEMAP

Executed this command against AxDB and AxDB_Original databases. 

sp_columns SECURITYROLEPRIVILEGERESOURCELICENSEMAP

Compared the number of columns in between both DBs and found AxDB has an extra column added as part of the refresh from sandbox database.

Open Object explorer and removed the additional column from SECURITYROLEPRIVILEGERESOURCELICENSEMAP



Synchronize the database in MS D365 FinOps and it completed successfully.

Monday, January 25, 2021

Dual-write learning series - Dual-write initial sync is the data integrator

One of the features of the dual-write is initial sync where you copy data from the source app (Finance Operation OR DataVerse) to the target app (Finance Operation OR DataVerse) depending on the selection in Master for initial sync option. 

This initial sync is the Data Integrator service running behind the scene and copies your data over. You configure the application id for data integrator and add it both apps (Finance Operation OR DataVerse), I have documented it my previous post The Dual Write implementation - Part 2 - understand and action pre-requisites

Master for initial sync can be either Common Data Service (Dataverse) or Finance and Operations apps. For example, If I choose Finance and Operations app in below example where I am syncing Functional Locations then all records will be copied from Finance and operations to Dataverse.








Initial Sync is a full push means if an individual row fails to sync, you cannot resync only failed ones. If the initial synchronization only partially succeeds, a second synchronization runs for all the rows, not just the rows that failed to be synced during the initial synchronization.

For example;

1st initial sync Run for 1000 records from FO to CDS à 700 passed and 300 failed

2nd initial sync Run will again run for 1000 records 

Do check Considerations for initial Sync from Microsoft Docs 

Initial Sync runs against all legal entities configured for dual-write. If you have entered a filter for a specific legal entity in a table map at Finance and Operations app side, as shown below as an example, this will not work for initial sync as it will run against all legal entities configured for dual write under environment details.



Wednesday, January 13, 2021

D365FO: Right click on any control at D365FO browser takes you directly to the control in AOT

Last week I explored very Interesting feature especially for developers where you right click on any field/control on the form and follow these steps.












This opens the visual studio in non admin mod, Opens the correct form, and takes you directly at the control in AOT.

NOTE: You can only get this feature within Development VM where your browser and Visual Studio are in same machine. I am at 10.0.14 but not sure when this great feature was available first :(



Sunday, December 27, 2020

Another step closer - Finance Operations data in Power Platform - Virtual Entities

This post focuses on the integration technologies available to have the Microsoft Dynamics 365 Finance Operations data available in Dataverse/Common Data Services/CDS. What could be better then having The biggest ERP system's data in Power Platform. You can Power Portals, Power Apps, Power BI analytical reports, use power virtual agents for inventory closing and year end closing processes, manage expenses and employee/contractors time entry processes, most of these processes can be even without logging to MS ERP (Dynamics 365 Finance Operation) so can safe on license cost too. 

Let's see what options are available to integrate F&O data with Power Platform however, this post is dedicated to Virtual Entities. 

3 Options available out-the-box to integrate F&O data with Power Platform;

👉 Data Integrator - Click on link to read more

👉 Dual-Write - Click on link to read more

👉 Virtual Entities - MS Tech Talk on Virtual entities 




Before we jump to the installation and configuration part, let's see when were the virtual entities available and what features these have come up with compared to other two integrations technologies.

Virtual Entities Generally available

✔️ Finance and Supply Chain Management App: 10.0.12 or later

✔️ Dataverse: Service update 189 or later

Virtual Entities features

✔️ Finance and Operations is available as a data source in Dataverse

✔️ No Finance and Operations data replication in Dataverse

✔️ Access all public data entities of Finance and Operations in Dataverse

✔️ Support all CRUD operations

Install Virtual Entities solution

Head to this link https://appsource.microsoft.com/en-us/product/dynamics-365/mscrm.finance_and_operations_virtual_entity and Get it Now










Enter your work or school account and Sign in

Choose the environment where you want to install this solution













Wait for finish to installing









Finance and Operations Virtual Entity solution shows as Enabled









Finance and Operations Virtual Entity solution is installed successfully - Hurray!! that was easy








Register an App in Azure Active Directory

The AAD application must be created on the same tenant as F&O.

  1. Log on to http://portal.azure.com
  2. Azure Active Directory > App registration
  3. New Registration


    Define these attributes
    1. Name
    2. Account type
    3. Redirect URI - leave blank
    4. Select Register
    5. Make note of the Application (Client) ID, you will need it later


Register an App



Create a symmetric key for the application, Save and note it for later use.


Steps to follow in Dataverse environment 

Log on to Dataverse environment and click on Advance settings


Go to Administrator













Choose Virtual entity data sources


Finance and operations is available as of the data source in Dataverse




















Click on Finance and Operations and following screen pops up, this is where the connections established


























Configuration in Finance and Operations

  1. Log on to Finance and Operations and go to System Administration | Users | Users
  2. Create a new user and assign 'CDS virtual entity application' role to it - don't assign system admin role to this user - This user is used to look at the metadata of the data entities from the Dataverse instance.
  3. Enter Application Id in System Administration | Setup | Azure Active Directory applications screen with the User ID = <The user created in step 1>


Test Finance and Operations data in Dataverse

Log on to Dataverse instance and click on a little funnel to open advance find and look for 'Available Finance and Operations Entities' in the list of tables in Dataverse instance. 


















By default not all the entities are enabled this is to avoid cluttering the user experience in Dataverse but individual entities can be enabled e.g. I enabled DataManagementDefinitionGroupEntity and mark visible to make this as a virtual entity in Dataverse.















To illustrate this example, I created an export data project in Finance and Operations under Data Management with the name 'CDSVirtualEntitiesExport' - The data entity behind this data export projects is DataManagementDefinitionGroupEntity which is marked as virtual entity in above step. 
















Restart the Advance find in Dataverse instance and look for Definition Group (mserp) table map and Run to see the output












This is it for today, with next post I will explain how to do customization/extension in F&O and get data into Dataverse using Virtual Entities. 

Hope you may have enjoyed the post, please do provide your feedback. Enjoy your break!!


MS D365 FinOps: How to create new LCS project and deploy Tier 1 (DEV) VM - Even you are not a MS customer or partner :) - Part I

Scenario: You are willing to work on Microsoft Dynamics 365 Finance Operations  product and want to get your hand dirty with some developmen...