Create view on data for SYS schema for reporting

[Verakso]

On our HANA sandbox, we have many users and different roles.

The users I can get by writing the following SQL

SELECT * FROM SYS.USERS

In addition, I can get a list of the assigned roles by executing

SELECT * FROM SYS.GRANTED_ROLES

So I thought, why not join these two tables in a Calculation View, and use that View for a report in SAP BO to show what roles the users have, so I could see if they have the proper roles or are missing some.

However, when I try to validate this new view I created

I get the following error

The first things that comes to my mind is, that I probably are missing some rights, but I am running this under SYSTEM account.

So any thoughts or ideas?

I am “just” trying to expose the two SYS views in my BO platform, to do some simple reporting on these data.

What is best practice for doing reporting on data from SYS

[Former Member · Accepted solution]

Hi Thomas,

You can include HANA views ( including scripted cal. views) and columnar tables in a calculation view.

Refer Lars reply at http://scn.sap.com/thread/3732812 .

-Tarun

[Former Member]

Hi Thomos,

The reason for the error is we can row storage structures or DB views created on row storage tables as source in graphical models. You should able to create SQL Script based calculation view.

Please try scripted calculation view.

Regards,

Venkat N.

[Verakso]

This makes sense, I just briefly forgot that HANA is both column and row based.

But i still have some trouble getting it right.

I can't even get this very simple test to work, I made a scripted calculation view like this:

BEGIN

     var_out = CE_COLUMN_TABLE("SYS"."USERS",[USER_NAME])

END

And I still is getting and error while checking, so perhaps a little insight in how to create these scripted calculation views, and perhaps some insight whether to use plain SQL or the built-in CE_functions would be nice.

[former_member226419]

Please try as below:

select * from tabl1

inner join table2 as b

where a.key = b.key;

BR

Sumeet