6.3.4 SQL - Oracle Database

To create a connection to a database, click Home -> Manage Connections -> SQL.

There are two options when creating a connection for retrieving data from SQL — MS SQL Server and Oracle Database. Only one SQL connection can be added to a document at a time.

When creating a new connection, you should first generate the integration scripts by selecting Oracle Database:

The form that appears allows you to choose the type of SQL connection and, optionally, the schema name.

Select a folder to save the integration scripts:

Creating Database Scripts

DPM Authority can produce 3 database scripts which can be used to facilitate the integration with an existing database:

1. Create_AS_DPM_Data_Tables.sql.  This script creates the two tables (context and data) required for the integration.  The script should be edited to include the name of the database which will contain the two tables.
2. Create_AS_DPM_Dimension_Reference_Tables.sql.  This script can be run on the database but it is for reference purposes only and is not a requirement for the integration.  It can be used as a reference for extracting all possible sheet or row dimension member values. 
3. Create_AS_DPM_Definition_Tables.sql. This script can be be used for extracting types of values applicable to cells in the corresponding variable row tables.

Drag and drop the generated SQL file from the saved location into the working tabof the relevant connection in Oracle Database (this may take some time for large scripts), e.g. TEST as shown below :

• Click the Run Script button.
• Select the connection, then click OK.

• You can view the results in the Script Output tab.
• To verify that the tables have been created, click the Refresh button.
• Expand the Tables folder node to view all the created tables.

Creating a Connection using SQL Oracle in DPM Authority

To create a new connection, click the Add button in Data Connections form above.

Binding

By creating a binding, you can automate the population of data into the fields required for the connection:

The binding information is retained for future use.

Note that you may skip binding creation and manually provide the Host, Service name and Schema (if applicable).

The schema name should match the one defined when the integration scripts were created, if applicable.

You can check the Service Name and Port in the Connection Properties tab in SQL Developer:

Table Field

This field is a combobox with the following options:

• Table code (e.g. S.27.01.01.01      )
• Table group code / filing indicator (e.g.     S.27.01     )

By default, this is set to Table code for both new and existing documents.

Depending on the selected option, the system looks for either the table code or the table group code in the SQL table.

Defaulting all data objects to use the SQL Connection

If you intend to source all data objects from the SQL Server Database, set the option Use this SQL connection for all tables to true.  If true;

• when the connection is saved - the data adaptor of all data objects in existing tables will be set to SQL; and
• the data adaptor of all data objects in new tables will be set to SQL.

If you intend to source some tables from the SQL Server database and other tables from different data sources, this should be set to false, and you will need to manually set up the adaptors for each table.

After completing the required fields, select Connect to proceed.

Enter the appropriate Authentication details and click OK.