(v24) Data Cubes - Manual Select

Data Cubes - Manual Select

This applies to: Managed Dashboards, Managed Reports

When you create a data cube, you can enter an SQL or MDX statement to make a selection from a data connector instead of dragging native structures onto the canvas.

Create a new data cube with manual select

Important: If you don't have the appropriate application privileges, some options may be hidden from you.

1. Open the data cube work area. You can do this in one of several ways:

   • Select Create Data Cubes from the home page, then Manual Select from the Add/Edit menu

   • Select Data from the Managed Dashboards and Reports main menu, then select Create and Manual Select

   • Right-click Data Cubes in the Explore window, then select New Data Cube from the menu, then Manual Select from the Add/Edit menu

   The Open dialog opens, allowing you to navigate to an appropriate data source.

2. Select a data connector. (For OLAP databases, select the cube under the data connector you want to query.)

   Note: Data structures in your Symphony environment also include data sources discovered by Symphony in Managed Dashboard and Managed Reports data connectors, as well as shared from Data Discovery data connectors.

3. Enter your query in the script editor at the bottom of the data cube work area. Enter it manually, or use shortcuts to build it:

   Note: Inserted comments in your query may cause an issue when subquery optimization is enabled for some data providers.

   • The keyboard shortcut CTRL + SPACE opens a list of available SQL keywords and table names you can add to your query.

   • Drag a structure from the Explore window to the script editor (cube, column, measure, hierarchy level) to add it to your by name.

   • Drag a stored procedure from the Explore window to add it to the query.

4. Optionally, enter a placeholder in your parameterized SQL statement, with each parameter enclosed in dollar sign characters ($). See Using manual select placeholders.

5. Save and configure the transform you created.

Configure your manual select transform and preview your data

1. Select your transform, then Configure from the toolbar. The Manual Select configuration work area opens. Optionally, you can:

   • Update the Name, Description, and select a Color for the transform to display in the work area.

2. Configure the Timeout setting. Timeout (in seconds) controls how long to wait before terminating a long running command. The default is defined in application configuration settings.

3. In the Element Discovery Execution Behavior drop down, you can optionally temporarily change the behavior from Default to Top 1/Limit 1 or Single Row, as appropriate for your data source. This temporary limit allows you to build and test your data cube, then still run it against the full data set when you've made all adjustments needed.

   Note: Oracle managed data sources use ROWNUM = 1 or FETCH FIRST 1 ROWS ONLY behavior.

4. Enable or disable subquery optimization.

   • When enabled, your query can be incorporated automatically into a larger query of data cube transformations, metric set aggregations, and filtering, to optimize performance by doing this work in the database. Your query must be able to be enclosed in a SELECT statement, and not end in a semicolon (;).  

   • Disable if you are executing a stored procedure, performing advanced or database-specific SQL features such as creating temporary tables.

   • Disable if your data connector uses an ODBC driver that incorrectly reports its capabilities.

   When disabled, transforms connected to this manual select's output display an icon to indicate the data is brought into memory to perform the transformation instead of being performed in the database.

5. Open the Data Preview tab to see your output. If your query is unsuccessful, Symphony may display informative errors and warnings.

Note: When you've added, connected, and configured all elements, select Check In from the toolbar to make your data cube available to for use in your project and sharing with other users.

Add a manual select to an existing data cube

1. With your data cube open for editing, select Manual Select from the toolbar. The Open dialog opens, allowing you to navigate to an appropriate data source.

2. Select a data connector. (For OLAP databases, instead select the cube under the data connector you will be querying.)

3. Enter your query in the script editor at the bottom of the data cube work area. Enter it manually, or use shortcuts to build it:

   Note: Inserted comments in your query may cause an issue when subquery optimization is enabled for some data providers.

   • The keyboard shortcut CTRL + SPACE opens a list of available SQL keywords and table names you can add to your query.

   • Drag a structure from the Explore window to the script editor (cube, column, measure, hierarchy level) to add it to your by name.

   • Drag a stored procedure from the Explore window to add it to the query.

4. Optionally, enter a placeholder in your parameterized SQL statement, with each parameter enclosed in dollar sign characters ($). See Using manual select placeholders.

5. Save and configure the transform you created.

For more information, see:

• Data Cubes

• The Managed Dashboards Data Model