71,645
Like
Share
ReportWith dynamics 365 for operations you can export data entities to your own Microsoft Azure SQL database. This feature is also known as bring your own database(BYOD).More information on this topic can be found here
BYOD is heavily used by analytics team for different financial reports. Sometimes data on those report does not tally to AX. The purpose behind writing this code is two verify record count between AX table and table for published entity in BYOD and notify admin if there is any mismatch.You can utilize this in batch job to send alerts if you found any mismatch. If publish job fails there is no way Dynamics 365 for operations notifies you automatically unless you go and check your configured data projects.
Below runnable class shows how can you connect to BYOD. It uses same connection string which you set up in Data management > configure entity export to database
using System.Data.SqlClient;
using System.Text;
class ConnectToBYODB
{
/// <summary>
/// Runs the class with the specified arguments.
/// </summary>
/// <param name = "_args">The specified arguments.</param>
public static void main(Args _args)
{
DMFDataSource DMFDataSource;
CLRObject dmfEntityProxy;
CLRObject dmfExceptionData;
DMFEntity DMFEntity;
SqlDataReader reader;
Common Common;
FieldId fieldId;
Connection conn;
str connectionStr;
str sql;
#define.serviceHelper('Microsoft.Dynamics.AX.Framework.Tools.DMF.ServiceProxy.DMFSharedTypeServiceReference.ServiceContractClient')
fieldId = fieldNum(DMFDataSource, EntityStoreConnectionString);
boolean RecIdEnabled;
Recid MaxRecid;
Select DMFDataSource where DMFDataSource.SourceName == 'AnaLyticsUAT';
common= DMFDataSource;
try
{
if (!DMFEntityDbExporter::isEntityStoreEnabled())
throw error("EntityStore Not enabled");
if(!DMFDataSource.recid)
throw error("No Valid BYODB datsource provided in setup");
if(!DMFDataSource.EntityStoreConnectionString)
throw error("Connection String for BYODB is Empty");
dmfEntityProxy = new CLRObject('Microsoft.Dynamics.AX.Framework.Tools.DMF.ServiceProxy.DmfEntitySharedTypesProxy');
dmfExceptionData = new CLRObject('Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelperService.ExceptionHandler.ExceptionData');
System.Type serviceClientType = CLRInterop::getType(#serviceHelper);
str vsAssembliesPath = xInfo::directory(DirectoryType::Bin);
str configFilePath = Microsoft.Dynamics.IntegrationFramework.ServiceReference::GetConfigFilePath(serviceClientType, vsAssembliesPath);
CLRObject serviceContract = Microsoft.Dynamics.IntegrationFramework.ServiceReference::CreateServiceClient(serviceClientType, configFilePath);
dmfEntityProxy.set_ClientProxy(serviceContract);
if (common.RecId > 0 && common.orig().(fieldId) == common.(fieldId))
{
SysEncryptionLog::logStringField(common, fieldId, ReadWrite::read);
}
connectionStr = appl.DecryptFromStringForPurpose(common.(fieldId), common.encryptionPurpose(fieldId));
if (!dmfEntityProxy.TestEntityDBConnection(connectionStr))
throw error(strFmt("Connection to BYODB %1 failed",DMFDataSource.SourceName));
conn = new Connection();
using (SqlConnection connection = new SqlConnection(connectionStr))
{
connection.Open();
select firstonly DMFEntity where DMFEntity.EntityName == 'BatchJobEntity';
{
sql = "SELECT count(*) from "+ DMFEntity.EntityTable ;
SqlCommand command = new SqlCommand(sql, connection);
reader = command.ExecuteReader();
reader.Read();
info(strfmt("Number of Records %1",reader.GetInt32(0)));
reader.Close();
}
}
}
catch(Exception::CLRError)
{
CLRObject ex = CLRInterop::getLastException();
if (ex.get_InnerException())
{
ex = ex.get_InnerException();
}
if(ex)
{
error(ex.get_Message() );
}
}
}
}
*This post is locked for comments