Create a Restaurant Analysis Dashboard Using the Designer

Create an interactive Restaurant Analysis Dashboard in BDB, connecting visualizations to parameterized queries and dynamic filters.

Purpose

This guide walks you through the process of creating an interactive Restaurant Analysis Dashboard using the BDB Data Center and Dashboard Designer plugins. It demonstrates how to build datasets with parameterized SQL queries, create a reusable Data Sheet, and link visual components dynamically to filters and radio buttons.

Business Context

Restaurant managers and analysts often need to compare performance metrics such as order types, payment modes, and sales across time periods (month, quarter, year). This workflow shows how to achieve this using the BDB Platform’s integrated tools — combining ClickHouse datasets, data sheets, and dynamic dashboards for end-to-end business analysis.

Key Highlights

Category	Description
Goal	Build a Restaurant Analysis Dashboard integrating datasets, filters, and data sheets.
Data Source	ClickHouse Database
Primary Components	Radio Buttons, Combo Box, Pie Chart, Bar Chart, Data Sheet
Key Feature	Parameterized dataset queries using placeholders @date1@ and @input@
Dynamic Interactivity	All charts update automatically based on selected month, quarter, or year.

Workflow Overview

Step	Module	Description
1	Data Center	Create a ClickHouse connector and parameterized datasets.
2	Data Center	Create and publish a Data Sheet for dashboard integration.
3	Dashboard Designer	Design the layout with interactive charts, filters, and data sheets.
4	Dashboard Designer	Map datasets to components, apply connection conditions, and test dynamic filtering.

Step 1 – Create a ClickHouse Data Connector

1. From the App Menu, select Data Center.

2. Click Create (+) → Choose ClickHouse as the connector type.

3. Provide the following details:

   Field	Description
   Connector Name	e.g., Restaurant_ClickHouse_Connector
   Username / Password	As provided by your database administrator
   Host / IP Address	Enter the ClickHouse server address
   Port	Default: 8123
   Database Name	Your target schema (e.g., restaurant_analytics)
   SSL Type	Non-SSL

4. Click Save to complete setup.

   

Step 2 – Create Parameterized Datasets

Parameterized datasets allow users to dynamically filter dashboards based on input parameters such as Month, Quarter, or Year.

2.1 Dataset 1: Order Count Channel

Query:

SELECT
    Order_Type,
    COUNT(Order_Id) AS Order_Count
FROM Order_Sales_Data
WHERE
    (@date1@ = 'month' AND upper(monthName(parseDateTimeBestEffort(Order_Date))) = upper(substring(@input@, 6)))
    OR
    (@date1@ = 'quarter' AND toQuarter(parseDateTimeBestEffort(Order_Date)) =
        CASE upper(substring(@input@, 6))
            WHEN 'JANUARY' THEN 1 WHEN 'FEBRUARY' THEN 1 WHEN 'MARCH' THEN 1
            WHEN 'APRIL' THEN 2 WHEN 'MAY' THEN 2 WHEN 'JUNE' THEN 2
            WHEN 'JULY' THEN 3 WHEN 'AUGUST' THEN 3 WHEN 'SEPTEMBER' THEN 3
            WHEN 'OCTOBER' THEN 4 WHEN 'NOVEMBER' THEN 4 WHEN 'DECEMBER' THEN 4
        END)
    OR
    (@date1@ = 'year' AND toYear(parseDateTimeBestEffort(Order_Date)) = toInt32OrZero(substring(@input@, 1, 4)))
GROUP BY Order_Type;

Steps:

• Click the three-dot menu next to the connector → Create Dataset.

• Enter the name Order Count Channel.

• Paste the query above.

• Validate using sample parameter values (@date1@='month', @input@='2024-March').

• Click Save.

  

2.2 Dataset 2: Payment Mode

Query:

SELECT
    Payment_Mode,
    COUNT(Order_Id) AS Order_Count
FROM Order_Sales_Data
WHERE
    (@date1@ = 'month' AND upper(monthName(parseDateTimeBestEffort(Order_Date))) = upper(substring(@input@, 6)))
    OR
    (@date1@ = 'quarter' AND toQuarter(parseDateTimeBestEffort(Order_Date)) =
        CASE upper(substring(@input@, 6))
            WHEN 'JANUARY' THEN 1 WHEN 'FEBRUARY' THEN 1 WHEN 'MARCH' THEN 1
            WHEN 'APRIL' THEN 2 WHEN 'MAY' THEN 2 WHEN 'JUNE' THEN 2
            WHEN 'JULY' THEN 3 WHEN 'AUGUST' THEN 3 WHEN 'SEPTEMBER' THEN 3
            WHEN 'OCTOBER' THEN 4 WHEN 'NOVEMBER' THEN 4 WHEN 'DECEMBER' THEN 4
        END)
    OR
    (@date1@ = 'year' AND toYear(parseDateTimeBestEffort(Order_Date)) = toInt32OrZero(substring(@input@, 1, 4)))
GROUP BY Payment_Mode
ORDER BY Order_Count DESC;

Steps:

• Name the dataset Payment_Mode.

• Validate and Save.

---

2.3 Dataset 3: Date Filter

Query:

SELECT
    CASE
        WHEN @input@ = 'month' AND Order_Date IS NOT NULL THEN
            concat(toString(toYear(parseDateTimeBestEffort(Order_Date))), '-', monthName(parseDateTimeBestEffort(Order_Date)))
        WHEN @input@ = 'quarter' AND Order_Date IS NOT NULL THEN
            concat(toString(toYear(parseDateTimeBestEffort(Order_Date))), '-', toString(toQuarter(parseDateTimeBestEffort(Order_Date))))
        WHEN @input@ = 'year' AND Order_Date IS NOT NULL THEN
            toString(toYear(parseDateTimeBestEffort(Order_Date)))
    END AS datestring
FROM Order_Sales_Data
WHERE Order_Date IS NOT NULL;

Steps:

• Name the dataset Date_Filter.

• Validate by passing a parameter (@input@='month').

• Save once results are validated.

Step 3 – Create and Publish a Data Sheet

Data Sheets act as reusable, interactive tables that can be integrated directly into dashboards.

Steps:

1. In the Data Center, select Data Sheet → Create.

2. Enter a name, e.g., Restaurant_Data_Sheet.

3. Add columns:

   • Order ID

   • Order Type

   • Payment Mode

   • Order Date

   • Total Amount

   • Customer Name

4. Click Save.

5. Navigate to the Data Sheet list.

6. Select a Data Sheeet and open the Options context menu.

7. Click Publish → Choose user roles to share with.

8. Confirm and Save.

Step 4 – Design the Dashboard (Dashboard Designer Plugin)

1. Navigate to the Dashboard Designer Plugin (Either from the left navigation panel or the Apps Menu).

2. Click New Dashboard.

3. Name it Restaurant Analysis Dashboard.

4. Click Save.

4.1 Customize Dashboard Appearance

• Open Properties → General → Set Width, Height.

• To change the background:

  • Go to Style → Gradient → Select Color Code.

• Click Apply → Save.

Step 5 – Add Header Components

5.1 Add Box Component

1. Drag a Box from the Components Library.

2. Set size under Properties → General.

3. Change header background via Style → Gradient.

5.2 Add Label Component

1. Add a Label inside the Box.

2. Update text to Restaurant Analysis Dashboard.

3. Adjust:

   • Font → Bold, 24 pt

   • Alignment → Center

   • Background → Transparent

Step 6 – Add Filter Components

6.1 Add Radio Button (Month, Quarter, Year)

1. Drag a Radio Button onto the dashboard.

2. Under Properties → Data Source, add Labels & Values:

   • Label: Month → Value: month

   • Label: Quarter → Value: quarter

   • Label: Year → Value: year

3. Adjust:

   • Selection Color → Accent tone

   • Font Weight → Bold

   • Font Size → 14

6.2 Add Combo Box

1. Drag and drop a Combo Box next to the radio buttons.

2. Resize to match the layout.

3. Map it later to the Date_Filter dataset.

Step 7 – Add Visualizations

Visualization	Purpose	Data Mapping
Pie Chart	Order Count by Channel	Order Count Channel Dataset
Bar Chart	Payment Mode Distribution	Payment Mode Dataset
Data Sheet	Interactive order records	Published Data Sheet

7.1 Pie Chart

1. Drag a Pie Chart component.

2. Under Properties → General, configure size and title (e.g., Order Count by Channel).

3. Set Background Gradient for design consistency.

7.2 Bar Chart

1. Drag a Bar Chart component.

2. Configure size and title (e.g., Payment Mode Comparison).

3. Enable Legend for clarity.

7.3 Data Sheet

1. Drag and drop a Data Sheet component.

2. Connect it to the Restaurant_Data_Sheet created earlier.

3. Adjust:

   • Header Color

   • Row and Alternate Row Colors

   • Font Weight and Size

Step 8 – Connect Datasets and Apply Conditions

1. Click the Connector Icon (Plug) in the toolbar.

2. Create connections for:

   • Order Count Channel

   • Payment Mode

   • Date Filter

3. For each chart, map the correct dataset fields to the Category and Series.

8.1 Add Filter Conditions

To ensure dynamic filtering:

1. Go to each dataset connection.

2. Under Condition, link the following components:

   • Radio Button

   • Combo Box (Date Filter)

3. This setup ensures that when users select “Month,” “Quarter,” or “Year,” all charts update automatically.

Step 9 – Add Scripts

Filter Box Script

sdk.autoReload();

Automatically reloads datasets when the Combo Box input changes.

Radio Button Script

sdk.reload(['C_3']);

Forces the linked charts to reload when a different time period is selected.

Step 10 – Preview and Validate

1. Click Preview in the Dashboard Designer.

2. Select Month, Quarter, or Year using the Radio Button.

3. Choose a specific date from the Combo Box.

4. Verify that:

   • Pie and Bar charts update dynamically.

   • The datasheet reflects filtered results.

Step 11 – Edit and Update Data Sheet

1. Navigate to the Home Page → Data Sheets.

2. Open the previously created Data Sheet.

3. Click the + (Add Row) button.

4. Enter new data and click Save.

5. Return to the dashboard and click Preview — changes appear instantly.

Outcome

• A dynamic Restaurant Analysis Dashboard connected to ClickHouse data.

• Charts and Data Sheet synchronized through parameterized filters.

• Interactive filtering by Month, Quarter, and Year.

• Published Data Sheet supports real-time edits and reusability across dashboards.

Best Practices

• Use consistent naming conventions for datasets and connections.

• Keep SQL queries optimized — avoid redundant subqueries.

• Validate each dataset with sample parameters before saving.

• Always link filter components via Connection Conditions to ensure dynamic updates.

• Apply uniform font and color schemes for a professional UI.

Business Impact

This workflow enables restaurant owners and analysts to:

• View multi-level performance trends (monthly, quarterly, yearly).

• Analyze order channels and payment modes in real-time.

• Manage and visualize data sheets for record-level insights.

• Make data-driven operational decisions efficiently.