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.
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.
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.
Then add a new transformation and name it.
Now set the transformation input mapping. Include the following tables from the
in.c-tutorial storage bucket:
Then create the output mapping for the
to be stored in the
out.c-tutorial output bucket.
Use the following four SQL queries to create the output bucket tables.
Run the transformation.
This will create a background job which will
out.c-tutorialoutput 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:
Find the GoodData 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:
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.
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.
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).
Configure the type of each column. Mark
Do not set the Data Type column.
DATE column will be marked in red because a
Date Dimension has to be added.
Click on Add and create a new date dimension:
The configuration is now valid (
FirstOrder column is not red anymore) and can be saved:
Then go back to the writer configuration, and add the
Name the table user and set the columns in the following way:
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:
IGNORE(we won’t be needing it any more), and
You should obtain the following result:
user as a referenced table for the
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:
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:
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.
Clicking the GoodData Project link will take you directly to GoodData BI and automatically log you in. Then create your report:
First, specify a metric. It can be computed from columns we marked as
when setting up the writer (those are
Let’s add a metric for
Then specify how this metric should be sliced in the How section. Let’s slice it by
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.
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.