You can import data from a specified database connection and using SQL SELECT statement result data. Data import is similar to Excel and CSV file upload and Import from REST API. You can also schedule regular daily import from provided SQL SELECT statement results.
Please at first read about flex.bi cubes, dimensions and measures - you will need to understand the multi-dimensional data model to be able to map source file columns to cube dimensions and measures.
On this page:
toc
Create new source application
Go to Source Data tab and Add new source application and select SQL application type.
If you have already created another similar SQL data source, then you can export its definition and paste it in Import definition to create a new SQL source application with the same parameters.
...
In the next step, you will need to provide SQL source parameters that will be used to retrieve the data. See example:
In Database type, select database server type (MySQL, PostgreSQL, MS SQL, and Oracle are supported).
In flex.bi Enterprisebi Enterprise, you can also select Generic JDBC database type and specify the JDBC driver class name and JDBC URL. Please copy the corresponding JDBC driver jar file to flexbi_private/lib directory and restart flexrestart flex.bi Enterprise bi Enterprise after that.
Specify database server Host and optional Port parameter (depending on the selected database type, some other optional parameters might be available). Specify the Database name and database Username and Password that should be used. For Oracle database in the Database name field, provide database SID or Service Name.
...
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:
(example for MySQL database, change SQL conditions according to your database)
...
If an 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 flexin flex.bi should be replaced with updated source data during the incremental import.
...
See an example of SQL SELECT columns mapping:
After mapping all necessary columns, you can click Start import. If there will be any mapping errors, then they will be shown, and columns with errors will be highlighted. If you need to save a draft of mapping, then click Back to edit and confirm that you want to save changes.
...
If source columns mapping was saved without any validation errors, then SQL source application will be queued for background import. You will see the updated count of imported rows during the import:
You can later visit the Source Data tab again and click the Import button again to import the latest data from the SQL source. During each import, it will at first delete all data that were imported previously from this source and then import new data. In addition, you can also click Delete data to delete imported data from this source (you need to delete imported data also if you want to change source columns mapping).
...