Sneak: How to use Direct Query to create Custom Tables

The Tips&Tricks Series is produced by the Customer Success Team. It highlights several easy to follow instructions and range from simple tasks to more advanced use cases

How to use Direct Query to create custom Tables

The functionality in the direct query of a data source can be extended to create custom tables from SQL script instead of selecting whole tables or views in the model data flow.
There are many use cases for this feature including migrating reports to Pyramid that where previously based on a complex SQL query bring the data together from several joined tables.
The example here is when a user would like to combine the results sets of 2 or more select statements in a union operation. Let’s take the example that there are independent tables for invoices in each financial year. Table named PA_Invoice_2019 contains all the invoices for financial year 2019. There is an identically structured table with the invoices for 2020 etc.

1. Create a model as in Tip 1, selecting the server and database then click the box “Direct Query Data Source”. Navigate to the “Data Model” tab and the tables element. Click on the “Add a Query” option.

   

2. In the query window complete or paste in the SQL query. Then click on the “test” option and if it passes click “add”. Note you must give the query a “Table Name” in the dialogue box before testing and saving.

   

3. The new table formed from the custom query then becomes available in your data model. Note the custom query can be combined with whole tables selected in the “Data Flow” and it is possible to add multiple custom queries to the same model.

   

   
   To edit or delete the query right click on the table name and select the corresponding option from the list.

If you have questions about these tips (or improvements) please let us know in a comment.

P.S. We will post previous Tip&Tricks as well as all new ones to keep this information active and available to all users.