Skip to Content

Top N Performers in SAP Design Studio 1.2

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!

Add a comment
10|10000 characters needed characters exceeded

Related questions

5 Answers

  • Posted on Jan 30, 2014 at 12:33 PM

    Hi,

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

    Dirk

    Add a comment
    10|10000 characters needed characters exceeded

    • James Rapp Madireddy Rahul Reddy

      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

  • Posted on Jan 30, 2014 at 12:13 PM

    See SAP BusinessObjects BI4 - Supported BEx Query Elements 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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Feb 18, 2014 at 02:38 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Feb 18, 2014 at 03:07 PM

    Hello Madireddy,

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

    Ingo

    Add a comment
    10|10000 characters needed characters exceeded

    • 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).

  • Posted on May 13, 2014 at 06:45 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.