In this article, you will find step-by-step instructions about how to create a Transformation with a set of Operations and how to run it in the OWOX BI interface.
Important: Please make sure you meet the minimum requirements with OWOX BI Transformation functionality.
Step 1. Create a new Transformation
1.1. Go to the Workspace page. While you do not have any Pipeline, Streaming, or Transformation yet, then you will see the Welcome screen. Click on the “New transformation” button.
1.2. Otherwise, click the "+ New..." button in the top panel over the diagram if you have one or more Pipeline, Streaming, or Transformation. Click on the “Transformation” menu item.
1.3. Create a new blank or template-based Transformation. Choose one option from the list in the sidebar:
While browsing the Templates gallery, you can read the detailed template description and see a list of Operations.
After selecting an option, click on the “Create & Setup” button.
Step 2. Create Operations
2.1. On the Transformation page, go to the Code Editor tab. You will see that the new Transformation already has the first Operation with the SQL code "SELECT 1;".
How to configure a Transformation template
If you have created a Transformation from a template, you need to enter the correct values for the variables according to the instructions provided. You can find these instructions in the section that describes the templates.
2.2. Write your custom SQL code in the editor. And create multiple Operations in one Transformation. It will help break down the data transformation process into several logical steps. You can easily change the order of the Operations in the Transformation.
2.3. Validate your code. The entered SQL code undergoes validation to ensure that the query can be executed when the Transformation is run. If there are any errors during the validation process, you will see them in the top toolbar above the code. In this article, you can see some examples of common errors that can occur during SQL query validation.
When the code validation is successfully passed, you will see a green mark that says "Valid SQL code". You can proceed to the next step of this instruction.
2.4. (Optional) Specify a destination table for the Operation.
When you run the Transformation, the OWOX BI algorithm will save the result of executing SQL code into the table in Google BigQuery. You can learn more about setting up a destination table in this article.
2.5. (Optional) Add variables to the Transformation and use them in the SQL code.
Variables allow you to set values applied in multiple Operations without changing the SQL code before each Transformation run. Using variables greatly speeds up routine work. Read more about using variables in this article.
2.6. Preview your code. Go to the 'Preview' tab to see the compiled SQL query that will be executed when the Transformation is run. Read more about the 'Preview' tab in this article.
2.7. Save your changes.
Step 3. Share used datasets with OWOX BI
3.1. Go to the 'Used datasets' tab to view the list of used datasets.
3.2. If a Transformation uses data from BigQuery datasets that are not accessible to OWOX BI, the Transformation cannot be successfully executed. To run the Transformation and obtain the execution results for each Operation, you need to grant access to all the used datasets. To do this, click on the 'Share used datasets' button. You can read more details in this article.
3.3. (Optional) If you use a BigQuery table with data from Google Sheet documents then check this article.
Step 4. Run the Transformation manually
Click on the “Run” button to start the sequential execution of the Operations in the Transformation.
Important:Run is only available for Transformations that have no unsaved changes. If you have changed the code or made other changes to the Transformation, be sure to save those changes.
Also, you can stop running Transformation during execution. It's useful when you have multiple Operations and want to save resources by preventing additional query executions. Read more here.
Step 5. Viewing run history
After running the Transformation, the “Run history” tab opens. Here you find details about the execution of each Operation.
Read more about the Run history tab in this article.
Step 6. Debugging
Write, edit SQL code, and run the Transformation until you solve your task and get the necessary data structure in Google BigQuery.
Step 7. Autoruns with triggers
Once you've finished debugging and you are sure the Transformation works correctly, you can set up a time trigger. With autoruns, you will get up-to-date data for your reports without headaches. Read more about autorun triggers in this article.
Step 8 (optional). Describe your Transformation and Operations
You can specify an 'Owner name', 'Email', and 'Description' fields for your Transformation in the 'Info' tab. And also you can add a description for each Operation.
0 Comments