Retail chain's dashboard based on a ClickHouse® DB

We will use a Moscow retail chain's demo ClickHouse® sales database as our data source.

In this example, DataLens is used to visualize:

• Sales dynamics by day and month.
• Sales by product categories.
• Order heat map.

A connection named Sample ClickHouse will be created for database access.

To visualize and explore data, set up DataLens and follow the steps below:

1. Define the data source for a dataset.
2. Configure the dataset fields.
3. Create a line chart.
4. Create a bar chart.
5. Create a pivot table chart.
6. Create a heat map.
7. Create a dashboard.
8. Add charts to the dashboard.
9. Add selectors to the dashboard.

Getting startedGetting started

To get started with DataLens:

If you have any technical questions about the service, contact Yandex Cloud support. To ask for advice, discuss the solution to your problem or best practices of the service, write to the DataLens chat in Telegram.

Step 1. Create a connection and datasetStep 1. Create a connection and dataset

1. Go to the connections page.

2. Click Create connection.

3. Select the ClickHouse connection.

   1. In the window that opens, specify the connection parameters:

      • Connection type: Specify manually.
      • Host name: rc1a-ckg8nrosr2lim5iz.mdb.yandexcloud.net.
      • HTTP interface port: 8443 (default).
      • Username: samples_ro.
      • Password: MsgfcjEhJk.

   2. Enable Allow subqueries in datasets and queries from charts.

   3. Check the connection and click Create connection.

   4. Enter the connection name: Sample ClickHouse.

   5. Click Create.

   

Wait for the connection to be saved.

1. In the top-right corner, click Create dataset.

Step 2. Set up the dataset fieldsStep 2. Set up the dataset fields

1. Drag the MS_SalesFacts table to the workspace.

   

2. Drag the MS_Clients table to the workspace. The tables are automatically linked.

   

3. To check the link, click the link icon between the tables.

   

4. Tables are linked by the ClientID field. If needed, you can modify or expand the link by specifying another pair of fields. To close the link settings window, click the cross button or anywhere outside the window.

   

5. Drag the MS_Products table to the workspace. The table is automatically linked to the left (root) table MS_SalesFacts.

   

6. Drag the MS_Shops table to the workspace. The table is automatically linked to the left (root) table MS_SalesFacts.

   

7. Go to the Fields tab.

   

8. Delete the duplicate fields left over from joining the tables: ClientID (1), ProductID (1), and ShopID (1).

   

9. Create an order date field named OrderDate.

   1. Duplicate the OrderDatetime field.

      

   2. Rename the OrderDatetime (1) duplicate field to OrderDate: click the row name, delete the current name, and enter the new one.

      

   3. Change the data type from Date and time to Date.

      

10. For the ShopDistrictCoordinates field, change the data type to Geopolygon.

11. For the DeliveryDistrictCoordinates field, change the data type to Geopolygon.

12. For the DeliveryAddressCoord field, change the data type to Geopoint.

13. In the Aggregation column, select Sum for the Sales field.

    

    The aggregation field changes color to blue: Sales is now a measure.

    

14. Create a measure for the number of orders.

    1. Duplicate the OrderID field.

    2. Rename the OrderID (1) duplicate field to OrderCount.

    3. Change the aggregation type to Number of unique.

    

15. Create a calculated field for the average sales amount per order.

    1. In the top-right corner, click Add field.

    2. In the Name field, specify Sales per Order.

    3. In the column to the left, click the Sales field.

    4. Enter a /.

    5. In the column to the left, click the OrderCount field.

    6. Click Create.

       

16. Click Save in the top-right corner to save the dataset.

    

17. Enter a name for the dataset: Moscow Sales dataset, then click Create.

18. When the dataset is saved, click Create chart.

    

Step 3. Create a line chartStep 3. Create a line chart

To visualize sales dynamics by month, create a line chart.

1. For the visualization type, choose Line chart.

   

2. Add the sales date to the chart. Drag the OrderDate field from the Dimensions section to the X section.

3. Add a sales measure to the chart. To do this, drag the Sales field from Measures to the Y section.

4. Add the delivery type to the chart. To do this, drag the PaymentType field from the Dimensions to the Colors section.

   

5. Display the chart by month.

   1. Click the calendar icon next to the OrderDate field in the X section.
   2. In the Grouping field, select Rounding ⟶ Month and click Apply.

   

6. Save the chart.

   1. Click Save in the top-right corner to save the chart.

      

   2. In the window that opens, enter a name for the chart: Sales dynamics by month and payment type, and click Save.

Step 4. Create a column chartStep 4. Create a column chart

To visualize sales by brand and product category, create a column chart.

1. Copy the chart from the previous step.

   1. Click the down arrow next to the Save button in the top-right corner.

   2. Choose Save as.

      

   3. In the window that opens, enter the Sales by brand and category name for the new chart and click Save.

2. Select the Bar chart visualization type.

   

3. The OrderDate, Sales, and PaymentType fields are automatically added to the X, Y, and Colors sections, respectively.

4. Replace the months with brands on the X-axis. Drag the ProductBrand field from the Dimensions section to the X section and hold it over the OrderDate field until it turns red.

   

5. Replace payment types for product categories in the Colors section. Drag ProductCategory from the Dimensions to the Colors section and hold it over the PaymentType field until it turns red.

   

6. Sort the chart in descending order of sales measure. Drag the Sales field from the Measures section to the Sorting section.

   

7. Save the chart.

Step 5. Create a pivot table chartStep 5. Create a pivot table chart

To visualize sales by product and time, create a pivot table chart.

1. Copy the chart from the previous step.

   1. Click the down arrow next to the Save button in the top-right corner.
   2. Click Save as.
   3. In the window that opens, enter Sales by year and product as the name for the new chart.
   4. Click Save.

2. Choose the Pivot table visualization type.

   

3. The ProductBrand and Sales fields are automatically copied to the Columns and Measures sections, respectively.

   

4. Remove ProductBrand from the table.

5. Add the order date to the table. To do this, drag the OrderDate field from the Dimensions section to the Columns section.

6. Change the display format in the OrderDate field to years.

   1. Click the calendar icon next to the OrderDate field in the Columns section.
   2. In the Grouping field, select Date part ⟶ Year and click Apply.

   

7. Add the product category and subcategory to the table. To do this, drag the ProductCategory and ProductSubcategory fields from the Dimensions section to the Rows section.

   

8. Change the color of the sales measure in the table. To do this, drag the Sales field from the Measures section to the Colors section.

   

9. Save the chart.

Step 6. Create a heat mapStep 6. Create a heat map

To visualize the density of orders on the map of Moscow, create a heat map chart.

1. Copy the chart from the previous step.

   1. Click the icon next to the Save button in the top-right corner and select Save as.
   2. In the window that opens, enter Sales heat map as the name for the new chart.
   3. Click Save.

2. Select the Map visualization type.

   

3. Select the Points (heat map) layer type.

4. Add the delivery point coordinates to the map. To do this, drag the DeliveryAddressCoord field from the Dimensions section to the Points (heat map) section.

   

5. Save the chart.

Step 7. Create a dashboardStep 7. Create a dashboard

Create a dashboard to add these charts to.

1. Go to the DataLens home page.

2. Click Create dashboard.

   

Step 8. Add charts to the dashboardStep 8. Add charts to the dashboard

1. The first time you open the dashboard after saving, it opens in edit mode. If you open it later, click Edit in the top-right corner.

   

2. Click Add and select Chart.

   

3. In the window that opens, click Select.

4. Select the chart Sales dynamics by month and payment type. This automatically fills in the Title field with the name of the selected chart.

5. Click Add.

   

6. Similarly, add the following charts:

   • Sales by brand and category
   • Sales by year and product
   • Sales heat map

7. Position the charts on the dashboard however you like.

   

Step 9. Add selectors to the dashboardStep 9. Add selectors to the dashboard

Add selectors to filter your charts by date, Moscow districts, products, and customer statuses.

1. Click Add.

2. Choose Selector.

   

3. Add the calendar selector for the order date.

   1. Select the Moscow Sales dataset.
   2. Select the OrderDate field.
   3. This automatically fills in Title with the name of the selected field. Click the Show checkbox next to the selector title.
   4. Select the Calendar type.
   5. Enable Range.
   6. Click Add.

   

4. Add a product category selector.

   1. Select the Moscow Sales dataset.
   2. Select the ProductCategory field.
   3. This automatically fills in Title with the name of the selected field. Click the Show checkbox next to the selector title.
   4. Enable the Multiple choice option.
   5. Click Add.

   

5. Similarly, add selectors for the following fields:

   • ProductBrand
   • DeliveryDistrictName
   • DeliveryType
   • PaymentType

6. Position the selectors on the dashboard however you like.

7. Save the dashboard:

   1. In the top-right corner, click Save.

      

   2. Enter Moscow Shops dashboard for the dashboard name and click Create.

8. Your dashboard is ready. Now you can filter charts using selectors.

   

ClickHouse® is a registered trademark of ClickHouse, Inc.