D365 Finance & Operations and Dynamics AX Forum

Expand all | Collapse all

D365FO and Atlas 6.1 Reporting Tool - slow single cell functionality

  • 1.  D365FO and Atlas 6.1 Reporting Tool - slow single cell functionality

    Posted Sep 19, 2018 11:50 AM
    ​We recently implemented D365FO and are using Atlas 6.1 as an ad hoc reporting tool.  We have noticed that the Atlas retrieves (pulls) are slow when retrieving data - especially with multiple single cell data pulls and when 2 tables are joined together (such as the general ledger and the vendor table).  Does anyone else use Atlas as an ad hoc reporting tool and experience slowness?  Does anyone have any tips for making the single cell data pulls faster?

    #Focus-D365UG/AXUG
    #BIandReporting
    #Finance

    Thanks!

    ​​​​

    ------------------------------
    Michelle Steffen
    Corporate Controller
    Jasper Engines
    Jasper IN
    ------------------------------
    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


  • 2.  RE: D365FO and Atlas 6.1 Reporting Tool - slow single cell functionality

    TOP CONTRIBUTOR
    Posted Sep 19, 2018 03:38 PM
    Hi Michelle, we use Atlas 6.1 but are still a AX2012 R3 user.  That said we have used Atlas since Atlas 5.  We do vendor reports tying a voucher or a check to a vendor through GL.  Only issue we found was with using AX data cube which are a little different than a SQL cube.  Doesn't sound like what your trying to do.   Have you tried looking on there support sight for the free templates you can download.  Thinking it be a good way to help determine if the performance issues is Atlas or D365.  I found there support to be excellent so if you really think it's performance I would open a ticket with them.

    ------------------------------
    Mark Zerr
    AX Technical Analyst
    New Sunshine LLC
    Indianapolis IN
    ------------------------------

    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


  • 3.  RE: D365FO and Atlas 6.1 Reporting Tool - slow single cell functionality

    Posted Sep 21, 2018 08:20 AM
    ​Thanks to everyone for the suggestions - we will look into these!

    ------------------------------
    Michelle Steffen
    Corporate Controller
    Jasper Engines
    Jasper IN
    ------------------------------

    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


  • 4.  RE: D365FO and Atlas 6.1 Reporting Tool - slow single cell functionality

    Posted Sep 20, 2018 04:36 AM
    HI Michelle,

    I can't comment on D365 as we're only on AX2009 but to help with speed in general:

    • Remove any fields from the report that aren't being used (or rarely used) - the less data you bring back the quicker it will be retrieved. 

    • Instead of joining tables within Atlas, it can be quicker two bring in two separate tables and reference between the tables using managed columns with Excel's lookup formulas (VLOOKUP, INDEX/MATCH, SUMIFS & COUNTIFS) or cached tables with single cell lookups.

    • Not sure if this is true in Atlas 5.1, I'd recommend avoiding using multiple single cell lookups within reports, as this results in a separate database lookup for every cell (use one of alternatives for joining tables given above instead). E.g. retrieving one cell I guess takes something in the order of 0.1 second, so if the lookup is embedded within a report bringing back 1000 lines is going to take 100+ seconds to get all the data. This is was major cause of slow reports here until we switched to using table caches and Excel formula based lookups. 


    ------------------------------
    Nik Lawrence
    ERP Co-ordinator
    Hill and Smith Limited
    Wolverhampton
    ------------------------------

    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


  • 5.  RE: D365FO and Atlas 6.1 Reporting Tool - slow single cell functionality

    SILVER CONTRIBUTOR
    Posted Sep 20, 2018 09:37 AM
    Michelle,

    Have you tried caching your reports?  We use ATLAS exclusively for our financial reporting. We significantly increased our retrieval time by caching our reports. Check out the Global Software website for instructions on how to cache your reports.


    ------------------------------
    Amy Akley
    Sr. Financial System Analyst
    XTRA Lease
    Saint Louis MO
    ------------------------------

    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


  • 6.  RE: D365FO and Atlas 6.1 Reporting Tool - slow single cell functionality

    Posted Sep 20, 2018 04:37 PM
    Edited by Gina Pabalan Sep 21, 2018 11:20 AM
    ​This is likely an OData issue.  The attached link brings you to a detailed 17 page white paper that outlines all the different ways to report on your data from D365 Finance and Operations - also pointing out some key considerations for each.  Enjoy - and glad to jump on a call with anyone who has any questions.

    What you need to know about data and analytics for D365 F&O.  http://bit.ly/D365BIV1





    ------------------------------
    Gina Pabalan
    Director, Business Intelligence
    Edgewater Fullscope
    Ft Myers FL
    ------------------------------

    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


  • 7.  RE: D365FO and Atlas 6.1 Reporting Tool - slow single cell functionality

    SILVER CONTRIBUTOR
    Posted Sep 21, 2018 06:59 AM

    Hi Michelle,

    There have been a couple of great responses here so far. Caching is key. I've seen a well built Atlas report using cache run many, many times faster than one where each cell is directly calling the data. This is true whether connecting via the Business Connector in AX 2009 and AX 2012, or via OData in D365.

    The other good suggestion is retrieving the data you need first, then combining it once Atlas has it in-memory in your Excel environment. Nik mentions a few methods for doing that. Nik - we need to get you up to Atlas 6.2, there's now also an option called a merge.

    Email me the template (jason.carter@insightsoftware.com) and I'll take a look at how your formulas are set up. We can also schedule a Webex session to walk through how to use caching to maximize performance of your Atlas reports if you are not already doing so. Alternatively, in our Atlas Customer Portal along with the templates are some training videos, the 2 hour "Advanced Reporting" session includes an overview of how to build and use a cache.

    Regards,

    Jason



    ------------------------------
    Jason Carter
    Solutions Enablement - Atlas
    Insightsoftware
    Dilworth MN
    ------------------------------

    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


  • 8.  RE: D365FO and Atlas 6.1 Reporting Tool - slow single cell functionality

    D365UG/AXUG ALL STAR
    Posted Sep 21, 2018 07:50 AM
    ​Definitely look at caching. that is the way you can improve your performance. I have seen great benefits in performance from doing caching on reports. The Wiki has some good articles and the one Gina sent the link for is excellent.

    ------------------------------
    Paul Martin
    Production Program Manager
    Elite Comfort Solutions, LLC
    Rutherfordton NC
    ------------------------------

    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


  • 9.  RE: D365FO and Atlas 6.1 Reporting Tool - slow single cell functionality

    Posted Sep 21, 2018 10:31 AM
    Edited by Gina Pabalan Sep 21, 2018 11:20 AM
    ​Thanks @Paul Martin!

    The White Paper will be updated periodically as new information and features are released.  If you are interested in being notified of the new releases, please find me on LinkedIn and message me.  Feedback from the community is appreciated, especially if it is non-theoretical and based on practical experiences with the solution!

    As luck would have it, I did publish an update this morning - here is the new link!

    http://bit.ly/D365BIV1

    Enjoy, and hope to see you all in Phoenix!

    Gina
    727-224-1245


    ------------------------------
    Gina Pabalan
    Director, Business Intelligence
    Edgewater Fullscope
    Ft Myers FL
    ------------------------------

    The first step toward cloud success. - Migrate from AX to D365 with expert guidance from Microsoft. I'm Ready


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