Workflow 4

Creating a Restaurant Analysis Dashboard

Goal: To create a Restaurant Analysis Dashboard and demonstrate the usage of datasets and Data Sheets.

• Data Source: ClickHouse database.

• Key Process: Creation of complex datasets (e.g., Order Count Channel, Payment Mode, Date filter) that utilize parameter placeholders (@date1@, @input@) within their SQL queries to enable highly customizable filtering.

• Key Features: The dashboard uses Radio Button components mapped to "Month, Quarter, and Year" labels and values. Components include Pie Charts, Bar Charts, and the Data Sheet component. The charts are connected dynamically by including the radio button and filter components in the Connections Condition section of the charts. The workflow also includes creating a Data Sheet in the Data Center, publishing it, and integrating it into the dashboard

Workflow 4 demonstrates the process of creating datasets and data sheets and shows how to use them for building interactive dashboards. This workflow helps users efficiently organize, manage, and manipulate data, enabling them to uncover meaningful insights and make more effective data-driven decisions.

Creating a Restaurant Analysis Dashboard

Plugin 1- Data Center Plugin

The Data Center Plugin is used to create connectors and establish a connection to the data source. In Workflow 4, the data source is a ClickHouse database. To configure the data connector, provide the following details: Data Connector Name, Username, Password, IP/Host, Port, and Database Name. Once all details are entered, click Save to complete the configuration.

Create a Data Connector

  • From the App Menu, select Data Center.

  • In the connector settings, click Create+ and choose the desired data connector (e.g., a ClickHouse data source).

  • Provide a name for the connector and enter the required credentials: Username, Password, Host, Port, and Database Name.

  • select the SSL Type as Non SSL Type.

  • Click on Save button to complete the connector setup.

To Create Datasets for Dashboard Connection

Order Count Channel Dataset

  • Click the three dots next to the specific data connector and select New Dataset.

  • Enter a name for the dataset — for example, Order Count Channel.

  • In the query editor, enter the desired SQL query in the designated box.

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;

  • Validate the query by providing the required input values.

  • Click Continue to view the corresponding results.

  • Repeat the validation for other input sets if necessary.

  • Click Save to finalize and store the dataset.

Payment Mode Dataset

  • Provide the dataset name “Payment_mode” and enter the below 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;

  • Validate the query using the required input values

  • Click the Save button once the validation is successful.

Date filter Dataset

  • Provide the dataset name date_filter and run the 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

AND (

(@input@ = 'month' AND monthName(parseDateTimeBestEffort(Order_Date)) IS NOT NULL)

OR

(@input@ = 'quarter' AND toQuarter(parseDateTimeBestEffort(Order_Date)) IS NOT NULL)

OR

(@input@ = 'year' AND toYear(parseDateTimeBestEffort(Order_Date)) IS NOT NULL)

)

AND (

CASE

WHEN @input@ = 'month' THEN

concat(toString(toYear(parseDateTimeBestEffort(Order_Date))), '-', monthName(parseDateTimeBestEffort(Order_Date)))

WHEN @input@ = 'quarter' THEN

concat(toString(toYear(parseDateTimeBestEffort(Order_Date))), '-', toString(toQuarter(parseDateTimeBestEffort(Order_Date))))

WHEN @input@ = 'year' THEN

toString(toYear(parseDateTimeBestEffort(Order_Date)))

END

) IS NOT NULL;

  • Validate the query by providing the input value, such as Month.

  • Once validation is successful

  • click on Save button.

Plugin 2- Dashboard Designer Plugin

The Dashboard Designer Plugin is used to visualize data by creating interactive dashboards. In this example, a Restaurant Analysis Dashboard has been developed.

To design the dashboard, users can leverage a wide range of components such as Labels, Textboxes, Filters, Radio Buttons, Pie Charts, Bar Charts, and Data Sheet components. These elements can be easily dragged and dropped into the dashboard editor, enabling a flexible and intuitive design process.

To populate the dashboard with data, data connectors are created directly within the dashboard. These connectors are then configured with the datasets prepared in the Data Center Plugin, allowing the dashboard to seamlessly access and display the relevant data.

· Navigate to the Designer module from the app menu

· Click the plus (+) symbol to create a new dashboard.

· provide a distinctive dashboard name, and click Save the dashboard

Set up Data Connections

· Click the Connector button to access the Data Services section.

  • Create three connections by assigning descriptive connection names and selecting the appropriate data connectors.

  • For each connection, select the desired datasets to populate the relevant fields.

  • Repeat for the remaining two connections to ensure a complete setup.

To construct a comprehensive data sheet:

  • Go to the Data Sheet option in the Data Center section and click Create.

  • Provide a suitable name for the data sheet.

  • Add each column by entering its name and necessary details, then click the Add (+) symbol

· Follow the same process to add the remaining columns.

· Click Save to save the data sheet.

· Click Publish to make the data sheet available for use.

· Select the users with whom the data sheet should be shared, then click Save.

· In the Designer section, create a connection for the data sheet.

· Choose the desired data sheet and provide a connection name.

Customizing the Dashboard:

  • Go to Properties and adjust the dashboard dimensions.

  • Optionally, modify the background color by selecting the Gradient option and specifying a color code.

  • Click Apply, then Save to confirm changes.

Adding components for Dashboard Title:

· Drag and drop a Box component.

· In Properties > General, adjust dimensions (left, top, height, width).

· In Style > Gradient, remove the extra color bar and select a background color using a color code.

· Click Apply, then Save.

· Drag and drop the Label component.

· In Properties > General, adjust dimensions (left, top, width, height).

· Enter the label text.

· In Style, modify the background color, font color, and font size as desired.

· Adjust font weight and text alignment as needed.

Adding a Radio Button and Combo box:

· Drag and drop the Radio component from the component section.

· Customize its dimensions (left, top, width, height) to fit your layout.

· Adjust font size and select the appropriate selection color.

· In the dataset section, add labels for Month, Quarter, and Year and assign values.

· Adjust the font weight for clarity.

· Drag and drop the Combo Box component.

· Customize its dimensions (left, top, width, height) to fit the layout.

Creating a Pie Chart

· Drag and drop the Pie Chart component from the component section.

· In Properties > General, adjust the chart dimensions.

· Customize the background: navigate to Background > Gradient and select the desired color.

· Click Apply to save changes.

· Select a suitable title box color.

· Add a descriptive name or label for the chart.

· Adjust font size, set font weight to bold, and choose the font color.

Adding a Bar Chart:

· Check the Legend option under the Legend section.

· Drag and drop the Bar Chart component from the component section.

· In Properties > General, adjust the chart dimensions (left, top, width, height).

· Customize the background by navigating to Background > Gradient and selecting the desired color.

· Click Apply, then Save.

· Select a suitable title box color.

· Uncheck Show Dataset Description and add a descriptive chart label.

· Adjust font weight, size, color, and style.

Mapping Data set to the charts

  • Click the Dataset button and select the appropriate connection.

  • Drag the desired fields from the search section into Category and Series sections to ensure correct data mapping.

· Repeat these steps for all charts and combo box components.

· To ensure accurate data representation, connect the Pie Charts, Bar Charts, Radio Button, and Filters by including the radio button and filter components in the Connections Condition section of the charts.

· This setup allows the charts to automatically update whenever filter values change, ensuring a dynamic and interactive dashboard experience.

· Reload the script in the Radio Button component.

· sdk.reload(['C_3']);

  • Preview the dashboard and filter data by Month, then check filtering for Quarter and Year.

Editing the Data Sheet

  • Navigate to the Home page.

  • Open the previously created data sheet and add a new row using the plus (+) sign.

  • Enter the required values and click Save.

Adding and Customizing the Data Sheet in Dashboard

  • In the Designer, drag and drop the Data Sheet component.

  • Adjust its dimensions (left, top, height, width).

  • Navigate to Background > Gradient and select a color. Click Apply and Save.

Customizing the Charting Properties:

· Select a suitable title box color and font color.

· Adjust font weight, font size, and font family.

· Uncheck Show Dataset Description.

· Under Header, change the background color.

· Under Styles > Grid Color, modify row color and alternate row color.

· Under Row Style, adjust font color.

· Under Title, add a descriptive name or label for the chart.

· Click Save to finalize the dashboard.

Mapping Data Sheet Fields

  • In the Data Sheet component, select the appropriate dataset and map the required fields.

  • Click Save.

Script used in this workflow

Filter box=sdk.autoReload();

Radio Button = sdk.reload(['C_3']);

Previewing the Dashboard:

  • Preview the dashboard to visualize the result.

  • Ensure all components are correctly connected and the desired data is displayed in a clear and informative manner.

With this workflow, you should now be able to build dashboards using data sheets and filters effectively. All scripts and queries used in this workflow are provided in the tutorial dashboard document.

PreviousWorkflow 3NextWorkflow 5

Last updated