how to use Dynamic SQL inside of a calculation view

[Former Member]

Hi all,

I want to do a data selection over a analytic view based on a : dimension, a measure and a numbers of filters dynamically specified by the user:

The only solution I found to do this kind of data selection is to use Dynamic SQL this way:

select '||:currentDimension||' as ID, sum('||:currentMeasure||') as SUM_MEASURE from _SYS_BIC."efashion/EFASHION_STAR_SCHEMA"  '||:currentFilters||' GROUP BY '||:currentDimension;

but when I try to use this within a calculation view, I get an error saying that:

feature not supported: Dynamic SQL is not supported in the READ ONLY procedure/functionnSet Schema DDL statement

so how to do to get this to work in a calculation view, and if you have any other ideas on how to get this kind dynamic data selection, please feel free to share!

Thanks & Regards

Mohamed Ali

[former_member184768]

Hi Mohamed,

If you are using HANA SP06, then you can use dynamic Filters as explained in the document here.

I haven't tried it myself, hence cannot say for sure if it works for Calc views. Since you are internal to SAP, I think you can reach out to the author of the document directly and get additional details.

Regarding the dimensions and measures, I guess they are anyway dynamic based on the SELECT statement. I did not understand why they need to be dynamic. The Calc view can have 20 columns, but you can select any number of columns in the select statement.

Regards,

Ravi

[Former Member]

hi Ravi,

Thanks for replying,

The link you provided is not working!

Why I need dynamic SQL?

Well, because I don't in advance the number of filters I am going to receive,

for instance the data selection query could be like this.

select CITY, SUM(MARGIN) AS SUM_MEASURE from OLAPCube where MONTH_NAME='August' group by CITY; 

or it could be like this:

select CITY, SUM(MARGIN) AS SUM_MEASURE from OLAPCube where MONTH_NAME='August' and YR='2001' and ProductID=2255454 group by CITY; 

or it could be with no filter at all,

select CITY, SUM(MARGIN) AS SUM_MEASURE from OLAPCube  group by CITY; 

So the filter are optional and they can be of any number between 0 and number of dimensions in the OLAP cube - 1 (because there is one in the split by)

So, that why I had to use the dynamic SQL. Also there is a problem when passing the measure dynamically to a non dynamic SQL, for instance If I do this sum(:currentMeasure) I get an error saying that sum cannot work on non numeric values which means it is recognizing :currentMeasure as a string!

I hope this makes the question clearer!

Thanks & regards

Mohamed Ali

[former_member184768]

Hi Mohamed,

Can you please try the link again. If not, please check for the blogs in HANA Developer Center for Dynamic Filters in HANA by Rich Heilman.

http://scn.sap.com/community/developer-center/hana/blog/2013/07/01/using-dynamic-filters-in-sap-hana

My question was related to Dynamic Dimensions, not the filters. I understand the filters can be dynamic, but not sure if even the select columns need to be dynamic. Any reporting tool generates the select statement by its own. But again, I am not aware of your requirements for the dynamic SELECT statement.

Regards,

Ravi

[Former Member]

Hi Ravi,

Thanks the link is working now.

Well, I don't know what do you mean by the dimension and measures they are already dynamic, but you picture my requirement as a hierarchical data exploration in which the explored dimension after the select, will be in the filter in the next exploration and a new dimension is explored in the select, and so on and so forth till the end of the available dimension. So the dimension will change in each level of exploration. And yes, I can do select over a dimension dynamically by providing it as a variable ,

like for instance select :currentDimension, without the need of dynamic SQL, but this is not working for me when applied to the measure, for instance if I do select :currentDimension, sum(:currentMeasure) I get an error as explained above!

Thanks & regards

Mohamed Ali

[Former Member]

Hi Mohamed,

Did you used input parameters in Calculation View. Create the calculation view with input parameters and while creating universe you can pass the values for same input parameters from reports.

Have a look at the below document.

http://www.saphana.com/servlet/JiveServlet/previewBody/1688-102-1-2568/Using%20SAP%20HANA%20Variable...

If you want to create dynamic sql, then i believe it is not possible in calculation view screen, you can do the below things

create procedure using dynamic sql and call this procedure in calculation view using EXEC statement.

Regards,

Venkat

[Former Member]

hi Venkat,

Thanks for replying,

I cannot do the first solution because of the reasons presented in my reply to Ravi.

I also tried the second solution, but when activating the calculation view, the engine actually can see that called procedure contains dynamic SQL and throws the same error as before.

And thanks for the link.

Thanks & regards

Mohamed Ali