Importing data into Frepple

[Bruce Holt]

I am not familiar with Python, Django or JSON but would like to be able to import data  from CSV files starting with an empty database using a windows batch file, not a menu import function. I’m running FREPPLE in Windows. Any help on this would be grateful.

 

From: freppl...@googlegroups.com [mailto:freppl...@googlegroups.com] On Behalf Of Tim
Sent: Friday, September 14, 2012 3:20 PM
To: freppl...@googlegroups.com
Subject: frePPLe 0.9.2 post installation notes

 

I'm creating this post for the benefit of anyone moving to frePPLe 0.9.2.  I have done so and here is what I discovered...

The JSON file format used to upload data to frePPLe has changed slightly in 0.9.2.  The JSON field "level" has been changed to "lvl" for the following tables:

Location
Customer
Item
Buffer
Resource
Demand

The calendar format is very simple now (So simple, in fact, that I no longer try to upload calendar information in my JSON file... I just create calendars through the user interface).  There are check boxes for Monday through Sunday.  There does not seem to be a distinction anymore for type of calendar, just a value box.  Apparently now frePPLe knows what type of calendar you are using by whether you are referencing it from Location or Resource.  For a Location calendar, I use a calendar with a value of 1 for true and 0 for false (I hope that is correct!).

For creating bucket dates, I recommend using the command line tool "manage.py" for Linux or "manage.exe" for Windows:

manage.py frepple_createdates --start 2013-01-01 --end 2017-12-31
manage.exe frepple_createdates --start 2013-01-01 --end 2017-12-31

Very easy!

The "sparkline" graphs in 0.9.2 are a nice addition to frePPLe.  They allow you to see a small graph for the Inventory, Resource, Demand, Forecast and Operation reports.  You no longer need to go to the Plan for each entry in these reports to see a big graph.  If these reports don't appear to be working, make sure your "Time" filter is set within the time window you are working with.  My time filter at one point was set within the year 2012 but my data was for the years 2013 through 2017, so there was nothing to show on the reports.

I look forward to frePPLe 0.9.3!

[Johan De Taeye]

>>I am not familiar with Python, Django or JSON but would like to be able to import data  from CSV files starting with an empty

>>database using a windows batch file, not a menu import function. I’m running FREPPLE in Windows.

 

Erasing the database from the command line is easy:  “manage.py frepple_flush”

 

To load data from CSV files, you’ll need to script a session with the web server using a tool like “wget” or “curl”. It allows you to automate the actions you’ld do to upload the csv-files from your browser.   It’s not trivial however, because of the CSRF-token that is used in django & frePPLe for security reasons.

 

If this is a usage pattern that also other people would like to use, it’ld be feasible to create a command like “manage.py load_csv item my_item_file.csv”. Feel free to submit an enhancement ticket at http://sourceforge.net/p/frepple/bugs/, or, even better, submit the code.

 

>> The JSON field "level" has been changed to "lvl" for the following tables:

 

The level/lvl field is actually computed by frePPLe. It’s actually better NOT to specify it in your input data.

 

>>There does not seem to be a distinction anymore for type of calendar, just a value box.  Apparently

>>now frePPLe knows what type of calendar you are using by whether you are referencing it from Location

>>or Resource.  For a Location calendar, I use a calendar with a value of 1 for true and 0 for false (I hope that

>>is correct!).

Yes, that’s correct.

 

 

Regards,

 

Johan

[Tim Sharpe]

Bruce,

The JSON format is not too difficult.  It's data enclosed by two brackets and curly braces.  It is basically "fieldname" : "data", "fieldname2" : "data2", etc.

You could open up one of the sample JSON files that comes with frePPLe and copy & paste the JSON data into this website, which will format the data into a more readable style:

http://www.freeformatter.com/json-formatter.html

After studying the frePPLe JSON files, I wrote a program to take data from a database and write out a JSON file to load into frePPLe.  Perhaps you could do the same.

Here's some sample JSON data:

[

   {


      "pk": "factory 1",

      "model": "input.location",


      "fields": {


         "description": "A factory that manufactures, stores and packages products",


         "available": "Working Days"

      }

   },

   {

      "pk": "factory 2",

      "model": "input.location",


      "fields": {


         "description": "A factory that stores and packages products",


         "available": "Working Days"

      }

   }
]

[Bruce Holt]

Thanks Tim. I should be able to create the JSON files. However, how do you then update a FREPPLE project from the JSON files?

 

I have extracted data from an ERP system and have built the FREPPLE CSV files in the required IMPORT format. Of most importance is now how to batch update the CSV files without having to use the menu IMPORT functions for each file. I’m in a WINDOWS environment.

[Tim Sharpe]

Bruce,

There are a couple of ways to load the JSON file that I know of:

1. Tools - Execute - Database using the user interface. Click the Erase button to clear the database (assuming you have one large JSON file with all the data in it). Then use the drop down button next to the Load button to select the JSON file from a list of JSON files. This list is created from JSON files found in:

C:\Program Files\frePPLe 0.9.2\bin\custom\freppledb\input\fixtures

(Or something similar for previous versions of frePPLe)

Place your JSON file with filename extension ".json" there. Example: "erpfile.json".

2. Use the manage.exe command line tool found in:

C:\Program Files\frePPLe 0.9.2\bin

manage.exe frepple_flush (This empties the database)

manage.exe loaddata erpfile1.json erpfile2.json

I load one large JSON file. However, apparently you can load several smaller ones, so clearing the database multiple times wouldn't be appropriate in that situation.

To get help, use "manage.exe help", "manage.exe help frepple_flush" or "manage.exe help loaddata".

Getting the JSON file to load took me three weeks, mostly because I started with the Windows version. I could never figure out how to get it to tell me what was incorrect about the JSON file I was loading. The documentation seems to indicate that you can turn on some form of debugging, but I never got that to work so I went with the Linux version of frePPLe, which logs helpful error messages when loading the JSON file (Perhaps you could ask Johan how to turn on Windows debugging messages).

Creating one large JSON file can be frustrating. Some of the tables in frePPLe link to other tables, and if you have extra records in one table that don't link to corresponding records in another table, the JSON load will fail. That is why error messages are important.

I found that the trick to building a successful JSON file is focusing on Operations. I used the SQL criteria for Operations to limit the number of records for the Buffer and Flow tables (I wanted to work only with active Operations in my ERP system, so I didn't want extra Buffer or Flow records for inactive Operations... that would cause the JSON load to fail).

You could try to do what Johan mentioned elsewhere and create a script that mimics a user working with the user interface, but that would be tricky in my opinion. Until the manage.exe command line tool can import CSV files, JSON is probably the way to go.

[Johan De Taeye]

>>The documentation seems to indicate that you can turn on some form of debugging, but I never got that to work so I went
>>with the Linux version of frePPLe, which logs helpful error messages when loading the JSON file (Perhaps you could
>>ask Johan how to turn on Windows debugging messages).

The loaddata command is provided with the Django framework. FrePPLe takes no credit or blame for it J

I experimented a bit and a google search confirms: debugging where loaddata fails is not easy to trace. No solution for that.

The Windows & Linux version of the command should normally give the same level of debugging info.

 

Yet another approach to load CSV files is to use some utility program of your database, and load it directly.

Eg PostgreSQL & MySQL have efficient commands to do this:

  http://www.postgresql.org/docs/9.2/static/sql-copy.html

  http://dev.mysql.com/doc/refman/5.6/en/load-data.html

frePPLe does very few validations that aren’t enforced at the database level as well, so this approach should be effective.

It will also be faster and provide better debugging output.

 

Johan

      "pk": "factory 1",

      "model": "input.location",

      "fields": {

         "description": "A factory that manufactures, stores and packages products",

         "available": "Working Days"

      }

   },

   {

      "pk": "factory 2",

      "model": "input.location",

      "fields": {

         "description": "A factory that stores and packages products",

[Tim Sharpe]

Johan,

 

I didn't think about using database loader utilities, and I've used one before in Oracle!  Bruce could probably automate his CSV files this way.  I think the only trick would be to load the CSV files in the proper order to satisfy the database validations.

 

Maybe something like this?

 

1. Generate bucket dates with the manage.exe command.

2. Enter calendar information through user interface (If there aren't very many, as in my case).

 

Use database loader utility to upload CSV data to tables:

 

3. Location (These records point to a calendar in step 2)

4. Customer

5. Operation

6. Item

7. Buffer

8. Resource (These records point to a calendar in step 2)

9. Flow

10. Load

 

All this is just a guess because I succeeded with the JSON format instead of thinking to use a database loader utility.  But I did learn which tables need to link to which other tables, so I think the load order in steps 2 through 10 is correct.  Maybe this will help Bruce.

[Bruce Holt]

Thanks Tim. I am using your Option 1 to load the JSON Files. I am also building a single JSON file from data from an ERP system. I have also discovered that there is a lack of error messages when loading the JSON files. I have also created CSV files and have loaded the database using this method through the Frepple menu Import function. Workable but timely. Maybe a manage.exe load function can be incorporated into version 0.9.3. Thanks for your response.

      "pk": "factory 1",

      "model": "input.location",

      "fields": {

         "description": "A factory that manufactures, stores and packages products",

         "available": "Working Days"

      }

   },

   {

      "pk": "factory 2",

      "model": "input.location",

      "fields": {

         "description": "A factory that stores and packages products",

[Tim Sharpe]

Bruce,

Please make sure you've read my recent response to Johan.  He had a great idea about using a database loader program to upload CSV files.  I responded with what I think would be the proper loading order for the CSV files.  This is something I think you could automate.  But if you can create a JSON file, then there is no need to go through the trouble of loading CSV data files.

Tim

[Johan De Taeye]

I’ve added the relation between the entities in the documentation now:  http://frepple.com/pmwiki/pmwiki.php/UI/PlanInput

[Tim Sharpe]

Hi Johan,

The company I work for is again taking a renewed interest in frepple.  For a time it was decided it was not the highest priority, but that has now changed.

Our IT department (specifically, myself and a coworker) has successfully created a large JSON format upload file from our ERP system (Baan) so we can replicate our current work environment in frepple.  Now the task is to add in new item numbers, operations and resources that do not currently exist in our business model but may exist in the future if we get new business from our customers.

So now we are faced with how to get that new information into frepple.  Our plan is to use spreadsheets to upload that information.  That is something our costing department (which is interested in machine capacity) will maintain.  We will have a single spreadsheet with an item tab, an operation tab, a resource tab, etc., with this information and will upload it after each refresh of our ERP (via JSON file) upload into frepple.

My question is this: There appears to be no CSV upload capability for the forecastdemand table.  This table holds the forecast quantities.  I am currently using a spreadsheet with forecast quantities from our costing department to generate database INSERT commands to add this data to the frepple database.  I am using frepple 0.9.2.  In future versions, will there be a CSV way to add this data to frepple?

Our costing department is now very impressed with frepple.  I do not think the support fee you ask for will be a problem for us once I can successfully prove frepple will do what we need it to do (capacity study). Also, you once asked me for a company testimonal about frepple's usefulness.  I also think that will not be a problem once we get version 0.9.2 working to everyone's satisfaction.

I have also found a way to link our ERP system (which uses an Oracle database) to the postgreSQL database we are running frepple on.  This is useful for comparing current information in our ERP system to what is in the frepple database via SQL queries. I am willing to share the knowledge of how to set this up with anyone who is interested.

Thanks for the great software,

Tim

P.S. frepple's time to create a plan was at one point 37.5 minutes for us. Then we added 4 more hours of capacity per day and it dropped to 23 minutes.  So I assume more capacity equals easier planning for frepple and less plan creation time. Is that right? We are currently making a constrained plan but not respecting capacity.  We also do not consider items sent to us by suppliers, only items built by us. Hence, at the bottom level of the supply paths for built items are operations that have flow records adding one to a bottom-level component's buffer, but not subtracting anything from another buffer. The plan log file always complains about these items but makes a plan anyway. Is this a problem?  Can you have flow records that add one to a buffer but do not subtract from another buffer?

[Johan De Taeye]

Tim,

 

Can we have a one-to-one talk to go over your detailed questions & needs?

 

>> My question is this: There appears to be no CSV upload capability for the forecastdemand table.  

 

The forecast & forecastdemand table will disappear in the community edition of the upcoming 0.9.3 release. 

The forecasting module and related tables will only be present in the enterprise versions.  CSV upload capability will be present for that table.

 

>> I am willing to share the knowledge of how to set this up with anyone who is interested.

 

I can create you a page on http://frepple.com/pmwiki/pmwiki.php/Contrib/Main

 

>> frepple's time to create a plan was at one point 37.5 minutes for us. Then we added 4 more hours of capacity per day

>>and it dropped to 23 minutes. Then we added 4 more hours of capacity per day and it dropped to 23 minutes.  So I

>>assume more capacity equals easier planning for frepple and less plan creation time. Is that right?

 

The more constraints are present in the plan, the longer the planning time: The algorithm just needs to search longer to find all available material and capacity to plan the demand with a minimal delay.

There are a number of fields that can be very effective in keeping the search efficient.  It depends on the specific model which one is appropriate.  
Examples are: min-size of operation, max-lateness of demands, …

 

>>The plan log file always complains about these items but makes a plan anyway. Is this a problem?  

If it’s probably only a warning about some incomplete/invalid data.
Can you send an example of the message in the log file?

 

>>Can you have flow records that add one to a buffer but do not subtract from another buffer?

 

Yes, no problem at all.

 

Regards,

--
You received this message because you are subscribed to the Google Groups "frePPLe users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to frepple-user...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Tim Sharpe]

Johan,

 

My direct e-mail is Tim_Sharpe (at) hci.hi-lex.com . I appreciate your help.  Right now I have only one other question.

 

We have some resources (machines) that are two-sided, meaning they can produce two parts at the same time. I assume I need to convert them to one side as I don't believe frepple knows anything about sides.  So if two parts can be produced in 10 seconds, I tell frepple that it can produce one part in 5 seconds. I hope this thinking is correct.

 

If I am correct, in a future version of frepple could a field be added called "sides" to the resource table?  When planning, frepple could then divide duration_per by sides to make the adjustment.  I could then enter 10 seconds for the duration_per and 2 for "sides" and frepple could calculate 10 / 2 = 5 seconds when planning.  Is this possible or is my thinking incorrect?  Perhaps there is a better way?

Here is a portion of our plan log file that complains about component items we build from purchased items.  I believe the messages are created because our model does not include purchased parts at all.  Flow records for these items are a +1 for each item's buffer, but no subtractions from purchased parts buffers (because there are no buffers for purchased parts).

 

Importing operations...
Loaded 4174 operations in 0.37 seconds
Importing suboperations...
Loaded 0 suboperations in 0.00 seconds
Importing items...
Error: Invalid hierarchy relation between "CP09221" and "CP09221"
Error: Invalid hierarchy relation between "CR29006" and "CR29006"
Error: Invalid hierarchy relation between "CR29005" and "CR29005"
Error: Invalid hierarchy relation between "CR29004" and "CR29004"
Error: Invalid hierarchy relation between "CR29003" and "CR29003"
Error: Invalid hierarchy relation between "CR09269" and "CR09269"
Error: Invalid hierarchy relation between "CR09268" and "CR09268"

etc.

 

Thanks,

Tim

[Johan De Taeye]

>> If I am correct, in a future version of frepple could a field be added

>>called "sides" to the resource table?  When planning, frepple could

>>then divide duration_per by sides to make the adjustment.  I could
>>then enter 10 seconds for the duration_per and 2 for "sides" and frepple
>>could calculate 10 / 2 = 5 seconds when planning.  Is this possible or
>>is my thinking incorrect?  Perhaps there is a better way?

 

The current way of modeling is to use alternate operations:  Alternate 1 uses a single side machine and takes 10 seconds. Alternate 2 is a double sided machine that has a time_per of 5 seconds.

 

A more general term for the functionality you request would be “efficiency”: some resources can perform a test more efficient / faster.

The 0.9.3 release will add modeling of “skills” of the resources, and specifying required skills on the operation loads.  

Adding efficiency to the resource skills looks logical. It is however not easy to correctly account for this in the constrained plan…

 

>> Error: Invalid hierarchy relation between "CP09221" and "CP09221"

That’s an innocent error. You populated the “owner” field with the same name as the item.

Unless you want to model a hierarchical tree of the items, you can leave the field “owner” empty.

Kind regards,

[Tim Sharpe]

Johan,

 

Thanks again.

 

One more question: Is it possible for one person to execute a plan in the default database while a second person executes a plan in another (scenario) database?

 

Tim

[Johan De Taeye]

> One more question: Is it possible for one person to execute a plan in the default database while a second person executes a plan in another (scenario) database?

Yes, that's possible.

johan