.png "Employee"){kind=icon}
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:
- 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
- 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.
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
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
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
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.
Comparative Analysis
In this example, for a presentation variable selection of Category = Photo, it shows the relative difference in revenue for all other categories.
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.
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.
Ratio with running total measure/s
As a business user, I want to calculate a ratio of cumulative revenue and cumulative cost
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.
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.
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.
