Out of memory Error while querying SQL Script based Calculation View

[Former Member]

Hi All,

I wanted to test the performance of Graphical and SQL Script based Calculation views.

Created Graphical (CA_GRPH) and SQL Script (CA_SQL) Calculation views.

Analytic View (AN_GRPH) for both Calculation views are the same which is Graphical based (90 Attributes and 5 Measures)

In Analytic View data foundation I have a Fact table which has 1.5 Billion records and 9 Dimension Tables –collectively 500 million records (7 Attribute Views). 9 Referential joins with cardinality N:1 and 1 Referential join with cardinality N:N.

I wanted to keep (CA_GRPH) and (CA_SQL) as a base Calculation views and leverage those to create various calculation views (Will be creating different Calc views for respective Business segments)

In order to test this I have created below calc views on top of base calc views.

Graphical Based: Created (CA_GRAPH_XYZ) by having CA_GRPH in projection with 30 Calculated Columns. – This retrieves data in 13 secs

SQL Script Based: Created (CA_GRPH_ABC) by having CA_SQL in projection view with 30 calculated columns – This errors out after 1.50 mins.

Could not execute 'SELECT "COLUMN_A","COLUMN _B"," COLUMN _C"," COLUMN _D", SUM("COLUMN _REVENUE") AS ...' in 1:50.480 minutes .

SAP DBTech JDBC: [2048]: column store error:  [2048] column store error: search table error: [1000002] Error executing physical plan: exception 1000002:

ltt/impl/memory.cpp:63

Out of memory ; $size$=1507711; $name$=ihm; $type$=pool; $inuse_count$=170104; $allocated_size$=219215007925

exception 1000002:

Any suggestion / help in fixing this issue will be greatly appreciated.

Regards,

Av

[pratik_doshi2]

It is simply because one of the join is returning the more number of rows.

Filter the rows first and then do the join if it is possible.

Reduce the number of rows before doing joins or group by in your view.

Can you tell how much number of rows your base calculation view is returning?

[Former Member]

Hi Pratik,

Thanks for your suggestion, the base calc view works fine when i select couple of columns. However when i do a SELECT * or SELECT COUNT(*) it gives me the same error as mentioned above.

Regards,

Av

[pratik_doshi2]

Hi ,

So how much number of Rows it returns when it is working ?

Try to restrict the rows with the more filter conditions and if not possible test with the LIMIT rows clause of the select statements.

It should work with above mentioned clause.

Regards ,

Pratik Doshi

[Former Member]

its returns a million rows when i query with few columns, guess i have to identify the column which is bloating up the result rows. however i would need all the columns in a single view. will check that. Thanks for your time and suggestion.

Regards,

Av

[pratik_doshi2]

Please post if you are facing the problem or mark the thead as answered.

[Former Member]

i was on vacation, haven't worked on that issue, will update you soon. Thanks

Regards,

Av

[rindia]

Hi Saravanan,

Could you paste your Graphical and SQLscript for better understanding.

Regards

Raj

[Former Member]

Hi Raj,

Thanks for your time, please find edited snap hot of Analytic View (AN_GRPH) below,

Calculation view(CA_SQL)

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

BEGIN

       var_out =

     SELECT

            "COLUMN_1"

            "COLUMN_2",

            .

            .

            .

            .

            .

            "COLUMN_84",

            "COLUMN_85;",

            SUM("REVN") AS "REVN",

            SUM("MGN") AS "MGN",

            SUM("ORD_QTY") AS "ORD_QTY",

            SUM("SYS_QTY1") AS "SYS_QTY1",

            SUM("SYS_QTY") AS "SYS_QTY"

FROM

      "_SYS_BIC"."XYZ/AN_GRPH"

GROUP BY

            "COLUMN_1"

            "COLUMN_2",

            .

            .

            .

            .

            .

            "COLUMN_84",

            "COLUMN_85";

END

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

Later i have built one more Calculation view(CA_GRPH_ABC) using (CA_SQL)in projection. i have 30 calculated measures in this final calc view. this final calc view is throwing above mentioned error.

Not sure if i can use SQL script based calc view in graphical based calc views?

Regards,

AV

[rindia]

Hi Saravanan,

I see that you are using plain SQL in SQLScript. You can optimize your script by using CE functions.

Also don't mix SQL with CE functions which will impact the performance.

Please go through the SQLscript guide here.

Regards

Raj

[Former Member]

Raj,

Thanks for your suggestion. Actually i am migrating my existing solution from Teradata and Tabular-OLAP cubes to HANA.

I have around 300 odd complex metrics which was written in SQL and i would like to leverage those codes in HANA as my timelines are limited.

Could you please let me know if can use the plain SQL script with multiple CASE WHEN statements to create a Calc view?

Also can i use that SQL script base Calc view in another Calc view which is Graphical based?

Regards,

Av

[former_member182302]

I think you are trying to use "Case" statements in script based calc view and then again use it graphical view.

Why don't you try using "Decision" Tables?

Regards,

Krishna Tangudu

[Former Member]

Thanks Krishna, Will check that.

Regards,

Av

[rindia]

Hi AV,

plain SQL script with multiple CASE WHEN statements can be used in creating a Calc view but performance might be compromised.

Script base calc view can be included in graphical view.

Now coming back to your first question: Is your view CA_GRPH_ABC returning data or getting still error

Regards

Raj

[Former Member]

Hi Raj,

Thanks for your time. i still get error when i query CA_GRPH_ABC.

Regards,

Av