Creating SQL Spreadsheets with SQL Query Builder Wizard

  To create a SQL spreadsheet:

1. Start Web Analysis Studio.

2. Perform one:

   • Select File, then New, then Document.

   • Click .

   • Click , and select Document.

   • In the View Pane Browser tab, right-click a document and select Edit.

   • Press Ctrl+N.

     The Document Designer is displayed.

3. Drag the SQL Spreadsheet icon from the component toolbar to a document panel.

   The Enter SQL Query dialog box is displayed.

4. Select a supported JDBC driver type from the JDBC Driver list.

   You can select from Microsoft SQL Server, IBM DB2, Oracle, JDBC-ODBC Bridge, and Other.

   When the selection is made, a driver name populates the corresponding Driver text box. If Other is selected, you must enter a driver name definition.

   When the selection is made, a sample database connection string syntax populates the corresponding JDBC Connection String text box. When Other is selected, you must enter a JDBC database connection string.

5. Edit the sample database connection string syntax so that it specifies your RDBMS computer name and database name.

6. Enter a user name and password for the relational data source in the corresponding text boxes, or select Username/Password to enter your current logon credentials.

7. Optional: Define query governor parameters for row limits and fetch size.

   The default settings limit the result set to 250 rows, being fetched 100.

   You could define a SQL query by entering standard SQL syntax, in the panel at the bottom of the dialog box, but use the SQL Query Builder as described in these steps.

8. Click Query Builder to display the SQL Query Builder Wizard.

   The SQL Query Builder dialog box features tabs corresponding to aspects of your relational data source:

   • Tables—specifies the relational table(s) to query.

   • Columns—specifies the columns to be returned by the query.

   • Filters—narrows the focus of the SQL query by specifying filter criterion for Where clauses.

   • Groups—organizes relational data on the server before it is returned.

   • Sorting—orders relational data in ascending or descending order per column.

   • Mappings—relates relational columns in the SQL query result set to multidimensional dimensions from an OLAP data source.

9. Click Tables, and select one or more tables.

   To select a table, click the table name in Available Tables, and click one of the arrow buttons. The selection moves to Selected Tables. You can also move selected tables up and down in order.

10. Click Columns, and select one or more columns.

    To select a column, click the column name in Available Columns, and click one of the arrow buttons in the center of the tab. The selection moves to Selected Columns.

11. Optional: To change a column name, or apply a function call to the column, click Add Column Advanced, the middle button on the Columns tab before moving the column to the Selected Columns frame.

    Add Columns Advanced on the Columns tab displays the Select Column Advanced dialog box, enabling you to apply function calls to a selected column (on the Columns tab) or manually enter a RDBMS supported function call. You can select from these functions: None, Average, Count, Maximum, Minimum, or Sum.

    If you would like to change the column name in the SQL spreadsheet display, enter an alternate name in Displayed As.

12. Optional: To define Where clauses for the SQL query, click Filters.

    The Filters tab enables you to define complex Where clauses by selecting parameters from drop-down lists. Select parameters from four columns: Operator, Column, Comparator, and Value.

    To define a Where clause:

    1. Click Add to add a row to the Filters frame.

    2. Click the Column cell in the row and select a column name from the list.

    3. To filter the selected column, click the Comparator cell. You can select from these options:

       • Enter a constant.

       • Right-click and select the value of another column member.

       • Enter a dynamic text label, a variable that is dynamically replaced with a value at runtime. See Dynamic Text Labels.

    4. Click the Value cell and select a filter value. This limits the return set to those rows that satisfy filter criteria.

       You can compose compound statements by adding additional rows, selecting operands from the Operator column and parentheses from the (and) lists.

       Note that Select Distinct changes the selection statement to a SELECT DISTINCT statement.

13. Optional: To define a dynamic SQL spreadsheet, enter dynamic text labels for values on Filters.

    You can enter Dynamic Text Labels that reference data, metadata, fixed references and time format syntaxes. See, Dynamic Text Labels.

14. Optional: To define a GROUP BY clause for the SQL query, click Groups.

15. Optional: To define a SORT clause for the SQL query, click Sorting.

16. Optional: To map columns in your relational data sources to similar OLAP dimensions, click Mappings.

    You can associate columns in your relational data sources with similar dimensions in an OLAP data source, by mapping columns to OLAP dimensions. These mappings, used when drill linking from an OLAP source to a SQL data object, enable the OLAP query to pass its Where clauses to corresponding relational columns.

17. Click OK to query the relational data source and display a SQL spreadsheet.