Create a Sales Analysis Dashboard

Visualize sales data using a Data Store as the primary input source.

Purpose

This guide explains how to create a Sales Analysis Dashboard on the BDB Platform using an existing ClickHouse Data Store as the data source. It walks through the process of creating SQL-based datasets, configuring visual components such as Pie Chart, Bar Chart, and Time Series Chart, and applying in-depth property customization for professional visualization.

Business Context

This workflow enables business users and analysts to visualize sales metrics such as total revenue, profit, and regional trends over time. By connecting directly to a ClickHouse Data Store, users can leverage real-time data without external files, ensuring accuracy and freshness for decision-making.

Key Highlights

Category	Description
Goal	Build a time-based Sales Analysis Dashboard using ClickHouse Data Store datasets.
Data Source	ClickHouse Connector with published datasets.
Primary Components	Pie Chart, Bar Chart, and Time Series Chart.
SQL Logic	Separate datasets for item-wise profit and yearly time-series trends.
Customization Focus	Background colors, title box color, font customization, and axis formatting.
Outcome	A fully interactive dashboard providing visual insights into item performance and regional revenue trends.

Workflow Overview

Step	Module	Activity
1	Data Center	Create ClickHouse connector and SQL-based datasets.
2	Data Center	Publish datasets for use in Designer.
3	Dashboard Designer	Create and configure dashboard layout.
4	Dashboard Designer	Map datasets to charts, customize visuals, and preview dashboard.

Step 1 – Create a ClickHouse Data Connector

1. From the App Menu, open Data Center.

2. Click Create (+) to create a new connector.

3. Select ClickHouse Data Connector.

4. Provide the following configuration details:

   Field	Description
   Connector Name	Sales_Data_Store_Connector
   Username / Password	As provided by your database admin
   Host (IP)	Enter ClickHouse server address
   TCP Port	Default: 8123
   Database Name	Target schema containing your sales data

5. Click Save to complete the connector setup.

Step 2 – Create and Publish Datasets

2.1 Create Time Series Dataset

1. In the Data Center, locate the connector → click the three dots ⋮ → Create Dataset.

2. Provide a dataset name: Time_Series_Data.

3. Paste and validate the following SQL query:

SELECT
  Region,
  toYear(toDate(OrderDate)) AS OrderYear,
  SUM(TotalRevenue) AS TotalRevenue,
  SUM(TotalProfit) AS TotalProfit
FROM sales_world_training_data
GROUP BY Region, OrderYear
ORDER BY OrderYear, Region;

1. Validate the query and review the output.

2. Click Save, then Publish to make it available for the Designer.

2.2 Create Bar Chart Dataset

1. From the same connector, click ⋮ → Create Dataset again.

2. Provide a dataset name: Bar_Chart_Data.

3. Paste the SQL query below:

SELECT
  ItemType,
  SUM(TotalProfit) AS Total_Profit
FROM sales_world_training_data
GROUP BY ItemType
ORDER BY Total_Profit DESC;

1. Validate → Save → Publish.

Step 3 – Create and Configure the Dashboard

3.1 Launch the Dashboard Designer

1. From the App Menu, open Dashboard Designer.

2. Click New (+) → Dashboard.

3. Provide a name, such as Sales Analysis Dashboard.

4. Select workspace → click Save.

3.2 Configure Dashboard Properties

1. Right-click on the dashboard background → Properties.

2. Under Background, click More Colors → choose a color theme (e.g., light gray or blue gradient).

3. Adjust the dashboard’s Width and Height in Properties → Size.

4. Save the configuration.

Step 4 – Add and Customize Components

4.1 Box Component (Header Container)

1. Drag a Box from the Components Library.

2. Adjust its size and position using the General tab.

3. Change its background color (e.g., gradient blue) under Style → Gradient.

4.2 Label Component (Dashboard Title)

1. Drag a Label into the header Box.

2. Update text → Sales Analysis Dashboard.

3. Adjust font size (24 pt), font weight (Bold), alignment (Center), and color (White).

4. Save changes.

4.3 Pie Chart Component

1. Drag and drop the Pie Chart onto the canvas.

2. In Properties → General, adjust Left, Top, Height, and Width.

3. Under Background, choose a contrasting color.

4. Update Title → Profit Distribution by Item Type.

5. Uncheck Show Dataset Description.

6. Modify Title Box Color, Font Color, and Font Size.

7. Under Formatter, select Percentage for units.

Sample Mapping:

• Category → ItemType

• Series → Total_Profit

Optional Script:

sdk.applyAggregation("pie_chart", "ItemType", "Total_Profit", "SUM");

4.4 Bar Chart Component

1. Drag and drop a Bar Chart from the Components Library.

2. Adjust dimensions to align below or beside Pie Chart.

3. Change background color via Style → Gradient.

4. Add Title → Total Profit by Item Type.

5. Uncheck Show Dataset Description.

6. Update Title Box Color and Font Color as desired.

7. Set X-Axis Description → Item Type.

8. Set Y-Axis Description → Total Profit (USD).

Sample Mapping:

• Category → ItemType

• Series → Total_Profit

Optional Script:

sdk.applyAggregation("bar_chart", "ItemType", "Total_Profit", "SUM");

4.5 Time Series Chart Component

1. Drag and drop a Time Series Chart.

2. Adjust Left, Top, Height, and Width to align with the other charts.

3. Set Background Color → Gradient Blue or Gray.

4. Update Title → Revenue and Profit Trends Over Time.

5. Modify Title Box Color, Font Color, and Size.

6. Under Axis Settings:

   • X-Axis Description → Year.

   • Y-Axis Description → Revenue / Profit (USD).

   • Enable Show Axis Label (Second Axis) and set Formatter → Auto.

7. Adjust axis font colors and sizes for better visibility.

Sample Mapping:

• Category → OrderYear

• Series → TotalRevenue, TotalProfit

• Group → Region

Step 5 – Add Connections

5.1 Add Data Service Connections

1. Click the Plug (Connector) icon → Add Data Service.

2. Select your existing ClickHouse Data Connector.

3. Choose the published datasets:

Connection Name	Dataset Used	Component Linked To
Bar_Chart_Conn	Bar_Chart_Data	Bar Chart
Time_Series_Conn	Time_Series_Data	Time Series Chart
Pie_Chart_Conn	Bar_Chart_Data (or custom)	Pie Chart

1. Save each connection after mapping.

Step 6 – Map Datasets to Chart Fields

Chart	Category	Series	Group (Optional)
Pie Chart	ItemType	Total_Profit	—
Bar Chart	ItemType	Total_Profit	—
Time Series	OrderYear	TotalRevenue, TotalProfit	Region

Additional Formatting:

• Assign custom colors to series.

• Set data label rotation (45° for Bar Chart).

• Update axis tick marks and value labels.

Step 7 – Preview and Validate

1. Click Preview Dashboard.

2. Verify:

   • Pie Chart shows profit distribution.

   • Bar Chart displays the top Item Types by Profit.

   • Time Series Chart plots Revenue and Profit over Years and Regions.

3. Adjust any axis titles or color schemes for better readability.

4. Save the dashboard.

Step 8 – Publish the Dashboard

1. Click Save → Publish.

2. Select the workspace and assign user roles.

3. Confirm publish action.

4. The dashboard will be available under the Home Page.

   

Outcome

• A Sales Analysis Dashboard built on ClickHouse Data Store.

• Live datasets power real-time charts without external files.

• Interactive visuals with customized colors and axis formats.

• Data mapped for yearly and item-wise analysis.

Best Practices

• Use descriptive names for datasets and connections (Time_Series_Data, Bar_Chart_Data).

• Validate queries before publishing to prevent load errors.

• Align font and color themes for consistent UI aesthetics.

• Limit categories in Bar/Pie charts to improve readability.

• Preview frequently during layout changes.

Business Value

This dashboard provides a consolidated view of global sales performance — helping organizations to:

• Compare regional revenues and profits over time.

• Identify top product categories by profitability.

• Leverage data from a centralized ClickHouse Data Store for real-time decision making.

• Enable executives and analysts to monitor KPIs visually without manual report creation.