Page Comparison

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 addresses	Turnover value aggregated to administrative units

Image Removed

Image Added

Image Removed

Image Added

Transactions table

The CSV file can be downloaded here: transactions.csv

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

...

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)

...

Transactions dataset syntax

{
    "name": "transactions",
    "type": "dataset",
    "title": "Transactions",
    "description": "Online and offline transactions done by our customers.",
    "ref": {
        "type": "dwh",
        "subtype": "basic",
        "primaryKey": "transaction_id",
        "categorizable": true,
        "fullTextIndex": false,
        "properties": [
            {
                "name": "transaction_id",
                "title": "Transaction ID",
                "column": "transaction_id",
                "type": "integer",
                "filterable": false
            },
            {
                "foreignKey": "customers",
                "name": "customer_id",
                "title": "Customer ID",
                "column": "customer_id",
                "type": "integer",
                "filterable": false
            },
            {
                "name": "store_id",
                "title": "Store ID",
                "column": "store_id",
                "type": "integer",
                "filterable": false
            },
            {
                "name": "date",
                "title": "Date",
                "column": "date",
                "type": "date",
                "filterable": true
            },
            {
                "name": "amount",
                "title": "Amount",
                "column": "amount",
                "type": "decimal(19,6)",
                "filterable": true
            },
            {
                "name": "day_index",
                "title": "Week day index",
                "column": "day_index",
                "type": "integer",
                "filterable": false
            },
            {
                "name": "day_name",
                "title": "Week day",
                "column": "day_name",
                "type": "string",
                "filterable": true
            },
            {
                "name": "hour",
                "title": "Hour index",
                "column": "hour",
                "type": "integer",
                "filterable": false
            },
            {
                "name": "time_category",
                "title": "Time category",
                "column": "time_category",
                "type": "string",
                "filterable": true
            },
            {
                "name": "channel",
                "title": "Channel (online/offline)",
                "column": "channel",
                "type": "string",
                "filterable": true
            }
        ]
    }
}

...

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

• by clicking three-dots icon next to project title on

  a 

  a Project page, and

  selecting 

  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.Image Removed

...

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.

tomas.schmidl@secure.clevermaps.io/project:k5t8mf2a80tay2ng/dump:$ copyMetadata --objectName customers_metric --newName turnover_metric
Object turnover_metric.json successfully created as a copy of object customers_metric.json

tomas.schmidl@secure.clevermaps.io/project:k5t8mf2a80tay2ng/dump:$ copyMetadata --objectName customers_indicator --newName turnover_indicator
Object turnover_indicator.json successfully created as a copy of object customers_indicator.json

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.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 

{
    "name": "turnover_metric",
    "type": "metric",
    "content": {
        "type": "function_sum",
        "content": [
            {
                "type": "property",
                "value": "transactions.amount"
            }
        ]
    }
}

{
    "name": "turnover_indicator",
    "type": "indicator",
    "title": "Turnover value",
    "description": "Total turnover value of all transactions",
    "content": {
        "metric": "/rest/projects/$projectId/md/metrics?name=turnover_metric",
        "scale": "standard",
        "distribution": "geometric",
        "visualizations": {
            "dotmap": true,
            "heatmap": true
        },
        "format": {
            "type": "number",
            "fraction": 0,
            "symbol": "CZK"
        }
    }
}

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

...

Updated transactions dashboard syntax

{
    "id": "j2idsg8tq7icp62f",
    "name": "business_overview_dashboard",
    "type": "dashboard",
    "content": {
        "blockRows": [
            {
                "type": "indicator",
                "indicator": "/rest/projects/$projectId/md/indicators?name=customers_indicator",
                "indicatorDrill": "/rest/projects/$projectId/md/indicatorDrills?name=empty_indicator_drill",
                "layout": "primary"
            },
            {
                "type": "indicator",
                "indicator": "/rest/projects/$projectId/md/indicators?name=turnover_indicator",
                "indicatorDrill": "/rest/projects/$projectId/md/indicatorDrills?name=empty_indicator_drill",
                "layout": "primary"
            }
        ]
    },
    "accessInfo": {
        "createdAt": "2020-06-23T17:26:08Z",
        "createdBy": "/rest/accounts/00ubfu7fdyIoFKxXm0h7"
    },
    "links": [
        {
            "rel": "self",
            "href": "/rest/projects/k5t8mf2a80tay2ng/md/dashboards/j2idsg8tq7icp62f"
        }
    ]
}

Use pushProject to upload modified dashboard into the project.

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  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 only datasets and  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 the importProject command 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 the x_min, x_max, y_min, y_max properties 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. importProject 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 parameter. The importProject command  command performs a data model validation using validate before the import itself.

tomas.schmidl@secure.clevermaps.io/project:k5t8mf2a80tay2ng/dump:$ importProject --project q1zdp9d0ao78rdv5 --datasets
Validating DWH model/data integrity of project q1zdp9d0ao78rdv5... OK

Importing project q1zdp9d0ao78rdv5...

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:

Import finished!
Source project: q1zdp9d0ao78rdv5 (can-dim-adm-units-cz-en)
Destination project: k5t8mf2a80tay2ng (First project)
Imported datasets: 21
Imported metadata objects: 0
Imported CSV files: 11

To view detailed imported content, use status command.
Now you can make changes, or import another project.
When you're done, use addMetadata command to add the metadata to the project.
To push the data to the project, use pushProject command.

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.

...

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

...

Customers dataset excerpt

    ...
{
    "foreignKey": "zsj_d_dwh",
    "name": "neighborhood_code",
    "title": "Neighborhood code",
    "column": "neighborhood_code",
    "type": "string",
    "filterable": false
},
    ...

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.Image Removed

...

Image Removed

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

...

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

...

Business overview view excerpt

  ...
    "content": {
        "icon": "shopping_cart",
        "dashboard": "/rest/projects/$projectId/md/dashboards?name=business_overview_dashboard",
        "defaultGranularity": "/rest/projects/$projectId/md/datasets?name=zsj_dwh",
        "mapOptions": {
  ...        

...