Skip to Content
author's profile photo Former Member
Former Member

SAP HANA Scripted Calculation View (Very Slow)

Hi Guys,

I have 100624357 records in a HANA Colum Table. The data is about Invoice items like:

Invoice Number Product Name Quantity Price Amount

I001 P001 10 40 400

I001 P002 10 30 300

I001 P003 10 30 300

I002 P001 12 40 480

I002 P003 10 20 200

I003 P001 10 30 300

I003 P002 10 20 200

If Product A and Product B are in the same Invoice then they are associated with each other. I want to show this association / relation between different products like:

Product A Product B Association

P001 P002 20%

P001 P003 20%

P002 P003 10%

For this I created a Calculation View (scripted) like:


/********* Begin Procedure Script ************/

BEGIN

occ = SELECT "Product Name" as "Product Name",

COUNT(DISTINCT "Invoice Number") AS subtotal

FROM "Table"

GROUP BY "Product Name";

item = SELECT "Invoice Number",

"Product Name",

FROM "Table"

GROUP BY "Invoice Number", "Product Name";

var_out = SELECT a."Product Name" AS prod_a,

b."Product Name" AS prod_b,

(COUNT(distinct b."Product Name") / c.subtotal ) * 100 AS perc,

FROM :item AS a INNER JOIN :item AS b ON a."Invoice Number" = b."Invoice Number"

AND a."Product Name" != b."Product Name"

INNER JOIN :occ AS c ON c."Product Name" = a."Product Name"

GROUP BY a."Product Name", b."Product Name", c.subtotal

ORDER BY perc DESC;

END /********* End Procedure Script ************/

But this calculation view is taking a lot of time and then crashes at the end. When I select top 10000 records then it is taking 10 seconds, but this is not the solution. I want to get the product association from the whole data. Please some one guide me how to get the association between products in an efficient way.

Thanks and best regards.

Fahad

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Dec 16, 2013 at 11:34 AM

    Hi Fahad,

    You're using SQL, try using SQLScript instead.

    Can you please put the logic to compute Association Column in simpler words, maybe I can help you with the SQLScript or Graphical approach then.

    --

    Shreepad

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Patil,

      Thanks for your reply, it was really helpful, but the actual problem is with our hana system because we don't have enough RAM available w.r.t the data volume thats why the calculation is taking lot of time. Apart from this issue your provided solution is working good,

      Thanks and best regards.

      Fahad

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.