You can import data from 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 eazyBI cubes, dimensions and measures - you will need to understand multi-dimensional data model to be able to map source file columns to cube dimensions and measures.
Create new source application
Go to Source Data tab and Add new source application and select SQL application type.
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
In the next step you will need to provide SQL source parameters which 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 Private eazyBI you can select also Generic JDBC database type and specify JDBC driver class name and JDBC URL. Please copy corresponding JDBC driver jar file to
eazybi_private/lib directory and restart Private eazyBI after that.
Specify database server Host and optional Port parameter (depending on selected database type some other optional parameters might be available). Specify Database name and database Username and Password that should be used.
Enter SQL SELECT statement which should be used to retrieve the data. Before SQL SELECT statement you can add SQL comment (either start line with
-- or enclose in
*/) and then this comment will be shown also in Source Data source applications list (can be useful if many SQL SELECT sources are used in the same account).
Source columns mapping
SQL source columns mapping is similar to Excel or CSV file columns mapping and Import from REST API. Please review these documentation pages to learn more about columns mapping to eazyBI dimensions and measures.
See 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 draft of mapping then click Back to edit and confirm that you want to save changes.
Importing of source data
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 Source Data tab again and click Import button again to import the latest data from 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).
As it was mentioned in the beginning you can export SQL source application definition by clicking Export definition from Source Data tab source application listing and then copy this definition and paste in Import definition field when creating different source application.