User Guide: Kumo StudioConnect Data

Choose Table Relationships

View as Markdown

Identify the relevant entities and events

To choose which tables to link, think about the main entities in your enterprise and how they relate to and interact with each other. This is similar to designing a data model for your data warehouse or BI tools. To connect tables into a graph, at least one table must contain a primary key.

For example, in an e-commerce graph, you may have:

  • Many Shoppers, each of which may have one or more sessions
  • Within those sessions would be one or more web interaction events such as Clicks, Add-to-Carts, Purchases, and so on, usually done in relation to products
  • Products could be sold by different Merchants

In this case, you would link together tables for representing:

  • Shoppers (primary key (PK) = Shopper ID)
  • Products (PK = Product ID)
  • Merchants (PK = Merchant ID)
  • Sessions (time column = Session End Time and an ID column for Shopper)
  • Either a single events table or separate tables for clicks, add-to-carts, and purchases, each with:
    • A time column for the time of event
    • ID columns for the relevant shoppers, products, and merchants
  • In the case of frequently changing data for shoppers/products/merchants, you can also include tables for them. Each fact in these tables represents a snapshot of the data at a particular point in time, with appropriate ID columns.

Your organization’s graph will likely differ from this example.

How comprehensive your graph needs to be depends on the predictions you plan to make. For example, if one of your predictions is the “30-day sum of future purchase values for each customer”:

  • At a minimum, you need to include two tables:

    • A table with a row per unique customer, with a customer ID primary key column.
    • A table with a row per historical purchase event. It must include at least a time column for the purchase and a customer ID foreign key (FK) column linking it to the customer table. Also include a column for the numeric value of each purchase.

You may eventually also want to include information relevant to predicting future purchases, such as fact tables for web engagement data, or an additional dimension table for product catalog metadata. To start, include the minimum set of tables and add more over time, without changing your predictive query definitions.