Unable to view data of Analytical View having calculated attribute/measure

[hemant01_kumar]

Hi,

I have few Analytical View deployed in my test system. i am able to see the data of few analytical view by executing select statement (with group by clause) using studio.

Example: SELECT CAST(SUM(COL1 as DOUBLE) ) as COL1 , CAST(COL2 as NVARCHAR) as COL2 FROM _SYS_BI.<ANALYTICAL_VIEW> GROUP BY COL1

the same SQL query is throwing me error for  the Analytical View containing calculated Attribute or calculated measure.

Please help to understand the SQL syntax to get the data.

Thanks

Hemant

[Former Member]

Hi Hemant.

On the analytic view giving you an error can you still run the data preview or is this too not retrieving data? If the data preview is not getting data then you need to review your model. EG review filters etc and warnings on activation. If you cant see data preview information then a select will also not show any data.

Next, If you can see data preview information than I think this may be a syntax issue. EG analytic views and calculated attributes use the OLAP and CALC engines of the HANA database. SQL syntax uses a different engine. If you run a select * statement on your analytic view does it work? If it does, can you see the calculated attribute in the output? If not then you will need to use CE logic to see it I think. Can you confirm on the points above and Ill try to assist further. I think Ive experienced a similar issue as you but I need more information to understand your issue.

My issue was I could not select on the calculated attribute in my stored procedure as the attribute is generated after the select statement so I was not able to get at it with normal SQL. I had to use CE function logic.

Kind regards,

Danielle

[hemant01_kumar]

Hi Danielle,

I am able to see the data preview using studio GUI. but unable to get the data using SQL. For me it looks as the syntax problem.

is there any system table/view where i can see the list of calculate attribute/measure for a given analytical view?

Thanks

Hemant

[Former Member]

Hi Hemant,

I do not know of such a location in HANA that holds a global list. You will only be able to see which calc attributes/measures you can call from a view in the output of the analytic view itself. EG on the right side of the model make sure you see the output of the fields you are calling in your SQL.

Can you run a SELECT * FROM ? Your From needs to be in the following format "_SYS_BIC"."package.subpackage/ANALYTIC_VIEW_NAME"

General syntax below.

SQL Syntax 1:		CONVERT( datatype, expression [, style] )
SQL Syntax 2:		CAST( expression AS datatype )
Parameters:		expression  Any expression of any type. datatype The new data type. This parameter is not a expression. style A integer value for compatible with the MS SQL Server.
Escape Syntax:		{fn CONVERT( expression, datatype )}
Return Type:		definite with the parameter datatype.

Examples:

• SELECT convert( varchar(30), price ) 
• SELECT convert( nvarchar(30), now(), 102) 
• SELECT {fn convert( {fn now()}, nvarchar(30))} 
• SELECT cast( price as varchar(30)