Table not visible in DWC HANA Database explorer

[rphss]

Hi

I have created a database user in DWC to access the schema with external tools.

In same space I have created a data flow target table , deployed it and filled it.

By using the open database explorer I am not able to see the table, only views are shown.

Any reason for this.

Ralf

[kpsauer · Accepted solution]

I guess you have applied the relevant privileges as described here: https://help.sap.com/viewer/9f804b8efa8043539289f42f372c4862/cloud/en-US/7eaa370fe4624dea9f182ee9c9a...

In case there is still an error, I would recommedn to open an incident.

[rphss · Accepted solution]

I nearly read all chapters, but this I missed.

Thanks its working now.

[kpsauer]

Hi Ralf,

I would try a simple CREATE TABLE specifying the columns.

CREATE COLUMN TABLE "<YOURSPACE>#<OPENSQL>"."Table01" (
  "Column_1" NVARCHAR(10) NOT NULL,
  "Column_2" NVARCHAR(10),
  "Column_3" DATE,
  "Column_4" NVARCHAR(10),
  "Column_5" NVARCHAR(10),
  "Column_6" NVARCHAR(10),
  "Column_7" NVARCHAR(10),
  "Column_8" DECIMAL(15,2),
  PRIMARY KEY ("Column_1")) UNLOAD PRIORITY 5  AUTO MERGE ;

In case you want to avoid typing hundreds of columns and data types, then a workaround would do the trick.

Create a database analysis user in the admin area under "Configuration" > "Database Access". That user gives you read access to all schemas. You can use it to get to the create table statement of the table you want to create. In the schema selector pick the space schema your table currently exists in. It should then show up in the table list as in my screenshot.

Identify your table and right click "Generate CREATE Statement". You should get something like

CREATE COLUMN TABLE "BLACKFOREST"."Table_1"(
	"Column_1" NVARCHAR(100),
	"Column_2" NVARCHAR(100),
	"Column_3" NVARCHAR(100),
	"Column_4" NVARCHAR(100),
	"Column_5" NVARCHAR(100),
	"Column_6" NVARCHAR(100),
	"Column_7" NVARCHAR(100),
	"Column_8" DECIMAL(5, 2)
) PAGE LOADABLE
UNLOAD PRIORITY 5 AUTO MERGE;

You could use that with your OpenSQL db-user and adjust the first line

CREATE COLUMN TABLE "<YOURSPACE>#<OPENSQL>"."Table01" ( ... );

Hope this helps.

Best regards
KP

[rphss]

Hi Klaus-Peter,

Thanks, I used your option 1

Option 1) copy P table info from BW table and adapt to nvarchar etc. and created the table. 😞

Option 2) geht nicht

message :

/*The definition for catalog object "GV_0MATERIAL_HYB_VIEW" is not available.

You may have insufficient privileges on this object.

*/

There are lot of privilige messages as I am in DWC. Not sure if I am doing something wrong .

The Database Explorer user is the "schema#xxx" which is given in DWC SPACE Data Acces Database User.

With this user I was able to create the table as per option 1.

Back in Data flow, I have create in same space a data flow and chose the new table from the repository and set the new table as target . all fine so far.

But loading data into failed with General error;258 insufficient privilege: Detailed info for this error can be found with guid
And I have got DW Admin assigned.
Reason I guess is that the data flow is created with my user in DWC and not the data base user?
Might be, I missed an important step for authorization for this Schema..

[kpsauer]

Good to hear that it worked.

Thanks

KP

[rphss]

Hi,

Yes, but Data Flow not working as of privilige missing.

Any clue?

Ralf

[avinash_eppar]

Group messages: Group: default; Messages: Graph failure: target1 failed with the following error: General error;258 insufficient privilege: Detailed info for this error can be found with guid 'F32ACA108F097243B6ECB4E4E786A9EF' Process(es) terminated with error(s). restartOnFailure==false

[kpsauer]

Hi rphss ,

that is by design, as we only expose views for consumption with external tooling and not tables from the space schema. That includes tools like the database explorer.

In case you want to manage the table with the database explorer, you could create the table in the open SQL schema and still be able to write via data flow into the table.

Best regards
KP Sauer

[rphss]

Hi Klaus-Peter,

Ok thanks, I will try to find the documentation for your approach and give it a try.

Ralf

[rphss]

Hi Klaus-Peter,

I have tried to create the table based on the view in database explorer SQL , but was not successfull.

I tried many statements , like the one below. also I tried opening the view but whole output could not be copied with column definition.
Example

CREATE TABLE "schema#xxx"."MATERIAL" AS SELECT * FROM "SCHEMA#xxx"."GV_0MATERIAL_HYB_VIEW";

Any idea which SQL is working for my goal?

Ralf