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.
2 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.
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‑9.
Figure 3‑9 Database connections
Figure 3‑10 Sharing button in the Datasource definition screen
Testing a Datasource connection
It is possible to test the correctness of your connection to an external database (using native or ODBC drivers) when you are creating or editing an existing data source. This is done via the “Test Connection” button in the “Datasource” definition window. (See Figure 3‑11 below).
Figure 3‑11 The “Test connection” button in the “Datasource” definition window
Sharing databases
This option makes it possible to share data sources with other QLoader users, whether using ODBC or Native drivers. This is done via the “Sharing” button which is available when you are editing an existing datasource definition. Clicking on the “Sharing” button opens a window where you can select the QLoader users that you wish to share with. (See Figure 3‑12).
Figure 3‑12 The “Sharing” button in the “Datasource” definition window
Updating data source definitions
You can modify the data source definitions by choosing the pencil icon. The changes in the data source definitions require an update of the related table definitions. Saving the changes displays a message, as shown in Figure 3‑13, asking you to confirm the update.
Figure 3‑13 Updating data source message
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. There is no limit to the size of text files. This is due to the fact that the Default value in configuration.xml is -1.
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‑14.
Figure 3‑14 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‑15.
Figure 3‑15 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‑16. The local file is stored in the data folder of the project.
Figure 3‑16 Creating a local file data source
Uploading remote files
The remote data provider option allows you to load files using a web address. You have to choose a name, the Uniform Resource Identifier (URI), and the HTTP method (GET, POST).
How to upload a file using URI
1 In Files, select the remote data provider icon. URI appears.
2 In URI, enter the following information, as shown in Figure 3‑17:
In Name, type the name you want to assign to this data source.
In URI, type the URI, specifying the file location.
In Method, select the method for obtaining the file. Contact the file provider to identify the method.
In Parameters, enter parameters if needed.
Choose + to add parameter. In Name, type the parameter name. In Value, enter the parameter value, as shown in Figure 3‑17.
To modify a parameter, select a column and change the entry.
Choose - to remove a parameter.
Figure 3‑17 Providing URI properties
3 Choose OK. The new flat file data source appears in Files, as shown in Figure 3‑18.
Figure 3‑18 Creating a remote file data source
Uploading files using a file list
On many occasions, due to a large data volume and file size restrictions, it is convenient to break the data into multiple files. In such cases you must use a file list to create a file data source and upload the data into a single table.
How to upload files using a file list
1 In Files, upload all the files you want to include in a list, the same way you upload local files.
2 Press and hold Ctrl while using the mouse to select multiple file names, as shown in Figure 3‑19.
Figure 3‑19 List file data source
3 In Files, choose New File list. File names that you selected in step 2 appear, as shown in Figure 3‑20.
Figure 3‑20 Selecting a list file
4 In Name, type a name for the new list file data source, then choose OK.
The list file data source appears in Files, as shown in Figure 3‑21.
Microsoft SQL Server
.png){kind=small}
