Data Entities

Maintain Dynaway Analytics

Determining the versions of the individual stored procedures can be done by executing the stored procedure 'Reporting.Diagnostics'. This diagnostics script will provide information about the installation including reading the definition of the stored procedures and show the metadata which is part of a specially crafted tag within the stored procedures provided.

Configure Dynaway Analytics Backend (ETL)

Important configuration information for the ETL package is stored in the table 'Reporting.Settings' as Key/Value pairs.

Configure Dynaway Analytics Backend (Entities)

This part requires knowledge of Dynamics 365 F&O LCS.

To enable the analytical data warehouse to be built for the Asset Management Module (AM), a prerequisite is to install and schedule several entities for export.

The entities are provided through Microsoft Life Cycle Services (LCS) and since this is licensed software, like the rest of the Dynaway Analytics-package, access to the packages will be provided by Dynaway.

Note: Since new versions will be released on a regular release cycle it is very important to be aware that pulling a new release from LCS can result in compatibility issues with the ETL-layer of Dynaway Analytics. Incompatibility can lead to the update failing, thus rendering all Dynaway Analytics PowerBI dashboards inoperable.

Configure Entities: Bring your own database (BYOD) in F&O

Dynaway Analytics solution uses Bring Your Own Database (BYOD) feature, which allows to export data entities from the application to your own Microsoft Azure SQL database. When all above requirements are met in your Dynamics 365 FO environment you can proceed with creating Azure SQL database which will keep data exported from your Dynamics 365 FO environment. Data from this database will be used on Power BI dashboards and reports for displaying detailed statistics about your maintenance processes.

Create Azure SQL Database

Before you can configure data entities export and use BYOD feature, you must create an Azure SQL database in Azure portal. Please note that end users incur all cloud computing costs related to creating and maintaining new Azure SQL database.

1. Go to Microsoft Azure portal: https://portal.azure.com/#home.
2. Click Create a resource button.
3. In 'Create a resource' page click SQL Database link button. See screenshot 01.
4. On the 'Create SQL Database' page in the 'Basics' tab, select your preferred subscription and create new resource group by clicking Create new button. See screenshot 02.
5. In the Database details section, specify preferred database name and click the Create new button in order to create new database server. See screenshot 03.
6. On the 'Create SQL Database Server' page, specify preferred server and authentication details. Enter server name, database name, server admin login and server admin password and afterwards click the OK button. See screenshot 04.
7. When all settings in the 'Basics' tab are OK, click the Next : Networking > button.
8. In the 'Networking' tab, specify preferred settings related to network access and connectivity for your new database server. Remember that your new BYOD database must be accessible to Dynamics 365 Finance and Operations apps. Therefore, carefully configure networking setup including firewall rules and connection policy and afterwards click Next : Security > button. See screenshot 05.
9. In the 'Security' tab, specify preferred settings related to security and afterwards click Next : Additional settings > button. See screenshot 06.
10. In the 'Additional settings' tab, specify preferred settings related to sample data, database collation, and maintenance window. Afterwards click Next : Tags > button. See screenshot 07.
11. In the 'Tags' tab, specify preferred settings related to tags regarding SQL server and SQL database. Afterwards click Next : Review + create > button. See screenshot 08.
12. In the 'Review + create' tab, review all the settings related to new SQL server and SQL database. If they are OK, click Create button. See screenshot 09.
13. Deployment of the new SQL server and SQL database should start shortly and completed within several minutes. See screenshot 10.
14. After creating your own Azure SQL database, you can proceed in Dynamics 365 FO environment with configuring data entities export to database.

Note, you can view the official Microsoft documentation about creating an Azure SQL database here.

01.

02.

03.

04.

05.

06.

07.

08.

09.

10.

Configure Data Entities Export

Before you can export any maintenance data to your new Azure SQL database, you need to configure entity export to database, which will allow you to connect your Dynamics 365 FO environment to your new Azure SQL database and then export relevant data to this database.

1. Go to Data management workspace in D365 Finance and Operations. See screenshot 01.
2. In the Data management workspace, click the Configure entity export to database tile. See screenshot 02.
3. Click New and give the connection a name. Remember the name as you'll need it later on. Also, fill in the 'Connection String' for your Azure SQL DB. Note that the 'Default extension' field shown in the image below does not apply to BYOD and can be left blank. See screenshot 03.

Publishing data entities schema

1. You then need to refresh and publish the 'AM BI Entities'. Go to Data management > Framework parameters > Entity settings > Refresh entity list. When configuration keys are modified, entity list must be refreshed manually from Data management > Framework parameters > Entity settings > Refresh entity list. See screenshot 04.
2. After clicking the Refresh entity list button, you will see the message that the refreshing process has started, and later you will see a notification message once it's finished stating that it has been refreshed.
3. Go to the Data management workspace and click the tile 'Configure entity export to database'.
4. In the 'Entity store' form, select your configured Azure SQL database and click the Publish button from the top menu. See screenshot 05.
5. In the 'Target entities' form, click the 'Filters' button in the top left corner. See screenshot 06.
6. Each data entity used in Dynaway Analytics for Asset Management solution has a 'amanalytics' tag specified on it. It can be used to filter data entities in the 'Target entities' form and display only these related to the Dynaway Analytics solution. In order to do that, add new filter for the 'Tags' field, set the search option to 'contains' for this filter and put in 'amanalytics' tag in the search text box. See screenshot 07.
7. Click the Apply button in the Filters pane. Data entities visible in the 'Target entities' form will be filtered out and only data entities related to Dynaway Analytics solution will be displayed in the grid. See screenshot 08.
8. Select all visible data entities by clicking checkmark field in grid header row and afterwards click the Publish button in the top menu. See screenshot 09. You will then see a message stating that the publish batch job has been started. When the job is finished, you will see a notification message in the action center.
9. The actual data update in your Azure SQL database will occur when you export the data from Dynamics 365 FO environment. At this point, you have only created the schema in your database for all relevant data entities used during exporting data.

Note: you can find more details about publishing data entities schema to databases in the official Microsoft documentation.

Export

1. To set up a scheduled refresh of data to your Azure SQL DB, click Export from the main Data management workspace.
2. Enter an Export project name and a description. Below the 'Selected Entities', click the Add multiple option.
3. Search for ‘AMAnalytics’ in the 'Tags' text box. Using this tag, both Dynaway specific Analytics data entities along with Microsoft data entities utilized in the solution will be selected. See screenshot 10.
   1. Below job details, select the 'Target Data Format'. That's the name of the data connection to Azure SQL DB you created earlier.
   2. Entities can be exported from multiple companies if you select Export across all companies.
   3. Select all the AM BI Entities and click Add Selected.
4. Once you created the Export Job, click the option Create Recurring Data Job to set up a schedule for exporting the data.
5. In the Data management workspace, you can now see the job you created. In the right-side panel, you can see a history of the last updates.

01.

02.

03.

04.

05.

06.

07.

08.

09.

10.

Troubleshooting Errors

In this section you can read what steps to take if you've run into these errors:

• Entities cannot be published error.
• Export jobs hanging and returning error messages.
• Export job succeeds but is not adding new records.

Entities cannot be published error

1. Ensure that there is a correct source when publishing data entity schema. See screenshot 01.
2. Ensure that there is a correct target (AMAnalytics) when exporting entities. See screenshot 02.
3. Remove staging tables from the database. It appears that the framework cannot handle dropping staging tables before publishing them.

Export jobs hanging and returning error messages

1. Remove all data entities with 'AMAnalytics' tag from the environment.
2. Remove all staging tables for AMAnalytics data entities from Azure SQL database.
3. Synchronize whole database.
4. Install the latest Dynaway deployable package again.
5. Compile and synchronize database.
6. Refresh data entities list.
7. Publish AMAnalytics data entities schemas to Azure SQL database.
8. Export AMAnalytics data entities to Azure SQL database.

Export job succeeds but is not adding new records

1. Uncheck the ‘Incremental Update’ box and rerun the export.
2. If the above does not fix the issue, remove the entity, re-add it and uncheck incremental update before running the export again.

01.

02.

Deploy a DACPAC with SqlPackage

To deploy the ETL side of Dynaway Analytics it is a requirement to have the SQL Server instance configured.

The Dynaway Analytics ETL package is delivered in a zip-file containing 4 files:

• Example-sqlpkgdeploy.bat
• DynawayAnalytics.dacpac
• DynawayAnalytics.dll
• DynawayAnalytics.pdb

The example batch-file is configured for use with SQL Server Authentication and thereby username and password.

This is the content of the Example-sqlpkgdeploy.bat:

SqlPackage.exe /Action:Publish /SourceFile:"DynawayAnalytics.dacpac" /TargetDatabaseName:<YOUR DATABASE NAME HERE> /TargetServerName:"<YOUR SQL SERVERNAME HERE>" /TargetUser:<USERNAME> /TargetPassword:"<YOUR PASSWORD HERE>" /Diagnostics:True

Modify the file to point to the database where 'Dynamics 365 for Finance and Operations- entities' and the entity export has completed successfully. (See previous section for further details on entity-export configuration).

After finishing deploy the update process must be configured. Refer to the example of configuration for Azure SQL instances utilizing Azure Data Factory in the section 'Configure update frequency for ETL with Azure Data Factory'.

Configure Update Frequency for ETL with Azure Data Factory

To enable automated updates of the data warehouse. it's necessary to create an Azure Data Factory-job.

• 1. Log in to the Azure Portal.
  2. Type 'data factor' in the search bar.
  3. Create an Azure Data Factory entity without a template and setup or reuse existing storage and settings.
  4. On the overview of the Azure Data Factory, click the Author button.
  5. Create a blank pipeline.
  6. In the pipeline, find the 'Stored Procedure' located in the General Section of the Activities bar.
  7. Configure the 'Stored Procedure' object to connect to the Azure SQL Database with the correct credentials. It's encouraged to follow your organization's security best-practices which may require use of the Azure Vault for storing credentials to connect to the SQL Server.
     • Note: The credentials chosen must have permissions similar to dbo, including permissions to create, drop, alter tables, indexes, stored procedures, and user defined functions in the database.
  8. Find the stored procedure named 'Reporting.RunUpdate'. This process is responsible for managing the execution of the steps in ETL.
  9. Test that the configuration can connect successfully to the database.
  10. In the pipeline toolbar, find the 'Trigger' option and create a scheduled trigger that will execute the process after any entity export processes configured in Dynamics 365 F&O is completed. Ensure that the corerct time zone is chosen when configuring the trigger (the default selection is set to UTC).
      • Note: We encourage to run a 'Debug' test run to validate that the process works. Additionally, we recommend building out the data factory job to deliver notification of failures to system administrators and supervisors responsible for ensuring the integrity of the reporting data.
  11. After finishing configuration and testing, click the Publish all button within the Data Factory workplace to enable the job to run.