D365 Finance & Operations and Dynamics AX Forum

Expand all | Collapse all

Automatically Extract Data From D365 F&O

  • 1.  Automatically Extract Data From D365 F&O

    Posted Jun 01, 2020 05:04 PM
    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
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Automatically Extract Data From D365 F&O

    Posted Jun 02, 2020 02:41 AM
    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
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Automatically Extract Data From D365 F&O

    SILVER CONTRIBUTOR
    Posted Jun 02, 2020 05:24 AM

    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
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Automatically Extract Data From D365 F&O

    Posted Jun 02, 2020 06:42 AM
    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
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: Automatically Extract Data From D365 F&O

    SILVER CONTRIBUTOR
    Posted Jun 04, 2020 04:23 AM
    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
    ------------------------------

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role