Visualizing data from a ClickHouse database

The data source is a table from a ClickHouse database with information about sales, including total amount, region, segment, and year.

DataLens can be used to visualize sales, for example:

  • By year and region.
  • By region, segment, and year.

To visualize dependencies, you need to connect to the ClickHouse database, create a dataset based on the connection, and configure a dashboard by adding the corresponding charts to it.

This includes the following steps:

Create a connection to the ClickHouse database where the table is stored.

Step 1. Create a dataset

Create a dataset based on the connection to the ClickHouse database where the table is stored.

  1. Go to DataLens.

  2. Click Create dataset.

    image

  3. Select the Sample ClickHouse connection.

    image

  4. Enter the connection name: My DataSet.

  5. Under the word Table, select the SampleLight table.

  6. Click Create.

    image

  7. In the Aggregation column, select Amount for the Profit field.

    image

  8. In the Aggregation column, select ** Amount** for the Sales field.

    image

  9. Rename the Customer ID field to Customer Count: click on the field name, delete the current name, and enter the new one.

  10. In the Aggregation column, select Number of unique for the Customer Count field.

    image

  11. Create a calculated field for calculating sales per customer.

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

      image

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

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

    4. Enter a /.

    5. In the column to the left, click on the Customer Count field.

    6. Click Create.

      image

  12. Click Save in the upper-right corner to save the dataset.

    image

Step 2. Create a bar chart

To visualize sales by year and region, create a bar chart type chart.

  1. Click Create chart in the upper-right corner.

    image

  2. In the second column of the window that opens, click Line chart and choose a different chart type: Bar chart.

    image

  3. Add years to the chart. To do this, from the first column in the Dimensions section, drag the Year field to the X-axis in the second column.

  4. Add sales information to the chart. To do this, from the first column in the Measures section, drag the Sales field to the Y-axis in the second column.

  5. Add a color division by region to the chart. To do this, from the first column in the Measures section, drag the Region field to the Color section.

    image

  6. Save the chart.

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

      image

    2. In the window that opens, click Save.

      image

Step 3. Create a pivot table chart

To visualize sales by region, segment, and year, create a pivot table type chart.

  1. Copy the chart from the previous step.

    1. Click image in the upper-left corner next to the chart name.

    2. Choose Copy.

      image

    3. In the window that opens, enter the name of the new chart: My DataSet — Pivot table.

    4. Click Ready.

      image

  2. In the second column of the window that opens, click Bar chart and choose a different chart type: Pivot table. image

  3. Add information about regions to the table. To do this, from the first column in the Dimensions section, drag the Region field to the Rows section.

  4. Add information about segments to the table. To do this, from the first column in the Dimensions section, drag the Segment field to the Rows section.

  5. Add a color division by sales per customer to the chart. From the first column in the Dimensions section, drag the Sales per Customer field to the Color section.

  6. Click Save in the upper-right corner to save the chart.

    image

Step 4. Create a dashboard

Create a dashboard that the chart and table will be added to.

  1. Go to the DataLens homepage. To do this, click on the word Yandex DataLens in the upper-left corner.

  2. Click Create dashboard.

    image

  3. Enter the dashboard name: Dashboard.

  4. Click Create.

    image

Step 5. Add charts to the dashboard

Add a chart and table to the dashboard.

  1. In the dashboard that opens, click Edit in the upper-right corner.

    image

  2. Click Add.

  3. Choose Chart.

    image

  4. In the window that opens, click Select.

  5. Select the My DataSet — Pivot table chart. This automatically fills in the Title field with the name of the selected chart.

  6. Click Add.

    image

  7. Add another chart. Perform all the steps from the beginning and specify My DataSet — Bar chart in step 5.

    image

  8. Place the charts on the dashboard however you like (for example, stretch them and drag the chart to the right of the table).

    image

Step 6. Add selectors to the dashboard

Add selectors to be able to filter sales by year (the Year field) and category (the Category field).

  1. Click Add.

  2. Choose Selector.

    image

  3. Under the word Dataset, click Select.

  4. Select the dataset named My DataSet.

  5. Under the word Field, click Select.

  6. Choose the Year field. This will automatically fill in the Title field with the name of the selected field.

  7. Under Default value, specify 2014 and 2015.

  8. Enable the Multiple choice option.

  9. Click Add.

    image

  10. Add another selector.

    1. Perform all the steps from the beginning and specify the Category field in step 6.

    2. Don't specify a default value in step 7.

      image

  11. Click Save in the upper-right corner to save the dashboard.

  12. Place the selectors on the dashboard however you like (for example, drag them to the top of the dashboard above the pivot table).

    image