Build a User Profile Dashboard with StarRocks - DataWorks

After you process data in DataWorks Data Studio, use the SQL query feature in DataAnalysis to run queries against a data source and convert the results into charts — no export or separate BI tool required. This topic walks through four visualization scenarios using user profile data: member distribution by province, page views by age range, page views by gender, and page views by gender and zodiac sign.

Prerequisites

Before you begin, ensure that you have:

Processed the required data — the example data must be processed into basic user profile data using DataWorks Data Studio

Visualization scenarios

The following table lists the four scenarios covered in this topic and the chart type used for each. For each chart, your query must return at least one dimension column (such as a category or label) and one measure column (such as a count or sum).

Scenario	Chart type	Why this chart
Numbers of registered members in different provinces and cities	Stacked Bar Chart	Compares counts across categories
Distribution of page views of members in different age ranges	Pie Chart	Shows proportional distribution
Distribution of page views of members by gender	Pie Chart	Shows proportional distribution
Numbers of page views of members counted by gender and zodiac sign	Grouped Column Chart	Compares a measure across two dimensions

Go to the SQL Query page

Log on to the DataWorks console. In the top navigation bar, select the desired region. In the left-side navigation pane, choose Data Analysis and Service > DataAnalysis. On the page that appears, click Go to DataAnalysis. In the left-side navigation pane of the page that appears, click SQL Query.

Configure a data source

Grant permissions on a data source. To use a data source in DataAnalysis, your account must have the required permissions. Go to the Security Center page and choose Security policy > Data query and analysis control. On the page that appears, click the Queryable data source tab, find the data source you want to query, and click Authorization in the Actions column. For more information, see Use the data query and analysis control feature.
Create a temporary file.
- Method 1: The first time you go to the SQL Query page, or if no SQL query file is open, click Create SQL Query in the right-side area.
- Method 2: If a SQL query file is already open, click the icon to the right of the existing file name.
For more information about SQL query files, see SQL query.
Select a data source. On the configuration tab of the SQL query file, click the icon in the upper-right corner. Select a workspace, a compute engine type, and a data source. This example uses a MaxCompute data source.

Write an SQL statement

On the configuration tab of the SQL query file, enter the following SQL statement and click the icon to query data in the ads_user_info_1d_starrocks table. This example queries the dt="20241126" partition.

-- If the desired partition is not found, run show partitions tablename to check available partitions.
select * from ads_user_info_1d_starrocks where dt='Data timestamp';

View results and configure charts

After the query completes, click the icon on the left side of the results area to switch to the chart view. To open the chart configuration tab, click the icon in the upper-right corner of the chart.

The chart preview updates as you adjust settings — you do not need to save after each change. Click Save in the upper-right corner only when you are ready to permanently save the chart.

Numbers of registered members in different provinces and cities

A Stacked Bar Chart compares registered member counts across provinces. The uid field is aggregated by distinct count to calculate the number of unique members per region.

Double-click the chart title and enter a name.
Set the chart type. On the right side of the configuration tab, click Style Settings. In the panel that appears, click the Change Chart tab and select Stacked Bar Chart.
Map data fields. On the left side of the configuration tab, click Data Settings. In the panel that appears:
1. Drag the uid field to the X-axis area and choose Aggregate > Distinct Count from the X-axis drop-down list.
2. Drag the region field to the Y-axis area.
Rename the axis titles. On the right side of the configuration tab, click Style Settings. In the panel that appears, click the Global Settings tab. In the Chart Information section, click Details and then click the Chart Style tab.
1. In the X-axis section, click Axis Title and change the Displayed Title value from uid to Number of Registered Members.
2. In the Y-axis section, click Axis Title and change the Displayed Title value from region to Province.
3. Click Save in the upper-right corner.
Verify the final chart.

Distribution of page views of members in different age ranges

A Pie Chart shows how total page views (PVs) are distributed across age groups. The pv field is summed per age_range category.

Double-click the chart title and enter a name.
Set the chart type. On the right side of the configuration tab, click Style Settings. In the panel that appears, click the Change Chart tab and select Pie Chart.
Map data fields. On the left side of the configuration tab, click Data Settings. In the panel that appears:
1. Drag the age_range field to the Category area.
2. Drag the pv field to the Value area and choose Aggregate > Sum from the Value drop-down list.
3. Click Save in the upper-right corner.
Verify the final chart.

Distribution of page views of members by gender

A Pie Chart shows the proportion of page views between genders. The pv field is summed per gender category.

Double-click the chart title and enter a name.
Set the chart type. On the right side of the configuration tab, click Style Settings. In the panel that appears, click the Change Chart tab and select Pie Chart.
Map data fields. On the left side of the configuration tab, click Data Settings. In the panel that appears:
1. Drag the gender field to the Category area.
2. Drag the pv field to the Value area and choose Aggregate > Sum from the Value drop-down list.
3. Click Save in the upper-right corner.
Verify the final chart.

Numbers of page views of members counted by gender and zodiac sign

A Grouped Column Chart compares page view counts across genders, further broken down by zodiac sign. The Split field divides each gender group into separate bars — one bar per zodiac sign value within each gender cluster on the X-axis.

Double-click the chart title and enter a name.
Set the chart type. On the right side of the configuration tab, click Style Settings. In the panel that appears, click the Change Chart tab and select Grouped Column Chart.
Map data fields. On the left side of the configuration tab, click Data Settings. In the panel that appears:
1. Drag the gender field to the X-axis area.
2. Drag the pv field to the Y-axis area and choose Aggregate > Sum from the Y-axis drop-down list.
3. Drag the zodiac field to the Split area.
Rename the axis titles. On the right side of the configuration tab, click Style Settings. In the panel that appears, click the Global Settings tab. In the Chart Information section, click Details and then click the Chart Style tab.
1. In the X-axis section, click Axis Title and set the Displayed Title to gender.
2. In the Y-axis section, click Axis Title and set the Displayed Title to pv.
3. Click Save in the upper-right corner.
Verify the final chart.

What's next

To share a chart, click Save in the upper-right corner to permanently save the chart, then share it. If you want to visualize processed data by using other types of charts, click Save As.
To view all charts saved with Save As, click Cards in the left-side navigation pane of the DataAnalysis page.