Core Data model design for charting data

Question

I am sorry for the verbose explanation, I do however feel it is necessary for me to explain the details so that I can gain help in the best way to create the correct core data model to fulfill my requirements.

I am simply trying to find out the best way to create my data model so that I can achieve the below in a quick efficient fashion, which is not currently possible with my current MySQL setup. I am using MySQL to return all the values from the server, but now wish to use CoreData to cache this data, and only download the new dated data.

I have a database that stores datasets like this:

{
    "Agent_Id" = 32;
    "Order_FinalisedDate" = "2014-05-17 03:39:54";
    "Order_Id" = 2596;
    "Order_ReferenceNumber" = "Pavan/0905/016";
    "PieChart_Id" = 13585;
    "PieChart_QuantityATOP" = 150;
    "PieChart_TotalAmount" = 52500;
    "Product_Id" = 20;
}

I have 13,585 of these data sets flying in from the server every time I load the viewController. It takes over 1 and a half minutes to process the data retrieved from the server. This is highly inefficient and I seek to create a Core Data model to help me with the processes to cut down significantly on time.

This is the current process that takes place without Core Data

1. I am only interested in the last 12 rolling months just like how banks return your accounts for the last 12 months for example. So I get a live dynamic array from the server in JSON format, copying them nicely over to its custom BankDateDataModel data model in an array called monthsChartDataDictionary which stores the data in this format yyyy-MM.

2. I then go through all the sales and grab and categorise them if it has a date that can be associated to a matching date in the monthsChartDataDictionary array.

   //Go through each data set
   for(id key in JSONDictionary){
       //First lets store each data set which we will use for another process.
       PieChartSliceDataModel *pcs = [[PieChartSliceDataModel alloc] initWithJSONData:key];
       [allPieChartDataArray addObject:pcs];
   
       /**
           Check to see if we have a date entry in the format of `yyyy-MM`
           If there is a 
       */
   
       if([monthsChartDataDictionary objectForKey:pcs.PCS_StringOrderFinalisedDate]){
           [[monthsChartDataDictionary objectForKey:pcs.PCS_StringOrderFinalisedDate] addObject:pcs];
       }
   }
   

   And this is essentially what is created - monthsChartDataDictionary. a Dictionary which has its keys in the format of yyyy-MM and an array of data sets in their associated keys.

   {   //An nsdictionary holding over 13,500 elements.
       "2013-05"=(..,..,..);
       "2013-06"=(..,..,..);
       "2013-07"=(etc);
       "2013-08"=();
       "2013-09"=();
       "2013-10"=();
       "2013-11"=();
       "2013-12"=();
       "2014-01"=();
       "2014-02"=();
       "2014-03"=();
       "2014-04"=();
       "2014-05"=();
   }
   

3. A for loop is then run to achieve two things at once, see a) and b) below:

   a) For each month in the monthsChartDataDictionary dictionary, I get the total sales for that month by tallying the PieChart_TotalAmount for each dataset. This is used to create a line chart showing total sales across the year.

   b) A new dictionary is made - finalPieChartData where each key is in the format of yyyy-MM and the values stored are an array of products that show the total sale values for each product. This is so that a pie chart can be made showing sales across products within any given month. Where 12sp B - 250 is the name of a product.

How can I best represent all of this in a core data model?

Im thinking if theres a way to store all the required data, with total sales values too, and seperate sales across products for given months... all stored in core data... or if its best to make these calculations when they are retured from the core data database, im guessing this will only cause more delay.

I have this so far:

My logic in creating this was based on the following thoughts:

1. That I could have a database that stores all the individual datasets
2. Another table that stores the total monthly sales for the line charts, so I dont have to keep performing the calculation, unless ofcourse I need to update sales figures for the latest month as I obtain new sales that are still taking place in the same current month.
3. And along with that montly sale core data model, there would be an array of pie chart slices that represent the total sale for each product id within any particular month. This will mean that I dont have to keep processing the calculations again and again.

Is this correct? Is there an efficient way to store everything?

Update 1: updated the proposed core data model in an attempt to make things more efficient once implemented into the application

Answer 1

For an Order Entry system you need a Month to date (MTD) and Year to date (YTD) table in the server side with the following fields: date updated, total sales amount and total quantity of orders. The tables should be updated live with the orders or updated nightly by batch. The app needs only download the MTD data to generate the required sales charts. There could be some value in creating a local store for the MTD tables but with the reduced amount of data, it may not be necessary.

If filtering is required, consider creation individual tables (MTD) for the required filters instead of trying to tabulate and recompute everything in the app.

Answer 2

NSDateFormatter is very harmful for the performance. If you are using it in your 1st during mapping you should minimize it's allocations. You can provide code snippet of 1st step if you wan't

Also you can use in xcode product-profile-time profiler. It can help you to track low performance operations.

As for working with web-services and core data take a look an restKit

Also you might peace of advice under this links from raywenderlich on working with web services and core data part 1 part 2