D365 Finance & Operations and Dynamics AX Forum

Expand all | Collapse all

BI and AX Database

  • 1.  BI and AX Database

    Posted Feb 01, 2019 01:39 PM
    We are currently on AX 2012 R3 C12, and are looking at minimizing the impact of reporting on our AX database.

    Currently, our data warehouse hits our database at certain times, then converts data needed for BI needs, etc etc. We are looking at ways to get the data warehouse from hitting the main database at all, and have come up with 2 solutions.

    1st: Log Shipping. Set up log shipping on the AX 2012 database. Every x time whatever changes were done get moved over to a copy. We then point the data warehouse to the copy, meaning they get current data without hitting the AX 2012 database.

    Pros: Minimal hit on database. Log shipping is very small, and only updates what changed. Automated, any changes to the database, even code wise, will change and update. Don't need to fiddle with any connections with database changes. The cloned database is guaranteed to be read only via setup. Meaning that if someone did funky things they shouldn't to it, it won't impact prod data. Most importantly, the data warehouse hits the cloned database, which helps the performance on the AX database. Outside of AX. Don't have to worry about AX crashing, it is based on the SQL database.

    Cons: Wont update the copied database if there are connections for some reason. Might be how it was set up, but if the data warehouse hits the cloned database, it will pause all updates until after the data warehouse refreshes the data. It is outside of AX, less to no control.

    2nd: Entity Store (file:///C:/Users/sharm/Downloads/Entity%20store%20Microsoft%20Dynamics%20AX%202012%20R3.pdf). Use entity store to export entities at set time, have data warehouse hit the entity.

    Pros: Built into AX. Only incremental changes set. Might be easier to upgrade when we change to D365.

    Cons: Have to change the entity if database is changed. Must change if any coding changes are done / new fields are added.

    What I haven't been able to find is a performance comparison of the two. Has anyone tried both and found one better then the other? Are there things that we should be looking at besides these two options?

    Thank you for your insights, it is greatly appreciated!

    Robert Shannon
    ERP Analyst
    Leatherman Tool Group
    Portland OR

  • 2.  RE: BI and AX Database

    Posted Feb 04, 2019 12:38 PM
    Hi Robert,

    Does the BI tool that you're working with support OData integrations? As I understand it, AX 2012 has an OData Query Service that you can enable and you should be able to access your AX data directly from the BI (see: https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/data-entities/odata).

    If the tool you're working with doesn't support OData, you have a need to utilize your specific data warehouse, or you don't want to directly access AX, you might want to consider another replication option. Many tools support replicating directly to a data warehouse and include transformation functionality that would be able to shape your data as a part of the replication. Most replication tools support automated, incremental replication as well, so you'd be able to schedule the replication and only pull the changes.

    Jerod Johnson
    Technology Evangelist
    CDATA Software
    Chapel Hill NC

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