Create a Data Model

• 1. Importing your data
• 1.a) Import the orders fact table
• 1.b) Define the primary key
• 1.c) Tag the foreign keys
• 1.d) Import the dimension tables
• 1.e) Import the event table
• 2. Creating your model
• 2.a) Exploring the Fact table in a sheet
• 2.b) Joining the dimension tables through the Unlock Feature
• 2.c) Joining another fact table through linked view

Here is the model we will build with KAWA.

1. Importing your data

For this scenario, we will import data from four CSV files, each one corresponding to a table of our schema.

In KAWA, create a new workspace for this use case: Client Order Model

1.a) Import the orders fact table

From the datasource section, click on “+” > Upload a file.

Download this file and upload it into KAWA.

Click on Next:

1.b) Define the primary key

To define the primary key, do the following:

1.c) Tag the foreign keys

The next step is to configure the attributes on the dimensions that you will use to perform the joins - the foreign keys.

Here, we have two foreign keys: Customer ID and Product ID.

On each of those two columns, do the following: Create Attribute, Type in the name of the new attribute, and click Next.

The target configuration should be like this:

When your configuration is correct: click on “Save and Run” at the bottom left.

1.d) Import the dimension tables

Repeat the process described above to import the two dimension tables:

The configuration for them should be:

1.e) Import the event table

Import the event csv into KAWA. There is no configuration to apply there, no primary key nor attribute.

2. Creating your model

This is the list of existing datasources that you need to start this step:

2.a) Exploring the Fact table in a sheet

Go in the Sheets section and open the orders - transaction sheet (The name may differ if you named your datasource differently on the import step)\\

Click on the “Lineage” menu item at the top:

The current model is simplistic: One sheet and one Data Source. We call this Data Source the primary Data Source of the model because it will impose its primary keys. All the JOINS that we will do will be LEFT JOINS on this data source.

Click on the “Model” menu item at the top:

Here you can see all the columns imported from the Primary Data Source - The orders.

2.b) Joining the dimension tables through the Unlock Feature

In the “Views” section, click on “+ Add Fields”. Notice that the two dimension tables are available for joining. click on UNLOCK for both of them, and Save.

This is what you obtain:

Go ahead and add the Customer Name from the customer dimension. Add as well Category, Sub-Category and Product name from the product dimension.

Click on the “Lineage” menu item at the top:

You can now see that three data sources are used to feed our Model. Hover over the edges to obtain details about joins (List of foreign keys that are used to perform the LEFT JOINS)

2.c) Joining another fact table through linked view

Create the Linked view to the Order sheet

Click on the “Customer Name” Field in your grid. This field was added from the “+ Add field” menu, from the customer dimension.

From there, pick the “Even” sheet, resulting from the Event import.

We are telling KAWA that the “Customer Name” column from the current sheet will be linked to the “Attendee” column of the events sheet.

When you click on “Apply”, you should see the following:

The top grid will filter the bottom on all events whose attendee is the “Customer Name”

Add columns from the bottom Grid to the Top one

Just drag and drop the “Cost” column from the bottom to the top. A new column will be added like so:

This new column will be the SUM of the “Cost” of all events associated with each customer.

Click on the “Lineage” menu item at the top:

Here is the final lineage where all the elements appear.