Transforming your data

On this section, you'll learn how to create and execute transformation jobs in your data warehouse. Data transformation is useful if you need to organize, clean, denormalize, filter, rename, or pre-aggregate the raw data in your warehouse so it can be ready for your analysis


Transforming your data

You should create transformation jobs if you need to prepare the raw data you have stored in your warehouse so it can be ready for your analysis. To start a transformation over your data you need to have some active database in your SlicingDice account containing the data you want to transform.

To start transforming your data you should create a transformation job. Access the Transformation Jobs tool on the Data Loading & Transformation Module. On this tool, click on Create New Transformation Job.

List of
        transformation jobs

Now you'll go through a wizard which you'll configure your transformation job as you want to prepare your stored data. The first step is to define a transformation job name, possible labels, a description, the target database that will have its data transformed and the job frequency that defines if this transformation will run manually or automatically (every 1, 3, 6 12, 24 hours or every 10 minutes).

After concluding these configurations, click on Save & Continue.

Step 1
        transformation jobs

On the second step you'll configure the transformation steps of the transformation job you're creating. These steps are SQL queries that you can use to transform your data as you want. You can add how many steps you need clicking on Add transformation step and defining the queries you'll use.

Step 2
        transformation jobs

Click on Add transformation step and on the pop-up shown define a first step with a name, description and an example query that will select all data from a given dimension called default - SELECT * FROM default;

Add step
        on transformation job

Also, create a second step for this guide purpose with another query like this SELECT COUNT([entity-id]) AS QuantityEntities FROM {{ ref('step_1') }}; that will return how many entities are stored on the result data set retrieved on Step 1.

Steps
        created on transformation job

Beside each step you will have the following features:

  • - Edit this step
  • - Run and returns a result preview for this step
  • or - Change the order of your steps
  • - Delete this step

Notice that you can also click on Preview Transformation Job Result to see how is the result of all steps you created when they are executed following the order you defined.

Preview
        of the transformation job result

When everything is ready click on Save & Continue.

On the third step you'll configure your destination configuration that represents where the result from your transformation job will be used or stored. Actually there are three different destinations for your transformed data:

  • SlicingDice dimension: Your data will be sent to an existing or a new SlicingDice dimension, as you prefer. You'll be able to query it and use it like any other dimension you have.
  • Reporting & Dashboards tool: Your data will be sent to SlicingDice's Reporting & Dashboards tool as a dataset ready to be used to create powerful charts and dashboards. Notice that is mandatory to send your transformed data to a SlicingDice dimension when choosing the Reporting & Dashboards tool destination
  • Machine Learning: Your data will be sent to SlicingDice's Data Intelligence Module as a dataset ready to be analyzed by our Machine Learning tool.
Step 3
        transformation jobs

Choose the destinations you want to send your transformed data and then click on Save & Continue.

On the next step you can confirm all the configuration you set for your transformation job. Check if everything is fine and then click on Submit

If your transformation job was configured as manual you can execute it on the Transformation Jobs section clicking on the Play button. If it was set as automatic it'll run automatically according to the job frequency you defined.

Step 3
        transformation jobs