The procedure for creating a cached model for a dashboard is composed of four steps - choose the data source, select and edit tables, select and edit columns, and load the data.

In a cached model, you use data from the existing data sources available on the server. The data from these data sources can be added one by one to the data model. Also, note that only one user can edit a cached model at a time.

To Create a Cached Model

Follow the below steps to create a cached model for a dashboard -

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

   
   

   
   

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

   
   

   
   

3. Enter the name for the cached model in the Input Cached Model Name dialog box, and click OK.

   
   

   
   

   Note: The model is created and saved as soon as you click OK after providing the model name.

4. A Data Model Designer appears. Currently, there is no data added to the data model.

   
   

   
   

5. To add data from a data source, click the Get Data button on top of the designer. You can also add data from multiple data sources, one after another, using the following steps.

   
   

   
   

6. In the Get Data window that appears, choose a data source from the list.

   The list shows the available data sources available on the server.

   
   

   
   

7. Click the Next button to proceed further.

8. Select the tables and views you want to include in the cached model. You can choose to display tables or views in the list by clicking the Tables or Views button, respectively.

   
   

   
   

   
   

   You can also create custom tables using SQL expressions if the existing tables do not meet your requirements. Click the Add Table by SQL button and enter the SQL expression.

   
   

   
   

   
   

   Click the Validate button to verify the SQL expression. Once validated, click the Add Table button to return to the Get Data wizard. If you want to add more tables using SQL expressions, click the Save and Continue Adding button.

   Note: Use the Edit button in the Select and Edit Tables window to reconfigure the custom table.

   
   

9. If the selected data source uses table/column mapping, you can see the mapped table names in the Alias column. You can add aliases or modify the existing aliases for the table names.

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

11. Click the Next button to proceed further.

12. Select the columns that you want to include in the cached model, and change the data types for the columns.

    
    

    
    

    
    

    You can also create custom columns using SQL expressions if the existing columns do not meet your requirements. Click the Add Column By Sql Expression button and enter the following details -

    • Table Name - The name of the table where you want to add the column.

    • Column Name - The name of the column.

    • Type - The data type of the column. It can be set to Number, Text, Boolean, Date, and DateTime. If you do not specify the type, the data type is automatically determined based on the SQL expression.

    • SQL Expression - A valid SQL expression for the column.

    Note: You cannot add a new column to a custom table.

    
    

    
    

    Click the Validate button to verify the SQL expression. Once validated, click the Add Column button to return to the Get Data wizard. If you want to add more columns using SQL expressions, click the Save and Continue Adding button.

    Note: Use the Edit button in the Select and Edit Columns window to reconfigure the custom table column.
    

13. If the selected data source uses table/column mapping, you can see the mapped table names in the Alias column. You can add aliases or modify the existing aliases for the column names.

14. Click the OK button to proceed further.

    
    

    Now, the data from the data source will begin to load. You can see the loading progress for each table under the Progress header.

    
    

    
    

    
    

    Once the data is completely loaded, you will see the load status for each table under the Status header, and the number of rows loaded in each table under the Total Number of Rows header.

    
    

    
    

    Tip: If you face a failure during the loading of data, a Reload button will appear next to the failed entities. Click the Reload button to load the data again for the failed entities, until they get successfully loaded.

15. Now, click the Done button.

    Based on the defined settings, a cached model is created as shown below.

    
    

    The Data Model Designer displays the aliases for the entities and attributes names defined while editing tables and columns in the Get Data dialog box.

    
    

    
    

    
    

    Use the Reload button in the designer to manually refresh the cached data for the selected entities in the model.

    
    

    
    

To Save a Cached Model

Follow the below steps to save a cached model in Wyn Enterprise.

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

   
   

   
   

2. In the Update dialog box, enter the comment (if any) for the cached model as shown.

   
   

   
   

3. Then, click the Save button to save the cached model to the latest version.

   The saved cached model will appear in the Data Model tab of Document Types.