- Getting Started
- Administration Guide
-
User Guide
- An Introduction to Wyn Enterprise
- Document Portal for End Users
- Data Governance and Modeling
- Working with Resources
- Working with Reports
- Working with Dashboards
- View and Manage Documents
- Understanding Wyn Analytical Expressions
- Section 508 Compliance
- Subscribe to RSS Feed for Wyn Builds Site
- Developer Guide
Create a Cached Dataset
The Cached Dataset executes a preset query and caches the data in the server to provide high-performance data analysis and interactivity. While creating a dataset, you need to establish a relationship between the data in two tables that determines how the data appears in the result set.
Reusing Cached Dataset
When creating the cached dataset, you can re-use the existing dataset (only the dataset that has the read permission).
Example: You may create a new dataset A reuse B, B can reuse C, C reuse D and so on.
Even after selecting an existing dataset, you can still edit this dataset like rename, adding the filter rules, adding the calculated field based on this dataset, etc. but you cannot change the field type. You can add the relationship manually. In the information panel, the data sources and datasets reused by the document need to be displayed. Circular re-use is not allowed. The information panel will display the details related to these reused datasets under the Referenced Documents section.
Union operation between tables combines data as:
Union Operation | Description |
|---|---|
Union | Removes any duplicate rows. |
Union All | Retains the duplicate rows. |
Join operation between tables combines data as:
Join Operation | Description |
|---|---|
Inner Join | Matches rows from Table1 with rows in Table2, and allows the user to retrieve records that show a relationship in both the tables. Inner join produces a set of data that matches both Table1 and Table2. |
Left Join | Allows users to select rows that match from both the left and right tables, plus all the rows from the left table (table 1). This means only those rows from table2 that intersect with table1, appear in the result set. |
Right Join | Allows users to select rows that match from both the left and right tables, plus all the rows from the right table (table 2). This means that only those rows from table 1 that intersect with table 2, appear in the result set. |
Full Join | Allows users to select rows that match from both the tables along with the rows that do not match any row in the other table. |
Create Cached Dataset
Follow the below steps to create a cached dataset for a dashboard. These steps are also applicable in case of a cached dataset for a report.
On the Resource Portal, navigate to Create (+) > Prepare Data of the Dashboard section.
To create a cached dataset for the dashboard, click the Create button as shown.
Dataset Designer (Cached Dataset) window is displayed.
3. In the Dataset Designer (Cached Dataset) window, you can select the data sources or datasets. The list displays both the datasets and data sources. You can click the filter icon
to display either datasets or data sources or both.
The data sources and datasets can be distinguished by the symbols as well. Symbol
represents datasets and symbols
represents the data sources.
Selecting a Data source
Here we have selected a data source InsuranceDataSource from the available data sources and clicked OK. You can see the tree view of the selected data source and the data tables and fields contained in the data source.
Define Relationship
Drop a Table (Sheet1) on Relationships. You can view the data in the added table by selecting Preview. You can add single or multiple tables from the data source.
Let us add another table (Sheet2). While you are dropping the second table, set the relationship to either Union or Join. In this case, let the relation be Join. You can see a join condition is already defined. You can modify the condition as required. Note that the condition field type should be correct.
Let us keep the default join condition, which is Inner Join: CSAT of sheet1 matches the CSAT of Sheet 2.
Preview the final data set by selecting Preview.
Edit Fields
Go to the Fields tab. Here, you can modify the field format, include or exclude a field from the final dataset, set data category and aliases, or add descriptions for the fields. Also, you can add SQL Expression, Calculated, or Category fields.
Note: The data category is used to give visualization hints related to the attribute type when generating a smart scenario through the Visualization Wizard. Different data types have different possible values. For example, the string data type supports these values: Geography (Country, State/Province, City, District), Image URL, or Image Binary. The numeric data type supports these values: Currency, Geography (Latitude, Longitude).
Click Preview.
Add Filters
In the Filters tab, you can add data filtering conditions to the source data table or to the final dataset depending on your requirements. Fill in the filter details in the lower section of the Filters tab as shown below. Note that the Value field is disabled since we have not yet added the Parameters. Let us add the parameters that will be later specified in the Value field.
Add Parameters
Fill in the details to add a dataset parameter. The added parameters can be multivalue. You can choose the following as Parameter types:
User Context - choose from the user context (claims) in the Value field and select the data type. This parameter is applicable to the specified tenant.
Organization Context - choose from the organization schema added by admin, in the Value field, and select the data type. This parameter is applicable to the specified group of users.
Constant - select the data type and specify its default value.
Go back to the Filters tab and set the added parameters in the Value fields as shown below.
Enter the parameter values in the Set Preview Parameter dialog. For example, enter 'Female' for GenderParameter and '45' for AgeParameter, and then click OK.
Click Preview to view the dataset.
Add Options
Use this tab to add an option to configure the incremental update setting for a dataset. Click the Add button to add a field as an incremental update setting.
Select the Table Name and Field which need to use the incremental update setting. Note that an incremental update occurs only on a single database.
Click Preview to fetch any incremental data rows in the dataset.
. Finally, click Save, enter the name of the dataset, specify the category and enter a comment, and click Save to save the dataset.
You can view the added dataset in the Categories tab of the Resource Portal.
Note: To add the custom table in the cached dataset, you can refer to the Add Custom Table topic.
Selecting a Dataset
You can re-use the existing datasets to create the new datasets. Here we are selecting an already existing dataset the Customer_Products_Dataset and Store_Details_Dataset from the available list and click OK.
Note: You can only select the datasets to which you have read permission, and you have execute permission on datasets referenced by those datasets.
If the referenced dataset contains unsupported parameters, then a message will be displayed.
Also, circular reuse is not allowed. In this example: There are three datasets as follows:
CacheDataset-A
Reuse-CacheDataset-A: It is reusing the CacheDataset-A dataset.
Resue-CacheDataset-B: It is resuing the Reuse-CacheDataset-A dataset.
So, now if we edit the CacheDataset-A and try to add the Reuse-CacheDataset-A in this, then an error message is displayed as it is a case of circular re-use. You can click the Show Details button to see the error as shown below.
Define Relationship
Drop both the DefaultTables on Relationships. You can view the data in the added table by selecting Preview. 2. Define the relationship to either Union or Join. In this case, we have set the relation to Join. You can see a join condition is already defined. You can modify the condition as required. Note that the condition field type should be correct.
Let us keep the default join condition, which is Inner Join: Customer Id of defaulttable of Customer_Products_Dataset matches Customer Id of defaulttable of Store_Details_Dataset.
Preview the final data set by selecting Preview.
Edit Fields
Go to the Fields tab. Here, you can modify the field format, include or exclude a field from the final dataset, set data categories and aliases, or add descriptions for the fields. Also, you can add SQL Expression, Calculated, or Category fields.
Note: The data category is used to give visualization hints related to the attribute type when generating a smart scenario through the Visualization Wizard. Different data types have different possible values. For example, the string data type supports these values: Geography(Country, State/Province, City, District), Image URL, or Image Binary. The numeric data type supports these values: Currency, Geography (Latitude, Longitude).
While editing the field of the table which is referenced to other datasets, the Field Format and Target Type can be modified. (Only the fields of the Cached dataset designer, supports modifying the Target Type). If the field belongs to the referenced dataset, then you can not create a new field by Sql expression based on this field.
Note: When the dataset is reused, the Analysis Expression Fields of the referenced dataset are not displayed.
Click Preview.
Add Filters
In the Filters tab, you can select a referenced dataset to create a Dataset filter. Fill in the filter details as shown below.
Add Parameters
Fill in the details to add a dataset parameter. The added parameters can be multi-value. You can choose the following as Parameter types:
User Context - choose from the user context (claims) in the Value field and select the data type. This parameter is applicable to the specified tenant.
Organization Context - choose from the organization schema added by admin, in the Value field, and select the data type. This parameter is applicable to the specified group of users.
Constant - select the data type and specify its default value.
Go back to the Filters tab ,and set the added parameters in the Value fields as shown below.
Click Preview and enter the parameter values in the Set Preview Parameter dialog and then click OK.
Click Preview to view the dataset.
Add Options
In cached dataset designer, when a dataset references other datasets, the dataset does not support incremental updates.
Finally, click Save, enter the name of the dataset, specify the category and enter a comment, and click Save to save the dataset.
You can view the added dataset in the Categories tab of the Resource Portal.
Limitations
The cached dataset designer does not support selecting a dataset to create incremental Update.
In the Field tab, you cannot create a new field by Sql expression.