Monthly Archives: June 2019

INET Dynamics
Microsoft Dynamics ERP Systems

Dynamics 365 Finance and Operations Tips & Tricks: Database Logging – Part 2

 

Categories: Dynamics 365
Tags:

 

 

Microsoft Dynamics 365 for Finance and Operations (D365FO) provides Bring Your Own Database (BYOD) feature, which helps you to export data entities to your own external databases serving businesses different needs.

Overall, with BYOD, you can complete the following tasks:
● Configure Microsoft Azure SQL databases so that you can export entity data from D365FO into it.
● Export either all the records (full push) or only the records that have changed or been deleted (incremental push).
● Export on regular schedule with recurrence option of the Finance and Operations batch framework.
● Access the entity database by using Transact-SQL (T-SQL), and even extend the database by adding more tables.
● Export entities into multiple databases.

Some common scenarios that BYOD is utilized are:

● Export data from Finance and Operations into your own data warehouse.
● Get data for other analytical purposes that use other tools requiring T-SQL access to data.
● Perform batch integration with other systems.
● Any other purposes of testing, validating, or integrating data.

If you have integrated solutions that require direct T-SQL access to the database, BYOD is the recommended upgrade path.

To use BYOD, you will need to complete several steps as below:
1. Create a SQL database to connect with your D365FO environment
2. Configure the BYOD connection in D365FO client.
3. Identify data entities that you want to use and publish them (publishing a data entity makes them available to export to BYOD)
4. After the above set up steps, you can create an export in Data Management Workspace with target format is your configured external database.

In this article, we will go over how to configure BYOD. In the next articles, I’ll provide more examples of exporting to BYOD and some common errors.

1. Create a SQL database to connect with your D365FO environment

You can create an Azure SQL Database easily on Azure portal.

Dynamics 365 Database

● Go to Azure portal -> SQL databases -> Add

You should also create a SQL user account for sign-in to the database. Please remember to note and save the server name, database name, and the SQL user account and password. These details are required when configuring the database on D365FO client.

● After selecting the new server as above, choose the appropriate Resource group and pricing tier as you want

Here, for example, I’m using a minimum basic SQL database. However, tiers affect functionality options. The basic tier does not support clustered columnstore indexes.

For integration with a business intelligence (BI) tool, premium databases with the option of clustered columnstore indexes (CCIs) can meet your performance needs. Since CCIs are in-memory indexes that enhances the performance of reading queries that are the major part of analytical and reporting tasks.
For general storing or integration purposes, you may find a standard database sufficient, however, depending on your data size and performance requirements, you can always go for a premium.
● Azure DB Connection String format we use here is ADO.NET

2. Configure the BYOD connection in D365FO client.

Go to System administration > Workspaces > Data management > Configure entity export to database.

To configure a new database, select New, and then enter a unique name and a description for the new database. Then, you can paste the connection string in the Azure portal here and remember to replace the appropriate authentication details (highlighted in screenshots).
Also, as explained above, here I use the basic database tier, so I have to turn off clustered columnstore indexes option. Otherwise, you will get an error.

Select Validate, and make sure that the connection is successful. One common error in this step is an error about not being able to access Azure server

The solution for the above error is enabling access in the Azure portal. So you need to go to the Azure DB > Firewall settings > Add client IP to allow access.

3. Publish data entities

To export to Azure DB, data entities must be published to it. Publishing a data entity to Azure DB creates tables and necessary structures of the data entity in Azure DB.
If data entities are not published, when exporting data entities to Azure DB, an error will show up.
● Go to System administration > Workspaces > Data management > Data entities
On default, you can see that the value of published field is No. To publish a data entity, you can simply select it and click Publish.

4. After successfully configuring the BYOD connection, we can now export data entities to it.

After entities are published to the destination database, you can use the Export function in the Data management workspace. You can create an export project with target format is now selected as your newly added BYOD.

In the next article, we will go through examples of exporting full push an incremental push to BYOD and some important behaviors of these options. If you face any issues importing your own database and require assistance, I-Net Dynamics is a certified Microsoft Dynamics re-seller with a experienced team of local support professionals who can assist you!

INET Dynamics
Microsoft Dynamics ERP Systems

Dynamics 365 Finance and Operations Tips & Tricks: Database Logging

 

Categories: Dynamics 365
Tags:

 

Written by Xuan Luan for I-Net Dynamics

What is Database Logging?

Database logging is a feature that allows you to track specific changes to tables and fields. The feature is also available in AX2012 and there is not much of a difference in setting it up in Microsoft Dynamics 365 Financial Operations.

Some example cases of utilizing database logging that we have come across are
● Businesses that want to keep tracks of changes to specific tables. These tables might have sensitive information or serve their specific business requirements, of which they want to have auditable records of changes.
● Some companies want to monitor changes of users in systems. For example, they want to record employeess who have been added and removed from the system.

Database logging and performance:

The feature is useful in some scenarios; however, just a quick reminder, logging requires resources and meticulous management. It might lead to some performance impacts when you overuse it. Although this document about performance implications of database logging is for AX2012, the same applies to Microsoft Dynamics 365 Financial Operations.

How to set up database logging

In this blog, we will guide you step by step to set up database logging to tracks roles assigned to users in the system.

1. This should be set up by the Admin user. Only users with the System Administrator role can access the System administration module.

2. Navigate to the feature: Click System administration > Setup > Database log setup.

The database log setup form will be opened. You might find enabled logs here. If what you want is not already listed, you add it by clicking New.

3. Click New to start the Logging database changes wizard

4. In the next window, you should be able to find tables that you want to track. Please be noted that not all tables are displayed. You can see more by clicking on two checkboxes “Show all tables” and “Show table names” at the bottom, which helps you to find required tables faster.

5. After selecting the table Security User Role, you can set what fields and changes you need. Logging everything is time-consuming and redundant, you might filter the fields depending on your needs. Here for the demonstration purpose, I’ll select all the fields.

6. Clicking Next, in this window, you can select what types of changes to track. Changes that can be tracked include insert, update, delete, or rename key. Again, I select everything here.

7. Click Next and then Finish to save. You might have some popups reminding about performance impacts, choose Yes. After that, you should see info log of operation completed.

How to see logs

You can navigate to System administration > Inquiries > Database > Database log.

1. Let’s test our new database log by the roles of a user. Here, I’m the Admin user with below roles.

2. Let’s remove the role System tracing user

3. Go to the database log form to check. You can click Refresh to get the latest data and also, sort the table to see the results better.

4. You should see the record as below

5. Also, you might want to check more details of this action by selecting the row and choose History tab. To see the full details, please click on Show cumulative changes and then full details will be shown correctly as below.

We hope that this article has been helpful to understand how you might utilize the Database Logging feature in Dynamics 365 Financial Operations. Please look forward to our future articles about some tips and tricks of our products. If you are interested in exploring any of our products and services, please contact us here.