Joining Sources using Cube Joins

Akuko provides powerful capabilities to join data from multiple sources using Cube Joins. This feature enables you to combine datasets and create more insightful data stories. Follow this guide to learn how to define and use Cube Joins effectively.

tip

There is a way to do this without writing any schema. In a Space's Sources list, New view builds the join through a form: you pick the two Sources and the columns to match on, and Akuko sets primary_key on both sides for you — which is the step this guide is easiest to get wrong. Reach for the hand-written schema below when you need something the form does not offer.

Prerequisites

Before you begin, ensure you have:

• Access to Akuko: You should have an active Akuko account with the necessary permissions to create and manage Sources, Dimensions, and Cube Joins.

Step 1: Access Your Source

1. Log in to your Akuko account.

2. From the Akuko dashboard, navigate to the "Sources" section.

3. Select the Source where you want to define your Cube Join.

Step 2: Define Primary keys

1. Within your Source, locate the "Cube" section or an equivalent section where Cube Joins can be defined.

2. Tell Cube which dimension is the primary key by adding the property primary_key to the relevant dimension.

   "uuid": {
        "sql": "uuid",
        "type": "string",
        "primary_key": true, <--
        "title": "UUID"
      },

3. Click "Save" to save your changes.

caution

The property is primary_key, with an underscore — not primaryKey.

This is worth care because getting it wrong fails silently: a primaryKey key is simply ignored, the schema saves without complaint, and the join then returns nothing with no error to explain why. If a join is not working, check this spelling first.

Both true and "true" are accepted.

caution

Make sure all Sources you intend to join have a primary_key defined.

Step 3: Define Cube Joins

1. Add a joins object to the Cube where the key is the Cube name and the value is an object with a sql and relationship property.

{
"joins": {
    "<CUBE_NAME>": {
      "sql": "<SQL>",
      "relationship": "<RELATIONSHIP>"
    }
  },
"dimensions": [
    // list of dimensions 
]
,
"measures": [
    // list of measures
]
}

2. Replace <CUBE_NAME>, <SQL> and <RELATIONSHIP> with their appropriate values.

• CUBE_NAME: You can find this at the top of your target Source.
• SQL: This should be a SQL statment that tells Cube which dimension to join on:

"sql": <CURRENT_CUBE_NAME>.sector = ${<TARGET_CUBE_NAME>.id}

• RELATIONSHIP: This should be a string that represents the type of join you are creating hasOne, hasMany or belongsTo.

"Cube_4292329": {
      "sql": "${Cube_2937885.sector} = ${Cube_4292329.id}",
      "relationship": "hasOne"
    },

info

For a definition of relationships, see the Cube docs.

3. Click "Save" to create the Cube Join.

Step 4: Create dimensions

1. Once your Cube Join is defined and saved, you can create diemnsions that reerence the join.

 "event_name": {
        "sql": "${<TARGET_CUBE_NAME>.name}",
        "type": "string",
        "title": "Event Name"
  },

caution

Make sure to include the variable interpolation syntax ${} in your Cube or the join will fail.

Step 5: Utilize the Joined Data

Once your Cube Join is defined and saved, you can use the resulting Cube in various components within your Posts.

Conclusion

Cube Joins in Akuko empower you to combine data from different sources to create more comprehensive and insightful data stories. By following this guide, you've learned how to define Cube Joins and leverage the joined data within your Posts.

Explore the possibilities of combining multiple sources to unlock deeper insights and present richer data stories to your audience.