SAP IDT - Managing Connections
A connection in IDT is used to connect to a relational or an OLAP data source using SAP BusinessObjects reporting tool. It can be a local connection or a connection published in a central repository.
Different Types of Connections Supported in IDT
Following are the types of connections that are supported in IDT −
This is used to design Data Foundation layer in Information Design tool. You can import tables and joins from data source.
Using an OLAP connection, you can design a business layer while creating a Universe. It allows you to connect to multidimensional schema directly and to import them for Universe design. Later, they can be used with SAP Business Objects reporting tool.
To connect to SAP NetWeaver BW, you can use SAP BICS client middleware to provide access to BEx query. Connections in IDT can be locally saved or they can be secured and published in a central repository.
Local connections are saved as .cnx files and they can be accessed by any user who is running IDT. Once you publish the connection to the repository, they are changed to a secured connection.
A secured connection is published into the repository and saved in Connection folder. You can also create secured connections by using Insert Relational and Insert OLAP connection commands from the repository resource view.
Following are the key properties of a secured connection −
Users must be authenticated.
User rights can be defined at the user level to grant or deny access to connections or connection properties.
Secured connections can be used or shared by authenticated users.
Creating a New Connection in IDT – Relational
Go to Information Design tool under BI client. To create a new Relational connection, first start with a new project under Local Project view.
Click New → Project → Enter the Project Name → Finish.
Right-click on Project name → New → Relational Connection.
Enter the connection/resource name → click Next.
Select the middleware as per data source. You can select SAP or non-SAP as data source and set up a relational connection to the database.
We have selected SAP from the list → SAP HANA → JDBC → click Next.
Enter the Authentication mode, user name and password. Enter the host name and the Instance number and click Next.
In the next window, you can define connection parameters like - Time out, Array fetch size, Array Bind size, etc. Click Finish.
A Relational connection to source database is created with .cnx file extension. You can click Test Connection.
Lower part of Window tells you about connection parameters - Login parameters, configuration parameters, etc.
→ Test Connection → Successful
Similarly, you can create a Relational connection for other data sources.
We have to publish this connection to the Repository to make it available for use.
Right Click on connection name → Publish connection to Repository → Enter BO repository password → Connect → Finish → Yes.
.cns- secured Repository connection
.cnx-local unsecured - If you use this connection it will not allow you to publish anything to the repository.
This shows how a relational connection can be created in the Information Design Tool. To summarize the steps that are involved in creating a connection −
- Enter the name of the connection
- Select the middleware for the target database
- Enter the login parameters to connect to the relational data source
- Configure and customize parameters to optimize the connection and Finish
- To change it to secure, publish the connection to the Repository
Renaming or Deleting a Connection
To rename or delete a connection, right-click on connection name under Local Projects area.
You can also delete or edit a Relational connection like this.
Creating a New Connection in IDT – OLAP
An OLAP connection is used to connect to multidimensional view or a data model to design Business Layer in Information Design tool.
To create a new OLAP connection, right-click on Project name → New → OLAP Connection.
Enter the connection name, description and click Next.
Next, select an OLAP middleware driver. It shows a list of all available OLAP data sources. You can select any of OLAP data source as per requirement.
Note that you don’t need to create a Data Foundation, as an OLAP directly hits the Business Layer. You can define dimensions and measures and other properties at Business Layer.
Select the suitable middleware driver to connect to OLAP data source and click Next.
Enter the connection parameters, authentication details, system name, instance number, etc. and click Next.
You can also select a specific cube to connect or all the cubes in the design time repository. If you select a particular cube, then using this OLAP connection you can design Business Layer only on the objects available in that cube.
Now, you need to publish the connection to the Repository. Right-click on connection → Publish the Connection to the Repository.
Login Parameters for Connection
When you create a relational or an OLAP connection in IDT to connect to any data source, you have to provide login parameters of source database such as Host Name and Instance Number.
While creating a connection, you can configure various connection parameters −
Connection Pool Mode − This is used to keep the connection active.
Pool Timeout − When you set the connection pool mode to keep connection active for, length of time in minutes to keep connection open.
Array Fetch Size − This tells the maximum number of rows that can be fetched from the database.
Example − Suppose you enter a value of 25 and you query returns 150 rows so it will be fetched with 6 fetches of 25 rows each.
Array Bind Size − This field is not required for designing Universe in IDT.
Login Timeout − It determines the minutes a connection attempts timeout and an error message is displayed.
There are various custom parameters that can also be defined like ConnectInit, Hint.
Editing a Connection
To edit a connection, double-click on the connection name in Local Project View.
Go to General Information tab → Edit.
To edit the connection parameters, click Edit button. You can edit the authentication details and connection parameters.
To change the middleware driver, click Change Driver. Select the Driver from the dropdown list.
You can also test the availability of the database server, click Test Connection.
The "Connection Definition" pane shows the information stored about the connection – Login parameters, Configuration parameters, etc.