Level-Based Aggregate in Incorta

[suxinji]

Overview

In business analytics, you may run into the cases that require aggregation at different levels of details. For example, in the product sales business, the price discount may vary depending on geographic area, business unit, product category, sales channel, etc.  To understand the factors that may impact the discount given, you can get basic statistics about discounts at each level and compare them.

Incorta has a variety of level based aggregate solutions.  In the solution we discuss in this article, you can calculate the level-based measure using an Incorta Aggregate Table Insight or create it as an Analyzer Table in the schema.

In another article, we will show you how to create an Incorta Materialized View using Spark SQL to perform the same level-based aggregate.

Solution

Go to Schema -> +New -> Select Incorta Analyzer

Select Aggregated Table

Here is an example. In this case, you want to know different levels of discount given by sales and for each level, what the row count, minimum discount, maximum discount, average discount, and median discount are.

You can use Incorta formulas: count(), groupBy(), min(), max(), average(), and median(). The groupBy() input allows you to define the level for which the aggregate will be calculated.

median(
	RevenueRecognition.RawTransaction.DISCOUNT_RATE, 
	groupBy(
		RevenueRecognition.RawTransaction.DE_AREA, 
		RevenueRecognition.RawTransaction.BU, 
		RevenueRecognition.RawTransaction.SUB_BU, 
		RevenueRecognition.RawTransaction.SALES_COVERAGE_CODE, 
		RevenueRecognition.RawTransaction.ATTRIBUTE_CD, 
		RevenueRecognition.RawTransaction.SOFTWARE_USAGE_CD
	)
)

Note:

The hierarchy levels are implicitly defined via the grouping attributes (dimension) defined for the aggregated table.  The order of columns listed in the groupBy() function has to match the order of the columns mentioned in the grouping attributes but can be a subset of these attributes.