Manual Select
Enter a SQL or MDX statement to make a selection from a data connector instead of dragging native structures onto the canvas.
1. Input
The input to a Manual Select is a custom query executed against a data connector that you select.
You can write your own query, or paste one obtained from another tool to retrieve the same results.
2. Add the transform
If you already have a blank or existing data cube open, click Manual Select in the toolbar to add it.
Otherwise, you can create a data cube from the main menu and choose Manual Select from the menu that appears, so that this transform will be added automatically when it opens.
Select a data connector in the dialog that appears.
For OLAP databases, you can also choose to expand the data connector and select a cube to query.
Enter the query in the Script Editor window that opens.
To help construct your query, you can also drag data structures such as tables or cubes, or their columns, measures, or hierarchy levels from the Explore window into the Script Editor.
Click Save.
3. Configure
To configure the transform, select it and choose Configure in the toolbar or context menu, or double-click it.
The Timeout (in seconds) can be customized to determine how long to wait before terminating a long-running command.
3.1. Editing the query
You can edit the Query Statement directly from the configuration dialog, or click Edit query in editor to reopen the same Script Editor window shown above that opened when first adding the Manual Select.
3.2. Enable subquery optimization
Subquery optimization allows your query to be incorporated automatically into a larger query that also performs data cube transformations, metric set aggregations, and filtering, so that this work is done in the database to optimize performance when possible.
Uncheck the Enable Subquery Optimization option if your query cannot be enclosed in a SELECT statement, including statements executing a stored procedure or using advanced or database-specific SQL features such as creating temporary tables. You may also need to uncheck this option if your data connector uses an ODBC driver that incorrectly reports its capabilities.
When this option is unchecked, transforms connected to the manual select transform's output will display an icon and tooltip indicating that the data is being brought into memory to perform the transformation rather than using the database's or other external data source's capabilities.
3.3. Placeholders
The Manual Select transform also lets you define a placeholder and use it in your manual queries. A placeholder inserts a parameter into the query, which you can set from outside the transform or optionally the data cube, for example by connecting it to a filter on a dashboard.
See Using manual select placeholders for more details.
3.4. Session schema
Some data providers support an Allow Session Schemas option in version 24.2 and higher that can be enabled when creating or editing a data connector, such as PostgreSQL, MySQL, and Oracle.
When enabled in the data connector you're using, a Session Schema option will be available when configuring the Manual Select transform allowing you to enter the name of a schema to use.
If you want to allow the schema to be changed via a parameter on a dashboard, report, or other view, you can also create a public parameter:
- Click Parameters.
- Click Add parameter in the new dialog that opens.
- Set Parameter Type to Transform Setting and Transform Setting to Session Schema, then click Save.
- Select the Public option in the next set of options that appears before clicking to Save.
4. Output
Open the Data Preview window when the transform is selected to see its output. If the query did not succeed, this may include errors or warnings explaining why.
