Microsoft Dynamics 365 | Integration, Dataverse...

Questions on using Data Export Services

(1) Share

Report

Posted on by D_PBI

Hi,

I'm new to Dynamics 365 (D365) so do bear with me. A key consideration in this post is my limited understanding of option sets, pick lists, etc.. and how they are structured in D365.

I am looking to undertake some Power BI reporting on the D365 data and seeking the best method to do so.

Having researched a little, I intend to take the D365 data and, using Data Export Services, populate a Azure SQL Server database with selected Entities. Before I embark on this journey, I have some questions that I would appreciate answering:

1) SQL server is 'schema-on-write' so there should be the need to pre-create the SQL database objects up-front prior to exporting the selected Entities. However, does the Data Export Services tool automatically create the SQL database objects on-the-fly based on the Entity you've selected to export to SQL Server and therefore is no need to for me to create the objects up-front, or does the Data Export Services tool not create any objects automatically and therefore I will need to create the SQL objects up-front prior to using the Data Export Services?

2) With my limited D365 knowledge, I am thinking that an Entity, say a Customer Entity is made from multiple tables (6 for example) within D365. When using Data Export Services to export the Customer Entity, will the Data Export Services (DES) automatically select all 6 tables or is the some logic performed between D365/DES that either:
a) automatically selects all 6 tables for the Customer Entity, or would I have to understand all the table names and how to locate them to select them for export?
b) automatically de-normalises the 6 tables into a single Customer Entity table so the DES only need to export a single table - this would save me de-normalising the tables after export/before reporting?

3) Once the Data Export Service has been configured and export the selected Entities, when the source D365 system is changed, keeping with the Customer Entity, say a table is removed or additional fields are created - will the already configured DES tool automatically adjust to export the new Entity structure or will this be a manual process by a developer (me)?

Any feedback you can provide will be much appreciated as, quite frankly, I do not understand the D365 structure.
Thanks.

I have the same question (0)

All responses (2)

Answers (0)

Suggested answer

Christoph Thaler 5,442 on at

Like (0)

Report

Hi,

As your question is in the general forum I don’t know the product you are talking about. Usually our will find some documentation about entities in the product docs pages like here docs.microsoft.com/.../data-entities for D365 Finance and Supply Chain Management.

I will try to answer your questions for D365 F and SCM. In General you can do a lot with entities but it always depends on you needs how you use the possibilities.

First you have to decide what kind of data you need and if this data is maybe available in PowerBI reports. By default the entity store is available. So you can just export the data and start your BI work. Some cubes are already available. You can check the default cubes in „Entity store“ form

Another option is Bring Your Own Database. You can find details here https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/analytics/export-entities-to-your-own-database.

Regarding you question 2, it is option 2. You will get one set of data and it is not necessary to know or handle the tables from D365F/SCM.

Regarding question 3, if you modify the table structure in D365 you have to modify the entity as well. This is not done automatically.

Overall it is a complex topic and needs some experience how to get the best solution. Maybe you have contact to an experienced consultant or developer. I think this might save you from marking some failures (we all made in the past) and will speed up your solution.

Was this reply helpful? Yes No
Suggested answer

saurabhtiwarii on at

Like (0)

Report

The Data Export Service creates tables for both data and metadata. A table is created for each entity and M:N relationship that is synchronized. Once an Export Profile is activated, these tables are created in the destination database.

Table details for the destination Azure SQL Database ::

https://docs.microsoft.com/en-us/power-platform/admin/replicate-data-microsoft-azure-sql-database#table-details-for-the-destination-azure-sql-database

Replicate data to Azure SQL Database ::

https://docs.microsoft.com/en-us/power-platform/admin/replicate-data-microsoft-azure-sql-database

An example of how the replicated tables look like after the Data Export Services set up ::

Please mark my comment as answered if this helps. :-)

Thanks,

Saurabh

Was this reply helpful? Yes No