Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Tim Stutzman]

Hello,

We are currently implementing D365 F&O right now and still learning, so I apologize in advanced if my question is sounds ignorant…

What is the best method set a scheduled event to have data exported out of D365?  If possible, we would like to export Released Product, Customer, Invoice and Sales Order data out of F&O into our on-prem data warehouse (SQL Server).  I know ideally it would be better to move our data warehouse to Azure that would make things easier, but we do not have the resources to do that at this time and it will have to be a project to do after we go live with D365 F&O.

I have read about BYOD and how you can extract into Azure SQL Server and then it looks like we could pull that data into our on-prem data warehouse.  I have also read about using Power Apps and Power Automate to extract data, but haven’t found clear documentation on how to exactly do that.  I have also looked at developing a C# application and use OData to connect and extract.

If anyone can point me in the right direction to the best method to extract data via a schedule and transfer it to on-prem SQL Server, it would be greatly appreciated.

Thanks,
Tim

——————————
Tim Stutzman
Pure Archery Group
Eugene OR
——————————

[Jonathan Howe]

As you say, the obvious approach is Data Management and schedule an export project to AzureSQL. This works very well for us. For on-prem can you use SSIS and ODATA? I’ve not tried it but if there is an odata connector you could extract data from any data entity that has the public flag set which is true for the most commonly used data entities..If your data volumes are large odata is not a good bet and power automate is not really intended to extract data in bulk.
?

——————————
Jonathan Howe
IT Manager
Cross Manufacturing
Bath, UK
——————————
——————————————-

[Neill Riordan]

Hello Tim,

All of the things you have mentioned have their pros and cons.

The first question is volume how many of each entity are you looking at extracting each day as this will provide you with a good steer on what technology to use.  Things like the Released products entity is a under review due to MS new stance on anti patterns so you will need to be ready to change.  Also do you need to use this entity what is the actual data you want and could you use a data area id filter on another entity instead.

BYOD used to be the only approach we had however now the accepted first option is to use the Azure Data Lake.  This is out of the box in terms of F&O being able to carry out this function and it offers a much lower cost than BYOD. See here https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/data-entities/entity-store-data-lake For the long term strategy and the likely movement to the cloud of your DW i would be recommending this approach.

——————————
Neill Riordan
Head of F&O
KPMG Microsoft Business Solutions
UK
——————————
——————————————-

[Ben Morgan]

Hi Tim,

The most straightforward way to accomplish what you’re looking to do is the BYOD route.  You can see the basic setup details here: https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/analytics/export-entities-to-your-own-database.  Once this is done, you just have to find the entities you wish to use, publish them to the database, and then schedule how often you want the export to run within data management.  If there aren’t delivered entities that contain what you need, it’s simple to create custom entities as needed and export them through the same method.  

Please let me know if I may provide additional information.

——————————
Ben Morgan
M3 Engineering & Technology Corp
——————————
——————————————-

[Neill Riordan]

Just to follow up on my previous post the Azure Data Lake is currently in public preview early and is currently down for GA in Sep 2020. See here in docs https://docs.microsoft.com/en-us/dynamics365-release-plan/2020wave1/finance-operations-crossapp-capabilities/entity-store-data-azure-data-lake-ga

The table extract to data lake is a little behind but can be found here https://docs.microsoft.com/en-us/dynamics365-release-plan/2020wave1/finance-operations-crossapp-capabilities/tables-customers-adls-gen2-data-lake

If you then want to get your data Tim you can consult these options here https://docs.microsoft.com/en-us/sql/integration-services/data-flow/azure-data-lake-store-source?view=sql-server-ver15

I would like to think your current partner can provide some expertise in this area either from a guidance point of view or actual support.  If this would be a problem i could point you to a member of our american team as neeed

——————————
Neill Riordan
Head of F&O
KPMG Microsoft Business Solutions
UK
——————————
——————————————-