During development, it is often useful to be able to see what data is present in different tables. For instance, you might want to check that a transformation works as intended. Discovery Hub provides two different ways of viewing the content of a table.
The preview table feature gives you a basic overview of the content of a table. To preview the content of a table, follow the step below.
- Right click a table on a data warehouse or staging database and click Preview Table. The preview table window opens.
- You have a number of options for previewing the data:
- In Select Top, type the number of rows you want to fetch and display. Click Apply Top to apply the setting. Please notice that the select top is applied before any sorting of data.
- In the Date format list, click the date format you want to use for dates in the data. Click Apply Format to apply the settings.
- In the Instance list, click the instance of the table you want to preview.
- Click Count Rows to
- When you are done, click Close to close the window.
The Query Tool is a powerful supplement to the preview table feature that gives you more flexibility in exploring the content of a table. You can execute any SQL query to see the data you want to see the way you want to see it.
Opening the Query Tool
You can open the Query Tool in three different ways.
- >Right click a table, click Preview Table and click Query Tool in the Preview Table window.
- >Right click a table, click Advanced and click Query Tool.
- >Click table and press F8 on your keyboard.
The Query Tool opens with a query that selects the content of the currently selected table, similar to the query that is executed to get the content for the preview table window.
To execute a query, follow the steps below.
- >Open the Query Tool using one of the options described above.
- >If available, choose the Source and Account you want to query. Account is only displayed when using an adapter with multiple possible accounts.
- >Enter your query in the top text box of the Query Tool window. You can enter multiple queries that will be executed in sequence by Discovery Hub.
Adjust Max no. of rows to the maximum number of rows you want to have returned.
- >Click Execute
- OR -
Press F5 on your keyboard.
- >If you want to stop the query before it completes, click Stop.
When they query is complete, you can see the result in the Result tab. If you have entered multiple queries, you can select the query result you want to see in Result set. If your query resulted in a message, for example because of a syntax error, the Message tab will display this message.
Drag-and-drop and the Query Tool window
The Query Tool supports drag and drop of tables and fields.
- >You can drag a table or a field into the query to place the table name in the query.
- >If you drag a table to an empty query, the default query is generated. The default query fetches everything in the table.
- >If you drag a table from another source into the window, you will be asked if you want to change connection and generate a default query. If you answer No, the name is simply added to the query.
Sorting and filtering data
The Query Tool enables you to sort and filter the results.
Note: Only the rows returned by the query are available for sorting and filtering in the Results tab. If you want to sort or filter all the rows in a table, the most efficient way is to include the conditions in the query, e.g. by using "order by" or "where" clauses. Fetching thousands of rows and sorting them using the tools provided in the Results tab can be very slow.
To sort the data, follow the steps below.
- >Open the Query Tool and execute a query as described above.
- >In the Result tab, click a column heading to sort the rows on the value in that column. Click again to switch between ascending or descending order.
To apply a filter, follow the steps below.
- Open the Query Tool and execute a query as described above.
- >In the Result tab, click the filter icon besides the name of the column you want to filter on. You have five filtering options:
- >(All) is equal to no filtering.
- >(Custom) opens the Custom Filter window, where you can add conditions for filtering.
Each condition evaluates the value of the row field compared to the possible values in the column. The comparison can be made on Equals, Does not equal, Less than, Less than or equals to, Greater than and Greater than or equal to. Click Add to add an additional filter and click Delete the currently selected condition. You can choose to filter on Any or All conditions, i.e. stringing the conditions together with "or" or "and". Click OK to activate the filter.
- >(Blanks) shows rows where the column in question is blank, i.e. empty.
- >(NonBlanks) shows rows where the column in question is not blank.
- >A specific value. All unique values in the column is listed and can be chosen as a filter.