D365 Finance & Operations and Dynamics AX Forum

Expand all | Collapse all

Strategy for Moving Data from D365FO to BYOD to an on-Premises Data Warehouse

  • 1.  Strategy for Moving Data from D365FO to BYOD to an on-Premises Data Warehouse

    Posted Jun 24, 2019 09:51 AM
    ​We are exploring best practices regarding a strategy for moving data from D365FO to BYOD to an on-Premises Data Warehouse. While researching the issues, it appears the recommended approach is to create separate custom data entities for each AxDB table that you want to bring into your data warehouse. For a variety of reasons, out-of-the-box data entities are not recommended for use in an external data warehouse reporting environment. Some of the reasons advanced for not using out-of-the-box data entities include: these entities were designed to load data up into D365 and not for reporting, some, like the customer entity,  have an extreme number of fields containing over 200 columns from nine different tables, setting up incremental loads may be problematic, they are not as flexible as custom data entities, and out-of-the-box entities don't exist for many tables.

    What are your thoughts regarding a good approach.

    Also, now that Microsoft is moving towards having as many as eight D365 releases in a year, how do you manage change and minimize effects on a downstream data warehouse? For example, if we are creating data entities from core AxDB tables, is there a way to prevent synchronization problems if Microsoft decides to revise the D365 table structure in future releases?

    Thank you for your advice on this.

    David

    ------------------------------
    David Greenberg
    Illinois Tool Works
    Troy OH
    ------------------------------


  • 2.  RE: Strategy for Moving Data from D365FO to BYOD to an on-Premises Data Warehouse

    Posted Jun 25, 2019 10:36 AM

    Hi David

    We are currently developing a DataWarehouse in Azure where we are loading D365 out of the box data entities, as well as custom entities.

    Our approach is to move the full BYOD entities into an Operational DataStore (ODS), which is in our case generated by a DataWarehouse automation tool called BIGenius. From the ODS, we are moving/loading only the needed attributes into our Core DataWarehouse (which is also generated by BIGenius and directly linked to the ODS).
    The BYOD export is running on incremental basis, where we also discovered issues with the export of entities on incremental basis. That's something we are monitoring closely, but still need to gain more experience. Since we are running daily loads for D365, we would have the flexibility to switch to a full load for entities which are problematic…. however, we trust in Microsoft to fix existing issues here.

    I understand your fears of data changes based on the periodic release cycles. However, with the approach of the automatically generated ODS environment, you would be able to catch most changes. Of course, you still have to run updates of your DWH environment parallel to the D365 release…however if I understood correctly, you can skip a certain number of D365 releases and only need to run about 3 updates a year…. but this is something you need to validate.

    I also understood that Microsoft is pushing the functionality of the Entity store export to an Azure Data Lake V2. However, last time I looked at it…it was still in Preview. Also I assume it will take some time until it is really an alternative for the BYOD export.

    hope this helps you make your decision. Would be very interested in hearing other strategies and experiences with D365 data access.

    kind regards

    Andreas



    ------------------------------
    Andreas Fitzi
    SIGVARIS
    ------------------------------



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