Custom Query Scripts allow data modelers to write custom queries against data sources. The results of these scripts can then be used as data sets or tables in the model for analysis.
NOTE: Custom scripting is not available on all data source types. See the data source listing to see which source types (connectors) support scripting.
Using Custom Scripts
From the table selection stage in the modeler, click the script icon (orange highlight below).
You will be presented with the following dialog box.
- Provide the name of the table or data set that will be derived from the query
- Enter the relevant script that will be executed against the data source.
Depending on the source, there are different script types that can be used.
- Relational Databases: The script must be ANSI SQL or the SQL that the data source itself supports
- MongoDb: The script must be JSON script
- BI Office: The script must be MDX. DAX can be used against tabular data sources