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.

[atillacemyildiz · Accepted solution]

@Cocquerel, hello again after months 🙂

We used this structure properly in our Development system and did not encounter any problems. However, recently our ERP and BW systems went live. We encountered the following problem in the Live system:

We were pushing the output of the Calculation View, in which this CDS View was used, to a DSO. This logic worked perfectly in the Development system, but when pushing the output of the Calculation View to DSO in the Live system, we sometimes saw that the data came correctly, and sometimes we saw that 0 records came.

Don't get me wrong, when we look at the output of Calculation View, there are approximately 300 thousand rows of data, but sometimes 0 records were received when pushing to DSO. Data extraction is done hourly as Full Load. Sometimes the data comes in correctly, sometimes it doesn't come at all. For this reason, we cannot design the report we want.

If you have any guesses, I'd be happy to hear from you.