1. Home
  2. On-premises 5.2
  3. Guides
  4. Tours
  5. Concepts
  6. Tools
  7. References
    1. Apache Spark
    2. Connectors
    3. Built-in Functions
    4. Incorta Connector SDK
    5. Security
    6. Visualizations
      1. Insights over Result Sets
      2. Advanced Map
      3. Aggregated Table
      4. Area
      5. Area Range
      6. Bar
      7. Bubble
      8. Bubble Map
      9. Column
      10. Combination
      11. Combo Dual Axis
      12. Combo
      13. Donut
      14. Dual X-Axis
      15. Heatmap
      16. Funnel
      17. Gauge
      18. KPI
      19. Line
      20. Line Time Series
      21. Listing Table
      22. Map
      23. Organizational
      24. Packed Bubble
      25. Percent Area
      26. Percent Bar
      27. Percent Column
      28. Percent Line
      29. Pie
      30. Pie Donut
      31. Pivot Table
      32. Pyramid
      33. Radial Bar
      34. Rich Text
      35. Sankey
      36. Scatter
      37. Solid Gauge
      38. Spider
      39. Stacked Area
      40. Stacked Bar
      41. Stacked Column
      42. Stacked Column and Line
      43. Stacked Line
      44. Sunburst
      45. Tag Cloud
      46. Time Series
      47. Treemap
      48. Waterfall
    7. Other
  8. Integrations
  9. Data applications
  10. Releases

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.

Note

Starting with the Incorta 5.2.5 release, the insights over result sets is a fully supported General Availability (GA) feature. Before this release, it was a labs feature.

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

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.

Note

The result set feature is available starting the 5.2 Release.

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

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