In SAP BW, there is this concept of "Constant Selection" where you can mark a selection in the Query Designer as constant. This means that navigation and filtering will have no effect on the selection at runtime.
In SAP HANA, there is no feature that directly supports this functionality. We have to model it ourselves. One way to implement it is through "self joins" and "dynamic joins".
One application of "Constant Selection" is when calculating the market share of a product of a company against the same product of other companies. The problem arises when there are additional attributes (e.g. Country) that a user can select that can affect the result.
Let's take this table as an example:
As you can see from this table, companies A, B, and C have products in the US. And only companies A and B have products in Canada and not company C.
If we want to calculate the "market share" of a product of a company, in BW we should make the "sales by company" as the constant because we want to relate the sales of the product of the individual company to the total sales of the product of the whole group of companies.
To implement this in SAP HANA, we need to create a calculation view and use the same table in two "aggregation" nodes. Create the first aggregation node as below:
In the second aggregation node, we will use the same table but will not expose "COMPANY" because we want to total sales by company. When adding the aggregated measure "SALES" from this node, rename it as "TOTAL_SALES".
Now create an inner join between these two aggregation nodes on "COUNTRY" and "PRODUCT". This effectively is a "self join" as we are joining a table to itself.
Lastly, to get the market share percentage, we need to create a "calculated column" in the final aggregation node and name it "MARKET_SHARE" using the formula "SALES" / "TOTAL_SALES".
Now let's run the following query including all attributes "PRODUCT", "COUNTRY", and "COMPANY":
The total sales are of a particular product totaled for all companies in a country which is correct. But now, suppose we want to see the market share of a company's product in North America (both US and Canada).
To do this, we remove "COUNTRY" from the query:
But since Company C does not have products in Canada, the total sales for C is only for US. This is not what we want since we want the total sales to be across both US and Canada.
To solve this problem, we need to change the join between the two aggregation nodes to a "dynamic join".
Now let's rerun the same query without the "COUNTRY".
Problem solved! The total sales is across both US and Canada for all companies. The dynamic join takes into consideration the attributes you use in your query. Since "COUNTRY" is not part of the query, HANA does not execute the join on "COUNTRY" which allows the total sales to remain "constant" across all companies.
In SAP HANA, there is no feature that directly supports this functionality. We have to model it ourselves. One way to implement it is through "self joins" and "dynamic joins".
One application of "Constant Selection" is when calculating the market share of a product of a company against the same product of other companies. The problem arises when there are additional attributes (e.g. Country) that a user can select that can affect the result.
Let's take this table as an example:
As you can see from this table, companies A, B, and C have products in the US. And only companies A and B have products in Canada and not company C.
If we want to calculate the "market share" of a product of a company, in BW we should make the "sales by company" as the constant because we want to relate the sales of the product of the individual company to the total sales of the product of the whole group of companies.
To implement this in SAP HANA, we need to create a calculation view and use the same table in two "aggregation" nodes. Create the first aggregation node as below:
In the second aggregation node, we will use the same table but will not expose "COMPANY" because we want to total sales by company. When adding the aggregated measure "SALES" from this node, rename it as "TOTAL_SALES".
Now create an inner join between these two aggregation nodes on "COUNTRY" and "PRODUCT". This effectively is a "self join" as we are joining a table to itself.
Lastly, to get the market share percentage, we need to create a "calculated column" in the final aggregation node and name it "MARKET_SHARE" using the formula "SALES" / "TOTAL_SALES".
Now let's run the following query including all attributes "PRODUCT", "COUNTRY", and "COMPANY":
The total sales are of a particular product totaled for all companies in a country which is correct. But now, suppose we want to see the market share of a company's product in North America (both US and Canada).
To do this, we remove "COUNTRY" from the query:
But since Company C does not have products in Canada, the total sales for C is only for US. This is not what we want since we want the total sales to be across both US and Canada.
To solve this problem, we need to change the join between the two aggregation nodes to a "dynamic join".
Now let's rerun the same query without the "COUNTRY".
Problem solved! The total sales is across both US and Canada for all companies. The dynamic join takes into consideration the attributes you use in your query. Since "COUNTRY" is not part of the query, HANA does not execute the join on "COUNTRY" which allows the total sales to remain "constant" across all companies.
- SAP Managed Tags:
3 Comments
