PLEXOS Tools

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.
 

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.

Examples:

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 t_model.name AS 'Model', t_parent_object.name AS 'Parent Object', t_collection.name AS 'Collection', t_child_object.name AS 'Child Object', t_property.name AS 'Property',
t_key.band_id AS 'Band', t_timeslice.name 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_model.name, t_collection.name, t_parent_object.name, t_child_object.name, t_property.name, t_key.band_id, t_timeslice.name, 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


WE'LL KEEP YOU UPDATED
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.
NAME
COMPANY
FOUND BY
EMAIL