- SAP Lumira Tutorial
- SAP Lumira - Home
- SAP Lumira - Overview
- SAP Lumira - Data Sources
- SAP Lumira - Data Acquisition
- SAP Lumira - Editing Acquired Data
- SAP Lumira - Viewing Connections
- SAP Lumira - Working with Excel Files
- SAP Lumira - Working with CSV Files
- SAP Lumira - Connecting HANA Views
- SAP Lumira - Download from HANA
- SAP Lumira - Universe as a Data Src
- SAP Lumira - Using Query with SQL
- SAP Lumira - Working Modes
- SAP Lumira - Prepare Phase
- SAP Lumira - Visualize Phase
- SAP Lumira - Compose Phase
- SAP Lumira - Share Phase
- SAP Lumira - New Document
- SAP Lumira - Creating Charts
- SAP Lumira - Chart Types
- SAP Lumira - Conditional Formatting
- SAP Lumira - Preparing Data in Tab
- SAP Lumira - Editing Data
- SAP Lumira - Numbers & Dates
- SAP Lumira - Enriching Data
- SAP Lumira - Working with Datasets
- SAP Lumira - Visualizing Data
- SAP Lumira - Legend Colors
- SAP Lumira - Creating Stories
- SAP Lumira - Slideshows
- SAP Lumira - Customizing Stories
- Lumira - Charts, Stories & Datasets
- SAP Lumira - Publishing Datasets
- SAP Lumira Useful Resources
- SAP Lumira - Questions Answers
- SAP Lumira - Quick Guide
- SAP Lumira - Useful Resources
- SAP Lumira - Discussion
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.
Database | JDBC Driver Name |
---|---|
Oracle | ojdbc14.jar |
Microsoft SQL Server | sqljdbc4.jar |
Teradata | terajdbc4.jar and tdgssconfig.jar |
Sybase | jconn4.jar |
IBM DB2 | db2jcc.jar or db2cc.jar and db2jcc_license_cu.jar for versions earlier than 9.5 |
IBM Netezza | nzjdbc.jar |
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.