Creating a T-SQL Data Source

A T-SQL Data Source provides access to the contents of multiple tables from a single database. One table is selected as the primary source of data, with additional tables added as required. The combined data can be used by a KnowledgeKube model as though it was from a single table.

This type of data source is only compatible with SQL Server data connections, including the one used by Data Designer.

Implementation:

  1. Switch to the Definition panel.
  2. Click to open the Model Data Sources window.
  3. Click or New to open the New Data Source dialog.
  4. Select the A Microsoft SQL Server T-SQL Script radio button.
  5. Click the New Data Source from T-SQL Statements button. This will open the KnowledgeKube Data Source dialog.
  6. Use the first drop-down menu to choose a connection to supply the data.
  7. Use the second drop-down menu to choose a table from the selected database to act as the primary source of data.
  8. Enter a meaningful name for the data source in the text field provided.

If you right-click the name field, you can select Use Table Name to auto-fill the name field with the name of the primary table.

  1. To limit the number of rows your data source will fetch, enter a value in the Maximum Rows field. If you don't want to limit your data source in this way, enter a value of 0 here.
  2. (Optional) To make the data source available to other models in the repository, tick the Shared check box.
  3. Switch to the Data Source Join Tables panel.
  4. Tick the check boxes of any tables you want to join with the primary one.
  5. Switch to the Options panel.
  6. If the target database was created using an application incompatible with the OPTION (RECOMPILE) parameter, untick the Enable SQL OPTION (RECOMPILE) check box. This parameter was added to SQL Server in the 2005 edition, so most modern databases will not require you to make this change.
  7. Click Save.

When editing an existing T-SQL data source, the process will end here. However, when creating a new T-SQL data source, you will be presented with the Data Source Query Editor window, requiring you to complete the remaining steps.

  1. Delete any SELECT statements referring to fields you do not need. This will stop the data source retrieving values from those fields.

At this stage, you can use the Filters tab to add and modify filters associated with the data source.

  1. Modify the JOIN statements at the bottom of the query; the TableName.FieldName combinations should correctly identify the fields in each table that join it to one of the other tables (i.e. they identify the Primary Key to Foreign Key relationship between those tables). For example:

JOIN MySecondTable ON MyFirstTable.PrimaryKey = MySecondTable.ForeignKey

Press Ctrl+Space to access an IntelliSense contextual menu containing a list of available tables and fields. Use this menu to quickly embed these details into your query as required.

  1. Click in the editor toolbar to test your query. The values returned by the test query will appear in the Results tab. If an error occurs, check the contents of the query and try again.
  2. After confirming the query works as intended, click in the toolbar to save changes and close the editor.

If you want to access the query editor for an existing T-SQL data source, either select the data source and click Filters, or double-click the data source.