FineBI

Connect to Hologres

1. Install FineBI.

   To install FineBI, see the FineBI official documentation.

2. Install the JDBC driver.

   Download the JDBC driver from the official PostgreSQL website.

   Note

   • You must use JDBC driver version 42.2.18 or later.

   • Place the downloaded PostgreSQL JDBC driver package in the <FineBI installation directory>\webapps\webroot\WEB-INF\lib directory and restart FineBI.

3. Connect to Hologres.

   1. Log on to FineBI as an administrator and choose Manage System > Data Connection > Data Connection Management.

   2. Click New Data Connection and select Hologres on the All tab.

      Note

      If your FineBI version is earlier than 5.1.14, you can also use a PostgreSQL data source to connect to Hologres.

   3. On the Hologres page, configure the connection settings. These parameters include Data Connection Name, Driver (select org.postgresql.Driver), Database Name, Host, Port, Username, Password, and Encoding (defaults to auto). For the Mode parameter, you must first click Connect to Database to load the schema list. The Data Connection URL format is jdbc:postgresql://hostname:port/database.

      The following table describes the parameters.

      Parameter	Description
      Data Connection Name	Enter a name for the Data Connection. For example, Hologres.
      Driver	Select org.postgresql.Driver for the driver.
      Database Name	The name of your Hologres database.
      Host	The public endpoint of your Hologres instance, which you can find on the instance details page in the Hologres console under Network Information.
      Port	The port of your Hologres instance, which you can find on the instance details page in the Hologres console under Network Information.
      Username	The AccessKey ID of your Alibaba Cloud account. For instructions, see Create an AccessKey pair.
      Password	The AccessKey Secret of your Alibaba Cloud account. For instructions, see Create an AccessKey pair.
      Data Connection URL	Use the format jdbc:postgresql://<host>:<port>/<databasename>. The parameters are described as follows: host: The public endpoint of the Hologres instance. port: The port of the Hologres instance. databasename: The name of your Hologres database.
      Advanced Settings	This example does not involve advanced settings. If your business requires this configuration, see the FineBI official documentation.

   4. After you configure the parameters, click Connect to Database to load the list of schemas. Then, select the schema you want to use from the drop-down list.

   5. In the upper-right corner of the page, click Test Connection. A Connection successful message indicates that FineBI can connect to the Hologres database.

   6. In the upper-right corner of the page, click Save to complete the Data Connection configuration.

4. Add data tables.

   1. In the left navigation pane, click Data Preparation to open the data list page.

   2. Open the target business package details page and click Add Table. From the drop-down menu, you can select Database Table, SQL Dataset, Excel Dataset, or Self-service Dataset.

   3. You can load data from Hologres by using options such as database tables or SQL datasets. This example uses a database table. Click Database Table to open the table selection page. After you select the Data Connection that you created, the right side of the page lists all data tables from the schemas in the selected database.

   4. Select the tables you want to load and click OK in the upper-right corner. FineBI loads the selected tables. Added tables are grayed out and cannot be selected again.

   5. After the tables are loaded, you can select a table from the business package's data list to begin data preparation. When a table is selected, the Data Preview tab displays its fields and records. Other tabs, such as Lineage Analysis, Associated View, Update Information, and Update Progress, are also available for these tasks.

5. Analyze data.

   After you load the data tables, you can use them to build visualizations. For more information, see the FineBI official documentation.

Best practices for the time filter control

When you create a table in Hologres, you can set the Segment_key (aliased as event_time_column) property to optimize indexing for time-based filter conditions. This practice prevents a full table scan and speeds up queries. By default, Hologres uses the first timestamp-type column in the table as the Segment_key.

[1] on lineitem  (cost=0.00..1.00 rows=1000 width=24)
                                            Segment Filter:
((l_shipdate >= '1993-01-01 00:00:00+08'::timestamp with time zone) AND (l_shipdate <= &#x27;1998-
12-31 23:59:59+08&#x27;::timestamp with time zone))
                                            Cluster Filter:
((l_shipdate >= '1993-01-01 00:00:00+08'::timestamp with time zone) AND (l_shipdate <= &#x27;1998-
12-31 23:59:59+08&#x27;::timestamp with time zone))
Optimizer: HQO version 0.10.0

To create a parameterized SQL Dataset, follow these steps:

1. Log on to FineBI. In the left navigation pane, click Data Preparation to open the data list page.

2. Open the target business package details page, click Add Table, and select SQL Dataset.

   Note

   • FineBI supports parameterized datasets. For more information, see the FineBI official documentation.

   • The parameterized SQL Dataset feature allows you to use parameters to create date controls.

3. Enter a name for the table in the Table Name field and your SQL query in the SQL statement module. Click Refresh, and the system will automatically recognize the parameters, which you can then configure. In the upper-right corner, click OK to save the dataset. For this example, enter test_bi_timestamp as the Table Name and use the following SQL statement: SELECT id,log_timestamp from test_bi_timestamp where log_timestamp <='${time}'::timestamp. Set the parameter name to time, its type to Date, and its default value to 2021-05-08.

   SELECT 
     id,
     log_timestamp 
   from test_bi_timestamp 
   where log_timestamp <='${time}'::timestamp;

4. On the dashboard, select the Date control. Select Bind Parameter, select time from the list, and then click OK.

5. Set and apply the date filter. Select the Date filter component and set the date to 2021-05-08. The bar chart on the left updates to show the record count for the selected date.

6. After the configuration is complete, you can view the executed SQL query in the logs. For information about how to view logs, see the FineBI official documentation.

   The generated SQL is as follows:

   select 
     "T_13E5C8"."id" as "id", 
     "T_13E5C8"."log_timestamp" as "log_timestamp"
   from (
     select 
         id,
         log_timestamp 
     from test_bi_timestamp 
     where log_timestamp <='2021-05-08'::timestamp
   ) as "T_13E5C8"
   limit 5000;

   The generated execution plan is as follows:

    Limit  (cost=0.00..0.15 rows=1000 width=16)
      ->  Gather Motion  (cost=0.00..0.14 rows=1000 width=16)
            ->  Limit  (cost=0.00..0.10 rows=1000 width=16)
                  ->  Parallelism (Gather Exchange)  (cost=0.00..0.10 rows=1000 width=16)
                        ->  DecodeNode  (cost=0.00..0.10 rows=1000 width=16)
                              ->  Limit  (cost=0.00..0.10 rows=1000 width=16)
                                    ->  Index Scan using holo_index:[1] on test_bi_timestamp  (cost=0.00..1.00 rows=1000 width=16)
                                          Segment Filter: (log_timestamp <= '2021-05-08 00:00:00+08'::timestamp with time zone)
    Optimizer: HQO version 0.8.0
   (9 rows)

   When the Segment Filter keyword appears in your SQL's execution plan, it indicates that Segment_key has taken effect, and you can use the time control created from a parameterized SQL Dataset.