How to use CDS View in Calculation View?

[atillacemyildiz]

Hi, 

I want to create a new Calculation View with a CDS View named CSTOCKQUANVALCUR, but I cannot create a Virtual Table because I think CSTOCKQUANVALCUR needs parameters.

That's why I looked at the view that is the source of CSTOCKQUANVALCUR and saw that the CDS View named 'PMATSTKQTYVALCU1' has no parameters. Also, when I checked, I saw that I could create a Virtual Table with this CDS View.

After creating the Virtual Table, I selected this CDS View in SQL Console, but I saw that no data came. I selected this View directly in the Source System instead of Virtual Table, but no data came again.

However, when I do Data Preview in ABAP or look at SE11/SE16N, I see data coming.

My question is this: How can I use this CDS View in Calculation View? If there is an authorization problem, how can I pass it?

[Cocquerel · Accepted solution]

The business logic of CDS view is relying on a session variable called CDS_CLIENT to filter the data depeding on the slient number. When accessing the CDS view via the ABAP layer, this session variable is automatically provided by the kernel. When accessing the CDS view via SDA (hanaodbc driver), it is missing by default and that explains why you get no data because it filter based on a client number that is blank.
One workaround is to add this missing session variable in the extra properties of your SDA remote source with the following syntax (SESSIONVARIABLE:CDS_CLIENT=<client-number> , example SESSIONVARIABLE:CDS_CLIENT=001 )

This workaround is not the recommended solution by SAP. Instead of using hanaodbc adapter, you should use the abapodbc one. Unfortunately, this abapodbc driver is only available on hana cloud for the time being. Using this adapter, it has also the advantage that it allows to create virtual tables on top of CDS views having parameters (see https://help.sap.com/docs/hana-cloud-database/sap-hana-cloud-sap-hana-database-data-access-guide/vir... )

[atillacemyildiz · Accepted solution]

Cocquerel, thank you for your answer. I haven't had the chance to try the method you mentioned yet, but something came to my mind. When I ran Select in Remote Source itself, rather than in BW, no data came too. Should it come when I run Select in Remote Source itself?

[Cocquerel · Accepted solution]

Yes, if you are running SQL statement directly on HANA layer of the source system, you will face the same issue.
The workaround in the case is the run the following statement prior to do a SELECT on the CDS view:
SET 'CDS_CLIENT'='001';
Note that this statement and the SELECT should be done in the same SQL console to keep the session context.

[roland_szajko · Accepted solution]

An addition to Cocquerel-s answer is the note:

2511210 - Access to ABAP-Managed Database Objects

which in summary says, not to consume an ABAP CDS view directly on the database. These should be consumed always through the ABAP application server.