[]
        
(Showing Draft Content)

Create a Direct Query Model

The procedure for creating a Direct Query Model for a dashboard is typically composed of four steps -

  1. Select the model type.

  2. Specify the data source.

  3. Configure data source connection details. And,

  4. 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 (version 8.0 or above), 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, views, and materialized views you want to include in the Direct Query Model. You can choose to display tables or views (including materialized 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

Note: With Wyn Enterprise Release Version v7.1, you can view and select materialized views in the Select Tables and Views section when using a PostgreSQL data provider.

  1. 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 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 a data source from the Data Source dropdown for the Direct Query Model. The Data Source dropdown lists the data sources that are available on the server and supports Direct Query Models including the following:

    Shared Data Source

    Note: The data source list displays the data sources with sufficient permissions only, i.e. Execute/Create, Read/Write, and Full Control.

  5. Click the Next button to proceed further.

  6. Select the tables, views, and materialized views to include in the Direct Query Model. You can limit the currently displayed tables or views (including materialized views) in the list by clicking the Tables or Views button, respectively.


    Select tables and views

Note: With Wyn Enterprise Release Version v7.1, you can view and select materialized views from the Select Tables and Views section while using the PostgreSQL data source.

  1. 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.

  2. 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

Add Data Security Filters for Entities

You can configure security filters for entities with multiple conditions in the direct query model designer. To add the data security filters, follow the steps given below.

  1. On the model designer, select the entity for which you need to add the data security filters and click the Data Security tab.


    Click Data Security Tab


    Add Security Filter option is displayed.

    2. Click the Add Security Filter option. The configuration of Groups and Rules is displayed. These configurations have the conditions like AND or OR.


    Data Security Filters

  2. Add the security filters. In this example, we will add 2 Rules in a Group.

  • Click the Add Group button.


    Click Add Group Button


    A group will be added with the option to add more groups or rules as shown below.


    Option to Add More Groups and Rules


    By, default a rule with And condition is added. You can modify the rule. In this example, we have added two rules with And conditions in this group as shown below.


    Add Rules to Group


    Once the data model is saved, you can use this model to create a dashboard and the dashboard will display the filtered data, based on the data security filters applied in this model.


    Dashboard With Filtered Data

  • Group and Rules Conditions

    • You can configure Groups and Rules, using 'AND' or 'OR' conditions.

    • Each Rule contains the following: Left Type, Left Value, Data Type, Operator, Right Type, and Right Value.

    • Left Type value options: Attribute, User Context, Organization Context, Constant.

    • When the Left Type option selected is Context, the Left Value is a single-valued Context Value.

    • Data Type value options: Text, Number, Boolean, Date, DateTime, Guid.

    • When the Left Type option selected is Attribute, then Data Type is not allowed to be modified and the value of Data Type will be the Data Type of the selected Field.

    • The optional Operator values corresponding to the Data Type are listed in the following table.

    Data Type

    Optional Operator

    Text

    =, !=, Is, NotIs, In, NotIn, Contains, NotContains, StartsWith, NotStartsWith, EndsWith, NotEndsWith.

    Number

    =, !=, <, <=, >, >=, Is, NotIs, In, NotIn.

    Boolean

    =, !=, Is, NotIs.

    Date

    =, !=, <, <=, >, >=, Is, NotIs.

    DateTime

    =, !=,

    Guid

    =, !=, Is, NotIs, In, NotIn.

    • Right Type value options: User Context, Organization Context, Constant.

    A few important points to be considered while adding the data security

  • When an Attribute is deleted, the security filter that uses the attribute is automatically deleted.

  • When the Add group button is clicked, an And group is added and a default Rule is added.

  • When all Rules and Groups in a Group are deleted, the Group is deleted and the root Group is not deleted.

  • The depth of the Group is capped at 20.

Synchronize Data Source Changes with the Data Model

If the data source is changed, then the data model designer will notify you and reload/refresh changes and you can continue to edit the changed tables instead of deleting them from the data model. So, you can synchronize the data source changes with the data model using the Compare Datasources and Sync Schema options under the Settings tab.


Sync Schema and Compare Datasources Options


Scenarios that can change the data source are listed below.

  1. Column Changed: Delete a column, Add a new column, Rename a column, and Change the column type.

  2. Table Changed: Delete a table, Rename a table, and Add a new table.

  3. Customized Table (SQL Query): Edit the SQL directly.

Steps for Synchronizing the data source


Change the the data source, for example, if columns are added to the data source, then:

  1. Click the Settings tab and click the Compare Datasources button.


    Click Compare Datasources Button


    An error message is displayed.


    Error on Compare Datasources

  2. Click the Show Details button. The error details will be displayed as shown below.


    Error Details

  3. Click Dismiss All on the error message to close the dialog box.

  4. Click the Sync Schema button to synchronize the data source changes.

If all entities are loaded successfully, the data changes are synchronized with the data model, and the Sync Schema button is disabled.


There is a Sync table data and structure option on the Entity tree node as well.


Sync table data and structure button


Click this button to sync the current entity. If the data is loaded successfully, the sync button of the current entity will be removed. If the current successfully reconfigured entity is the last in the model that can be synchronized, then the batch synchronization button will be disabled.

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