Visualizing MatrixOne Data with FineBI

Overview

FineBI is a next-generation big data analytics tool that empowers business professionals to gain deep insights and leverage their data. In FineBI, users can easily create diverse visualizations, analyze data freely, and explore their datasets. FineBI boasts various data connectivity features and can be used to build complex reports constructing data-driven decision analysis systems. It finds wide application in corporate management, production control, financial intelligence, and sales operations.

MatrixOne supports integration with the data visualization tool FineBI. This article will guide you on connecting to the standalone version of MatrixOne using FineBI and creating various visual data reports, assembling them into dashboards for data analysis and exploration.

Before you start

Note

The FineBI version used in the operations shown in this document is FineBI Linux 6.0. You can choose to install the Linux_unix_FineBI6_0-CN.sh package.

Connecting to MatrixOne Service via FineBI

  1. After logging into FineBI, select Management System > Data Connection > Data Connection Management > New Data Connection as shown below, then choose MySQL:

image-20230808174909411 2. Fill in the MatrixOne connection configuration, including the database name, host, port, username, and password. Other parameters can be left at their default settings. You can click the Test Connection button to verify if the connection is functional and then click Save :

image-20230808182330603

Creating Visual Reports Using MatrixOne Data

  1. Create Demo Data:

First, log in to the MatrixOne database and execute the following SQL statements to create the necessary data tables and views for the demo:

`` 1. create database orders; 2. use orders; 3. CREATE TABLEcategory(product_category_nameVARCHAR(255) DEFAULT NULL, 4.product_category_name_englishVARCHAR(255) DEFAULT NULL ); 5. CREATE TABLEitem(order_idVARCHAR(255) NOT NULL,order_item_idINT DEFAULT null, 6.product_idVARCHAR(255) DEFAULT null, 7.seller_idVARCHAR(255) DEFAULT null,shipping_limit_dateDATETIME DEFAULT null, 8.priceDECIMAL(10,2) DEFAULT null, 9.freight_valueDECIMAL(10,2) DEFAULT null 10. ); 11. CREATE TABLEreview( 12.review_idVARCHAR(255) NOT NULL, 13.order_idVARCHAR(255) DEFAULT null, 14.review_scoreTINYINT DEFAULT null, 15.review_comment_titleVARCHAR(255) DEFAULT null, 16.review_comment_messageTEXT DEFAULT null, 17.review_creation_dateDATETIME DEFAULT null, 18.review_answer_timestampDATETIME DEFAULT null, 19. PRIMARY KEY (review_id) 20. ); 21. CREATE TABLEorder_time( 22.order_idVARCHAR(255) NOT NULL, 23.customer_idVARCHAR(255) DEFAULT null, 24.yINT DEFAULT null, 25.qINT DEFAULT null, 26.mINT DEFAULT null, 27.dDATE DEFAULT null, 28.hINT DEFAULT null, 29.order_purchase_timestampDATETIME DEFAULT null 30. ); 31. CREATE TABLEorders( 32.order_idVARCHAR(255) NOT NULL, 33.customer_idVARCHAR(255) DEFAULT null, 34.order_statusVARCHAR(255) DEFAULT null, 35.order_purchase_timestampDATETIME DEFAULT null, 36.order_approved_atDATETIME DEFAULT null, 37.order_delivered_carrier_dateDATETIME DEFAULT null, 38.order_delivered_customer_dateDATETIME DEFAULT null, 39.order_estimated_delivery_dateDATETIME DEFAULT null, 40. PRIMARY KEY (order_id) 41. ); 42. CREATE TABLEproduct( 43.product_idVARCHAR(255) NOT NULL, 44.product_category_nameVARCHAR(255) DEFAULT null, 45.product_name_lenghtINT DEFAULT null, 46.product_description_lenghtINT DEFAULT null, 47.product_photos_qtyINT DEFAULT null, 48.product_weight_gINT DEFAULT null, 49.product_length_cmINT DEFAULT null, 50.product_height_cmINT DEFAULT null, 51.product_width_cmINT DEFAULT null, 52. PRIMARY KEY (product_id) 53. ); 54. CREATE TABLErfm( 55.customer_idVARCHAR(255) DEFAULT null, 56.user_typeVARCHAR(255) DEFAULT null, 57.shijian` DATE DEFAULT null 58. );

  1. CREATE view total_order_value as select t.order_id,product_id,seller_id,(pricetotal)+(freight_valuetotal) as order_value from (select order_id,count(*) as total from item group by order_id) t join item on t.order_id=item.order_id;

  2. CREATE view order_detail as select a.order_id,product_id,seller_id, customer_id,round(order_value,2) as order_value, y,q,m,d,h,order_purchase_timestamp from total_order_value a inner join order_time b on a.order_id=b.order_id; ```

Next, use the following SQL import statements to import the prepared demo data into the respective tables of the MatrixOne database.

Note

Please note that the path /root/data/table_name.csv is the path to the data files for each table. You can generate your data following a similar process.

1. use orders; 2. load data local infile '/root/data/category.csv' into table category FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\r\n"; 3. load data local infile '/root/data/review.csv' into table review FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\r\n"; 4. load data local infile '/root/data/product.csv' into table product FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\r\n"; 5. load data local infile '/root/data/item.csv' into table item FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\r\n"; 6. load data local infile '/root/data/order_time.csv' into table order_time FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\r\n"; 7. load data local infile '/root/data/orders.csv' into table orders FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\r\n"; 8. load data local infile '/root/data/rfm.csv' into table rfm FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "\r\n"; 2. Add Data Sets:

In FineBI, click Public Data, then click New Folder to create and select a folder. After that, click New Data Set, choose SQL Data Set, and add the SQL query to the selected folder. Enter the dataset name and input the SQL query as shown below:

1. select d, 2. count(order_id) as order_num, 3. count(DISTINCT customer_id) 4. from orders.order_detail 5. group by d 6. order by d

You can click the Preview button to view the results of the SQL query and then click OK to save it:

image-20230809091306270

Below are examples of all the query SQL used in this demo:

``` 1. -- Daily active users and order count 2. select d, 3. count(order_id) as order_num, 4. count(DISTINCT customer_id) 5. from orders.order_detail 6. group by d 7. order by d

  1. -- Monthly active users and order count
  2. select count(DISTINCT customer_id),
  3. count(order_id),
  4. concat(y, '-', m)
  5. from orders.order_detail
  6. group by y,m

  7. order by y,m

  8. -- Active users and order count in different periods

  9. select h,
  10. count(DISTINCT customer_id),
  11. count(order_id) order_num
  12. from orders.order_detail
  13. group by h

  14. order by h

  15. -- User count by type

  16. SELECT count(*),
  17. user_type
  18. from orders.rfm

  19. GROUP BY user_type

  20. -- Monthly GMV

  21. select y,m,
  22. sum(order_value),
  23. concat(y, "-", m) month
  24. from orders.order_detail
  25. group by y,m

  26. order by y,m

  27. -- Quarterly GMV

  28. select y,q,
  29. sum(order_value) gmv,
  30. concat(y, "季度", q) as quator
  31. from orders.order_detail
  32. group by y,q

  33. order by concat(y, "季度", q) asc

  34. -- Quarterly ARPU

  35. select y,q,
  36. round((sum(order_value)/count(DISTINCT customer_id)),2) arpu,
  37. concat(y, "季度", q) as quator
  38. from orders.order_detail
  39. group by y,q

  40. order by y,q

  41. -- Monthly ARPU

  42. select y,m,
  43. round((sum(order_value)/count(DISTINCT customer_id)),2) arpu,
  44. concat(y, "-", m) as month
  45. from orders.order_detail
  46. group by y,m

  47. order by y,m

  48. -- Important retained users' popularity index

  49. SELECT e.product_category_name_english good_type,
  50. SUM(a.order_value) ordder_total_value,
  51. ROUND(AVG(c.review_score), 2) good_review_score,
  52. (0.7SUM(a.order_value)+0.310000*ROUND(AVG(c.review_score), 7))
  53. top_rank_rate
  54. FROM orders.order_detail a
  55. INNER JOIN
  56. (SELECT customer_id
  57. from orders.rfm
  58. WHERE user_type='重要挽留用户' ) as b ON a.customer_id=b.customer_id
  59. LEFT JOIN orders.review c ON a.order_id=c.order_id
  60. LEFT JOIN orders.product d ON a.product_id=d.product_id
  61. LEFT JOIN orders.category e ON d.product_category_name=e.product_category_name
  62. where e.product_category_name_english is not NULL

  63. GROUP BY e.product_category_name_english limit 50

  64. -- General retained users' popularity index

  65. SELECT e.product_category_name_english good_type,
  66. SUM(a.order_value) ordder_total_value,
  67. ROUND(AVG(c.review_score), 2) good_review_score,
  68. (0.7SUM(a.order_value)+0.310000*ROUND(AVG(c.review_score), 7))
  69. top_rank_rate
  70. FROM orders.order_detail a
  71. INNER JOIN
  72. (SELECT customer_id from orders.rfm
  73. WHERE user_type='一般挽留用户' ) as b ON a.customer_id=b.customer_id
  74. LEFT JOIN orders.review c ON a.order_id=c.order_id
  75. LEFT JOIN orders.product d ON a.product_id=d.product_id
  76. LEFT JOIN orders.category e ON d.product_category_name=e.product_category_name
  77. where e.product_category_name_english is not NULL
  78. GROUP BY e.product_category_name_english limit 50 ```
  79. Update Data:

After saving the dataset, you need to click the Update Data button and wait for the data update to complete before proceeding with the analysis:

image-20230809091814920 4. Create Analytic Themes:

The analytic theme in this example is used to visually present data for general retained users, important retained users, monthly ARPU, quarterly ARPU, active users in different periods, daily active users, monthly active users, and order counts. It assists in decision-making and improving business operations. Here are the specific steps to create an analytic theme:

  • Click My Analysis, then click New Folder to create and select a folder.
  • Click New Analytic Theme, select the dataset created in the previous step, and then click OK.

image-20230809092959252

Note: You can use the Batch Selection feature to select multiple datasets for theme analysis.

image-20230809092959252

Click the Add Component button, choose the chart type, drag the fields from the left to the right as needed, double-click to modify the field visualization name, and change the component name below to describe the content of the report analyzed by the component:

image-20230809092959252

image-20230809092959252 5. Assemble Dashboards:

Click Add Dashboard to add the components you just created to the dashboard. You can freely drag and resize the components and change the component names below to describe the report’s content analyzed by the component.

image-20230810123913230 6. Publish Dashboards:

After assembling the dashboard, click Publish, set the publication name, publication node, and display platform. Then click Confirm, and your dashboard will be successfully published.

image-20230810123913230

Now, see the newly published dashboard under Navigation and see how it looks.

image-20230810131752645