This is a public Forum  publicRSS

Topic

    Pavol Procka
    Report stopped working due to amount of data.
    Topic posted July 10, 2017 by Pavol ProckaWhiz, last edited July 17, 2017 by Danette BealGuru 
    72 Views, 9 Comments
    Title:
    Report stopped working due to amount of data.
    Content:

    Hi,

    I have a problem with one of our scheduled reports. It is supposed to show aggregate of all agent achievements in monthly intervals for the last 6 months This used to work until about 4 weeks ago (we did not change anything since), but lately it stopped delivering the report with the error:

    "Unable to deliver scheduled report: Executing view query failed.

    Query processes too much data - add filters to limit the query or remove tables that are not required."

    Normally, I understand why - it says it in the error message - the amount of data, however I am wondering how come this was not a problem before (we have been using RightNowfor about 4 yrs now and the report used to come as scheduled, once a week until 12/06/2017 (that is the last one that was sent)?
    I am able to run it to a max. of -110 days, but with -115 and more ( I would need -180) it gives the error.

    Also, if I just create a simple report showing all transactions (was trying to see what is that big data) from the last 180 days, this works, brings cca 18 million lines, so I doubt it is really the amount of data that is causing the problems in the filtered one. Even if I tried to remove the other columns and only left Response Sent (next to Agent and Date/Time Interval), the report will simply not run.

    I am attaching the report definition, in case somebody has a suggestion what to replace the Response Sent, Trashed, Queued and Created column definitions with?

    Please note that I already tried to create a dashboard with 4 separate reports in which I simply used filters to specify what transactions types I want, but it behaves in the same way (can run it to about 4 months back, but not 6 months.
    Btw. I am on the Report Database, not Operational.

     

    Many thanks

    Pavol

    Version:
    Service Cloud May 2016

    Answer

     

    • Gregg Kosel

      Have you referred to the "Analytics Performance Best Practices" guide yet? I use this all the time when I run into this issue and am usually able to find and resolve the problem.

      Here is the link:

      What are some best practices for creating reports and using the analytics features in Oracle Service Cloud?
      https://cx.rightnow.com/app/answers/detail/a_id/2053/kw/Analytics%20Performance%20Best%20Practices

      Here are a couple of others that you might find helpful:

      Hopefully, these resources will help you find a solution.

      Gregg

    • Sheri Hastings

      Can you just use 2 reports filtered by date? The first report will show the first 90 days and the second report will show the second 90 days.  I realize that does not answer your question but it would at least get you around the need to report 180 days?

       

    • Pavol Procka

      Thanks both for the suggestions.

      Gregg - I went through the best practices and even tried to explain my user that he should not really need the aggregate date for such a long period - as we are sending him this report weekly - so he could simply have a monthly or a weekly report and manually add the data to one sheet he can keep - but the user did not like this as it is more comfortable to be provided these values on one page. (He is trying to see a comparison of agent achievements month by month).

      What I wonder about is, how come my simple transactions report for 180 days works when it has to analyze and report 18 million lines? I understood the maximum rows a report can analyze is 5 million?

       

      Sheri - Because our user would like to be scheduled to receive this report every week I do not think I could incorporate your suggestion. No idea how I would go about setting the 90+90 days dynamically so that they are always 180 days from the day the schedule fired. Any suggestions there?

    • Gregg Kosel

      Pavol,

      Your report links the Transactions table which is very large without using an Outer Join and a script to reduce some of the unnecessary transaction records that are pulled into your data set.

      I would use the information in Transaction Code Descriptions area of the Oracle Service Cloud User Guide to filter out some of the unnecessary attributes or transaction types that are not needed for your report. 

      https://documentation.custhelp.com/euf/assets/docs/august2016/olh/wwhelp/wwhimpl/js/html/wwhelp.htm#href=c_apx_transaction_codes.html

      Hopefully, this will help.

      Gregg

    • Pavol Procka

      Hi Gregg,

       

      thanks for the suggestions. I do not have any experience with PHP/Custom Scripts in RN, but we do have a resource that might be able to help.

      Just wanted to confirm, am I understanding you correctly that I need a "Custom Script"?

       

      Many thanks

      Pavol

    • Danette Beal

      Hi Pavol,

      The first thing I would point to is database size. I haven't actually reviewed your xml, but I would be willing to bet your database is hitting a limit where its hitting the max capacity to run reporting. When you run it manually can you queue it? Can you run a report -110 days to -200 days? What happens?

      If you are indeed hitting max database limits, you need to start looking at filtering your reporting or different ways retrieve the data.

      Danette, Community Manager

    • Pavol Procka

      Hi Danette,

      If I run it manually, it tries to queue, but then after a few minutes it fails.

      I do believe the problem is with the database threshold as well, however I then do not understand how come I was able to run a report that returns 18million lines (only a Transactions table and column in it) and not run when I run it on the same Transactions table, but with filters and also other tables (where the inner joins should already decrease the load significantly). Also, I am sure that this must have been hitting the same tresholds even before the 12th of June (until when the report worked fine) as we are creating about a 100 000 Transactions a day for about 2 years now.

      If the threshold is 5mil, how can I be able to get 18 mil results?

      Many thanks

      Pavol

    • Danette Beal

      Hi Pavol,

      I removed your xml file because it contained your schedules. If anyone were to save these schedules in their own site, it would email out to the indicated email addresses. This also exposes those email addresses to anyone on the community. Once you remove the schedules, feel free to upload the xml again.

      After looking at this, you have account joined to transactions. If i'm understanding you correctly, it runs with just incidents > transactions but not Incidents>transactions>accounts?

      Danette, Community Manager

    • Pavol Procka

      Hi Danette,

       

      In the first place, thanks for your response.

      The thing is, I need the Accounts table in the report as the main purpose here is a comparison of the agents achievements over the last 6 months.

      So we need to be able to display the email incidents processed by all agents in a specific department for specific time. Basically if I remove the Accounts table, I will not be able to use the Acc. Country, Acc. Group and Acc. ID filters.

      Is there a way to display this in a different way?

      P.S. I also had a general query about the number of analyzed rows open in a separate thread (https://cx.rightnow.com/app/account/questions/detail/i_id/1496916/track/AvOp0wpXDv8S~cgPGkhy~6L0LHUqfy75Mv8T~zj~PP~a) where I received the suggestion to use "efficient composite index" to reduce the rows that are analyzed, but am not sure how I can change the indexing of my filters. Any suggestions?

      Many thanks

       

      Pavol