SAP Lumira - Using Query with SQL

SAP Lumira allows you to create dataset by using SQL query for target data source, manually. You can specify the source tables, columns, procedures, and functions to acquire data in SAP Lumira.

You can use JDBC drivers for typical databases like Oracle, SQL Server, IBM DB2, Sybase, Teradata.

Connecting to a Query with SQL Data Source

To connect to a database using SQL query to acquire data, you should have a good understanding of your database and good command over SQL.

You should have correct database drivers installed for your middleware, which allow the client application to connect to middleware and to database.

JDBC drivers have to be installed for database middleware for using SQL query. The access driver is the .jar file. You can download this file from the vendor site and copy to the driver folder in application path.

Given below are the steps to connect to a query with SQL Data Source.

Step 1 − To use SQL query to create dataset, go to File → New.

Select the option Query with SQL to download a dataset and click Next.

Step 2 − Select SQL query. All queries in green represent the drivers, which are installed properly for middleware.

Step 3 − Select database middleware for target database and click Next.

Step 4 − Enter the Login Credentials, like host name, Instance number, User name and Password and click Connect as shown below.

Step 5 − In the left pane, it will show you all the Schemas and tables in the target database.

Select the target table and add to the Query panel. You can click the Preview option to see the data preview.

Step 6 − You can use the SELECT statement only in the SQL editor to acquire data from database tables. Click Create to add the dataset to the Prepare tab. You can also use “Select All” option, if you want to use all the columns or uncheck the box to remove any columns to dataset. Click Create.

Specifying Query with SQL connection properties

Using SQL Query option also allows you to create your own data provider, by manually entering the SQL for a target data source to acquire table data.

While using Query with SQL, connection information for the target database should be entered and various connection properties can be used.

• User Name − To connect with target database

• Password − To connect with target database

• Server and Port name − of the target database

• Database − Name of the database

You can select Advance options like −

• Connection Pool Mode − To keep connection active

• Pool timeout − Time duration to keep connection active in minutes.

• Array Fetch Size − to determine number of rows to fetch from target database.

• Array Bind Size − Larger bind array, more number of rows will be fetched.

• Login Timeout − Time before a connection attempts a timeout.

JDBC Driver Properties

These are various connection properties, which can be defined while using query with SQL option in creating Lumira dataset.