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 are not having 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. Overview page anatomy
Let's look at the Transformation page anatomy. The page consists of the following sections and elements:
- The top bar shows the scenario title with control buttons and a button to return to the previous screen.
- The tab panel: Run history, Code editor, Autorun triggers, and Info tab.
- The tab content: depending on the selected tab, the content of this tab is displayed in the central part of the screen.
Step 3. Rename Transformaiton (optional)
You can rename the Transformation, just click on the name and write a new one.
Then click outside the title area. Save your changes.
Step 4. Create Operations
4.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
4.2. Write your custom SQL code in the editor, or copy the ready-made code from the Template gallery.
4.3. You can 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.
4.4. Click on the context menu icon to:
- rename Operation (also, you can use double-click by Operation name);
- disable the SQL code querying while the Transformation running (in this case Operation does not remove from the Transformation);
- delete the Operation.
4.5. 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. Learn more about setting up a destination table in this article.
4.6. (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.
4.7. Save your changes.
Step 5. 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.
Step 6. 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 7. Debugging
Write, edit SQL code, and run the Transformation until you solve your task and get the necessary data structure in Google BigQuery.
Step 8. 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 9. Describe your Transformation and Operations (optional)
9.1. Transformations details
Open the 'Info' tab on the Transformation page. Here you can see three inputs: Owner name, Email, and Description. With the 'Owner name' and 'Email' fields, you can easily specify who is responsible for the current transformation, which can be helpful when working with colleagues in a team.
The 'Description' field allows you to provide more information about the transformation you are working on. You can use formatted text, such as bold, italic, underline, headers, bulleted and numbered lists, links to other web pages, code snippets, quotes blocks, and more to make it easier for your teammates to understand your idea and solution.
9.2. Operations details
You can also write another description on the 'Details' tab for Operations. This will give you the ability to provide additional information about the internal SQL code you are working on.