FineBI
FineBI is a powerful big data analytics tool. FineBI allows organizations to analyze and share data for informed decision-making. It helps transform raw data into insightful visualizations, track KPIs, identify trends, and predict future outcomes.
PostgreSQL compatibility mode in YDB enables the use of FineBI to query and visualize data from YDB. In this case FineBI works with YDB just like with PostgreSQL.
Warning
At the moment, YDB's compatibility with PostgreSQL is under development, so not all PostgreSQL constructs and functions are supported yet. PostgreSQL compatibility is available for testing in the form of a Docker container, which can be deployed by following these instructions.
Prerequisites
Before you begin, make sure that the following software is installed:
-
PostgreSQL JDBC driver uploaded to FineBI.
Note
You can download the latest PostgreSQL JDBC driver from the Download page of the PostgreSQL web site. For information on how to upload the PostgreSQL JDBC driver to FineBI, refer to the FineBI documentation.
Adding a database connection to YDB
To connect to YDB from FineBI using the PostgreSQL wire protocol, follow these steps:
-
Log in to FineBI as the
admin
user. -
Navigate to System Management > Data Connection > Data Connection Management.
-
Click the New Data Connection button.
-
In the Search field, type
postgresql
to find the PostgreSQL icon. -
Click the PostgreSQL icon.
-
Enter the YDB credentials in the corresponding fields:
-
Data Connection Name. The YDB connection name in FineBI.
-
Driver. The driver that FineBI uses to connect to YDB.
Select
Custom
and the installed JDBC driverorg.postgresql.Driver
. -
Database Name. The path to the database in the YDB cluster where queries will be executed.
Alert
Special characters in the path string must be URL encoded. For example, ensure that you replace slash (
/
) characters with%2F
. -
Host. The endpoint of the YDB cluster to which the connection will be made.
-
Port. The port of the YDB endpoint.
-
Username. The login for connecting to the YDB database.
-
Password. The password for connecting to the YDB database.
-
-
Click Test Connection.
If the connection details are correct, a message confirming a successful connection will appear.
-
To save the database connection, click Save.
A new database connection will appear in the Data Connection list.
Adding an SQL Dataset
To create a dataset for a YDB table, follow these steps:
-
In FineBI, open the Public Data tab.
-
Select a folder, to which you want to add a dataset.
Warning
You must have the Public Data Management permission for the selected folder in FineBI.
-
Click Add Dataset and select SQL Dataset from the drop-down list.
-
In the Table Name field, enter a name for the dataset.
-
In the Data from Data Connection drop-down list, select the YDB connection you created.
-
In the SQL Statement field, enter the SQL query to retrieve the necessary columns from a YDB table. For example,
SELECT * FROM <ydb_table_name>
for all columns.Tip
To create a dataset for a table located in a subdirectory of a YDB database, specify the table path in the table name. For example:
SELECT * FROM "<path/to/subdirectory/table_name>";
-
To test the SQL query, click Preview. If the query is correct, the table data will appear in the preview pane.
-
To save the dataset, click OK.
After creating datasets, you can use data from YDB to create charts in FineBI. For more information, refer to the FineBI documentation.
Creating a chart
Let's create a sample chart using the dataset from the episodes
table, as described in the YQL tutorial. Among other things, this tutorial covers how to create this table and populate it with data. It will be a pie chart that demonstrates how many episodes each season of a given series contains.
The table contains the following columns:
series_id
season_id
episode_id
title
air_date
To create a chart, follow these steps:
-
In FineBI, open the My Analysis tab.
-
Click New Subject.
The Select Data dialog box will appear.
-
In the Select Data dialog box, navigate to the dataset for the
episodes
table and click OK. -
Click the Component tab at the bottom of the page.
-
In the Chart Type pane, click the Pie Chart icon.
-
In the list of columns in the
episodes
dataset, click the arrow next to theepisode_id
column and select Convert to Dimension from the drop-down list. -
Drag the
season_id
column to the Color field. -
Drag the
title
column to the Label field. -
Drag the
series_id
column to the Filter field.The Add Filter to episodes.series_id dialog box will appear.
-
In the Add Filter to episodes.series_id dialog box, select
Detailed Value
and click Next Step. -
Specify the following condition:
series_id
Equal To
Fixed Value
2
-
Click OK.
The diagram will display data only for the series that has the ID of
2
. -
Click Save.