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

Calc View and Out of Memory Errors

I have successfully tested two analytic views, each selecting a fact table (joined to attribute views). Function and performance were good, as expected. These fact tables are identical except certain measures exit in one fact table but not the other. To provide a single view of the data, I created a calculation view that joins the two analytic views. With queries that are relatively non-selective, e.g.,,

WHERE calendar_year_name = 'CY2012'

I hit out of memory errors. The peak memory usage in the server is consistent with these errors.

Queries that are more selective (e.g. below) execute without error and but take considerable longer than the queries against analytic views.

WHERE calendar_year_name = 'CY2012'

AND department_name = 'Computers'

AND category_name = 'Total Personal Computers'

AND region_name = 'South America'

I wish to avoid joining the two fact tables in the ETL process. Is joining analytic views in a calculation view the best solution? The impression I get is that data is being copied from one layer/engine to another during this process (this the out of memory errors). Is that impression reasonable.

One other detail that might be useful In the calculation view joins I've created joins between all attribute columns. This appears to be necessary in order to get those columns in the output of the join. The tables really only need to join on a few columns (the primary keys of each fact table). Are my joins (on all attributes) the correct practice? Would the number of joins have any bearing on the out of memory errors?

Thanks.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Posted on May 19, 2013 at 10:09 PM

    Dear William,

    How about creating calculation view based on column tables, instead of using analytic views, and can use measures from multiple fact tables which is a restriction in analytic view.

    Also the performance would be better. Please try and let me know your results.

    Regards

    Raj

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on May 20, 2013 at 02:07 PM

    Hi William,

    You shouldn't be joining multiple analytic views, but rather combine them using 'Union With Constant Value' approach, starting on slide 34 here: http://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/6056911a-07cc-2e10-7a8a-ffa9b8cf579c?overridelayout=true

    That should help.

    Cheers,

    Jody

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      William Endress wrote:

      Thank you for the replies.

      The Building Advanced Data Models with SAP HANA presentation (in Jody's message) suggests a UNION between two analytic views, so I will give that a try first (because it's easy, understanding that script with CE functions would be better).

      If you're talking about the UNION node in graphical CalcView, it's the same as CE_UNION_ALL. 'Union with constant value' can be accomplished graphically (recommended), with CE functions, and/or with SQL.

      What would be the best practice for adding the constant to the analytic view. I could imagine:

      - Adding new columns to the fact tables, but I would rather not do that because it would add a bit of extra space (but probably not much, because there would be only a single value, right?) and I would prefer not to change the table definition.

      - Creating a regular/SQL DDL view over the table and adding the constant in the view. Would the view disrupt performance?

      - Add a calculated measure to the analytic view with the calc'd measure set to a constant (seems easy, so that would be a nice approach).

      Add a calculated measure

      When I try the script/CE function method, I'm assuming that I can add the constant in the script, right? (I haven't yet made a script/CE function calc view).

      Yes

      With either approach, how will the constants impact aggregate with functions such as min, median and average?

      I don't get your question

      Because union is recommended, it would seem that HANA is happier scanning the union rather than the join between to large tables. Correct? Is the join one of the cases where too much data gets moved between engines? What is the use case for join in the calculation view?

      It's not a matter of data movement between engines. A couple comments:

      1) Generally speaking, fact data from 2 sources should always be combined via 'Union with constant value' (UCV) rather than joins. In the UCV approach - no operation is required for UNION ALL - HANA essentially just stacks two chunks of memory. After UNION ALL, aggregation is performed - which is HANA's performance hallmark. So, UNION ALL + AGGREGATION is much faster than large joins.

      2) That being said, let's say you have 500M sales records from 10 stores for thousands of products, a few thousand days, a few thousand sales per day. If you query two analytic views per store and per year - then the number of result records is in the tens (i.e. 10 stores in 2012, 10 stores in 2011 let's say). In a case like this - a join should actually behave just fine regarding performance. So, it depends on granularity.

      Thanks!

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.