By adding a custom table, you can manually fill in an SQL query statement to generate a custom table. The custom table editor allows you to directly fill in the name of a stored procedure defined on the data source to call it. Note that the custom table option does not support cross-source operations, so you can only create custom tables based on a single data source.

1. In the lower-left corner of the Dataset Designer, click +Add Custom SQL Table.

   
   

2. In the Custom SQL Table dialog, enter the name of the newly added custom table, select the required data source, enter the SQL query command, and click OK.

   
   

In case you are using MongoDB as the database, do the following;

3. Enter the name of the custom table, select the required data source, select the query type as Text, Native Query, or Stored Procedure enter the SQL query command, and click Next.

4. On clicking Next, the schema editor will appear in the popup with query results where you can select one data type from 6 types of data types including Number, Text, Date, DateTime, Boolean, and Guid for a column. Click OK to finish.

   
   

   Note: You cannot perform the following operations in the schema editor,

   1. Rename the table.

   2. Rename Column.

   3. Add Columns.

   4. Delete Columns

   5. Reorganize Columns.

5. View the custom table under Custom Tables in the Data Sources and Datasets area of the Dataset Designer.

   
   

Limitation of Using MongoDB

The followingare a few limitations of using MongoDB when adding custom SQL tables;

1. The MongoDB query schema specified within the dataset primarily affects the inference of the data type for the custom SQL table. However, the complete schema acquisition for the table still relies on inference from the initial N rows of data. This process cab be illustrated by the list of data sources located in the left panel of the dataset designer.

2. By disregarding the current association with MongoDB in the cached model, you can start by creating a cached dataset using MongoDB. Afterwards, you can simply reference this dataset within the cached model.