6.3.2 SQL Server - Creating a Connection

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.

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.

The scripts can be generated by clicking on the Create Integration Scripts button in the screenshot above. The form that appears allows you to choose the type of SQL connection and, optionally, the schema name.

Creating a Connection using MS SQL Service

To create a connection, click the Add button.  

Binding

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

Please note that MS SQL Server or Oracle Database must be selected in this form. The binding information is retained for future use. The schema name should match the one defined when the integration scripts were created, if applicable.

Note that you may skip binding creation and manually provide the server, database name, and schema details.

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. See this article. 

After completing the required fields, select Connect to proceed.

Enter the appropriate Authentication details and click OK.