Database to SQL Script Utility

The Database to SQL Script utility will produce an SQL Script from any given PLEXOS Database, (both Input and Solution databases), which will create and populate an SQL Database with the corresponding schema and data. It currently creates Primary Keys, Foreign Key Relationships and Unique Constraints.

Command Line Usage

DatasetToSQL <database_filename> [output_filename] [--drop] [--target=<dbms>] [--progress] [--compress] [--filter=<classname>] [--periodtype=<periodtype>] [--dataonly] [--dbname=<name>]
<database_filename> can be an input database (*.xml) or a Solution database (*.zip)
--drop                       Generate SQL to DROP the database if it already exists.
--target                     Generates additional SQL supported by a specific DBMS. (default=sqlserver)
                             Valid <dbms> values are: sqlserver, mysql, oracle
--progress                   Generates progress messages in the SQL Script.
--compress                   Writes the output file to a ZIP archive.
--filter                     To split up large databases you can provide a class filter for the data files.
--periodtype                 To split up large databases you can provide a period type filter for the data files.
--dataonly                   Generates INSERTs for data-related tables only.
--dbname                     Use specific database name rather than the filename of the dataset.
--simple                     For Solution databases, creates a database with a simpler and faster schema.

Alternate Options (SQL Server only):

DatasetToSQL <database_filename> <output_filename> [--drop] [--execute] [--sqlserver-server=<server>] [--sqlserver-integrated] [--sqlserver-login=<login>] [--sqlserver-password=<password>] [--sqlserverdatafiles] [--sqlserverdatafilepath-out=<path>] [--sqlserverdatafilepath-in=<path>] [--overwrite]
--execute                    Execute the SQL (SQL Server only; requires server and login information)
--sqlserver-server           Server name (SQL Server only)
--sqlserver-integrated       Use Integrated Security (SQL Server only)
--sqlserver-login            Login name (SQL Server only)
--sqlserver-password         Password (SQL Server only)
--sqlserverdatafiles         Generate data files for solution data allowing for faster data import in SQL Server.
--sqlserverdatafilepath-out  The path in which to generate the solution data files for SQL Server (default=current directory).
--sqlserverdatafilepath-in   The path in which SQL Server will be able to access the solution data files (default=sqlserverdatafilepath-out).
--overwrite                  Generate code to re-use an existing database, clearing its data first.

DBMS Support

At the present time, SQL Server, MySQL and Oracle are supported. Use the –target command line argument to specify which DBMS you intend to run the script for.

Usage Notes

The sql script generated may be quite large. It may be necessary, therefore, to execute the resulting script from the command line, using sqlcmd with the –i argument in SQL Server, or with the mysql command line in MySQL, or sqlplus for Oracle.

SQL Server Options

The most efficient method to import Solution Data into SQL Server is by generating data files which are imported using SQL Server’s BULK INSERT feature. This involves generating the data files using the –sqlserverdatafiles flag, and specifying –sqlserverdatafilepath-out (where the files will be written to) and –sqlserverdatafilepath-in (where the files will be read from by SQL Server. This path must be visible to the instance of SQL Server running the import script.) Alternatively, you can avoid the need to create these files and potentially have to move files around for SQL Server by providing DataSetToSQL with SQL Server login details and the –execute flag. DataSetToSQL can then run the entire script and import the data itself without generating any files.


sqlcmd –i script.sql
mysql –-user=root < script.sql

To reduce file size, you can use the --compress option to write to a .zip file.

Solution Query Example

A basic query in SQL Server of the solution database would be as follows.

SELECT AS 'Model', AS 'Parent Object', AS 'Collection', AS 'Child Object', AS 'Property',
t_key.band_id AS 'Band', AS 'Timeslice', t_sample.sample_id AS 'Sample', t_period_0.datetime AS 'Datetime', t_data_0.value AS 'Value', t_unit.value AS 'Units'
FROM t_membership
INNER JOIN t_collection ON t_membership.collection_id = t_collection.collection_id
INNER JOIN t_object t_parent_object ON t_membership.parent_object_id = t_parent_object.object_id
INNER JOIN t_object t_child_object ON t_membership.child_object_id = t_child_object.object_id
INNER JOIN t_property ON t_collection.collection_id = t_property.collection_id
INNER JOIN t_unit ON t_property.unit_id = t_unit.unit_id
INNER JOIN t_key ON t_membership.membership_id = t_key.membership_id AND t_property.property_id = t_key.property_id
INNER JOIN t_model ON t_key.model_id = t_model.model_id
INNER JOIN t_timeslice ON t_key.timeslice_id = t_timeslice.timeslice_id
INNER JOIN t_sample ON t_key.sample_id = t_sample.sample_id
INNER JOIN t_data_0 ON t_key.key_id = t_data_0.key_id
INNER JOIN t_period_0 ON t_data_0.period_id = t_period_0.interval_id
WHERE t_key.phase_id = @phase_id AND t_collection.collection_id = @collection_id AND (@property_id IS NULL OR t_property.property_id = @property_id)
ORDER BY,,,,, t_key.band_id,, t_sample.sample_id, t_period_0.datetime

Of the various “numbered” tables, such as t_data_0, t_data_1, t_period_0, t_period_1, etc; the number refers to a period type, where the numbers correspond as follows:

0 = Interval, 1 = Day, 2 = Week, 3 = Month, 4 = Year

phase_id’s correspond as follows:

1 = LT Plan, 2 = PASA, 3 = MT Schedule, 4 = ST Schedule

Solution Query Example - Simple Schema

A basic query in SQL Server of the solution database would be as follows. In this format, all ids for parameters can be acquired from lookup tables.

SELECT AS 'Parent Object', AS 'Model', AS 'Phase', AS 'Collection', AS 'Child Object', AS 'Category', AS 'Property',
       t_solution_data.band_id AS 'Band', AS 'Timeslice', AS 'Sample',
       _date AS 'Datetime',
       t_solution_data.value AS 'Value', AS 'Units'
FROM t_solution_data
INNER JOIN t_model ON t_solution_data.model_id = t_model.model_id
INNER JOIN t_object t_parent_object ON t_solution_data.parent_id = t_parent_object.object_id
INNER JOIN t_object t_child_object ON t_solution_data.child_id = t_child_object.object_id
INNER JOIN t_collection ON t_solution_data.collection_id = t_collection.collection_id
INNER JOIN t_category ON t_solution_data.category_id = t_category.category_id
INNER JOIN t_property ON t_solution_data.property_id = t_property.property_id
INNER JOIN t_timeslice ON t_solution_data.timeslice_id = t_timeslice.timeslice_id
INNER JOIN t_sample ON t_solution_data.sample_id = t_sample.sample_id
INNER JOIN t_unit ON t_solution_data.unit_id = t_unit.unit_id
INNER JOIN t_phase ON t_solution_data.phase_id = t_phase.phase_id
INNER JOIN t_period_type ON t_solution_data.period_type_id = t_period_type.period_type_id
WHERE t_solution_data.period_type_id = @period_type_id AND
      t_solution_data.phase_id = @phase_id AND
      t_solution_data.collection_id = @collection_id AND
      t_solution_data.property_id = @property_id AND
      t_solution_data.model_id = @model_id AND
      t_solution_data.timeslice_id = @timeslice_id AND
      t_solution_data.sample_id = @sample_id
ORDER BY t_solution_data.model_id, parent_id, t_solution_data.collection_id, child_id, t_solution_data.property_id, t_solution_data._date

Sign up to our mailing list and we’ll keep you in the loop of the latest PLEXOS and Energy Exemplar news, events and updates.