on 12-28-2015 4:27 AM
Hello,
In my requirement, I've to show Top N records from a table using Graphical Calculation View as Calc View is good in performance for handling huge amount of data. Here, 'N' can vary depends on user's input.
I tried using Rank Node available in the calculation view. But it ranks for a each and every particular data set and returning top N records of each data set. But it doesn't gives Top N from overall data set.
For Ex: suppose consider following example:
Year | Customer ID | Value |
---|---|---|
2014 | 101 | 10 |
2014 | 102 | 20 |
2014 | 103 | 30 |
2015 | 101 | 15 |
2015 | 102 | 25 |
2015 | 103 | 35 |
2014 | 103 | 60 |
Suppose if I've to get top 2 records, following should be the result:
Year Value
2014 60
2015 35
But I get the following result after using rank node:
Year Value
2014 60
2014 30
2015 35
2015 25
I used Threshold as 2, Order By: Value, Partition By Column: Year in my Graphical Calculation View.
How can we get the following result(to show top N records):
Year Value
2014 60
2015 35
Using a Graphical calculation view.
Also, what I observed by using a Rank node in a calc view is, it's taking too much time for calculation and to display the result.
Is it possible to get Top N records using Rank node in calculation view? Please suggest.
with Regards,
Antony Jerald.
Hi Antony
This is expected behaviour as you put Year in "Partition By Column".
To achieve the stated objective create dummy calculated column , assign some constant value to it .
and then in "Partition By Column" use this Dummy Column. All other setting can be same.
Cheers
Anindya
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Thanks for your reply.
While creating Dummy and using it in Partition by column, it takes huge amount of time(nearly 30 mins) to fetch and show the result (of top 20 records from the table) as Table has millions of entries.
Also, while I write the select query and check the correctness of the result, I could find some mis-match is there in calc view using rank node. And I think there's a variation in data is because of using calendar day range used in the where clause of the select query which I use for checking correctness. I think in this scenario, using dummy too fails to show correct result.
Not sure how it works to calculate top n records using rank node.
Regards,
Antony Jerald.
If your filter is applied after selecting top N , result will vary .
My Base table :
Top two sales ( Qty) should be 99 and 77. Same information is shown in Calc View.
If I additionally apply another where condition ( by use of a variable in calculation view) , this result set would be further restricted.
Positioning of "WHERE" clause is important . Check the SQL statement and output. If you are using a HANA variable , that would be applied on the result set i.e after your Rank Node ( with top N) and Aggregation Node (I assumed that's your model)
I would not be able to comment on performance before seeing your actual data model. I have table with 20 M records and rank node works fine there.
Cheers
Anindya
Hi,
The table has nearly 32.5 Crores of records.
As said, while creating dummy and using it in rank node for Partition, it takes nearly 30 mins to fetch top N records from the table.
Calculation View model looks like following:
Projection_1 -> Aggregation_1 -> Rank_1 -> Aggregation ->Semantics.
Sort Direction: Descending
Threshold: 20
Order By: Value
Partition: Dummy
Dynamic Partition Elements is checked
Generate Rank Column is checked.
Please suggest if we can optimize the same.
Regards,
Antony Jerald.
The way you described your problem initially, you do not need Dynamic Partition criterion to be checked.
If you want to partition the data only with the partition by columns that query requests for processing the rank node, select the Dynamic Partition Elements check box.
First of all, are you getting intended result from your view ?
If yes, please close this thread .
For performance you can open another thread with detail of your model and Plan Viz .
Cheers
Anindya
User | Count |
---|---|
95 | |
11 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.