Wyn Enterprise User Guide

Wyn Enterprise: Create a Direct Query Model

The procedure for creating a direct query model for a dashboard is typically composed of four steps - select the model type, specify the data source, configure data source connection details, and select the desired tables and views.

In Wyn Enterprise, you can create a direct query model by manually filling in the configuration details in the designer or by re-using an existing data source available on the server. This article describes the detailed steps to create a direct query model using both these methods.

To Create a Direct Query Model Using an Embedded Data Source

An embedded data source is defined in the direct query model itself and is used only by that model. Use the below steps to create a direct query model with an embedded data source.

  1. On the Resource Portal, navigate to the Create tab and then select Prepare Data under the Dashboard section.

    Create a Direct Query Model

  2. To create a new direct query model, click the Create button.

    Create Direct Query Model

  3. In the Create Data Model window that appears, set the Data Source Type to Embedded Data Source.

    Set the Data Source Type for Direct Query Model

  4. Select the Data Provider you want to use for the direct query model. The supported data providers are Oracle, SQL Server, MySQL, Postgres, MariaDB, IBM DB2, and BigQuery.

    Select Data Provider for Direct Query Model

  5. Based on the chosen data provider, the configuration details for the data source can vary. Let's say, you chose the SQL Server provider as shown below.
    You can connect to the data source by either entering the configuration details in the respective fields or directly through the connection string.

    Enter data source configuration details

  6. To verify the data source connection, use the Test Connection button, and then click the Next button to proceed further.

  7. Select the tables and views you want to include in the direct query model. You can choose to display tables or views in the list by clicking the Tables or Views button, respectively. You can use the following options as well -

    • Include current relations - This option is used to include or exclude the existing relations in the selected tables and views.
    • Include related tables - This option is used to include or exclude the related tables in the selected tables and views. It is disabled in case you have not included current relations in the selected tables and views.

    Select tables and views

  8. Now, click the Done button.
    Based on the defined settings, a direct query model is created as shown below.

    Direct Query Model Designer

To Create a Direct Query Model Using a Shared Data Source

A shared data source is the one that already exists on the portal and can be reused to create different direct query models. It is best suitable in scenarios where you want to use an existing data source that takes advantage of the data pulling capabilities provided by a data model.

  1. On the Resource Portal, navigate to the Create tab and then select Prepare Data for the dashboard.

    Create a Direct Query Model

  2. Click the Create button to create a direct query model for the dashboard.

    Create Direct Query Model

  3. In the Create Data Model window that appears, set the Data Source Type to Shared Data Source.

    Use shared data source for direct query model

  4. Select the Data Source you want to use for the direct query model. The list displays the existing data sources available on the server that supports direct query - SQL Server, MySQL, Oracle, Postgres, MariaDB, IBM DB2, and Google BigQuery, Web JSON, Snowflake, and ClickHouse.

    Shared Data Source

    Note: The list displays only those data sources on which you have sufficient permissions i.e. Execute/Create, Read/Write, and Full Control.

  5. Click the Next button to proceed further.

  6. Select the tables and views you want to include in the direct query model. You can limit the currently displayed tables or views in the list by clicking the Tables or Views button, respectively.

    Select tables and views

  7. Uncheck the Include current relations option to exclude the existing relations in the selected tables and views. You can uncheck the Include related tables option to exclude the related tables in the selected tables and views.

  8. Now, click the Done button.
    Based on the defined settings, a direct query model is created as shown below.

    The Data Model Designer displays the mapped name of entities and attributes if the chosen data source uses table/column mapping.

    Data Model Designer

To Save Direct Query Model

  1. Click the Save button to use the direct query model for binding data in dashboards.

    Save Direct Query Model

  2. In the Save As dialog box, enter the direct query model name and comment (if any) as shown.

    Save As Dialog box

  3. Then, click the Save button.
    The saved direct query model will appear in the Data Model tab of Document Types.

    View saved direct query model on the Resource Portal