How To Use the SQLite Manager extension to Firefox for querying output data from projects with more than 2GB of output

[leonardo-frid]

 

Objective of the Tutorial

Some users work with very large output data sets in Path, particularly if they are running many simulation sets or iterations of their model runs.  Version 4 of path is limited to having 2GB of output in memory for graphing which can be problematic when creating graphs or reports for very large sets of output.  In future versions of Path we plan to make the output functionality more scalable to large data sets.  In the meantime this tutorial allows you to export output directly from the SQLite output database to a csv file that can be analyzed with other software.  The SQLite database format is required when the size of output in a project exceeds 2 GB which is the file size limit for a MS Access database.

 

Tutorial Steps

1. Ensure that your Path project option for output (File | Project Definitions | Project Options) is set to use the SQLite model output data type before you do your model runs.
2. Run the scenarios you wish to analyze
3. You will need to have Mozilla Firefox installed on your computer
4. Open Firefox and on the Firefox menu (Top Left) select Add-ons
5. Choose the Get Add-ons menu on the left and search for SQLite Manager
6. Install SQLite Manager
7. In the Firefox menu select: Web Developer | SQLite Manager.  This will open the SQLite Manager application in a new Firefox window.
8. In the SQLite Manager application select Database | Connect Database and find your output SQLite database.  This will be the file [Project Name].sqlite found in the same directory as your main Project mdb file ([Project Name].mdb).
9. This database contains three tables: OutputStratumState, OutputStratumTransition and OutputStratumTransitionState.  The table fields contain ID’s that reference corresponding tables in your Project mdb file.  If you want to include names instead of ID’s in your output you need to import some of these tables so that your queries can reference them.
10. The following are tables you will need to import: Scenario, Stratum, StateClass, StateLabelX, StateLabelY, TransitionGroup and TransitionType.
11. To import a table you will first need to export it from your Access mdb file to a csv format.  This is done by opening your mdb file in Access, highlighting the table and selecting External Data | Export To Text File. Export the file as a comma delimited file that includes the Field names in the first row.
12. In SQLite you can import the table by selecting Database | Import which will open up the import wizard.
13. Select the CSV tab in the import wizard, check comma delimited and the First Row Contains Field Names Checkbox.
14. Select your csv file to import.  Click OK and when prompted click OK to modify the table.
15. Set the data types for your table so that ID values are integers and text fields are text
16. Once your tables are imported you can create Views to search and export your data. 
17. To create a View in your database select View | Create View. Enter a name for the view such as vwOutputStratumTransition and enter the SQL for the select statement.   Below are some example views:

 

a.       Output Stratum State:

SELECT

Scenario.ScenarioID,

Scenario.Name,

Stratum.StratumID,

Stratum.Abbreviation,

OutputStratumState.Iteration,

OutputStratumState.Timestep,

OutputStratumState.StateClassID,

StateLabelX.Name,

StateLabelY.Name,

OutputStratumState.Amount

FROM OutputStratumState

INNER JOIN Scenario ON OutputStratumState.ScenarioID = Scenario.ScenarioID

INNER JOIN Stratum  ON OutputStratumState.StratumID = Stratum.StratumID

INNER JOIN StateClass ON OutputStratumState.StateClassID = StateClass.StateClassID

INNER JOIN StateLabelX ON StateClass.StateLabelXID = StateLabelX.StateLabelXID

INNER JOIN StateLabelY ON StateClass.StateLabelYID = StateLabelY.StateLabelYID

WHERE Amount > 0 AND Scenario.ScenarioID = 320 AND Stratum.StratumID = 3

 

b.      Output Stratum Transition:

SELECT

Scenario.ScenarioID,

Scenario.Name AS Scenario,

Stratum.StratumID,

Stratum.Abbreviation AS Stratum,

OutputStratumTransition.Iteration,

OutputStratumTransition.Timestep,

TransitionGroup.TransitionGroupID,

TransitionGroup.Name AS TransitionGroup,

OutputStratumTransition.Amount

FROM OutputStratumTransition

INNER JOIN Scenario ON OutputStratumTransition.ScenarioID = Scenario.ScenarioID

INNER JOIN Stratum  ON OutputStratumTransition.StratumID = Stratum.StratumID

INNER JOIN TransitionGroup ON OutputStratumTransition.TransitionGroupID = TransitionGroup.TransitionGroupID

WHERE Amount > 0 AND Scenario.ScenarioID = 320 AND Stratum.StratumID = 3

 

18.  Once your view is created you can select it in the tree and select View | Export View.  In the export wizard select the settings you wish for exporting (csv with First row containing column names is recommended) and click OK.  Depending on the amount of data  the export may take a few minutes

[Author]

Posts