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:

  • FineBI.

  • 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:

  1. Log in to FineBI as the admin user.

  2. Navigate to System Management > Data Connection > Data Connection Management.

  3. Click the New Data Connection button.

  4. In the Search field, type postgresql to find the PostgreSQL icon.

  5. Click the PostgreSQL icon.

  6. 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 driver org.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.

  7. Click Test Connection.

    If the connection details are correct, a message confirming a successful connection will appear.

  8. 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:

  1. In FineBI, open the Public Data tab.

  2. 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.

  3. Click Add Dataset and select SQL Dataset from the drop-down list.

  4. In the Table Name field, enter a name for the dataset.

  5. In the Data from Data Connection drop-down list, select the YDB connection you created.

  6. 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>";
    
  7. To test the SQL query, click Preview. If the query is correct, the table data will appear in the preview pane.

  8. 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:

  1. In FineBI, open the My Analysis tab.

  2. Click New Subject.

    The Select Data dialog box will appear.

  3. In the Select Data dialog box, navigate to the dataset for the episodes table and click OK.

  4. Click the Component tab at the bottom of the page.

  5. In the Chart Type pane, click the Pie Chart icon.

  6. In the list of columns in the episodes dataset, click the arrow next to the episode_id column and select Convert to Dimension from the drop-down list.

  7. Drag the season_id column to the Color field.

  8. Drag the title column to the Label field.

  9. Drag the series_id column to the Filter field.

    The Add Filter to episodes.series_id dialog box will appear.

  10. In the Add Filter to episodes.series_id dialog box, select Detailed Value and click Next Step.

  11. Specify the following condition:

    series_id Equal To Fixed Value 2

  12. Click OK.

    The diagram will display data only for the series that has the ID of 2.

  13. Click Save.