Top N Performers in SAP Design Studio 1.2

[madireddy_rahulreddy]

Hello experts,

We are facing a scenario where we need to show the top 20 performers in a crosstab in SAP Design Studio 1.2 . Design Studio is consuming BW queries which are on top of HANA models. There are about a million records. Currently BW is sorting and filtering out the top 20 which is taking up a lot of time(about 30 seconds).Is there any workaround for this? Can this be pushed to SAP HANA or Can we accomplish this in SAP Design Studio?

The Dashboard has few filters on top such as Organization, Country, Region etc. Whenever one of these filters is applied, the top N performers has to be recalculated

Thanks in advance for your help!

[tanisha_gupta20]

Hi Madireddy,

I have a similar requirement in my dashboard, I was wondering if you were able to resolve this issue.

I am using design studio 1.2 which will consume BW queries as data source over HANA system.

Regards,

Tanisha

[IngoH]

Hello Madireddy,

so you are using the BEx queries as a data source ? Why not use the conditions ?

Ingo

[madireddy_rahulreddy]

Hi Ingo,

We are currently using the conditions in Bex query itself but this is leading to performance issues. The query takes about 15-20 seconds to aggregate 1,00,000 records, sort them and filter the top 20. This filtering is currently taking place in application layer and we are looking for ways to push this to database layer(HANA).

[Former Member]

I'd suggest writing a custom component. I have a similar requirement and I put together a component in less than a day. It doesn't do everything I need yet, but it was pretty simple to get the basics down. I'm still trying to work out what I think the best design is for how it interacts with data sources.

[former_member131154]

Hi,

you could create a calcualtion view in HANA and built the topN within SQL directly.

Dirk

[madireddy_rahulreddy]

Hi Dirk,

We are currently trying to the same, but the problem is the filters in the dashboard. For example, My dashboard opens up with TOP 20 performers worldwide. Then the user applies a filter on Region and selects "ASIA". How can this filter be passed to the CALCULATION VIEW. We have about 3 dimension filters in the dashboard.

[former_member262292]

Hi,

could you use the method described here?

I haven't worked with HANA as datasource, so I'm not sure how this XSJS framework works.

Best regards,

Roman

[jmsrpp]

Hello,

There's a great example of how to achieve this in a Calculation View included with SAP HANA Interactive Education (SHINE).  The views you want to look at are:

sap.hana.democontent.epm.models.CV_SALESORDER_RANKING_SQL

sap.hana.democontent.epm.models.CV_SALESORDER_RANKING

The first is a script view that uses the dense_rank() function in HANA to create this ranking on the measures you specify.  You can use Input Parameters to pass filters into the view.  Here's an example I put together today that is also based on SHINE:

BEGIN

  var_out =

  SELECT
  productid,
  product_category,
  product_name,
  year,
  month,
  net_amount,
  quantity,
  sales_rank,
  volume_rank

  from(


SELECT
"PRODUCTID" as productid,
"PRODUCT_CATEGORY" as product_category,
"PRODUCT_NAME" as product_name, 
"YEAR" as year,
"MONTH" as month, 
sum("NETAMOUNT") as net_amount,
sum("QUANTITY") as quantity,
dense_rank() over ( order by sum("NETAMOUNT") desc ) as sales_rank,
dense_rank() over ( order by sum("QUANTITY") desc ) as volume_rank


FROM "_SYS_BIC"."openSAP.models/AN_SALES_HIERARCHY"


WHERE year = :IP_YEAR and month = :IP_MONTH


GROUP BY "PRODUCTID",
"PRODUCT_CATEGORY",
"PRODUCT_NAME",
"YEAR",
"MONTH"


ORDER BY
sales_rank,
volume_rank,
product_name
)


where sales_rank <= :IP_RANK;


END

Once created, this gives 3 Input Parameters so that I may use Design Studio to:

1. Set the values of the Month and Year Input Parameters and reprocess the view by using:
   
   APPLICATION.setVariableValueExt("IP_MONTH", MONTH_DROPDOWN.getSelectedValue("month"));
   
   If I wanted to use a drop down menu to control this selection.  Do something similar for any of your input parameters.
   
   
   
2. I can also enable the user to select the Top N value in Design Studio by using IP_RANK as an input parameter.  Use the same logic to return a dynamic ranking based on your requirements.

Jim

Message was edited by: James Rapp

[TammyPowlas]

See as this type of Top N is not supported in Design Studio

You should be able to get the Top N in the BEx Query; I am not familiar with the HANA options