SQL data source

To use SQL as a data source you have to do the following steps:

Create a new source application in the flex.bi source application tab.

Make sure you select SQL as your data source when creating a new source application

If you have created already another similar SQL data source then you can export its definition and paste it in Import definition to create new SQL source application with the same parameters.

SQL source parameters

For the following fields provide the information required:

Database type

Select the database type from the list of available options.

Host

Write the host IP of your selected database type.

Port

Write the port number your database uses, if you are using the default port for your selected database then you can leave this blank.

Database

Write your database name.

Username

Write the username of the user you wish to use to connect to the database.

Password

Write the password of the user you are using to connect to the database.

SQL SELECT statement

Write an SQL statement that will be used to retrieve data from the database. The SELECT part of the SQL statement determines the columns you will have to map in the next step.

Incremental import

By default, SQL import will always re-import all data. During the import the old imported data will be deleted and then replaced by new returned data. If SQL SELECT returns many rows then each data import might take long time as well as reports might return incomplete data while the old data are deleted and not all new data are imported.

If you would like to use incremental import to update just the recent source data after the initial full import, then click Incremental import parameters and enable it:

In addition, specify the incremental import WHERE conditions in the SELECT statement. Use special -- if incremental, -- else, -- end comments (as shown in the example) to specify which lines should be included only for the incremental import and which lines only for the full import. Typically you need to specify a condition that filters just recently updated rows using the corresponding date column.

If incremental import is used then it will be required to specify a Source ID column in the source columns mapping step. Source ID column value should provide a unique results row identifier. It is used to identify when some existing imported rows in flex.bi should be replaced with updated source data during the incremental import.

If you have previously imported all data without the incremental import option, then it will not be possible to modify the source columns mapping. Therefore, at first delete all imported data for this SQL source, and then modify the source columns mapping and specify the Source ID column.

Source column mapping

To conduct the Source column mapping please visit Data mapping.

When you have finished mapping your data, press 'Start Import' button.

If your import finishes successfully, you can see the results in the Analyze tab, in the cube you imported your data to.