Database Linking Overview
DPL provides you with the ability to access data stored in databases via its database linking feature. Database data can be used to initialize values for any type of node in DPL. Database data can also be used to initialize probabilities for discrete chance nodes.
Before using a database with DPL, you will need to set up an ODBC Data Source in Windows that connects with the database. Talk to your IT administrator for help on how to do this.
Once you have an ODBC Data Source in Windows, you need to go to Data | Database | Setup to provide DPL with information about the ODBC Data Source and the database connected to it. This is done using the Database Specification dialog.
The data needed to initialize values and/or probabilities for nodes in DPL may have varying dimensions and sizes. For scalar data, DPL can access data in database tables that are structured in a fairly standard way, i.e., the columns of the database table identify the data stored within them and within a single record numerous scalar values are stored, e.g., market_growth, market_size.
However in many cases, the data needed to initialize as node may need to be a vector (e.g., of sales over time or probabilities) or even a matrix (e.g., low, nominal and high peak sales by region). Non-scalar data must be stored in tables with a specific structure in the database. This structure can either exist in the tables themselves or be provided by a view or query of the tables. Specifically, tables storing non-scalar information must have fields for the following information.
|Model ID||ModelID||identifies to which model the data belongs (optional)|
|Project ID||ProjectID||identifies to which project the data belongs (required if tables stores project specific information)|
|Node ID||NodeID||identifies the data stored in the record|
|Dimensions||Dims||indicates whether the data is scalar (0), vector (1) or matrix (2)|
|Rows||Rows||indicates the number of rows for vector or matrix data|
|Columns||Cols||indicates the number of columns for vector or matrix data|
|Revision ID||RevisionID||identifies the revision of the data (if database is configured for revision tracking)|
|Revision Date||RevisionDate||identifies the date of the revision of the data (if database is configured for revision tracking)|
The default field names shown above can be changed for your specific database.
In addition to the above fields, there must be fields to store the actual data. These are identified to DPL by a Data fields prefix. This is Data_ by default. So the table must contain fields named such as Data_001, Data_002, etc.
If the table contains string data rather than numeric, then this information should be stored in fields using the String fields prefix which is Str_ by default (data is stored in Str_001, Str_002, etc.).
As with the default field names, the default field prefixes may also be changed.
All of this information is specified in the Database Specification Dialog.
Note with the table structure above each data element is stored in a separate database record, e.g., a sales forecast over 20 years might be stored in record with a Node ID of "sales_forecast", Dimensions equal to 1, Rows equal to 1, Columns equal to 20 and the actual values for the sales forecast in Data_001 through Data_020.
Setting up Database Initialization Links
Initialization links allow you to use database data to initialize nodes. For example, you might want a chance node to get its probabilities from a table in a database. Or you might want an array value node that represents a baseline forecast to get its data from a database table.
There are two ways to set up initialization links:
- If you want to add a group of new nodes, use Model | Links | Add | Database Initialization-Linked.
- If you want to link an existing node to a cell in the spreadsheet, use the Links tab of the Node Definition dialog and the links button on the Data tab to access the Select Database Link dialog.
Managing Database Links
Once your database links have been established, most maintenance activities can be done from the Manage Links dialog. In that dialog, you can change the data source to which nodes are linked, remove links, change the records in the database to which nodes are linked and view linked nodes.
Versions: DPL Enterprise, DPL Portfolio