5 Replies Latest reply on Feb 2, 2017 3:30 PM by dennis.meek

    Aggregating calculation results to speed up subsummaries, charts, ExecuteSQL, etc.

    dennis.meek

      Hi All!

       

      I'm started working with FileMaker Pro 15 four months ago and I'm struggling with finding a way to aggregate and cache calculation results so that my subsummary reports and charts will execute faster.

       

      I read both the FM Basic and Advanced manuals, read through the bulk of the FM 14 Missing Manual text, and I still stuck. Honestly, must have missed a concept for the above.

       

      I have 500K rows of sales data consisting of the following fields: primary key (UUID), location code, product ID, sale date (month & year), Dollar sales, and unit sales.

       

      I'm trying to report on sales by location and also as a rolled up number by month, year, quarter, etc.

       

      Does anyone have an example or know where I can find a paper on how to aggregate the data and store it some place to help speed up the rendering process?

       

      Ideally, I'd like to script a way to update the data when I need it refresh the data.

       

      I'm running the app on a Mac, but have hopes of moving it to FM Server in the future.

       

      Thanks in advance,

       

      Dennis

        • 1. Re: Aggregating calculation results to speed up subsummaries, charts, ExecuteSQL, etc.
          philmodjunk

          Aggregate data on a set of 500 records really shouldn't have performance issues, but when that number grows to be a much larger figure, a "summary table" that stores aggregate values as simple number fields where one record in the summary table represents a group of records in the original table can make for much faster reporting--First did that with FileMaker 3 or was it 4....

           

          Note that keeping such data correctly up to date when data in the original records changes can be a real headache, but can be done. If your data is pretty static--and sales data usually is, it's not so bad.

           

          Here's a basic outline of the method I set up to summarize the line items off of purchase orders into records of a summary table. The business where I created this method still uses it today:

           

          Add a field to the original table that you can set to a value to "mark" a record as having been used to generate a summary table record. Then set up your script like this:

           

          Loop

              Find all records not marked as "summarized"

              Exit loop if no records found

              Find all records of the same "group" as the current record. (In our case these were line items for the same material)

              Create a new record in the summary table and use set field to set the number fields to the value of summary fields in the original table that compute the needed aggregate values (totals, averages...)

              Use Replace Field Contents to "mark" these records as Summarized

          End Loop

           

          Note that this script ran late at night while the business was closed so there have never been any record locking issues with the replace field contents step.

          • 2. Re: Aggregating calculation results to speed up subsummaries, charts, ExecuteSQL, etc.
            mikebeargie

            *note*

             

            I have 500K rows of sales

             

            500,000 is a bit more beefy than 500.

             

            The method described is correct in terms of storing summarized data.

             

            I would highly recommend that you move to FileMaker server as soon as possible. Doing so opens up the capability of using "perform script on server", which will allow you to offload the "refresh" of the aggregate data to the server while the client continues on.

             

            Your data looks pretty vanilla though, aside from the sort operation for 500k records, you may want to revisit the indexing, and the difference between stored and unstored calculation fields.

             

            Defining field indexing options

             

            you may also get value of potentially converting some calculation fields to auto-enter number/text fields to gain performance.

             

            Researching in the forums can also lead you on to developing for performance. Try this thread:

            Design principles for performance

            • 3. Re: Aggregating calculation results to speed up subsummaries, charts, ExecuteSQL, etc.
              philmodjunk

              Thanks Mike, I did miss that "k", not that it affects my response as I assumed that this was needed for very large record counts. In the solution where I originally set up this approach, they routinely pull up cross tab reports showing monthly subtotals and averages of a material or group of materials purchased comparing these amounts over a 5 year span. The business generates a bit under a thousand records a day in the original table and operates 6 days a week. The summary table condenses the day's purchases down to less than 20 records so the efficiency savings here is quite large.

              • 4. Re: Aggregating calculation results to speed up subsummaries, charts, ExecuteSQL, etc.
                siplus

                What I can suggest to try is the following:

                 

                1) define 2 summary fields, Total( Dollar Sales ) and Total ( Unit sales ) in your data table.

                 

                2) create a table called Report. Add gStartDate, gEndDate etc. to it.

                 

                3) create a global field, gKey, in the Report table, and a TO called Totals, which is based upon your data table.

                 

                4) create a relationship between gKey in Report and UUID (primary key) in the Totals table.

                 

                5) in a script, fill gKey via SQL, with the ID's of records from the data table which meet your search criteria.

                 

                6) Look at the summary fields from 1) as seen via the relationship Totals that you defined in 4). They are your totals.

                 

                7) Put all the totals you care about in $$Variables and create your reports in the Report table, using merge fielding of the $$vars.

                 

                It works quite decently for me when reporting on big tables.

                2 of 2 people found this helpful
                • 5. Re: Aggregating calculation results to speed up subsummaries, charts, ExecuteSQL, etc.
                  dennis.meek

                  siplus,

                   

                  Thanks for the step by step. It's helping me work through the solution. If you don't mind I have a couple of questions.

                   

                  For the SQL script, I'm filling gKey like a list value or single row per record like a virtual list? Maybe a different way of asking is, will multiple UUID's be contained in a single "cell" that creates a form of aggregation. I'm new to this and trying to understand.

                   

                  Could you provide a little more detail to points 6 and 7?