Spreadsheet or Database? Let’s be honest about our preferences. Given a pile of data to work with your first click will be the one that opens Excel. Databases are great for validation but they are lousy at maths so we in the GRTMPS team try to offer you the best of both – so you can use the input database to protect yourself from time wasting over undefined codes, but use a spreadsheet to manipulate data. The tool for passing data in and out of the model database via Excel is called SSI – for SpreadSheet Import.
Let’s be honest about our preferences. Given a pile of data to work with, are you going to dump it into a spreadsheet or a database? Databases are great for validation but they are lousy at maths and most of us STEM types really, really like to work in Excel. We in the GRTMPS team try to offer you the best of both – so you can use the input database to protect yourself from time wasting over undefined codes, but use a spreadsheet to manipulate data.
The tool for passing data in and out of the model database via Excel is called SSI – for SpreadSheet Import. This allows you to both Export from the database into a spreadsheet and Import data records in on many of the data panels. You can do all sorts of useful things with this tool. Use it to back up a set of data before you edit it, to pass data from one database to another, to use the filter tools in Excel to make it easier to locate information in large lists, to collect data from other sources, etc. etc.
The tool for passing data in and out of the model database via Excel is called SSI – for SpreadSheet Import. This allows you to both Export from the database into a spreadsheet and Import data records in on many of the data panels. You can do all sorts of useful things with this tool. Use it to back up a set of data before you edit it, to pass data from one database to another, to use the filter tools in Excel to make it easier to locate information in large lists, to collect data from other sources, etc. etc.
All panels with SSI support have the same frame at the bottom, titled "Spreadsheet Import".
The frame has a button "Export to Spreadsheet". Using this button will give you an example of the required format for that particular panel.
Notes
Notes 1-6 are standard for most every panel and describe the generic formatting rules. The Specific Notes are particular to the type of data and will tell you useful things like which columns are required, and what entries are valid in certain places. By and large the entries are the same as what you see in the database, but in some cases they are a generic tag so that they are valid even if the label in the model has been changed (for example for the price units). The notes sections don’t need to be there. You can hide or delete them. You can also load information from a CSV file - which might be easier to generate from other tools that provide data.
Notes 1-6 are standard for most every panel and describe the generic formatting rules. The Specific Notes are particular to the type of data and will tell you useful things like which columns are required, and what entries are valid in certain places. By and large the entries are the same as what you see in the database, but in some cases they are a generic tag so that they are valid even if the label in the model has been changed (for example for the price units). The notes sections don’t need to be there. You can hide or delete them. You can also load information from a CSV file - which might be easier to generate from other tools that provide data.
Data
The SSI section starts with "G4Data:[something]". (even if you are using G5, short-sighted design choice on our part.) Some of these somethings are general – like Purchases, and some are specific like: G4Data:ProcUnitOperations:VAC – where VAC is a 3-character process unit name that must match the code for the unit panel where you are importing. Of course, you can always edit these names if you are moving data between units or have updated the model.
When you use the Import button on a panel it will only process data of that type. Any other SSI sections will be ignored. There can be multiple instances of the same data type in a workbook, on the same page or in separate sheets. So while the simplest set up is to have all of one type of data on a page, you can organize the information in other ways. Depending on what you are trying to do, you might have a lot of different types of data within one workbook … say everything that is needed to set up the planning basis for a particular month – or a full back up of a process unit representation – which requires several sections. Or you may have one kind of data spread over several workbooks which need to be imported in turn.
The next row is the "header row". Column A must be blank in the header. Columns can be in any order. Only those needed to make a distinct identifier for the record are required. If you start your workbook by doing an export from the target panel, even if there is no data in it, you will have the headers that you need. Required ones are always coloured red when exported. (In the example, the data in row 17, columns H-K is just for information.)
Fill in at least the required columns. The data section will be processed until there is a blank row.
Optional Columns
Some of the optional columns are part of the record if used, such as the limits on the purchases. Some are just for information. For example, the Description for the stream codes will start with what was in the database when the information was exported, but any changes won’t be imported back since stream text is edited in the Stream panel, and is read-only in purchases. Likewise the LimitBasis of Per Day or Per Period which depends on the account definitions. The import validation also won’t care about any changes in these items since the data was downloaded. If the limit basis was changed to Per Period for that account, the numbers would still be loaded even though they are described as Per Day in the SSI.
Some of the optional columns are part of the record if used, such as the limits on the purchases. Some are just for information. For example, the Description for the stream codes will start with what was in the database when the information was exported, but any changes won’t be imported back since stream text is edited in the Stream panel, and is read-only in purchases. Likewise the LimitBasis of Per Day or Per Period which depends on the account definitions. The import validation also won’t care about any changes in these items since the data was downloaded. If the limit basis was changed to Per Period for that account, the numbers would still be loaded even though they are described as Per Day in the SSI.
Comment Column
Column “A” can be used to control how a row is processed. An asterisk “*” means the contents are just text. The row will be ignored and doesn’t need to be formatted as a data record. If you are filling in the SSI with formulae that reference data from another source (also in Excel, of course) you will often find that you need a formula in the A column to put in the commenting asterisk if the data is incomplete or invalid. That’s usually easier than trying to skip rows. If you want to import data as a commented record mark it with a C and it will end up in the database with an asterisk “*”. If you want to remove a record that might be in the database, use a D.
In this revised SSI, rows 19, 23 and 29 are comments and will be ignored. Row 26 will be loaded into the database, but as a commented option. Row 27 will result in the removal of any purchase option for 2FO that matches in all the required columns. Exactly how this will manifest itself on the target panel will depend on whether you are importing into the base case, or an alternate and if you use the Modify or Replace option.
Data Validation
If there are problems with the data, the Import Log will list the bad rows. So If my SSI looks like this
If there are problems with the data, the Import Log will list the bad rows. So If my SSI looks like this
My attempt at importing will generate this.
As you can see, it doesn’t accept duplicate records or odd characters in the comment Column. The panel’s validation rules are also applied to the data when it is read. If it would give you a red-dot error, it won’t be imported – so you are protected against undefined codes etc, just as when inputting data manually.
Modify / Replace
If you use the Replace option, all the existing records will be removed and those from the SSI loaded. Anything in the original data which was not listed in the SSI is, therefore, deleted.
Some numbers have been changed and the LPG and the 2FO purchase are gone. The RG1 purchase information remains but is deactivated.
However, if you do an Import – Modify into the Base Case you will have
However, if you do an Import – Modify into the Base Case you will have
The 2FO record is eliminated because of the D marker, but the LPG remains because only records that match on the values in all the required columns will be updated. What looks like an LPG record in the SSI is actually just a comment because if the *, so it doesn't do anything. Any records in the spreadsheet that don’t already exist in the database will be added. Modify is the option to use if your spreadsheet doesn’t contain all of the data that is on the panels. For example, if you are updating the crude prices and demands that are handled in account BRX, but don’t have the infeasibility purchases in the SSI.
Model Data / Base Data / Alternate Data
The impact your import has on other cases depends on the scope of the data panel – Model or Base/Alternate. There are SSIs on some Model level data panels – for example, process unit operations. If you load modifications it will change that unit throughout the model database, affecting any case where the unit is active, regardless of which case you were in when the import was done. If you are intending to vary only the local version of the unit, switch the case to using a copy of it first.
The impact your import has on other cases depends on the scope of the data panel – Model or Base/Alternate. There are SSIs on some Model level data panels – for example, process unit operations. If you load modifications it will change that unit throughout the model database, affecting any case where the unit is active, regardless of which case you were in when the import was done. If you are intending to vary only the local version of the unit, switch the case to using a copy of it first.
If the data is Base/Alternate, like the purchases, and you load it into the base case, then the new records will be active in any of the alternate cases in which they had not been modified. So, considering the example above, the cases that had the same prices and limits for the crudes, would now have the new ones. While any case where the corresponding record was marked with an “m” because it had been changed or commented would continue to use its existing values. This of course can have somewhat unexpected consequences. If you are intending to use the same prices everywhere but have additional limits in an alternate case then it will have the old base price, rather than the new one. A quick scan through any alternate cases afterwards, looking for “m” records is advised.
If your new data is not intended to be a global revision then you should load it into an alternate case. The changes will only affect that case. Base records cannot be deleted in alternate cases so anything which would otherwise be deleted, like the 2FO record, will be commented.
Alternate Cases can be associated with different SSI files than the base case. If you are doing an archive with SSI files included and there are messages about missing files that you don’t recognize they are probably associated with alternate cases.
Modifications made in the database via the SSI system are tracked in the change log just as those done via the panel. So you can create a report via the Database Management panel if you want to see what has happened.
While the purchase data in the demo model updates almost instantly when imported, you will definitely notice a pause with big record sets. When uploading the several thousands of sales rows in some of the large distribution network models I have built, I usually press the Import button and then go put the kettle on.
So that’s how SSIs are put together and how they fit into the database model inputs. There are also some facilities for doing multiple imports – using the SSI panel in the g5 interface and the Work Flow tool, but I think I’ve gone on for long enough for now. You can find that information in Note 78.
Thanks to Rob Day for the question and Shari for some of the answer.
From Kathy's Desk: 20 Years of Working From Home in Splendid Isolation
25th March 2020.
25th March 2020.
Comments and suggestions gratefully received via the usual e-mail addresses or here.
You may also use this form to ask to be added to the distribution list so that you are notified via e-mail when new articles are posted.
You may also use this form to ask to be added to the distribution list so that you are notified via e-mail when new articles are posted.