OBIEE 12c - Admin Tool - Database user privileges

BASSAM CHEHABEDDINE

Dear Gents,

I would highly appreciate you help on the following ...

I connecting OBIEE 12C to oracle Database, and in admin tool i am putting the main schema name and password where i want to avoid that as it is not the best practice, while adding a new user

create user reports identified by 123456

create role bi_user_role

grant select all_table to bi_user_role

grant create session to bi_user_role

GRANT CONNECT TO bi_user_role

  grant bi_user_role to reports

and it shows errors while updating the row count in admin tool

and also the OBIEE answers is not working and give the following:

Error
	View Display Error
	Odbc driver returned an error (SQLExecDirectW).   Error Details Error Codes: OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65P State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000) State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS. (HY000) State: HY000. Code: 17001. [nQSError: 17001] Oracle Error code: 942, message: ORA-00942: table or view does not exist at OCI call OCIStmtExecute. (HY000) State: HY000. Code: 17010. [nQSError: 17010] SQL statement preparation failed. (HY000) Please have your System Administrator look at the log for more details on this error. Looking forward to your advise ... Best Regards, Bassam

Joel

Have you been able to connect directly to the database as the reports user in SQL developer? If so we’re you able to select from these tables?

BASSAM CHEHABEDDINE

Hi Joel,

Yes i am able to connect to reports in sql developers and able to write direct query analysis but can't use the answers features (drag and drop)

Best Regards,

Bassam

Gianni Ceresa

Did you check in the logs what exact query the BI Server is sending to the database?

Maybe it isn't using the fully qualified name and therefore there is a wrong or missing schema in the query or things like that.

BASSAM CHEHABEDDINE

Hi Gianni,

thanks for your feedback..

I am using the same RPD file and the only thing i change is the user, when using the schema name and password it works perfectly while using the reports the answers are not working.

knowing that i can add tables to the RPD while using the reports user but i cant update the rowcount of the tables.

Best Regards,

Gianni Ceresa

Still, did you check the query the BI Server is sending to the database? (that feeling of repeating myself )

Run an analysis giving you the error and find the physical query the BI Server sent to the database generating the error (either in the manage session page or the log files).

What is this query like?

Why do I suggest that? Because ...

BASSAM CHEHABEDDINE wrote:... when using the schema name and password it works perfectly while using the reports the answers are not working.

In the query you will see exactly what schema name is used if any, and you will know what needs to be fixed to get the right queries generated.

BASSAM CHEHABEDDINE

Dear Gianni,

please check below

SET VARIABLE QUERY_SRC_CD='Report';SELECT 0 s_0, "FCUBS"."STTM_BRANCH"."ALLOW_CORPORATE_ACCESS" s_1 FROM "FCUBS" ORDER BY 2 ASC NULLS LAST FETCH FIRST 65001 ROWS ONLY State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000) State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS. (HY000) State: HY000. Code: 17001. [nQSError: 17001] Oracle Error code: 1017, message: ORA-01017: invalid username/password; logon denied at OCI call OCISessionBegin. (HY000) State: HY000. Code: 17014. [nQSError: 17014] Could not connect to Oracle database. (HY000)

Gianni Ceresa

And the physical query is?

That one is the logical query.

By the way: the Oracle database is telling you that you have another issue:

ORA-01017: invalid username/password; logon denied

BASSAM CHEHABEDDINE

thanks for your support, it seems it  is working now ... what i only did is adding the schema name in each table i added, such as when add STTM_CUSTOMER admin tool is not taking the schema name with it so i just renamed the tablet to be fcubs.sttm_customer.

thanks for your support,

Best Regards,

Bassam