Configuring your Data Source
Akuko makes it easy to create graphics and charts based on the results of a query. This page explains how to organize your source so it is ready to use in your posts.
There are three main configuration parameters for an Akuko source: Dimensions, Measures, and Geometries.
Dimensions represent the attributes of the data. Think of a dimension as a column in your original tabular dataset. For example, if your data represents
world cities, the country, name, and population could all be possible dimensions, or columns, in the data source. Not all dimensions are the same. It is important to understand the kind of data they represent. A text field is not the same as a numerical field or a geolocation because each can be aggregated in different ways. Akuko supports the following data types:
string- Strings are used to represent text data. Anything from names to paragraphs can be stored using this type. Strings can be different lengths and include capital letters and special characters.number- Numbers can be both integers or floating-point numbers, such as 5.6. They are interpreted distinctly from text. Numbers can be used in aggregations, like sums and averages, which would result in errors with other data types.boolean- Booleans are logical data types that can only have the values true or false, or binary representations of 1s and 0s, with a very limited set of allowed values. They are useful for conditional logic, where you are comparing two values, and filtering.geo- Geolocation, or geo, is a spatial identifier used to display data on a map. Unlike other dimension types, it requires two fields to be defined: latitude and longitude.time- To create time series charts, Akuko needs the time dimension identified. Not all time data is extremely precise, you might only have the day of the year in one dataset while snapshots with minutes and seconds in another one. For ease of comparison, precise time values are gathered from all the data and stored as a timestamp.
When you create a source, Akuko automatically creates dimensions for you based on the data. From the data source's information page, changing a dimension type to the appropriate one is a one-click process.
Measures are aggregations from your data. They represent summary values not present in your original dataset but are the result of some calculation.
The simplest kinds of measures provide aggregations along a single dimension. They can be generated in the Measures tab of the Source panel. To do this, click Add Measure and reference the dimension name using a
${ } notation. Measures use PostgreSQL to develop calculations. 
Behind the scenes, Akuko interacts with sources via SQL queries. Therefore, the power of SQL is available to any analyst who is interested in using it. Beyond the standard Measure types, you can define your own custom measure using the SQL parameter.
The below example will show how to create a custom measure with a dataset about non-pharmaceutical interventions. In the example, we created a Google Sheet data source and named it Government Action.
Under Measures, we clicked Add measure to add a new measure. We filled in the information for the
Name, Label, SQL and Type. We used a number type for our custom SQL using a sum and case statement. A number type was used A number type is used for most measures and creating your own custom SQL, which does not use any of the aggregation functions provided in Akuko (i.e. count, sum, avg, min, max, countDistinct, or runningtotal). In this example, we wanted to calculate the number of countries that have noted ‘Yes’ for Non-Pharmaceutical Interventions (NPIs). There are a total of 102 different NPIs, but for this example, we wanted to obtain data specifically for intervention 1 (npi_1). Our SQL statement was:
SUM(CASE WHEN ${npi_1} = 'Yes' THEN 1 ELSE 0 END)
The system attempts to verify whether the entered SQL Statement is accurate. A green tick under the SQL text box indicates the statement structure is correct.
An error
Invalid sql will appear if the statement structure is incorrect.
You can use the Query section to confirm whether the measures you have set are functional. In case any changes are made to the SQL statement or Type, you will need to relink the query.
Geometries are a specific way of interacting with the data that will be displayed on a map. To learn more about geometries, please see our Geometry section.
Akuko needs to reference your data in a way that will allow queries to run smoothly. For example, it pre-populates the dimensions panel using the column names seen in the original dataset. This happens only after substituting all uppercase letters with lowercase letters and replacing the spaces with underscores. A field like
Capital City will be loaded as capital_city.If you are not happy with the default naming, you can provide alternative labels that will be shown to the users in the posts. Labels are available both from the Dimensions and Measures panes and will propagate across all posts once you edit them. In the example below, the data field named
average_population has been renamed with the label Avg Population, and the total_population was renamed with the label Total Population.
Often data loaded as number is actually representing a currency, percentage, or numbers too large and may need appropriate definitions to be useful.
You can style your data using the following style settings:
Prefix- Characters displayed before your data.Format- A style definition to specify the number of floating points to be displayed if your data is not integer.Suffix- Characters displayed after your data.
In a real-life example, non-styled data might appear in your table as shown below.
After some styling, we can remove the exponential notation, and add a $ and a k, indicating that our GDP estimate is in thousands of US Dollars.
The Query interface allows you to test if your data configuration is ready before using it in posts or making it available to other platform users.
To verify whether your setup is correct, select your dimension and measure of interest from the drop down on the left, and then click Run Query. You should see the result set of your query being returned on the right-hand pane as a JSON object.
Even though the text structure might seem cryptic, you can still see if the data returned matches your expectations or not by comparing what is displayed on the text structure with the data collected. In this case, NationalHealthData is the name of the source whereas .countryname is the column name (dimension) which is being returned when running the query. Most importantly, a popup should come at the top of the screen confirming that your query was successful.
Last modified 1yr ago