Visualizations → Insights over Result Sets

About an insight over a result set

The insight over a result set feature empowers Analyze users with a robust set of advanced analytic capabilities without requiring accessing the Schema Manager or Business Schema Manager or having much coding experience. Before this feature, building advanced analytic use cases, such as Top N, Top N%, level of detail (LOD) calculations, running totals, and moving average, required data engineers to pre-define the logic within complex materialized views or in business schema views. However, with this feature, Analyze users can easily implement them using the Analyzer. Analyze users first create a result set in the Analyzer (which is a runtime insight query in the form of a Listing table or Aggregated table insight) and then use this result set as a dataset to build the final insight.

This feature enables users to dynamically create complex queries in a two-step process with more control over the query’s order of operations. It enable users to apply conditional formatting with formulas and to create aggregate filters on Pivot tables. Result sets support dashboard prompts, presentation variables, applied filters, and built-in functions including level-based measures.

The following are some of the most significant use cases of this feature:

  • Comparative analysis
  • Percent contribution of rows and columns
  • Running totals
  • Rank analysis
  • Customer order frequency
  • Customer acquisition
Note: The Insight over Result Set is an Incorta Labs feature

The insight over a result set is available as an Incorta Labs feature starting the 2022.4.0 Release. The feature is enabled by default in the Cluster Management Console (CMC). For information about how to enable or disable it, see Additional Considerations.

An Incorta Labs feature is experimental and functionality may produce unexpected results. For this reason, an Incorta Labs feature is not ready for use in a production environment. Incorta Support will investigate issues with an Incorta Labs feature. In a future release, an Incorta Labs feature may be either promoted to a product feature ready for use in a production environment or be deprecated without notice.

As an Analyze user, you can perform the following actions:

For more information on how to use an insight over a result set, refer to the example of a Pivot table insight over an Aggregated table result set.

For examples of different business use cases, refer to the Insights over Result Sets article on the Incorta Community site.


Create a result set

Here are the steps to create a result set using a Listing Table or Aggregated Table visualization:

  • Sign in to Incorta Analytics.
  • In the Navigation bar, select the Content tab.
  • Open an existing dashboard or create a new one by selecting + New > Add Dashboard.
  • In the Action bar, select + (add icon), or select + Add Insight.
  • In the Analyzer, in the Data panel, select Manage Dataset.
  • In the Manage Dataset panel, in Result Set, select + Add New Result Set.
  • The Add Result Set window opens in the Analyzer to create and materialize a result set (subquery).
  • In the Insight panel, select V, and in Tables, select the Listing Table or Aggregated Table visualization.
  • From the Data panel, drag and drop various columns to various trays in the Insight panel:
    • Grouping Dimension
    • Measure
    • Individual Filter
    • Aggregate Filter
    • Distinct Filter
  • Configure the pill properties using the Properties panel.
  • Configure various insight properties using the Settings panel.
  • Select Save.
Note

You can only use columns from the result set in your final insight. For example, you cannot use a column from a result set with a column from a physical schema or business schema view.

Settings panel

With the Settings panel, you can configure additional properties for the Listing Table or Aggregated Table insight. To open the Settings panel, in the Add Result Set window, select Settings (gear icon) in the Action bar.

PropertyControlDescription
General → Page Sizespin boxEnter the number of rows per page
General → Max Rows Limitspin boxEnter the maximum number of rows to include in the table
General → Auto RefreshtoggleEnable this property to automatically refresh the insight preview while editing it in the Analyzer.

A tenant configuration in the Cluster Management Console (CMC) can globally enable or disable this feature at the tenant level.
General → Merge RowstoggleDisable the Merge Rows property in your result set insight.

This property is only available for an Aggregated Table insight.
General → Data SamplingtoggleEnabled by default. This property allows using a sample of the insight query result set, rather than the whole query result set to create the insight.

Disable it to use the whole query data list, which may impact the insight rendering time.
General → Inherit FilterstoggleEnable this property to apply existing dashboard prompts to the result set of your subquery.
Advanced → Max Groupsspin boxSet the maximum number of groups that cannot be exceeded in the Insight
Advanced → Join Measuresdrop down listSelect Yes to calculate the group-by function across joined tables.

Edit a result set

Here are the steps to edit an existing result set:

  • In the Analyzer, in the Data panel, select Manage Dataset.
  • In the Manage Dataset panel, in Result Set, select Edit (Pen icon) for a result set.
  • The Add Result Set window opens in the Analyzer to edit your result set.
  • Select Save.

Build an insight over a result set

Some visualizations support using result sets as the insight data set, while others do not support using them. For a list of the visualizations that support result sets, see Feature Limitations.

  • In the Analyzer, select Listing Table or V in the Insight panel, and then select a visualization.
  • In the Data panel, select Manage Dataset.
  • In the Manage Data Sets panel, in Result Set, select Insight Datasets, and then close the panel.
  • From the Data panel, drag and drop the required columns to the respective tray.
  • Name the insight.
  • Select Save.

Example of a Pivot table insight over an Aggregated table result set

In this example, you will create an aggregate filter in a Pivot table insight that shows the ratio of revenues to cost of goods sold. The example uses the SALES schema that comes with the Sample Data.

Note

In the CMC, you can create a tenant that includes Sample Data. The Sample Data includes the SALES schema.

Add an Aggregated table result set

  • In the Action bar, select + (add icon), or select + Add Insight.
  • In the Data panel, select Manage Dataset.
  • In the Manage Data Sets panel, in Result Set, select + Add New Result Set.
  • The Add Result Set window opens in the Analyzer to create and materialize a result set (subquery).
  • In the Insight panel, select Aggregated Table.
  • In the Data panel, select Manage Dataset.
  • In the Manage Data Sets panel, in Tables, select the SALES schema. Close the panel.
  • From the Data panel, drag and drop the following columns to the respective tray:
    • Year and Quarter from the Sales table to the Grouping dimension tray.
    • Category and Prod. Subcategory from the Products table to the Grouping dimension tray.
    • Revenue from the Sales table to the Measure tray.
  • From the Data panel, drag and drop Add Formula to the Measure tray. The Formula Builder automatically opens:
    • In the Formula Editor, enter the following formula: sum(SALES.SALES.AMOUNT_SOLD)/sum(SALES.SALES.COST_OF_GOODS) > 1.8
    • Select Validate & Save
  • In the New Formula Properties panel, for Column Label, enter Revenue to Cost Ratio.
  • Select Save.
Note

You can create a result set using Listing and Aggregated tables only.

Build a Pivot table insight over the Aggregated table result set

  • In the Analyzer, select Listing Table or V in the Insight panel.
  • In Tables, select Pivot Table.
  • In the Data panel, select Manage Dataset.
  • In the Manage Data Sets panel, in Result Set, select Insight Datasets, and then close the panel.
  • From the Data panel, drag and drop the following columns to the respective tray:
    • Category and Prod. Subcategory to the Row tray.
    • Year and Quarter from the Sales table to the Column tray.
    • Revenue from the Sales table to the Measure tray.
  • Name the insight Aggregate filter in a Pivot table.
  • In the Action bar, select Save.

Additional Considerations

Feature Limitations

Insights built over result sets still have some limitations.

  • A result set is limited to its insight. You cannot use the same result set for multiple insights.
  • Searching is not supported. When searching the dashboard by entering a value from the insight built over a result set, no search results will appear. However, selecting Find all records containing will filter the insight accordingly.
  • When creating an insight over a result set, you can select columns only from the result set. You cannot combine columns from the result set with columns from other objects. Columns from other objects will display #ERROR values.
  • Dynamic Hierarchy is not supported in insights built over a result set.
  • A result set cannot reference columns in Business Schema SQL Views
  • Not all visualizations support using a result set as the insight data set. The following is a list of the visualizations that support result sets:
    • Listing Table
    • Aggregated Table
    • Pivot Table
    • Column
    • Stacked Column
    • Percent Column
    • Bar
    • Stacked Bar
    • Percent Bar
    • Area
    • Stacked Area
    • Percent Area
    • Line
    • Stacked Line
    • Percent Line
    • Stacked Column and Line
    • Donut
    • Line Time Series
    • Time Series
    • Combo Dual Axis
    • Waterfall
    • Advanced Map
    • Treemap
    • Bubble
    • KPI

Steps to enable or disable the Insight over Result Set in the CMC

Here are the steps to enable or disable this option as the default tenant configuration in the CMC:

  • Sign in to the CMC as the CMC Administrator.
  • In the Navigation bar, select Clusters.
  • In the cluster list, select a Cluster name.
  • In the canvas tabs, select Cluster Configurations.
  • In the panel tabs, select Default Tenant Configurations.
  • In the left pane, select Incorta Labs.
  • In the right pane, turn the Enable Insights over Insights toggle on to enable the feature or turn it off to disable it.
  • Select Save.

Here are the steps to enable this option for a specific tenant configuration in the CMC:

  • Sign in to the CMC as the CMC Administrator.
  • In the Navigation bar, select Clusters.
  • In the cluster list, select a Cluster name.
  • In the canvas tabs, select the Tenants tab.
  • In the Tenant list, for the given tenant, select Configure.
  • In the left pane, select Incorta Labs.
  • In the right pane, turn the Enable Insights over Insights toggle on to enable the feature or turn it off to disable it.
  • Select Save.