Author: Small film front end team — small film maker

This paper mainly describes some analysis thinking of report business development and how to solve the repeated development process of experience exchange, will be divided into two parts. The first part is mainly how to analyze and solve the problem of repeated report development by means of our own research technology, and the second part is mainly to introduce the experience of customized development based on superset.

Friends, are you still suffering from endless report business, dimension change, caliber modification, and occasionally launch several new products? Seemingly similar function pages, you have to go through the code development joint adjustment deployment process, which is the taste, students with similar development experience can feel. So how do you get rid of boring report development? Let’s start with the report requirements and see if we can optimize it step by step.

The life cycle of a report is as follows:

Report Service Analysis

As can be seen from the above process, the report page development process includes:

  • Determine report topics, metrics, and dimensions
  • Data acquisition and packaging
  • Determine the chart presentation form

The whole development process needs the communication and cooperation of the demand side, the data group and the front end. Even a simple report completion cycle takes one to two days. Then we will look at how to solve the optimization step by step from the above process.

Determine report topics, metrics, and dimensions

First of all, each report analysis consists of three key elements: report subject, report index and analysis dimension. The report subject must clearly correspond to a certain analysis target, and then the analysis indicators and dimensions are successively established. For example, if we decide to make a retention analysis table, the indicators can be defined as usage duration, usage frequency, page visit and usage interval, and then the analysis dimension can be divided into three different time analysis granularity: “daily retention”, “weekly retention” and “monthly retention”. In fact, the presentation form of reports at this time has almost been determined. For example, for comparison of time dimensions, line charts can be used to facilitate horizontal comparison of data, while for conversion rates, funnel charts can be directly used.

So can we make this process configurable? For example, what the demand side wants is the report display form of line chart, so it can directly generate the report page by selecting the report display form and the required display indicators on the background of the page. Yes, this is similar to the page visual editing, but we do not need to do so complex in the early stage, page editing can choose to match to meet the requirements. Another problem is how to bind each module in the report to the data.

Data acquisition and packaging

BI students are mainly responsible for data acquisition and packaging. For general reports, they will run the scheduled task T+1 to brush the data into ali Cloud analytical database, and then perform SQL query string assembly according to the indicators in the report, and then return the desired results. As above, if the demand side changes caliber, it is generally necessary to adjust SQL manually in the code, which is also very inconvenient. In response to the above question, how to bind the components displayed in the report to the data source. Through the above description, it is not difficult to find that a substance is to bind the components to the SQL assembled query.

Configure the background on the report page

So how do you do that? We set up a report page configuration background. It mainly consists of the following parts

  • Requirements Management list page
  • Requirements Detail page
  • SQL assemble configuration page

The simple interaction process is shown in the figure below. Although this saves a little development cost, there are still many disadvantages. If there is no suitable basic component, the development is still needed, and the students in the data group are used to some BI tools, the operation cost is still quite high.

Secondary development of BI tools

After repeated investigation and comparison, we finally decided on the secondary development based on BI tools. On the one hand, the basic functions of open source BI tools can basically meet business needs, but also avoid developing from zero. In addition, the data group side also used Tableau and other BI tools before, so I can get used to it quickly. Out of several free open source BI, we finally chose Superset to do a split for the following simple reasons.

  1. Well-known Airbnb is open source and hosted by Apache, and the project maintenance is also quite active
  2. The back end is python based, open source, lightweight, and graphically rich
  3. Integration with most SQL-based RDBMS through SQLAlchemy, compatible with the vast majority of database types on the market
  4. Fine-grained and highly scalable secure access model that supports major authentication vendors (database, OpenID, LDAP, OAuth, etc.)
  5. Deep integration with Druid allows you to quickly parse large data sets, making it ideal for subsequent big data-based analysis
Dependent technology stack
  • python + flask
  • react + ts
  • Sqlite3 (default) or mysql
Project download and installation

Superset: github.com/apache/supe… The official documents: superset.apache.org/docs/instal… The project is based on Python, so you need to install Python locally first. If the version is larger than 3.7, it will fail to install dependencies later. Homebrew is recommended to install Python. Find the CONTRIBUTING. Md file in the home directory of the project, which contains commands for starting the project, injecting administrator users, injecting sample data, switching database, etc. Generally, the project can be started normally after the execution is completed, details are as follows:

# Create a virtual environment and activate it (recommended)
python3 -m venv venv # setup a python3 virtualenv
source venv/bin/activate

# Install external d`ependencies
pip install -r requirements/local.txt

# Install Superset in editable (development) mode
pip install -e .

# Create an admin user in your metadata database
superset fab create-admin

# Initialize the database
superset db upgrade

# Create default roles and permissions
superset init

# Load some data to play with (you must create an Admin user with the username `admin` for this command to work)
superset load-examples

# Start the Flask dev web server from inside your virtualenv.
# Note that your page may not have css at this point.
# See instructions below how to build the front-end assets.
FLASK_ENV=development superset run -p 8088 --with-threads --reload --debugger

Or you can install via our Makefile
# # # # # #
Copy the code
Project Brief Introduction

The main directories are superset and superset-frontend, with one back end and one frontend code.

Superset directory
  1. Config. py main configuration entry, like some pretty useful functions are not open by default, need to be modified in this. Such as query cache configuration, atlas edit page indicator drag and drop, etc.
# config.py
# The SQLAlchemy connection string.
Change the database type
SQLALCHEMY_DATABASE_URI = "sqlite:///" + os.path.join(DATA_DIR, "superset.db")
# SQLALCHEMY_DATABASE_URI = 'mysql://root:123456@localhost/superset'
# SQLALCHEMY_DATABASE_URI = 'postgresql://root:password@localhost/myapp'
Copy the code
  1. The module business code depends on the file name, such as dashboard/ is the business code corresponding to the kanban page, such as dashboard/api.py, is the specific business interface, but like the page list interface will be looked in the base class. If you can’t find an interface, go to the interface documentation and search for keywords globally. http://localhost:8080/swagger/v1
  2. The views/ front end is a multi-page application where a few page jumps and data injection are registered
  3. Security /, if there is any permission modification, this directory code should take a good look
Superset – frontend directory
  1. Json to see what dependencies the project uses. The main UI libraries are Antd and @superset-UI. 2. In webpack-config, you can know the entry files of the project. Generally, the page where the data is placed at the back end is not found in router.js.

Secondary development

Combined with the interactive advantages of other BI tools, a wave of optimization has been made for the unusable areas in Superset, mainly as follows

  • Change the logo and name, find the corresponding files to replace it
  • Access user third-party registration mode. Superset is created using the admin account or a user account that has permission to create
  • Permission optimization: The permission points in superset are too specific and scattered, especially the permission of data sets. If there are many data sets, it is very troublesome to check them one by one. Therefore, we packaged the permission points of multiple data sets into one permission point, and then associated roles through this permission point
  • Kanban optimization, all kanban in Superset are tiled, obviously if there are too many kanban, it would be very inconvenient to view, and the list form is too background, so we changed to menu directory + panel display
  • Some optimizations can be made by looking at the code first, or just not by default in config.py

Menu optimization

The following is a brief description of the transformation process of menu optimization

I originally obtained the dashboards table data directly. I planned to add a Dashboards_category table to control the relationship between menus and panels. The following two fields are the main fields

  • Dashboards_id specifies the ID of the associated Dashboards table. If it exists, it is the Dashboards page; otherwise, it is the folder
  • Parent_id, 0 indicates the top-level menu. Otherwise, associate the menu IDS in this table to form a directory number hierarchy
CREATE TABLE `dashboard_category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation time',
  `changed_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Record update time',
  `category_title` varchar(500) NOT NULL,
  `dashboards_id` int(11) DEFAULT NULLCOMMENT "Associate the DASHboards table ID. If it exists, it is the Dashboards page; otherwise, it is the folder. ", 'parent_id'int(11) DEFAULT 0COMMENT "0 is top level menu, otherwise associate other ids in this table to create directory hierarchy ", 'created_by'int(11) DEFAULT NULL,
  `changed_by` int(11) DEFAULT NULL.PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_departmentid` (`departmentid`) USING HASH,
  KEY `created_by` (`created_by`),
  KEY `changed_by` (`changed_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Copy the code

Since a role can be added on the panel properties, the user under this role can view the permissions of all the atlas in the panel, so what we need to do is to distribute and reclaim the permissions of the dataset management. We integrate the permissions of the dataset associated with the atlas in the panel, which is the permission group of the dataset mentioned before

For permission assignment, access control is performed on the Dashboard panel based on its configured role

  • After the specified role permissions are assigned, the atlas in this panel is accessible to all associated roles
  • Panel menu permission: Determines the menu permission from bottom up based on the permission of the role associated with the lower-layer dashboard

Permissions are distributed and reclaimed in Dashboard. The link diagram is as follows:

, recruiting

Xiaoying front End team, a young, active and creative team, belongs to Xiaoying Technology Development Center, based in picturesque Hangzhou. In addition to daily business docking, the team also carried out technical exploration and practice in the fields of interactive technology, image rendering, cross-end technology, engineering platform, performance experience, data analysis and visualization, promoted and implemented a series of internal technical products, and kept curious and continued to explore front-end technology. If you want to speak your mind boldly and confidently; If you want a chance to make your ideas work; If your idea of landing is supported by a team; If you are willing to work with a group of positive friends to continue to iterate on yourself, continue to improve technical ability; If you believe in the power of belief; Then join us! Quick stamp link > > > quvideo. Jobs. Feishu. Cn/s/eV8jfyf