D365 Finance & Operations and Dynamics AX Forum

 View Only
Expand all | Collapse all

Export of Data Entitites to BYOD database in Azure

  • 1.  Export of Data Entitites to BYOD database in Azure

    TOP CONTRIBUTOR
    Posted Feb 08, 2018 06:16 PM
    We recently setup a Sql database in Azure for export of D365FO data entities.  It was relatively easy to stand up and we are finally able to do some reporting against our production data without having to do SSRS customizations.  We have discovered a couple of glitches, so I thought I would post some tips.

    1.  Firstly, we've been a little frustrated with what data entities are not available yet.  After many back and forths with Microsoft and our Partner, it seems that your options are to create custom data entities yourself or request the data entities be added by Microsoft through the Ideas website.  We were initially told to open support tickets with Microsoft, but they simply say that we will have to do a customization and close the ticket.

    2.  You will probably have some data entities that throw errors when you attempt to export to your BYOD database but will export to excel or a csv.  This indicates that you are getting a primary key violation on your BYOD database.  This is technically a bug and should be reported to Microsoft, but a workaround is to delete the primary key on the table in the BYOD database after you publish the data entity.  You should then be able to export successfully.

    3.  You will also probably have some data entities that will say they exported successfully with a row count in the execution details, but when you query the target table in the BYOD database, it will be empty.  To fix this, you need to select Full push only and importable fields only when adding the data entity to a project.

    Hope some of you can make use of this information.

    ------------------------------
    Mark Schurmann
    Accounting Systems Manager
    Automobile Protection Corp
    Norcross GA
    ------------------------------


  • 2.  RE: Export of Data Entitites to BYOD database in Azure

    Posted Feb 09, 2018 10:03 AM
    Good feedback Mark!  We are creating a substantial data warehouse by leveraging BYOD as well.  Given we are a retail company some of the tables we desire are monster in size, and this resulted in a P6 sizing to support them.  We also found that we had to create a lot of custom data entities as well.  I would say anyone taking this approach should probably learn how to create them so you can really leverage the architecture.

    ------------------------------
    Jason Meyer
    CIO
    Bomgaars Supply Inc
    Sioux City IA
    ------------------------------



  • 3.  RE: Export of Data Entitites to BYOD database in Azure

    Posted Feb 20, 2018 03:20 PM
    Mark,

    This is good information.  For others looking into this, it may be useful to know that many of the out of the box entities are setup to be company-specific.  This means that it will require an export job to be setup per legal entity.  If you build your own data entity, you can bring in the dataareaid with a different name and then this allows you to export the dataareaid but not have company-specific exports (one setting to change too).

    It's also important to set change tracking on the entities so an incremental export is supported.

    To feed our data warehouse solution, we have built 300+ data entities that are copies of underlying D365 tables that we then export to an Azure SQL DB.  I know how much "fun" can be involved!

    Noah

    ------------------------------
    Noah Kluge
    Sr. Manager: Business Intelligence
    Armanino
    Wichita KS
    ------------------------------



  • 4.  RE: Export of Data Entitites to BYOD database in Azure

    GOLD CONTRIBUTOR
    Posted Feb 21, 2018 07:28 AM
    I also want to share that Delete operations for incremental load are not supported until Platform Update 11.  So make sure you upgrade to atleast PU11 before starting your BYOD setup.

    ------------------------------
    Steeve Gilbert
    Software Development Supervisor
    Boa-Franc S.E.N.C.
    QC, Canada
    ------------------------------



  • 5.  RE: Export of Data Entitites to BYOD database in Azure

    Posted Sep 13, 2021 04:56 PM
    Does anyone have feedback for when the export is missing data? We have an BYOD Azure database and a publish job setup for some entities. Change tracking is turned on for those entities (especially the large entities like GL Transactions). We also set a filter in the export to only include the last few months to try to limit the data. No matter what we do, our accountants come back to us with "your export is missing transactions" every few months.

    Sure enough, we go look, and the data is missing in the output. At this point we disable the entity, clear out the table, setup again, and then everything starts working again...and the missing transaction is there.

    It is just such a huge pain to have to disable/clear/enable the entities when there are problems with missing data. We rely on this good information and the exports have been problematic at best.

    ------------------------------
    Regards,
    Geoff Faulkner
    Director of I.T.
    Musco Family Olive Co
    ------------------------------



  • 6.  RE: Export of Data Entitites to BYOD database in Azure

    SILVER CONTRIBUTOR
    Posted Sep 14, 2021 08:01 AM
    We've had some intermittent problems with entity records not exporting when set to incremental.  It seems like certain entities are more prone to issues than others, but it's a rather difficult issue to trace down.  In our case, just doing a full export on the problem entity resolves the issue (no need to disable it or reset the table or anything like that), so I have just scheduled periodic full exports for the entities that we've had problems with.  Our overall exports for every entity are incremental and run hourly.  In addition, we have a full export job running every 12 hours just for entities that have had incremental export issues in the past. This cadence has cleared up our problems so far.

    Please let me know if I may provide additional information.

    Best,
    Ben

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



  • 7.  RE: Export of Data Entitites to BYOD database in Azure

    Posted Sep 15, 2021 09:18 AM
    I've seen this in some of my Incremental exports.  On the entity I was having trouble with, I noticed that Change Tracking is set to "Primary Table".  I wonder if some of the changes we're not seeing are occurring in other tables in the underlying view?  I plan on setting Change Tracking to "All Tables" in test, but the problem is so intermittent that I'm not sure when or how I'll know if that fixes it.

    What are your Change Tracking settings?

    Scott Castleberry
    TDIndustries, Inc.

    ------------------------------
    Scott Castleberry
    TDIndustries
    Dallas TX
    ------------------------------



  • 8.  RE: Export of Data Entitites to BYOD database in Azure

    SILVER CONTRIBUTOR
    Posted Sep 15, 2021 09:45 AM

    Ours is a mix, but the overwhelming majority are 'all tables'.  There are a couple with 'primary table' that are either single-table entities or instances where we really do only want to see updates on the primary table.  There are a couple with 'custom' that are built on views.  The rest are 'all tables' as that is basically our default. 

     

    BENMORGAN

    520.293.1488 ext. 7472

     

     






  • 9.  RE: Export of Data Entitites to BYOD database in Azure

    Posted Sep 15, 2021 11:00 AM
    Ben,

    That helps a lot - it looks like I don't need to update my Change Tracking settings.

    Thanks

    ------------------------------
    Scott Castleberry
    TDIndustries
    Dallas TX
    ------------------------------



  • 10.  RE: Export of Data Entitites to BYOD database in Azure

    SILVER CONTRIBUTOR
    Posted Sep 15, 2021 11:28 AM

    Glad to help.  I don't believe the change tracking type is the source of the issues we've seen.  Based on the evidence I've seen, it is more based on timing.  For example, many of our missing items were updated within a particular date range, which suggests there was some hiccup that occurred during that time.  Other missing items may have been similar, and doing a periodic 'full' refresh waylays those types of issues.  It appears there was also an issue within the change tracking system itself as identified in KB 4611858:

     

     

    This is available for version 10.0.20 and I'm working on installing it now but will probably leave the periodic full exports in place anyway just for safety. 

     

    Please let me know if I may provide additional information.

     

    Best,

    Ben

     

    BENMORGAN

    520.293.1488 ext. 7472

     

     






  • 11.  RE: Export of Data Entitites to BYOD database in Azure

    Posted Dec 20, 2021 12:32 PM
    We too have seen intermittent problems with records that are missing in the BYOD incremental export. I have a couple jobs running the exports at different frequencies - the highest frequency being every 15 minutes. All of my entities are custom, having no filtering or dependencies, and are basically a straight mapping of the table data source. All are configured for incremental push with change tracking set on primary table. We began to take notice of this issue recently after upgrading to 10.0.22 (PU46). After a full push, everything is in sync. I have an exact match of record counts between entity (count obtained via OData service $count) and a count of the records in the SQL Server target table. Monitoring these counts as closely as I can with a csharp tool i developed to run after the DMF execution (not an exact science), i can observe the results over time to see when the mismatched count starts consistently reporting a non-zero count difference and pinpoint a particular execution ID. On the target database I set up an Extended Events session to record the sql statement begin/end events. There is a pattern among the executions identified as problematic. During a normal "good" execution of the incremental push, three things happen for each entity:

               1) DELETE of records from the table where the execution id is the current execution id
               2) BULK INSERT with TABLOCK the new or changed records
               3) DELETE records with the same entity key as inserted records where the execution id is NOT the current execution id

    On the problem executions, 2 is happening before 1. So the records are inserted and then immediately deleted within a tiny fraction of a second. It is seemingly random. I think there is a race condition being created where the sequence of these transactions is not being properly managed. In all cases, executing a full push has corrected the problem temporarily, but over time it can occur again. I have a case open with Microsoft and have logged these findings in detail.​​

    ------------------------------
    Ryan McCoy
    Griffith Foods
    Alsip IL
    ------------------------------



  • 12.  RE: Export of Data Entitites to BYOD database in Azure

    Posted Dec 20, 2021 12:58 PM
    I should have also mentioned we are running D365 FinOp on premise so Azure Sql Database is not involved, but the issue may still apply.

    ------------------------------
    Ryan McCoy
    Griffith Foods
    Alsip IL
    ------------------------------



  • 13.  RE: Export of Data Entitites to BYOD database in Azure

    Posted Dec 21, 2021 09:53 AM

    Hi Ryan,

     

    I believe we had to move away from incrementals and go to full pushes for the same reasons. This also introduces other problems but it's the best option we have until we plan our potential move to the data lake.






  • 14.  RE: Export of Data Entitites to BYOD database in Azure

    Posted Dec 21, 2021 10:11 AM
    Given the number of entities I have, frequency required, and volume of data, I don't realistically have the option to move to 100% full push exports. I am going to test a workaround for this problem using INSTEAD OF DELETE triggers on the target tables. Not something I really want to do, but I am desperate at this point.

    ------------------------------
    Ryan McCoy
    Griffith Foods
    Alsip IL
    ------------------------------



  • 15.  RE: Export of Data Entitites to BYOD database in Azure

    Posted Dec 22, 2021 12:47 PM
    We've begun using additional strategies for some of our frequently changing data entities. In both these methods you stop using the BYOD publication process, and switch to using code for the data sync:

    • Querying against the OData entities for recently changed, which has required building some custom entities to expose the dates. You can either use that data to validate for missing rows, or to using it directly to build your BYOD data sets.
    • Using the business events to track a change happening, firing an event to an Azure queue, and having that drive an OData call (the data with the even itself is usually not everything we want) and a SQL table update on our BYOD server.
    I see a few entries in the fixes included in 10.0.23 which look like they may help with the BYOD issues, too. 561552, 579483

    ------------------------------
    Brent Hawthorne
    Sr Manager of Application Development
    Daisy Brand
    Dallas TX
    ------------------------------



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