Top 10 Items using Analytical View

[Former Member]

Hi All

I have a table in HANA which consists of items code, date, sales value. My requirement is to find top 10 items code based on sales value. can I achieve this by solely using Analytical value. I guess combination of analytical and calculation view will give me the result but my requirment is use only analytical view.

Thanks

Balaram

[Former Member]

@Henrique and @Ravindra:

we should not use top 10 in the query as it does not search the entire database to give you top 10 items code based on any conditions (sales value for eg). It picks up randomly top 10 records.

@Ravindra:

We should not use at reporting level as in that case, you have to bring to entire dataset to reporting layer and then calculate top 10 customer, which means you are not using HANA memory for calculation. This will degrade your reporting performance. Rather reporting layer should pass the input the parameter to HANA and HANA calculates the results and sends a small dataset to reporting layer.

Thanks & Regards

Balaram Agarwal

[henrique_pinto]

Balaram Agarwal wrote:
@Henrique and @Ravindra: 
we should not use top 10 in the query as it does not search the entire database to give you top 10 items code based on any conditions (sales value for eg). It picks up randomly top 10 records.

If you don't define an ORDER statement, it will bring the data in the same order it was inserted/updated, earliest entries first. However, by including ORDER BY SALES DESC in your select, it will bring everything sorted by the SALES value in descending order. In my quick tests here, HANA appears to perform the ORDER first and just then the TOP N.

Did you test yourself?

If you don't want to use TOP N, you could try the LIMIT [...] clause.

Check the details here: http://help.sap.com/hana/html/sql_select.html

[former_member184768]

Hi Balaram,

Most of the reporting tools fire SELECT TOP N query against the database when you use the TOP N functionality. So basically it is the same thing. This is what I meant by using it from the front end tool.

As Henrique correctly mentioned, you should provide the order by clause to sort the dataset, out of which you will select top 10.

Regards,

Ravi

[former_member184768]

Hi Balaram,

I think the TOP N should be left to the front end reporting and not restricted to the HANA information model. The TOP N records generally also depend on the selection criteria like date range, item codes (item categories) etc. Hence restricting this in the HANA information model may not be a good idea.

And secondly, why complicate things, when they can be simplified by a simple SELECT TOP ... statement.

Regards,

Ravi

[henrique_pinto]

You could just create a regular analytic view and use something like the statement below for querying the data.

SELECT TOP 10 ... FROM TABLE GROUP BY ... ORDER BY SALES DESC