Query Builder

Click this icon to view and amend the existing query for the active cube.

Overview

The "Query Builder" generates a diagrammatic representation of your SQL query, showing the things that provide your cube with Dimensions (things to analyse data by) and Facts (the data to analyse). E.g. the relevant tables with their associated joins, the selected fields, and any formatting and calculations applied to those fields.

If the connections between the tables aren’t too many or too complex, the selected tables and joins will be shown in the Query Builder. You can "visually" link/join the tables by selecting the relevant field from one table and dragging the link to the other table/field, then selecting fields from tables to be added into the SQL Query for Info-Explorer.

How to use

Use the Query builder to assist in creating your query. Here you can :

Double click on the table name to add the table to your query
Select the columns required by clicking on the tick box to the left of the column name or type the column name in the expression box
Type in the aggregate function (if required)
Type in an Alias (if required)
Select Sort Type and Sort Order and Group by if required
Test the query by clicking on the data tab to view the results
To join tables, click on the column in the first table and drag to the second table. The join type can be amended by double clicking on the line joining the tables
Type in functions (for example Rtrim) in the Expressions window
When complete, click File > OK and the new cube will be created in the workbook
The SQL query can also be typed and amended in the SQL Pane

Troubleshooting

When using the Query Builder in Orchid Info-Explorer the following error message may be displayed:

'Invalid SELECT Statement. Unexpected token "%" at line x'.

Info-Explorer will still be able to execute the SQL Query and display the result, as long as the syntax of the SQL Query is correct.

What causes the error?

The error occurs if the SQL Query has too many tables, and/or the syntax of the query is too complex, for the Query Builder to generate a diagrammatic representation of the query.

Typical examples occur when the query includes an Info-Explorer parameter, or when using the CAST function in SQL.

Solution: Amend the query in the Cube > Properties

If you receive this error message, you won’t be able to make changes to the SQL Query ‘visually’ using the Query Builder. Instead, any changes will have to be done directly by editing the SQL Query box in the Properties of the cube.

If you want to amend the query (or queries) in your cube without using the in-built Query Builder

Click on Properties
Click on the required Query in the "Cube" node, .e.g. "New cube".
Amend the query and/or copy and paste from SQL Management Studio
Click OK
Click to save your cube

Note: If you have multiple queries, you will have multiple entries under the node "Cube". You need to select each query one at a time.