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 TABLE `category` (`product_category_name` VARCHAR(255) DEFAULT NULL,
    4. `product_category_name_english` VARCHAR(255) DEFAULT NULL );
    5. CREATE TABLE `item` (`order_id` VARCHAR(255) NOT NULL, `order_item_id` INT DEFAULT null,
    6. `product_id` VARCHAR(255) DEFAULT null,
    7. `seller_id` VARCHAR(255) DEFAULT null, `shipping_limit_date` DATETIME DEFAULT null,
    8. `price` DECIMAL(10,2) DEFAULT null,
    9. `freight_value` DECIMAL(10,2) DEFAULT null
    10. );
    11. CREATE TABLE `review` (
    12. `review_id` VARCHAR(255) NOT NULL,
    13. `order_id` VARCHAR(255) DEFAULT null,
    14. `review_score` TINYINT DEFAULT null,
    15. `review_comment_title` VARCHAR(255) DEFAULT null,
    16. `review_comment_message` TEXT DEFAULT null,
    17. `review_creation_date` DATETIME DEFAULT null,
    18. `review_answer_timestamp` DATETIME DEFAULT null,
    19. PRIMARY KEY (`review_id`)
    20. );
    21. CREATE TABLE `order_time` (
    22. `order_id` VARCHAR(255) NOT NULL,
    23. `customer_id` VARCHAR(255) DEFAULT null,
    24. `y` INT DEFAULT null,
    25. `q` INT DEFAULT null,
    26. `m` INT DEFAULT null,
    27. `d` DATE DEFAULT null,
    28. `h` INT DEFAULT null,
    29. `order_purchase_timestamp` DATETIME DEFAULT null
    30. );
    31. CREATE TABLE `orders` (
    32. `order_id` VARCHAR(255) NOT NULL,
    33. `customer_id` VARCHAR(255) DEFAULT null,
    34. `order_status` VARCHAR(255) DEFAULT null,
    35. `order_purchase_timestamp` DATETIME DEFAULT null,
    36. `order_approved_at` DATETIME DEFAULT null,
    37. `order_delivered_carrier_date` DATETIME DEFAULT null,
    38. `order_delivered_customer_date` DATETIME DEFAULT null,
    39. `order_estimated_delivery_date` DATETIME DEFAULT null,
    40. PRIMARY KEY (`order_id`)
    41. );
    42. CREATE TABLE `product` (
    43. `product_id` VARCHAR(255) NOT NULL,
    44. `product_category_name` VARCHAR(255) DEFAULT null,
    45. `product_name_lenght` INT DEFAULT null,
    46. `product_description_lenght` INT DEFAULT null,
    47. `product_photos_qty` INT DEFAULT null,
    48. `product_weight_g` INT DEFAULT null,
    49. `product_length_cm` INT DEFAULT null,
    50. `product_height_cm` INT DEFAULT null,
    51. `product_width_cm` INT DEFAULT null,
    52. PRIMARY KEY (`product_id`)
    53. );
    54. CREATE TABLE `rfm` (
    55. `customer_id` VARCHAR(255) DEFAULT null,
    56. `user_type` VARCHAR(255) DEFAULT null,
    57. `shijian` DATE DEFAULT null
    58. );
    60. CREATE view total_order_value as select  t.order_id,product_id,seller_id,(price*total)+(freight_value*total) 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;
    62. 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
    9. -- Monthly active users and order count
    10. select count(DISTINCT customer_id),
    11. count(order_id),
    12. concat(y, '-', m)
    13. from orders.order_detail
    14. group by y,m
    15. order by y,m
    17. -- Active users and order count in different periods
    18. select h,
    19. count(DISTINCT customer_id),
    20. count(order_id) order_num
    21. from orders.order_detail
    22. group by h
    23. order by h
    25. -- User count by type
    26. SELECT count(*),
    27. user_type
    28. from orders.rfm
    29. GROUP BY user_type
    31. -- Monthly GMV
    32. select y,m,
    33. sum(order_value),
    34. concat(y, "-", m) month
    35. from orders.order_detail
    36. group by y,m
    37. order by y,m
    39. -- Quarterly GMV
    40. select y,q,
    41. sum(order_value) gmv,
    42. concat(y, "季度", q) as quator
    43. from orders.order_detail
    44. group by y,q
    45. order by concat(y, "季度", q) asc
    47. -- Quarterly ARPU
    48. select y,q,
    49. round((sum(order_value)/count(DISTINCT customer_id)),2) arpu,
    50. concat(y, "季度", q) as quator
    51. from orders.order_detail
    52. group by y,q
    53. order by y,q
    55. -- Monthly ARPU
    56. select y,m,
    57. round((sum(order_value)/count(DISTINCT customer_id)),2) arpu,
    58. concat(y, "-", m) as month
    59. from orders.order_detail
    60. group by y,m
    61. order by y,m
    63. -- Important retained users' popularity index
    64. SELECT e.product_category_name_english good_type,
    65. SUM(a.order_value) ordder_total_value,
    66. ROUND(AVG(c.review_score), 2) good_review_score,
    67. (0.7*SUM(a.order_value)+0.3*10000*ROUND(AVG(c.review_score), 7))
    68. top_rank_rate
    69. FROM orders.order_detail a
    70. INNER JOIN
    71. (SELECT customer_id
    72. 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
    80. -- General retained users' popularity index
    81. SELECT e.product_category_name_english good_type,
    82. SUM(a.order_value) ordder_total_value,
    83.  ROUND(AVG(c.review_score), 2) good_review_score,
    84. (0.7*SUM(a.order_value)+0.3*10000*ROUND(AVG(c.review_score), 7))
    85. top_rank_rate
    86. FROM orders.order_detail a
    87. INNER JOIN
    88. (SELECT customer_id from orders.rfm
    89. WHERE user_type='一般挽留用户' ) as b ON a.customer_id=b.customer_id
    90. LEFT JOIN orders.review c ON a.order_id=c.order_id
    91. LEFT JOIN orders.product d ON a.product_id=d.product_id
    92. LEFT JOIN orders.category e ON d.product_category_name=e.product_category_name
    93. where e.product_category_name_english is not NULL
    94. GROUP BY e.product_category_name_english limit 50

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