cancel
Showing results for 
Search instead for 
Did you mean: 
awarrier
Employee
Employee

A key area of product focus at Incorta is to provide powerful analytical capabilities while retaining the simplicity of creating insights on a dashboard. The Insight over Result set is one such feature designed to empower business users to leverage the Incorta Analyzer to build powerful two-step queries to solve some common business problems.

  • Percent contribution of rows in pivot tables
  • Comparative analysis
  • Running totals with formulas
  • Formulas and filters based on rank
  • SUM of MAX or AVG on top of MAX
  • Customer order frequency
  • Customer acquisition

Many of these can be built today using materialized views. However, doing it at load time makes it static and does not support interactivity with dashboard filters. They also require data engineers to build and maintain them. SQL and Analyzer business views are alternatives, but this is also not for business users as they do not have edit access to business views.

What is Insight over Result Set?

Insight over Result Set provides a way for an Analyze user to perform a two-step analytical process. This typically includes:

  1. Result Set: Creating a flattened list or aggregate result set at the desired level of aggregation. This is akin to creating an insight on a dashboard today
  2. Insight: Next, use this result set to apply post processing on top of it. The post processing may include applying formulas on top or rank or running total, materializing a formula before using it in pivot tables or conditional formatting etc.  

For the more technical users, the scope of this feature encompasses everything you can do in SQL with a subquery in the FROM clause, but using the Analyzer interface to create the result set (subquery) and then add to this result set with formulas, dashboard filters, presentation variables etc.

A key thing to note is that this result set is a standalone entity and does not join with other business views or schema tables. This enhancement will be considered for a future release.

Common business problems this feature addresses 

Pivot tables with % of total

As a business user, I want to create an insight that shows each subcategory's revenue contribution to the total category in a pivot table. I want to pivot the data by Year and Month to see the trend in % contribution.

You start by creating a new insight and navigating to the Analyzer UI. You now have a new option under ‘Manage Dataset’. In addition to Views and Tables, you have another option called Result Set. 

awarrier_0-1649887244758.png

 

 

Select the Result Set tab and click on the + Add New Result Set box

This will open up a new Analyzer window within the Create Insight flow called Add Result Set

awarrier_1-1649887244780.png

 

Create a result set, just like you create an insight. In this case, we create an aggregate table with a level based measure for the % contribution

awarrier_2-1649887244781.png

 


Next, save the result set. You will now be back in the Edit Insight pane of the Analyzer. You can select the result set that you just created under the Manage Dataset -> Result Set section

awarrier_3-1649887244788.png

 


 

The result set now shows up in the left panel of the insight. You can now select any column from this result set and create your final insight.

In our example, we use the result set with the % contribution column to create a pivot table as the final insight.

awarrier_4-1649887244789.png

 

Comparative Analysis

How does the revenue for one category compare with other categories in this grouping dimension cohort?
 
In this example, for a presentation variable selection of Category = Photo, it shows the relative difference in revenue for all other categories.
awarrier_5-1649887244778.png

 

To achieve this, we first create a result set as shown below. Note the presentation variable being referenced in the result set and passed through based on the selection on the dashboard.

awarrier_6-1649887244797.png

 

With this result set defined, next we create the insight and use the level based measure with an empty group by (). This helps us determine the relative different between a given category dimension and the selected presentation variable category dimension.

awarrier_7-1649887244791.png

 

 

Ratio with running total measure/s

As a business user, I want to calculate a ratio of cumulative revenue and cumulative cost

awarrier_8-1649887244785.png

 

To do this, you can first create a result set with the cumulative revenue and cumulative cost as running totals. Next, create an insight with a formula that calculates the ratio.

 

Year over Year analysis

As a business user, I want to calculate the year over year trend for each month, based on a chosen year via a presentation variable.

awarrier_9-1649887244787.png

 

The real value here is that the insight has a filter that respects the presentation variable for the comparison year. In the example above where Year = 2010.

However, this predicate is not enforced within the result set. So, the result set computes the Revenue, Revenue YTD and YoY formula for all the Years.

awarrier_10-1649887244796.png

 

awarrier_11-1649887244794.png

 

Having the ability to inherit filters is a nice capability in the result set. By default it is enabled, but it can be disabled for business requirements like the example above.

Hope these sample business problems help showcase the art of the possible with this feature. 

Contributors
Version history
Last update:
‎04-13-2022 03:02 PM
Updated by: