Private Synonyms

building semantic layer
1

Last summer, I discovered that UDT would not recognize Oracle private synonyms in a schema other than the one being logged in to.

For example, the connection is set to log in to schema A. Schema B contains a synonym named TABLEA. In XI3, the Table Browser will show TABLEA under B. In BI4, TABLEA does not appear; I also get an error when attempting to Refresh Structure.

I opened an incident with SAP on 8/27. Over five months of back-and-forth with the engineer, I was given several solutions that did not work. I was then told that private synonyms should not be used, and that I should instead create a multi-source universe in IDT.

I pushed back, and finally was told that this has been resolved with BI4.1 SP02 Patch 2. It does require a config change, though. In oracle.sbo, the following line is required:

<Parameter Name="Include Synonyms">True</Parameter>

Hopefully this will help someone else…

Joe

joepeters :us: (BOB member since 2002-08-29)

2

Great info…thanks for sharing :+1:

Nick Daniels :uk: (BOB member since 2002-08-15)

3

Hi Joe,

I am following the instructions in SAP note 1575246 for adding this option for universe design. I have added entries to both sbo and stg files, but I cannot get the new dropdown to appear for Tables Strategies…I have re-started SIA…

Any tips?

UPDATE…

I forgot that I was using the universe design client on my workstation rather than my server, so I ad to make the changes locally as well…

When I try and pull up the table browser, I get an error in retrieving owners ORA-00923. Any thoughts? Does my DB account need additional authorities to run the SQL below?

The SQL that I have added to the oracle.sbo file is

<Strategy Name="Allow_Public_Synonym">
<Type>STRUCT</Type>
<SQL>
SELECT ' ', '|', ATC.OWNER, '|', ATC.TABLE_NAME,'|', ATC.COLUMN_NAME,'|', DECODE(SUBSTR(ATC.DATA_TYPE,1,1),'N','N','F','N','D','D','C'),'|', ATC.NULLABLE,'|'
FROM 
    ALL_TAB_COLUMNS ATC 
WHERE 
    ATC.OWNER NOT IN ('SYS','SYSTEM','ORDSYS', 'MDSYS', 'OLAPSYS', 'CTXSYS', 'WKSYS', 'WMSYS', 'XDB') 
UNION 
SELECT 
    DISTINCT ' ','|', ASYN.OWNER AS "OWNER", '|', ASYN.SYNONYM_NAME, '|', ATC.COLUMN_NAME, '|', DECODE(SUBSTR(ATC.DATA_TYPE,1,1),'N','N','F','N','D','D','C'),'|', ATC.NULLABLE,'|' 
FROM 
    SYS.ALL_SYNONYMS ASYN, 
    SYS.ALL_TAB_COLUMNS ATC 
WHERE 
    ASYN.TABLE_NAME=ATC.TABLE_NAME 
AND 
    ASYN.TABLE_OWNER NOT IN ('SYS','SYSTEM','ORDSYS', 'MDSYS', 'OLAPSYS', 'CTXSYS', 'WKSYS', 'WMSYS', 'XDB') 
AND 
   ASYN.TABLE_OWNER=ATC.OWNER 
AND
    ASYN.OWNER NOT IN ('PUBLIC') 
UNION 
SELECT 
    DISTINCT ' ','|', DECODE(ASYN.OWNER,'PUBLIC',USER,ASYN.OWNER) AS "OWNER", '|', ASYN.SYNONYM_NAME, '|', ATC.COLUMN_NAME, '|', DECODE(SUBSTR(ATC.DATA_TYPE,1,1),'N','N','F','N','D','D','C'),'|', ATC.NULLABLE,'|'
FROM 
    SYS.ALL_SYNONYMS ASYN, 
    SYS.ALL_TAB_COLUMNS ATC, 
    DUAL 
WHERE 
    ASYN.TABLE_NAME=ATC.TABLE_NAME 
AND
    ASYN.TABLE_OWNER NOT IN ('SYS','SYSTEM','ORDSYS', 'MDSYS', 'OLAPSYS', 'CTXSYS', 'WKSYS', 'WMSYS', 'XDB') 
AND
    ASYN.OWNER IN ('PUBLIC') 
AND 
    ASYN.TABLE_OWNER=ATC.OWNER 
AND
    ASYN.SYNONYM_NAME NOT IN (SELECT DISTINCT TABLE_NAME FROM USER_TAB_COLUMNS) 
ORDER BY 3,5
</SQL>
</Strategy>

jemstar :ireland: (BOB member since 2006-03-30)

4

Have you made this change on the server and locally?

Restarting the connection server should be enough to implement the changes.

https://bobj-board.org/t/235080

Mak 1 :uk: (BOB member since 2005-01-06)

5

Yes, I discovered this…but ran into another problem, see above…

jemstar :ireland: (BOB member since 2006-03-30)

6

I would suggest it does.
Can you try it in a query tool to confirm?

Mak 1 :uk: (BOB member since 2005-01-06)

7

“ORA-00923” is " FROM keyword not found where expected". Is that really what you got? That implies a syntax error in the SQL, rather than a permissions issue.

I did not have to modify oracle.stg at all – just oracle.sbo for that one parameter change.

Joe

joepeters :us: (BOB member since 2002-08-29)

8

Hi Mak, Joe

thanks for both coming back to me so quickly.

It looks like the approach outlined in SAP note 1575246 has been somewhat superceded

By reverting to the original oracle.stg file and retaining the parameter setting in oracle,sbo, I lost the dropdown on the Table Strategies.

However now, I can see the private synonyms in the table browser under the user who own the private synonyms and the remaining tables are available as usual…

This is a result.

Thank you again Gentlemen

jemstar :ireland: (BOB member since 2006-03-30)

9

Thank you.
True in oracle.sbo on the server solved the issue. You made my day.

mahal (BOB member since 2017-06-15)

10

Thank You!

That was right on :+1:

aetzcaim (BOB member since 2010-08-10)