How can I use Attribute, Analytic and/or Calc Views? And where?

[Former Member]

I have successfully created Views. They appear in my package workshop.ve.models.

I was expecting, like traditional views, I would be able to make a query

     select * from viewname;

That does not work. Even if I use workshop.ve.models.viewname.

I can see the results of running the views in the view editor in HANA Studio - but is there a way I can then refer to such a view elsewhere, like in an XSJS file so I can use it as part of my application? Or directly in a SQL console? See below.

Here is one of my analytic views seen in SYSTEMS view under Content > models.

Attempt to access in SQL console. Not successful.

[Former Member · Accepted solution]

Very good. Analytic Views don't make sense for me. I think I need a Calculation View with SQL Script to express the desired logic. Still, I am not understanding the answers to my questions:

I can see the results of running the views in the view editor in HANA Studio - but is there a way I can then refer to such a view elsewhere, like in an XSJS file so I can use it as part of my application? Or directly in a SQL console?

If I create any of Attribute, Calculation or Analytic views, where then can they be referenced so that the net outputs can be expressed in some user interface?

I want to create a Calculation view with input arguments, and then be able, say in a UI5 interface to show the results to a user, or to use the list of values to change the appearance of a display.

In particular, I am computing whether objects interfere with each other inside HANA, and I will want to color them differently in my UI, when they do.

[henrique_pinto · Accepted solution]

Sure you can. In the Data Preview screen of HANA Studio, if you click on the upper right corner icon like an 'i' , it will show the executed SQL Query. You can do the same to see the syntax for querying with input parameters.

[Former Member · Accepted solution]

Henrique - please understand me. I am developing an application. The data preview will not work for customers. They will be running UI5, and seeing the results of having run the calculation view sql script in their own web page interface. Where do I put the "call" to a calculation view? If I have a procedure, then I use literally CALL.

[henrique_pinto · Accepted solution]

It seems like you didn't even read what I said.

Let me try again, in little pieces:

1) you can embed SQL code in your XS app

2) Calc Views are not executed thru CALL statements but rather SELECT, as actual views

3) you can see the SELECT statement for querying a given Calc View in the Data Preview

4) Again, I'm not saying to use the Data Preview, but to use the same SELECT statement it does - or at least to start from it

5) you can copy that code into an SQL Editor screen and tweak the statement to your desired output

6) when you're done, use that SELECT statement in your .xsjs file.

Best,

Henrique.

[Former Member · Accepted solution]

Hey Robert,

Maybe you could start by explaining what you're looking to achieve, what output you want. I'm sure we can then help you with the right solution. The more specific you can be, the better.

John

[Former Member]

The view name would be: "_SYS_BIC"."workshop.ve.models/PART_SPHERES"

You can't do SELECT * on an analytic view. You need to provide an aggregation level.

John

[Former Member]

Very good. So these views cannot be used like traditional views?

Where then are they useful?

[henrique_pinto]

Analytic Views are like multidimensional OLAP cubes.

They are not meant for regular queries, but queries with several dimensions (attributes) and key figures (measures) aggregated by these different dimensions. Since Analytic Views are optimized for that kind of query, they require an aggregation level for the measures you want in your result set.

Now, by looking at your AN definition, you should know which fields in the output layer are attributes and which ones are measures. Just do a regular SELECT with aggregation on the measures (SUM, MIN, MAX, COUNT - if you need AVG, try SUM/COUNT) and a GROUP BY CLAUSE on the attributes you want in your output.

On a side note, you can so SELECT * on Attribute and Calculation Views since they are not mainly rendered by the OLAP Engine, but by different engines - Join Engine in case of Attribute Views (aka Join Views) and the Calculation Engine in case of Calc Views.

If you need a regular SQL View for your transactional (not analytical) model, create a SQL View.
HANA supports that syntax just fine.

CREATE VIEW - SQL Reference - SAP Library

[Former Member]

Henrique said it right. I strongly recommend you read the modeling guide, available here: http://help.sap.com/hana/SAP_HANA_Modeling_Guide_en.pdf

An Analytic view is expressedly an OLAP view for aggregation, and so SELECT * would make no sense. The format of a query on an Analytic view should always be:

SELECT DIMENSION, SUM(MEASURE) FROM VIEW GROUP BY DIMENSION

John