D365 Finance & Operations and Dynamics AX Forum

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

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



  • 3.  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
    ------------------------------



  • 4.  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
    ------------------------------



  • 5.  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
    ------------------------------



  • 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 30 days ago
    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 30 days ago

    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 30 days ago
    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 30 days ago

    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

     

     






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