The Data Mart Editor allows you to add or edit your SQL code, change the Data Mart name, or delete it. In this article, we will describe all the features of the Data Mart Editor.
To open it, go to the menu and select Extensions > OWOX: Reports, Charts & Pivots > Add a new report or Edit current report as shown below:
Firstly, in the opened Extension panel, specify your Project.
Secondly, to edit your Data Mart, click the "Edit" icon from the right side of the Data Mart title to open the Editor window.
Here you will see the Data Mart Editor window:
Data Mart title
To change the Data Mart title use text input as shown below:
Revision history
Each change to the SQL code will be saved as a new version of the Data Mart. You can view all versions in the 'Revision History' selector.
Choose one of the previous versions to view SQL code:
You can only view and copy the SQL code of earlier versions.
Click the 'Back to current version' button to see the current version of the Data Mart code.
SQL code editor with autosuggestions
In the Data Mart editor, you could press Ctrl+Space (or Command (⌘)+Space) to get auto-suggestions in your code.
It will provide hints for:
- for projects, datasets, and tables, as in [my-project:dataset1.tableName]
- projects (after
[character) - datasets (after
[and:characters) - tables (after
.character)
- projects (after
- table fields from tables referenced with brackets
- BigQuery clauses, functions, and operators. Learn more
The 'Limit' Option with Default Value for All New Reports
The default value for the 'Limit' Option in the Data Mart Editor allows you to set a default value for the 'LIMIT' clause without hardcoding it into the SQL code. It works similarly to the SQL 'LIMIT' clause but allows you to easily change the limit value in the report without editing the SQL code.
Adjusting the 'Limit' Option From the Data Mart Config
When this option is enabled, the default value (e.g., 200,000 rows) will be used in each new report based on the Data Mart config. After running the Data Mart, you will receive a report containing the maximum number of rows defined in the option from the Data Mart config.
We recommend not hardcoding the SQL 'LIMIT' clause directly in your SQL code.
Instead, check the 'Limit' option and set the default value on your own for greater flexibility.
Adjusting the 'Limit' Value From the Report
Additionally, this number of rows will be displayed in the Extension panel in the 'Limit' section. This allows your colleagues and other managers to use your Data Mart and adjust the 'Limit' option for their purposes without opening the Data Mart Editor window.
In this case, the changed value for the 'Limit' option will be saved for this report only. Other reports based on the same Data Mart will have their own values for the 'Limit' option.
Use cases
When you click the 'Run' button in the report, here's what happens:
- The SQL code is executed in BigQuery to get the initial result.
- If the Data Mart 'Limit' option is enabled and the Report 'Limit' value hasn't been customized, the Data Mart 'Limit' option is applied to this result.
- If the Report 'Limit' value has been customized in the Extension panel, that value is applied instead.
Let's see examples in the table below:
| Use cases | SQL 'LIMIT' clause | Data Mart 'Limit' option | Report 'Limit' value | Maximum number of rows in the resulting report |
|---|---|---|---|---|
| Use only the SQL 'LIMIT' clause | 100 | — | — | 100 rows |
| Use only the Data Mart 'Limit' option [RECOMENDED] | — | 200 | — | 200 rows |
| Use only the Report 'Linit' value | — | — | 100 | 100 rows |
| Use the SQL clause and the Data Mart option | 100 | 50 | — | 50 rows |
| Use the Data Mart 'Limit' option and overwrite it with the Report 'Linit' value [RECOMENDED] | — | 200 | 500 | 500 rows |
| All Limits have been applied and the SQL clause is the largest | 100 | 50 | 25 | 25 rows |
| All Limits have been applied and the SQL clause is the smallest | 25 | 50 | 100 | 25 rows |
| None applied | — | — | — | Number of rows from the initial result from BigQuery |
These examples show you two key rules:
- The customized Report 'Limit' value will always overwrite the Data Mart 'Limit' option, whether increasing or decreasing the limit.
- If the SQL 'LIMIT' clause has the smallest value, you cannot increase it with the Data Mart or Report 'Limit' values. However, you can decrease it.
0 Comments