Connecting to data

BIRT Analytics Loader supports connections to relational databases and flat files. Data source connections are accessible across multiple projects associated with the same user. File connections are associated only with the project where they are defined. You cannot share file connections across multiple projects.

Connecting to databases

You can create and name as many data source connections as a load process requires. The data sources can be of different types. For example, a load process can use data from a database and data from a flat file repository. Each type of data source requires different connection information.

BIRT Analytics Loader connects to databases using native and ODBC drivers. BIRT Loader supports native drivers for the following database types:

Microsoft SQL Server

MySQL Database Server

Oracle Database

PostgreSQL

BIRT Analytics Loader also supports connecting to a database using an ODBC driver. To connect to a database having a native driver type not supported by BIRT Analytics Loader, you must provide an ODBC driver and set up an ODBC connection that uses the ODBC diver.

In BIRT Analytics Loader you can define a new data source, edit an existing one, or remove a data source, as shown in Figure 3-2.

Figure 3-2

Connection tools for database data sources

Using native drivers

Before creating a connection using a native driver make sure the driver is installed on your machine. For MySQL server for example, check if the path to the driver is in the System Path. To see the System Path, use Control Panel→System→Advanced System Settings
→Environment Variables→Path, as shown in Figure 3-3.

Figure 3-3

Editing Path variable

To create a data source connection you must provide the database connection information, as shown in Table 3-1. Contact your database administrator to obtain this information.

Table 3-1

Database connection properties

Property	Description
Name	The name of the database connection, which must be unique
Type	The type of connection, selected from the values in the drop-down list
Server	The database server, which can be a machine name or an IP address
Port	The connection port
User	A valid user name to connect to the database
Password	A valid password to connect to the database
Database	The name of the database
Driver	The driver name, required for ODBC drivers only
Connection string	Custom connection properties, required for custom ODBC drivers only

How to connect to a database using a native driver

1	In Databases, choose the + sign. Datasource appears, as shown in Figure 3-4.

Figure 3-4

Using a native driver

2	In Datasource, define the following properties:

In Name, type a unique name. Using a name that describes the database content helps identify different databases you define as data sources.

In Type, select the source database type from the list of available types. In this example, mysql is the source database type.

In Server, type the name of the server that hosts the source database. In this example, an IP address identifies that server.

In Port, type the connection port. The mysql database installation in this example uses the default port 3306.

In User, type a valid user name defined for the source database.

In Password, type a valid password defined for the source database.

In Database, type the name of a database that contains source data.

In Driver, optionally type a native driver name, or type no characters. A connection that uses a native driver requires no name.

3	Choose OK to create the named data source connection. A new data source connection name and type appear in Databases, as shown in Figure 3-5.

Figure 3-5

A data source connection that uses a native database driver

Using ODBC drivers

Creating a connection using an ODBC driver is very similar to the native driver connections. The difference is that you must provide a driver name as a connection property. As well as the predefined ODBC data source types, a custom ODBC data source is available. To use the custom ODBC data source, you must type the required connection parameters as a text value.

To determine the name of your ODBC driver, open Control Panel→ODBC Data Source Administrator. The driver name appears in the Drivers tab, as shown in Figure 3-6.

Figure 3-6

Finding an ODBC driver name

How to connect to a database using an ODBC driver

1	In Databases, choose the + sign. Datasource appears.

In Datasource, define the following properties for a standard ODBC data source, as shown in Figure 3-7:

Figure 3-7

Using an ODBC driver

In Name, type a unique name. Using a name that describes the database content helps identify different databases you define as data sources.

In Type, select ODBC from the list of available connection types.

In Server, type the name of the server that hosts the source database. In this example, a machine name, eng1-win32-vm, identifies that server.

In Port, type the connection port. The mssql database installation in this example uses the default port, 1433.

In User, type a valid user name defined for the source database.

In Password, type a valid password defined for the source database.

In Database, type the name of a database that contains source data.

In Driver, type the driver name, as defined in the ODBC Data Source Administrator. In this example, the name of the ODBC Microsoft SQL Server driver is SQL Server.

Alternatively, for a custom ODBC data source, define the following properties, as shown in Figure 3-8:

Figure 3-8

Using a custom ODBC driver

In Name, type a unique name. Using a name that describes the database content helps identify different databases you define as data sources.

In Type, select Custom ODBC from the list of available connection types.

In User, type a valid user name defined for the source database.

In Password, type a valid password defined for the source database.

In Database, type the name of a database that contains source data.

In Connection string, type the parameters required to connect to the custom data source.

2	Choose OK to create the named data source connection. A new data source connection name and type appear in Databases, as shown in Figure 3-9.

Figure 3-9

Database connections

Connecting to files

BIRT Analytics Loader can access data from a text file, or flat file, that conforms to a defined and consistent structure. These files are typically generated by business systems and applications that create logs. These files can also be spreadsheets saved as comma-separated values (CSV) files. Before using text file data in a load process, make sure the file uses a valid structure. The size of the text files is limited only by the limitations set by the operating system.

BIRT Analytics Loader supports the following options for defining flat file data sources:

Uploading a file from a local system

Uploading a file from a remote system

Uploading multiple files using a file list

Using BIRT Analytics Loader - Files, you manage data uploads from data files stored on local and remote systems, using the toolset shown in Figure 3-10.

Figure 3-10

Connection tools for flat file data sources

To prepare to access your text files you must know the file name and location.

Uploading local files

After selecting the file, it will be uploaded into the data folder of the load project. Because of network reasons there is a configurable maximum file size available.

How to upload a file from a local file system

1	In Files, choose the up arrow. File Upload appears, as shown in Figure 3-11.

Figure 3-11

Uploading a local file

2	In File Upload, navigate to the folder where the file is located.

3	Select the file and choose Open. The file appears in Files, as shown in Figure 3-12. The local file is stored in the data folder of the project.

Figure 3-12

Creating a local file data source

Copyright Actuate Corporation 2013

BIRT Analytics 4.2