Use Parameter Injection

1) To connect multiple datasets/data tables based on some fields, we commonly use 3 methods: filter, SQL statement and parameter injection.

2) Using filters will make the loading speed of the report be rather slow while SQL statements may be a little bit complex. If choosing parameter injection, you can improve the loading speed of the report while eliminate the need to write complex SQL statements. The superior performance of parameter injection is apparent when tackling datasets with small volume. For extreme large datasets, SQL statements is preferred.

3) The mechanism of parameter injection: Filter the data in the data query by the injected value so the query only reads the data after filtering. In this way, the performance of the report is improved. 

1. The administrator login the decision-making platform

1) Go to [Manage] > [Intelligent Operations] > [Platform Log].

2) Change the system log level to INFO and click [Save].

1. Create DB Query datasets

1) Create a dataset [ds1] and drag table ORDERS.

2) Create another dataset [ds2] and drag table [ORDERDETAIL].

2. Insert data columns

1) B3~E3: Insert data columns [ORDERID], [SIGNDATE], [SHIPDATE] and [AMOUNT] from [ds1].

2) F3~I3: Insert data columns [ORDERID], [PRODUCTID], [QUANTITY] and [PRICE] from [ds2].

3) B2~I2: Input title and fill with grey.

4) B2~I3: Align at the center and add borders.

3. Set a filter

1) Select F3 and edit the filter.

2) Set the condition as [ORDERID] Equal to B3 and click [Add]. (i.e. use ORDERID to connect the two datasets)

4. Preview

5. Analyze the log

1) The calculation takes 104 ms.

2) Obviously, all data of [ds2] are extracted. If the data volume of [ds2] is large, the method to extract all data can harm the performance.

1. Create a DB Query dataset

1) Create a dataset [ds1].

2) Use the following SQL statement

SELECT * FROM ORDERS aleft join ORDERSDETAIL b on a.ORDERID=b.ORDERID

2. Insert data columns

1) B3~I3: Insert data columns [ORDERID], [SIGNDATE], [SHIPDATE], [AMOUNT], [ORDERID], [PRODUCTID], [QUANTITY] and [PRICE] from [ds1].

2) B2~I2: Input title and fill with grey.

3) B2~I3: Align at the center and add borders.

3. Preview

4. Analyze the log

1) The time cost is reduced to 36 ms.

2) The execution of SQL statement is fast. However, the definition of SQL statements can be rather bother if there are a number of data tables included. In addition, the readability is poor.

1. Create DB Query datasets

1) Create a dataset [ds1] and drag table [ORDERS].

2) Create another dataset [ds2] and use the following SQL statements

SELECT * FROM ORDERSDETAILWHERE ORDERID='${orderid}'

2. Insert data columns

1) B3~E3: Insert data columns [ORDERID], [SIGNDATE], [SHIPDATE] and [AMOUNT] from [ds1].

2) F3~I3: Insert data columns [ORDERID], [PRODUCTID], [QUANTITY] and [PRICE] from [ds2].

3) B2~I2: Input title and fill with grey.

4) B2~I3: Align at the center and add borders.

3. Set parameter injection

1) Select F3 and go to [Cell Element] > [Injection]

2) Clicl [+] to add a condition for injection. Input orderid in the left. In the right, set the data type as formula and click to define the formula as B3.

3) When finishing the settings, a blue triangle exits at the lower-left corner of F3, indicating that parameter injection is applied on the data column in F3.

4. Preview

5. Analyze the log

The log says the data are extracted one by one according to the values of parameter dataset, so the memory can be released timely after the data are read.