Writing to GoodData

After manipulating data in SQL and writing data to Tableau, let’s now write data to GoodData Analytics. As Keboola Connection creates GoodData testing projects for you automatically, there is no need to have a GoodData account before you start.

Writing data to GoodData is very similar to writing data to Tableau, although there are some changes due to the fundamental differences in both platforms. The GoodData platform uses the concept of Logical Models where multiple tables are loaded into the platform together with their logical connection model (schema). Keboola Connection will assist you in creating the model.

Prepare Data for Writer

To load individual tables instead of denormalized tables, the transformation created earlier has to be modified. Go to Transformations and create a new transformation bucket and a new transformation. For the sake of practicing, let’s create a brand new transformation instead of modifying the existing one.

Screenshot - Transformation Intro

Apart from creating a new transformation, we also need a new transformation bucket, since the Tableau and GoodData transformations are not really related. If they were more complex, we would take out the similar parts into another transformation. Name the new transformation bucket Opportunity - GoodData and choose the Snowflake backend.

Screenshot - Transformation Bucket Create

Then add a new transformation and name it.

Screenshot - Transformation Create

Now set the transformation input mapping. Include the following tables from the in.c-csv-import storage bucket: opportunity, account, level, and user. If you loaded data using the Database extractor or Google Drive extractor, feel free to use the tables created by them (e.g., in.c-keboola-ex-db-snowflake-548904898.account or in.c-keboola-ex-google-drive-548902224.level-level). In either case, make sure that the destinations are set to account, opportunity, user and level. Then create the output mapping for the out_opportunity, out_user, and out_account tables to be stored in the out.c-opportunity-gooddata output bucket.

Screenshot - Transformation Input & Output Mapping

Use the following four SQL queries to create the output tables.

CREATE TABLE "tmp_level" AS
    SELECT "Name", CASE "Level"
        WHEN 'S' THEN 'Senior'
        WHEN 'M' THEN 'Intermediate'
        WHEN 'J' THEN 'Junior' END AS "Level"
    FROM "level";

CREATE TABLE "out_opportunity" AS
    SELECT *, CASE
        WHEN "Probability" < 50 THEN 'Poor'
        WHEN "Probability" < 70 THEN 'Good'
        ELSE 'Excellent' END AS "ProbabilityClass"
    FROM "opportunity";

CREATE TABLE "out_user" AS
    SELECT "user"."Name" AS "Name", "user"."Sales_Market" AS "UserSalesMarket",
        "user"."Global_Market" AS "UserGlobalMarket"
    FROM
        "user" JOIN "tmp_level" ON "user"."Name" = "tmp_level"."Name";

CREATE TABLE "out_account" AS
    SELECT * FROM "account";

Screenshot - Transformation Queries

Run the transformation.

Screenshot - Transformation Run

This will create a background job which will

  • take the four tables in the transformation input mapping from Storage,
  • modify them with the SQL queries of the Transformation script, and
  • create three new tables in the out.c-opportunity-gooddata output bucket in Storage.

To see if the transformation job has finished, go to Jobs, or click on the little Transformations job has been scheduled window that pops up after a transformation starts running. When finished, or while waiting for the job to end, continue configuring the GoodData writer.

Configure Writer

Start by creating a new writer in the Components – Writers section:

Screenshot - New Writer

The GoodData writer can have multiple configurations (as any other writer or extractor). Each configuration represents a set of data loaded into a single GoodData project. New Configuration to continue:

Screenshot - New GoodData Writer

And choose its name:

Screenshot - New GoodData Writer

Set Up GoodData Project to continue:

Screenshot - Setup GoodData Project

Keboola Connection can create a free Demo GoodData project for you. However, it expires in one month from the date it was created.

Screenshot - Create GoodData Project

Let’s create a Date Dimension.

Screenshot - Create a date dimension

Name the dimension first_order:

Screenshot - GoodData Writer Date Dimension

Now let’s configure the tables that are to be loaded to the project.

Screenshot - Add Tables to GoodData Writer

Add the account table from the out.c-opportunity-gooddata bucket. When adding a table, simplify the table title to just the table name (we have only few tables).

Screenshot - Add Table GoodData Writer

Configure the type of each column. Mark

  • the primary key, identifier, as CONNECTION_POINT,
  • columns which we want to measure as FACT,
  • all other columns used for slicing and filtering as ATTRIBUTE, and
  • date/datetime columns, being an exception, as DATE.

Do not set the Data Type column.

Set the previously created date dimension first_order to the FirstOrder column.

Screenshot - GoodData Writer Table Configuration Part 1

Save the column settings.

Screenshot - GoodData Writer Table Configuration Part 1 Save

Then go back to the writer configuration, and add the out.c-opportunity-gooddata.user table.

Screenshot - GoodData Writer Table Configuration Part 2

Name the table user and set the Name column to CONNECTION_POINT and everything else to ATTRIBUTE.

Screenshot - GoodData Writer User Table Configuration

Save the table configuration, and go back to the writer configuration.

Add four other date dimensions called created_date, close_date, start_date and end_date. In case of created_date, tick the Include Time checkbox when creating the date dimension. The result should look like this:

Screenshot - GoodData Writer User Table Configuration

Add the third table called out.c-opportunity-gooddata.out_opportunity. Name it opportunity and set the columns as follows:

  • Amount and Probability to FACT,
  • AccountId and OwnerId to REFERENCE and connect them to tables account and user,
  • CreatedDate, CloseDate, Start_Date and End_Date to DATE and connect them to the previously created date dimensions,
  • Id to IGNORE (we won’t be needing it any more), and
  • everything else to ATTRIBUTE.

You should obtain the following result:

Screenshot - GoodData Writer Opportunity Table Configuration

Screenshot - GoodData Writer Opportunity Table Configuration

Screenshot - GoodData Writer Opportunity Table Configuration

Save the table configuration, and go back to configuring the writer.

Now click on Run Component to push the tables to GoodData:

Screenshot - GoodData Writer Tables Finished

The tables will be written into GoodData by a background job. When a job is running, a small orange circle appears under Last runs, along with RunId and other info on the job. Green is for success, red for failure. Click on the indicator, or the info next to it for more details.

In the mean time, click on GoodData Project to reveal other options and Go To Project. This will give the current Keboola Connection user, you, access to the GoodData project referenced in the writer configuration.

Screenshot - GoodData Writer Access Project

Clicking the GoodData Project link will take you directly to GoodData BI and automatically log you in. Then create your report:

Screenshot - GoodData Introduction

First, specify a metric. It can be computed from columns we marked as FACT when setting up the writer (those are Amount and Probability). Let’s add a metric for SUM of Amount,

Screenshot - GoodData Create Metric

Then specify how this metric should be sliced in the How section. Let’s slice it by ProbabilityClass:

Screenshot - GoodData Slice Metric

Additional slices or filters can be added in the dashboard wizard. To close the wizard, click Done and the result will be shown to you as a table. To change the view, use the appropriate icon in the top right corner.

Screenshot - GoodData Chart

This will give you the same chart we produced in the Tableau Example.

The tutorial on writing data to GoodData BI platform using Keboola Connection ends here. Continue to Setting up Automation.