Manual Select
This applies to: Managed Dashboards, Managed Reports
Enter a SQL or MDX statement to make a selection from a data connector instead of dragging native structures onto the canvas.
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.
For example, the figure below shows the data retrieved by a simple query:
Add the Transform
If you already have a blank or existing data cube open, select the Manual Select transform from the toolbar.
Alternatively, create a data cube and choose Manual Select from the menu that appears. This transform will be added automatically when the new data cube opens.
Note: You may need to be assigned an application privilege by your administrator before you can access this transform.
Select a data connector. For OLAP databases, you can also expand the data connector and select a cube to query.
Enter the query in the Script Editor.
Note: For some data providers, inserting comments into your query may cause an issue when subquery optimization is enabled.
Use the keyboard shortcut Ctrl + Space for a list of available SQL keywords and table names. You can also drag the native structure (cube, table, column, measure, or hierarchy level) from the Explore window to the Script Editor to add the respective unique name.
Select Save.
Note: You can also drag a stored procedure from the Explore window onto the canvas instead of typing it into a query.
Note: Subquery optimization is enabled by default, which can mean inserting comments into your query, ending your query with a semicolon, or certain statements such as executing a stored procedure may result in an error unless the option is disabled. You can simply drag a Stored Procedure from the Explore window onto the data cube canvas instead of using a Manual Select.
Note: MDX queries must return a cellset or multidimensional data set (DRILLTHROUGH is not supported).
Configure
To configure the transform, select it and choose Configure in the toolbar.
The Timeout (in seconds) can be customized to determine how long to wait before terminating a long-running command.
Note: The Default Query Timeoutapplication configuration setting determines the initial timeout setting.
Edit the Query
Edit the Query Statement directly from the configuration dialog, or click Edit query in editor to reopen the Script Editor window.
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.
Your query must be able to be enclosed in a SELECT statement when this optimization is enabled. For statements such as executing a stored procedure, or advanced or database-specific SQL features such as creating temporary tables, uncheck the Enable Subquery Optimization option in the transform configuration dialog. 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 indicating that the data is being brought into memory in Symphony to perform the transformation rather than using the database.
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.
Session Schema
Some data providers support an Allow Session Schemas option in Symphony v24.2 and later. If supported, enable 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 is available when you configure the Manual Select transform. Use this 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:
Select Parameters.
Select Add parameter in the new dialog that opens.
Set Parameter Type to Transform Setting and Transform Setting to Session Schema, then select Save.
Select the Public option in the next set of options that appears, then Save.
Output
Open the Data Preview window to see the output. If the query did not succeed, this may include errors or warnings explaining why.
For more information, see:
Comments
0 comments
Please sign in to leave a comment.