Writing to GoodData

After manipulating data in SQL and writing data to Tableau, let’s now write data to GoodData Analytics. As KBC 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). KBC 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 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-tutorial storage bucket: opportunity, account, level, and user. Then create the output mapping for the out_opportunity, out_user, and out_account tables to be stored in the out.c-tutorial output bucket.

Screenshot - Transformation Input & Output Mapping

Use the following four SQL queries to create the output bucket 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
        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"
        "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-tutorial 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. Start by creating a new writer in the Writers section:

Screenshot - New Writer

Configure Writer

Find the GoodData writer:

Screenshot - New Writer

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. Create New Configuration to continue:

Screenshot - New GoodData Writer

When creating a new configuration, name it and select properties of the GoodData project. KBC automatically creates a free Demo GoodData project for you. However, it expires in one month from the date it has been created.

Screenshot - Create GoodData Writer Configuration

Whilst the configuration is being created, you’ll see a warning message saying that the project is being prepared and cannot be accessed yet. In the meantime, 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-tutorial 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.

Screenshot - GoodData Writer Table Configuration Part 1

The new DATE column will be marked in red because a Date Dimension has to be added. Click on Add and create a new date dimension:

Screenshot - GoodData Writer Date Dimension

The configuration is now valid (FirstOrder column is not red anymore) and can be saved:

Screenshot - GoodData Writer Table Configuration Part 2

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

Screenshot - GoodData Writer Table Configuration Part 2

Name the table user and set the columns in the following way:

Screenshot - GoodData Writer User Table Configuration

Save the table configuration, and go back to the writer configuration. Add the third table called out.c-tutorial.opportunity. Name it opportunity and set the columns as follows:

  • Amount and Probability to FACT,
  • AccountId and OwnerId to REFERENCE,
  • CreatedDate, CloseDate, Start_Date and End_Date to DATE,
  • 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

Specify account and user as a referenced table for the REFERENCE columns. Also, add a date dimension for each date column. In case of CreatedDate, tick the Include Time checkbox when creating the date dimension. The result should look like this:

Screenshot - GoodData Writer Opportunity Table Configuration Final

Save the table configuration, and go back to configuring the writer. The warning message about GoodData project being prepared should be gone by now. If not, refresh the page in a few moments.

Once the project is ready, add all three tables to the project upload by ticking the check right of their names. With all three tables marked, click on Upload project to push them 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 Enable Access To Project. This will give the current KBC 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 KBC ends here. Continue to Setting up Automation.