1. 

   Ubereil, Member

   When you look at a report for the first time I assume that the database query is run and then the result of the query is presented to the user. What I wonder is: the next time you look at the report, will the query be re-run or has the result been stored somewhere?

   I wonder because we're thinking of using MyDBR to create reports of how our services was used on a certain day. One such report will be preformed on a monthly basis and will be a report on how our service was used in the last month. Given that generating this report takes quite a while purely because of the database, it will be unsustainable if the report is recreated every single time we look at it (because you don't want to wait three hours when you want to quickly look up how many times the service was called in May or some such) - especially since nothing will have changed since we looked at it last time.
2. 

   myDBR Team, Key Master

   myDBR runs the report based on data on the database.

   If you wish to store the previously run report' results, you have multiple options: you can store the report output (HTML/PDF), create a cache in the database to store the results and use that cache for subsequent queries. myDBR proceudural approach makes it quite easy to do things like caching.

   --
   myDBR Team
3. 

   Ubereil, Member

   Thanks for the reply.

   When you say you can create a cache in the database, are you referring to the myDBR database or the database myDBR collects data from?
4. 

   myDBR Team, Key Master

   You can use either of them. If the cache is just for myDBR, you can use myDBR database to store the cache so your original database stays intact.

   --
   myDBR Team
5. 

   Ubereil, Member

   Is there some kind of myDBR functionality that lets you automatically store the result of a report so that this stored result will always be used when calling that report or do you have to set this up manually somehow? And if you have to set it up manually, do you know if there is some kind of example or tutorial somewhere?
6. 

   myDBR Team, Key Master

   Hi,
   there is no automatic functionality as reports can contain pretty much anything. The presudo syntax for such caching would be something like this:

   create procedure sp_DBR_myreport( myparam int )
   
   begin
   
   declare vCnt int;
   
   select count(*) into vCnt
   
   from mycache
   
   where value = myparam;
   
   if (vCnt = 0) then
   
       insert into mycache ( col1, col2, col3 )
   
       select col1, col2, col3
   
       from mydb.mytable
   
       where value = myparam;
   
   end if;
   
   select col1, col2, col3
   
   from mycache
   
   where value = myparam;
   
   end;
   
   

   
   --
   myDBR Team
7. 

   Ubereil, Member

   One last question: if we go with storing the output - is it possible to somehow automate this process? IE is it possible to write scripts that goes to a report page and stores the output as PDF or something like that, or is the only option that someone logs into the page, runs the report and then exports the report to PDF?
8. 

   myDBR Team, Key Master

   If you are willing to store/distribute the generated PDF's, you can of course reuse those. myDBR does not offer such caching.

   Storing the cached result to the database is most likely the easiest way. Without knowing the exact database structure / amount of data in question and the complexity of the reports, it is difficult to be more exact.

   --
   myDBR Team
9. 

   Ubereil, Member

   I think you misunderstood my question. We are willing to distribute the PDF's ourselves (assuming we find the price to be right - which is a question I don't have enough knowledge to answer) - the question is what options we have in getting hold of them. Is manually logging into the myDBR page, manually accessing the report and then manually downloading the PDF the only option, or is it possible to get hold of the PDF through some means that is automatable? For instance, can you go to an URL like http://url/myDBR/MyReport?param1=5&downloadPDF=true (I'm fully aware this is pseudo syntax, it's the idea I'm after) and you download a PDF (if that is possible we can write a script that accesses that url once a month)?

   The caching might work, but it has the drawback that we need to implement it ourselves. Since the big advantage of using myDBR is that we hardly have to do any work ourselves at all (save for installing it and writing a few SQL-commands) that makes it a fairly unattractive choice. All depending on how much work it is to generate reports that you never have to wait very long for, of course - if it is trivial it's not a problem. It's hard to tell how much time we have to be invested in it, though.
10. 

    myDBR Team, Key Master

    All myDBR reports (and other actions) are URL accessible. So every report is accessible through an URL. By default the the report output format is HTML, but you can request other formats by adding 'export'-parameter which defines the format.

    If you wish to get an PDF, you add a '&export=pdf' to the URL (other options include xlsx, json, csv, xml and php_serialized).

    As for the caching, if you keep the caching logic in the database, you need to do some work beforehand (a caching in report itself or a cron job to create the caching data), but you save time and effort, when you do not need to handle the distribution of the PDF's.

    The best option depends of course on number of users you are serving and the complexity of the data. Keeping the logic and the data in the database as long as you can saves you from hidden cost of PDF distribution and management.

    --
    myDBR Team
11. 

    Pena, Member

    Hi! I'm wondering the same thing.

    Here's what I need to do. I need to pull a lot of data from our database and display it in a single report. Something like a KPI report if you will.

    Is there a way to save results of these reports in myDBR?

    I would rather not create a summary table/cache in my own database and running all of those queries at once won't be possible. Let's say I would time this and run all of the queries once per day and save the results for each day, negating the need to run the heavy queries every time I need to see old data.

    In the example above you showed a table called mycache, what I'm wondering is that is this something I can create in the myDBR database and if so, how can I go about doing that.

    Some explanation how this works would be most appreciated!

    Thanks!

    -Pena
12. 

    myDBR Team, Key Master

    Hi,
    sometimes a summary table / cache table is the best way to go to speed things up. If you do not need absolutely latest live data and your data amount / reprot complexity is such that generating a report will take time and resources, you can consider using summary table / cache table.

    You can create the summry table to myDBR database or in your own database. Which place is better depends on your setup, so there is no one right answer. To create the cache table in myDBR database, you just create it as any other table. myDBR uses it's own naming scheme, so it will not touch your table.

    The summary table/cache can be generated using an extra cron job (it can be a separate myDBR report that you call from cron) or you can fill the cache within the report with method described above. Again, choosing between separate cron job / caching report, depends on complexity of the calculated data. If your need is simple enough, you can use a caching report. If your summary calculation is complex and/or affects multiple cache tables, you might want to consider a separate cron job.

    As said, keeping the cache logic and the data in the database as long as you can, saves you from hidden cost of PDF distribution and management and you can always be sure the data is correct.

    --
    myDBR Team

Reply

You must log in to post.