Exercise (optional): Add a Query Table to Data Source

With a data source managed by the ODX Server, there are situations that a Query Table can be handy for faster prototyping, testing, and development. In this example, you may want to see all the tables and schemas in a data source, and so you may create a query table (see below) to be included in your data source tables.

Query Tables should only be used where the built in data filters and selections are not sufficient.

It is recommended that you use the features in the right click menu to Select Columns and Filter Rows. However, There are specific cases where a query table needs to be used, for example, when the incremental time stamp used for incremental loading, can only be obtained using a join.

Steps:

  1. Ensure the ODX Server tab is open, or else open it by double-clicking the ODX in the Solution Explorer pane.
  2. Right-click the data source, in the ODX Server, and select Manage Query Tables.
  3. A pop-up window Query Tables will appear on screen. Select Add in the top left corner of the window.
  4. Default values for the Schema and Name will be populated. Type "INFORMATION_SCHEMA" into the Schema text box and "_all_tables_lookup".
  5. *In the Query textbox, type "SELECT * FROM INFORMATION_SCHEMA.TABLES".
  6. Click Validate Query, to ensure the query will work. Click Ok.
  7. Synchronize the data source.
  8. Open the data source Task and click Select Tables. Add the table "_all_tables_lookup" by checking the box in the left side of the pop-up window and clicking the button Include >.
  9. Execute the data source Task, the query table will be available to add to the data warehouse. We can verify the table is available by opening the Open option in the ODX Server.

*Note: This is just an example query: you may use your own queries in place of this example. With that said, even though the Query Tables functionality can be used broadly, it is best practice to apply business logic and transformations to the data warehouse directly, rather than the ODX data source. When using the Query Table functionality, you should be able to justify why you should apply this query outside of the MDW.

Next