ifference between revisions of "EMO:Connecting to a Database Server"

From EMO Wiki
Jump to navigation Jump to search
 
(25 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 
{{#hidens:}}
 
{{#hidens:}}
−
WARNING: Connecting to a local or networked database server is complex and can be time-consuming.  If no people in your company have previous experience of configuring ''EMarketOffer'' it is strongly recommended that you consult your company's technical support and also the Energy Link helpdesk for advice on the best approach.
+
'''WARNING!''' Connecting to a local or networked database server is complex and can be time-consuming.  If no people in your company have previous experience of configuring ''EMarketOffer'' it is strongly recommended that you consult your company's technical support and also the Energy Link [[Help:Contents|helpdesk]] for advice on the best approach.
 
----
 
----
  
Line 13: Line 13:
 
! Entity !! Type !! Trait !! Mandatory !! Defined by
 
! Entity !! Type !! Trait !! Mandatory !! Defined by
 
|-
 
|-
−
! Node
+
!Node
 
|
 
|
−
Demand (Load)
+
:Demand (Load)
 +
|
 +
:''Node.Load''
 +
|
 +
:No
 +
|
 +
:Date, TP, Node, Load
 +
|-
 +
!
 +
|
 +
:Price
 +
|
 +
:''Node.Price''
 +
|
 +
:No
 +
|
 +
:Date, TP, Node, Load
 +
|-
 +
!Unit
 +
|
 +
:Unit Node<br/>(Links Units to Nodes)
 +
|
 +
:''Unit.Node''
 +
|
 +
:Yes
 +
|
 +
:Unit, Node
 +
|-
 +
!
 +
|
 +
:Generator Unit<br/>(Links Units to Generators)
 +
|
 +
:''Unit.Generator''
 +
|
 +
:Yes
 +
|
 +
:Unit, Generator<br/>(Generators are always three alpha codes e.g. HLY = Huntly
 +
|-
 +
!
 +
|
 +
:Unit Generation
 +
|
 +
:''Unit.Generation''
 +
|
 +
:No
 +
|
 +
:Date, TP, Unit, Generation
 +
|-
 +
!
 +
|
 +
:Unit Offer Quantity
 +
|
 +
:''Unit.OfferQn<br/>(where n = band)''
 +
|
 +
:No
 +
|
 +
:Date, TP, Unit, OfferQn
 +
|-
 +
!
 +
|
 +
:Unit Offer Price
 +
|
 +
:''Unit.OfferPn<br/>(Where n = band)''
 +
|
 +
:No
 +
|
 +
:Date, TP, Unit, OfferPn
 +
|-
 +
!Circuit
 +
|
 +
:Circuit
 +
|
 +
:''Circuit.Name''
 +
|
 +
:Yes
 +
|
 +
:Date, TP, Name
 +
|-
 +
!
 +
|
 +
:Node From
 +
|
 +
:''Circuit.NodeFrom''
 +
|
 +
:Yes
 +
|
 +
:Date, TP, Circuit, NodeFrom
 +
|-
 +
!
 +
|
 +
:Node To
 +
|
 +
:''Circuit.NodeTo''
 +
|
 +
:Yes
 +
|
 +
:Date, TP, Circuit, NodeTo
 +
|-
 +
!
 +
|
 +
:Reactance
 +
|
 +
:''Circuit.Reactance''
 +
|
 +
:Yes
 +
|
 +
:Date, TP, Circuit, Reactance
 +
|-
 +
!
 +
|
 +
:Resistance
 +
|
 +
:''Circuit.Resistance''
 +
|
 +
:Yes
 +
|
 +
:Date, TP, Circuit, Resistance
 +
|-
 +
!
 +
|
 +
:Fixed Losses
 +
|
 +
:''Circuit.FixedLosses''
 +
|
 +
:Yes
 +
|
 +
:Date, TP, Circuit, FixedLosses
 +
|-
 +
!
 +
|
 +
:Capacity
 +
|
 +
:''Circuit.Capacity''
 +
|
 +
:Conditional
 +
|
 +
:Date, TP, Circuit, Capacity
 +
|-
 +
!
 +
|
 +
:Winter Capacity
 +
|
 +
:''Circuit.WinterCapacity''
 +
|
 +
:Conditional
 +
|
 +
:Date, TP, Circuit, WinterCapacity
 +
|-
 +
!
 +
|
 +
:Summer Capacity
 +
|
 +
:''Circuit.SummerCapacity''
 +
|
 +
:Conditional
 +
|
 +
:Date, TP, Circuit, SummerCapacity
 +
|-
 +
!Equation
 +
|
 +
:Formula
 +
|
 +
:''Equation.Formula''
 +
|
 +
:Yes
 +
|
 +
:Date, TP, Equation, Formula
 +
|-
 +
!
 +
|
 +
:Limit
 +
|
 +
:''Equation.Limit''
 +
|
 +
:Yes
 +
|
 +
:Date, TP, Equation, Limit
 +
|-
 +
!
 +
|
 +
:Type
 +
|
 +
:''Equation.EquationType''
 +
|
 +
:No
 +
|
 +
:Date, TP, Equation, EquationType
 +
|-
 +
!
 +
|
 +
:Season
 +
|
 +
:''Equation.Season''
 +
|
 +
:No
 +
|
 +
:Date, TP, Equation, Season
 +
|-
 +
!
 +
|
 +
:Active
 +
|
 +
:''Equation.Active''
 +
|
 +
:No
 +
|
 +
:Date, TP, Equation, Active
 +
|}
 +
----
 +
Note:
 +
#Mandatory Traits are the minimum required for ''EMarketOffer'' to operate
 +
#Either a single Circuit capacity OR two seasonal Circuit Capacities are mandatorily required
 +
----
 +
 
 +
===Input Data Structure===
 +
''EMarketOffer'' requires input data to generate the '''''Market Data Source''''' for viewing historical market conditions and to create the '''''Forecast Definitions''''' used to generate detailed market '''''[[EMO:Forecasting|Forecasts]]'''''.  Input data can be retrieved from external databases using standard SQL queries, which can be grouped together into '''''Database Connection Configurations.'''''
 +
 
 +
NOTE:  Only one Database Connection Configuration can be in use at any one time.  This "current" configuration tells ''EMarketOffer'' where to find the input data for the Market Data Source and for loading data into Forecast Definitions.  The most recently used configuration is opened automatically as the default configuration on startup. The '''''Data Sources Option''''' in the '''''[[EMO:The_File_Menu|Main Window File Menu]]''''' can be used to make an alternative configuration current.
 +
 
 +
===Input Data Handling Mechanism===
 +
A Configuration contains a collection of queries that may reference more than one online database. Each query can reference a single database using a pre-defined Connection containing the information required to interact with a database server; once defined a Connection can be used repeatedly by the queries in a Configuration.
 +
 
 +
The main component of a Connection is the Connection String which ''EMarketOffer'' uses to locate and connect to online database servers.  The string varies in format depending on the target database server type and its specific access security provisions.  It may contain: 
 +
*A data provider: this specifies the type of database server to be accessed (e.g. Oracle or MS SQL) and the specific access protocol to be used (e.g. OleDB or ODBC)
 +
*Name of the database to be accessed (if required)
 +
*The access parameters (typically User Name and Password)
 +
*A Data Source Name (DSN) file containing the above information
 +
 
 +
See the section on '''''[[EMO:Securing Database Access|Securing Database Access]]''''' for more information on Connection Strings.
 +
 
 +
A separate query is used for each data item to be imported. Each query must be associated with a Connection and contain a standard SQL-compliant query string defining the specific data to be returned.  Query strings should:
 +
*Be tested first for syntax against your database
 +
*Contain the return fields required by ''EMarketOffer''
 +
*Contain a 'Where' clause defining a select date range using the variables $DateFrom$ and $DateTo$
 +
*Allow aliasing of column names with attribute (trait) names used by ''EMarketOffer''
 +
----
 +
Notes:
 +
#All queries must contain the name of the Trait on each returned row
 +
#All queries (except for the Unit.Generator query) return  a row for each Trading Period in the entered data range
 +
----
 +
'''WARNING!'''  It is not possible to join data from two different databases within the same query, however ''EMarketOffer'' is designed to use a separate query for each input data item so this restriction should not constrain the program's operation.  If, for any reason, you do wish to use a data item that is sourced from more than one database, you should first create a view/query in an external database that combines the required data, and then use ''EMarketOffer's'' to connect to this view.
 +
 
 +
===Creating a Database Connectivity Configuration===
 +
The functions used in Configuration setup are fully described in the '''''[[EMO:Menu Bar|Menu Bar]]''''' segment under the '''''[[EMO:The Main Window|Main Window]]''''' section.
 +
 
 +
 
 +
[[Main Page|Home]]

Latest revision as of 14:08, 7 February 2014

WARNING! Connecting to a local or networked database server is complex and can be time-consuming. If no people in your company have previous experience of configuring EMarketOffer it is strongly recommended that you consult your company's technical support and also the Energy Link helpdesk for advice on the best approach.

Terminology

EMarketOffer's data design consists of 'things' included in the model, and their characteristics'. In this manual the 'things' are called Entities and their characteristics are called Traits. Traits may be static or dynamic.

For example the Circuit Entity can have the static Traits of Start Node and End Node, and the dynamic Traits of Powerflow and Loss by Trading Period.

Input Data Types

EMarketOffer can load the folowing types of Input data directly from external databases.

Entity Type Trait Mandatory Defined by
Node
Demand (Load)
Node.Load
No
Date, TP, Node, Load
Price
Node.Price
No
Date, TP, Node, Load
Unit
Unit Node
(Links Units to Nodes)
Unit.Node
Yes
Unit, Node
Generator Unit
(Links Units to Generators)
Unit.Generator
Yes
Unit, Generator
(Generators are always three alpha codes e.g. HLY = Huntly
Unit Generation
Unit.Generation
No
Date, TP, Unit, Generation
Unit Offer Quantity
Unit.OfferQn
(where n = band)
No
Date, TP, Unit, OfferQn
Unit Offer Price
Unit.OfferPn
(Where n = band)
No
Date, TP, Unit, OfferPn
Circuit
Circuit
Circuit.Name
Yes
Date, TP, Name
Node From
Circuit.NodeFrom
Yes
Date, TP, Circuit, NodeFrom
Node To
Circuit.NodeTo
Yes
Date, TP, Circuit, NodeTo
Reactance
Circuit.Reactance
Yes
Date, TP, Circuit, Reactance
Resistance
Circuit.Resistance
Yes
Date, TP, Circuit, Resistance
Fixed Losses
Circuit.FixedLosses
Yes
Date, TP, Circuit, FixedLosses
Capacity
Circuit.Capacity
Conditional
Date, TP, Circuit, Capacity
Winter Capacity
Circuit.WinterCapacity
Conditional
Date, TP, Circuit, WinterCapacity
Summer Capacity
Circuit.SummerCapacity
Conditional
Date, TP, Circuit, SummerCapacity
Equation
Formula
Equation.Formula
Yes
Date, TP, Equation, Formula
Limit
Equation.Limit
Yes
Date, TP, Equation, Limit
Type
Equation.EquationType
No
Date, TP, Equation, EquationType
Season
Equation.Season
No
Date, TP, Equation, Season
Active
Equation.Active
No
Date, TP, Equation, Active

Note:

  1. Mandatory Traits are the minimum required for EMarketOffer to operate
  2. Either a single Circuit capacity OR two seasonal Circuit Capacities are mandatorily required

Input Data Structure

EMarketOffer requires input data to generate the Market Data Source for viewing historical market conditions and to create the Forecast Definitions used to generate detailed market Forecasts. Input data can be retrieved from external databases using standard SQL queries, which can be grouped together into Database Connection Configurations.

NOTE: Only one Database Connection Configuration can be in use at any one time. This "current" configuration tells EMarketOffer where to find the input data for the Market Data Source and for loading data into Forecast Definitions. The most recently used configuration is opened automatically as the default configuration on startup. The Data Sources Option in the Main Window File Menu can be used to make an alternative configuration current.

Input Data Handling Mechanism

A Configuration contains a collection of queries that may reference more than one online database. Each query can reference a single database using a pre-defined Connection containing the information required to interact with a database server; once defined a Connection can be used repeatedly by the queries in a Configuration.

The main component of a Connection is the Connection String which EMarketOffer uses to locate and connect to online database servers. The string varies in format depending on the target database server type and its specific access security provisions. It may contain:

  • A data provider: this specifies the type of database server to be accessed (e.g. Oracle or MS SQL) and the specific access protocol to be used (e.g. OleDB or ODBC)
  • Name of the database to be accessed (if required)
  • The access parameters (typically User Name and Password)
  • A Data Source Name (DSN) file containing the above information

See the section on Securing Database Access for more information on Connection Strings.

A separate query is used for each data item to be imported. Each query must be associated with a Connection and contain a standard SQL-compliant query string defining the specific data to be returned. Query strings should:

  • Be tested first for syntax against your database
  • Contain the return fields required by EMarketOffer
  • Contain a 'Where' clause defining a select date range using the variables $DateFrom$ and $DateTo$
  • Allow aliasing of column names with attribute (trait) names used by EMarketOffer

Notes:

  1. All queries must contain the name of the Trait on each returned row
  2. All queries (except for the Unit.Generator query) return a row for each Trading Period in the entered data range

WARNING! It is not possible to join data from two different databases within the same query, however EMarketOffer is designed to use a separate query for each input data item so this restriction should not constrain the program's operation. If, for any reason, you do wish to use a data item that is sourced from more than one database, you should first create a view/query in an external database that combines the required data, and then use EMarketOffer's to connect to this view.

Creating a Database Connectivity Configuration

The functions used in Configuration setup are fully described in the Menu Bar segment under the Main Window section.


Home

Retrieved from "http://emo.energylink.co.nz/index.php?title=EMO:Connecting_to_a_Database_Server&oldid=5844"