6.3.2 SQL Server - Creating a Connection
To create a connection to a database, click Home -> Manage Connections -> SQL.
Creating Database Scripts
Report Authority can produce 2 database scripts which can be used to facilitate the integration with an existing database:
- 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.
- 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.
The scripts can be generated by clicking on the Create Integration Scripts button in the screenshot above.
Creating a Connection
To create a connection, click the Add button. Enter the server, Database name and click connect:
Enter the appropriate Authentication details and click OK.
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.