cancel
Showing results for 
Search instead for 
Did you mean: 

procedure to explore an analytic view

Former Member
0 Kudos

I am creating a procedure that can explore an analytic view given one dimension, one measure and a filter (where clause)

drop procedure dynamicExploration;

create procedure dynamicExploration(in currentMeasure double, in filter_string varchar(100), out dataSubset dataExplorationOutputType)

language sqlscript as

begin

  dataSplitby = select CITY as ID, SUM(:currentMeasure) as SUM_MEASURE from

    _SYS_BIC."package/KDD" Group by CITY;

    --dataSubset = APPLY_FILTER(:dataSplitby, :filter_string);

  dataSubset = select * from :dataSplitBy;

end;

where dataSubset is a data type defined as follows:

drop type dataExplorationOutputType;

create type dataExplorationOutputType as table("ID" varchar(100), "SUM_MEASURE" double);

but I am getting this error, could your please check what's wrong;

Could not execute 'create procedure dynamicExploration(in currentMeasure double, in filter_string varchar(100), out ...' in 166 ms 8 µs .

SAP DBTech JDBC: [266] (at 200): inconsistent datatype: only numeric type is available for aggregation function: line 4 col 36 (at pos 200)

Also, when I define the dimension as a parameter, this way:

drop procedure dynamicExploration;

create procedure dynamicExploration(in currentDimension varchar(100), in filter_string varchar(100), out dataSubset dataExplorationOutputType)

language sqlscript as

begin

  dataSplitby = select :currentDimension as ID, sum(MARGIN) as SUM_MEASURE from

    _SYS_BIC."kddSetupPackage.kddSetup.views/KDD" Group by :currentDimension;

  

    --dataSubset = APPLY_FILTER(:dataSplitby, :filter_string);

  dataSubset = select * from :dataSplitBy;

end;

I get an error saying:

(SQL error: feature not supported: not allowed over OLAP VIEW : search without aggregation or grouping by expression other than view column)

Could you please check what's wrong?

thanks

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

What you've written won't work for a few reasons.

First, :currentMeasure is a DOUBLE, so you are doing SUM(x.xxxx), which doesn't make any sense.


Even if you defined it as a CHAR what you have written won't work because you can't create dynamic SQL in this way.

You can use Dynamic SQL to solve this problem but it will run very slowly because HANA has to parse and build the SQL Expression every time. Please don't use this!

EXEC 'SELECT CITY as ID, SUM('' || :currentMeasure || '') as SUM_MEASURE FROM

_SYS_BIC."package/KDD" GROUP BY CITY';

Regards,

John

Former Member
0 Kudos

well thanks for replaying

but dynamic SQL is actually the issue, I have already a procedure doing analytic view exploration using dynamic sql but in the documentation it is not recommended to use it, for the obvious reason you mentioned. What I am looking for is a different way that is better in terms of performance.

Thanks

Former Member
0 Kudos

I would solve it like this:

- Expose the data using XS via OData

- Consume the OData metadata using your application layer

- Get your application layer to send queries to the OData service with the correct measure

That will perform great!

John

Former Member
0 Kudos

here is the thing:

- what I am trying to do  is to select a data subset

- then apply some data mining algorithm on it

- then expose the data joined with the result of the algorithm to be consumed in another app

I already have a full system doing what you already mentioned:

- exposing the analytic view with odata

- creating queries dynamically based on user interaction

- get the right data from the odata provider

I am even

- applying data mining on the client side and it is working fine

now I want to study what hana can offer at it's best. I am not looking for a working solution, I already have working solutions. I am looking for advice to do the "best" solution that hana can offer.

Thanks & Regards

Mohamed Ali

Former Member
0 Kudos

Well if you really want to go there... the way to do this is to build software which generates SQLScript Procedures with the right code.

If you are doing expensive data mining then the few hundred ms required to generate SQLScript procedures will be worth it.

John

Former Member
0 Kudos

so you mean that I have to generate a new stored procedure for each new data set I have?

Do you have any idea what in hana can help automate this process from the a given analytic view structure.

Any way, if i go with your idea I'll have to build a SQL procedure for each new measure and dimension selected by the user. Or I create a set of procedures to consider all the possible combination of them.

The filter problem can be resolve via the APPLY_FILTER function. But, still this cannot be a good solution. Just consider the overhead of the create of the procedures. And I am sure that won't be the only problem.

Thanks,

Mohamed Ali

Former Member
0 Kudos

Well you are drip-feeding me information and hoping I can help architect it

If you want flexible queries then that's exactly what SQL over an Analytic/Calc View provides. I'm not sure why you can't just generate SQL and send it to one generic view?

If you want structured queries then that's what SQLScript provides.

There are data mining scenarios where you might want to generate SQLScript procedures on the fly - that's basically what Predictive Analysis does.

If you want to write software on HANA then there are some additional options, like what SAS and Lumira hava done. SAS have been given access to the underlying insides of HANA, which allows you to write calculation plan operators.

John

Former Member
0 Kudos

Well thanks, I'll see if I can get to these underlying insides of HANA.

Very thankful that you shared your experience with me. Thanks again

Mohamed Ali