Configuring BYODW using data entities in Microsoft Dynamics 365 Finance and Operations One version

Among several analytics options in Microsoft Dynamics 365 Finance and Operations One version, one my favourite is BYODW

Sharing steps and visuals to easily understand and leverage in your project:

1) Configure entity export to database in D35DFO

2) Use connection string as follows to configure entity export to your own Azure SQL database

Data Source=<Logical Server Name>,1433; Initial Catalog=<your DB name>; Integrated Security=False; User ID=<SQL User ID>; Password=<Password>

Logical server name should be similar to the nnnn.database.windows.net and you should be able to find the logical server name from the Azure Management portal.

After you enter the connection string, click the Validate button and make sure that the connection is successful.

Create Clustered Columnstore indexes option optimizes the destination database for select queries by defining Clustered Columnstore indexes (CCI) for entities copied from D365FO. supported only on Azure SQL premium DBs,

3) Publish one or more Entities into the newly created database by selecting the Publish option from the menu.

Publish: Publish option defines the Entity database schema on the destination DB. When you select one or more entities and select the publish option, a batch job is started to create the Entities in the destination database. When the database definition job is complete, you will be notified by a message in the InfoLog (ie. the bell icon on top right)

Actual updating of data happens when you export data. At this point you are only creating the schema.

Drop entity option deletes the data and the Entity definition from the destination DB.

Compare source names option lets you compare the Entity schema in destination with the Entity schema in D365FO. This option is used for version management. You can also use the compare option to remove any of the unwanted columns from the destination table.

4) Enable Configure Change tracking

Change tracking is used by the system to identify changes made to tables as transactions are performed in D365FO.

There are several change tracking options that can be used.

For Change tracking functionality to work, you do need to enable change tracking option in the D365FO Database.

In case you re-publish and Entity that exists in the destination database, the system warns you that existing data will be deleted because of the new operation.

5) Export data into your Database

Once entities are published to the destination database, you can use the Export function in Data Management workspace to move data. Export function enables you to define a Data movement job that contains one or more Entities.

When adding an entity for data export, you have the ability to choose incremental export (called “incremental push”) or full push. In order for incremental push to work, you do need to enable Change tracking in the D365FO database and specify an appropriate change tracking option as described above.

You can create a data project with multiple Entities and it can be scheduled to execute using D365FO batch framework. You also schedule the data export job to run on a recurring basis by selecting the Create recurring data job option.

6) Configure DefaultDimension field on tables exposed via entities to export financial dimension and configure financial dimension configuration for integration applications

Note* After any change in data entities, need to drop from BYODW, refresh entity list, recreate them and republish them and export data to BYODW