Tutorial 2: Data model and data dimensions

In the first tutorial, we visualized our customers' data in the map. We added a simple metric and a simple indicator which worked with one dataset.

In this tutorial, we'll add more datasets to the project, take a look at the data model and learn how to work with data dimensions.

What you'll create

We will extend the existing dotmap and heatmap visualizations, and add a new one - areas.

Turnover value aggregated to customer addressesTurnover value aggregated to administrative units

Transactions table

The CSV file can be downloaded here: transactions.csv

NameTitleData type
transaction_idTransaction IDinteger
customer_idCustomer IDinteger
store_idStore IDinteger
dateDate of the transactiondate
amountTransaction amountdecimal
day_indexWeek day indexinteger
day_nameWeek daystring
hourHour indexinteger
time_categoryTime categorystring
channelChannel (online/offline)string

Download the CSV file and put it in the /data folder of your dump.

Transactions dataset

Let's have a look at dataset corresponding to the transactions table. Few things to note:

  • its subtype is basic, because it does not contain any geospatial information
    • therefore, it does not contain properties.featureTitle etc.
  • on the customer_id property, there's foreignKey to the customers dataset
    • so this property is linked to the primary key (Customer ID) of the customers dataset
  • the dataset is categorizable, and has some filterable properties (more later)

Use addMetadata and pushProject to add the dataset and upload the data.

Data model

Each CleverMaps project is based around its data model. Visualization of the data model can be viewed:

  • by hovering the Account icon on the bottom left of a Project page, and selecting Data model
  • by clicking on Menu in the top left and selecting Data model from the map

So, after we've added the transactions dataset, our data model consists of 2 datasets. They are native to this project, so they have a green label. You can also see that transactions dataset is linked to customers dataset using the customer_id foreign key.

Visualizing turnover

Now we can visualize the turnover generated by our customers. We will use the copyMetadata command to create a copy of the existing customers_metric.json and customers_indicator.json objects, and modify them before we add them to the project.

The copied objects have a new name property, and a new filename. But we still need to change these fields:

turnover_metric.json:

  • change content.type to function_sum
  • change content.type.content[0].value to transactions.amount

turnover_indicator.json:

  • change title to "Turnover value"
  • change description to "Total turnover value of all transactions"
  • change content.metric reference to turnover_metric
  • add content.format.symbol key with the "CZK" value 


Add the objects using addMetadata, and modify business_overview_dashboard so it contains reference to turnover_indicator. It should look like this:

Now, there's a new indicator on the dashboard, and you are able to see the turnover values aggregated to the customer addresses.

This is nice. But what if we could aggregate the turnover to some administrative units?

Importing a data dimension

Data dimensions are specific projects that contain prepared data (e.g. administrative units or demography). They can be imported into other projects and combined with their data.

Most dimension projects contain only datasets and corresponding data. Some dimensions (e.g. administrative units) also have views to preview their contents. 

We will import the administrative units dimension using the importProject command. This dimension contains the administrative units of the Czech Republic in 7 granularities - regions, counties, municipalities with extended competencies, municipalities, municipalities and city districts, city districts, neighbourhoods. Each granularity is defined by two datasets - one DWH dataset, and one vector tile dataset. The DWH dataset contains data about the name of the administrative unit, its bounding box (the x_min, x_max, y_min, y_max properties) and foreign keys to the neighbouring granularities. The vector tile dataset contains a reference to a vector tile service, which contains the polygons for each administrative unit.

We will specify just the project ID of the administrative units dimension project - q1zdp9d0ao78rdv5. import command also offers the option to specify a prefix for all imported files, or to import only parts of the project. We need just the datasets and the data from this dimension project, so we will use --datasets parameter. The importProject command performs a data model validation using validate before the import itself.

Here, we omit a significant amount of the import command output for the sake of readability. This command wraps a number of other commands, whose output might not be that relevant to you. What is relevant is the result:

The dimension has been successfully imported. You can review the import before pushing it into the project with status command.

Then use addMetadata and pushProject to upload it into your project. Let's have another look at the data model. You can see the dimension datasets with pink label.

We just have to connect the customers dataset to the zsj_d_dwh dataset (Neighborhoods), which is the smallest administrative unit.

Modify the neighborhood_code property of customers dataset. Add foreignKey property to the zsj_d_dwh dataset.

Use pushProject to push the changes. The data model should now look like this:

Open the Business overview view and see that the granularity has changed. Administrative units usually have multiple levels. By default we see the biggest level - Regions. Change the granularity to Neighborhoods in the granularity drop down menu in the upper left corner.

Visualize the Turnover value indicator to see the turnover aggregated to the Neighborhoods.

Optionally, set the defaultGranularity property to zsj_dwh in in business_overview_view to view Neighborhoods by default.