Question on Insert Tables in universe

building semantic layer
1

Hi ALL,
This is very crucial for us as we are implementing new DB .Our universe is set on old DB (say ABC) now they have new DB (Say XYZ) there are couple of tables that will be available in New DB (Deleted in old DB) they created synonums for those in old ABC and asking us to access from there.

My Question :
I know linking tables accross DB ,but Can I access the new tables (from new DB XYZ) in my existing universe(which is on ABC, DB).

They need an answer ASAP.Please help me.Thanks.

madhu@P (BOB member since 2008-08-04)

2

Since there are synonyms being created for those newly created tables in old DB, you should be able to access them. Have you tried it out
.

haider :es: (BOB member since 2005-07-18)

3

Hi Haider, Thanks a lot for the reply.
No ,I haven’t tried,but the synonium is under my schema ,but my universe is build in DB schema(common schema) then in that case how I have to work arround.

Thanks a ton.

madhu@P (BOB member since 2008-08-04)

4

Is the old DB schema your schema which you are stating, if yes then you should be able to access them from old schema(common schema).
Check this out directly in SQL plus(Oracle) or in query analyzer(SQL server)
.

haider :es: (BOB member since 2005-07-18)

5

No, Old DataBase is “XYZ” and xyz schema,but my schema is “MadhuP”, I can access new table in my schema in SQL editor,but i donno in xyz schema .Thanks.

madhu@P (BOB member since 2008-08-04)

6

Hi, Now the synonyms are under XYZ schema but Im able to see only tables in “Inser Tables” browser , I canot see the synonyms…

Can any one give a hand on this…Thanks

madhu@P (BOB member since 2008-08-04)

7

Hi All ,
Any inputs…

I cannot able to see the Synonym in my universe insert tables ,synonym was created in my DB schema as public .Does any one have an idea how to use synonyms in the universe.Thanks.

madhu@P (BOB member since 2008-08-04)

8

It has been a while since I checked, but I believe you need private synonyms, those owned by the account used in the connection. You could probably change that by altering the SQL in the strategy file… but I can’t remember which one that is either at the moment. There is a file that contains the SQL used to retrieve the table names. It does a union of my private tables + my private synonyms.

Dave Rathbun :us: (BOB member since 2002-06-06)

9

Hi Dave, Thank You for the input, well I will ask for a private synonym , so as per you once that becomes a private synonym in my DataBase connection .Then ,when I open the Universe and click on “Table Browser” can I be able to see that new table ?

Thanks a lot :slight_smile:

madhu@P (BOB member since 2008-08-04)

10

That is my understanding, yes.

Now that I have a bit more time, I did the research to find the file. If you look in your ConnectionServer folder you should find sub-folders for each database. For this example, I will use Oracle. In the Oracle folder is a file named oracle.stg. The “.stg” is short for strategy. Open that file in a text editor (Notepad will do, do not use Word or any sort of document processor). You will find this code:

	<Strategy Name="Classes_and_Objects">
		<Type>OBJECT</Type>
		<SQL>SELECT 
       U1.table_name,'|', 
       U1.column_name,'|', 
       translate(initcap(U1.table_name),'_',' '),'|', 
       translate(initcap(U1.column_name),'_',' '),'|', 
       U1.table_name||'.'||U1.column_name,'|', 
       '  ','|', 
       decode(SUBSTR(U1.DATA_TYPE,1,1),'N','N','F','N','D','D','C'),'|', 
       SUBSTR(U2.comments,1,474),'|', 
       'O','|' 
FROM USER_TAB_COLUMNS U1,USER_COL_COMMENTS U2 
WHERE 
    U1.table_name=U2.table_name 
and U1.column_name=U2.column_name 
UNION 
SELECT 
       S.SYNONYM_NAME,'|', 
       U1.column_name,'|', 
       translate(initcap(S.SYNONYM_NAME),'_',' '),'|', 
       translate(initcap(U1.column_name),'_',' '),'|', 
       S.SYNONYM_NAME||'.'||U1.column_name,'|', 
       '  ','|', 
       decode(SUBSTR(U1.DATA_TYPE,1,1),'N','N','F','N','D','D','C'),'|', 
       SUBSTR(U2.comments,1,474),'|', 
       'O','|' 
FROM ALL_TAB_COLUMNS U1, ALL_COL_COMMENTS U2, ALL_OBJECTS O, USER_SYNONYMS S 
WHERE 
      S.table_owner=O.owner 
AND   S.table_name=O.object_name 
AND   (O.OBJECT_TYPE='TABLE' OR O.OBJECT_TYPE='VIEW') 
AND   O.owner=U1.owner 
AND   O.object_name=U1.table_name 
AND   U1.owner=U2.owner 
AND   U1.table_name=U2.table_name 
AND   U1.column_name=U2.column_name</SQL>

See where it references USER_SYNONYMS? I believe that in the past I have changed that to ALL_SYNONYMS in order to get a list of every public synonym on the database.

Dave Rathbun :us: (BOB member since 2002-06-06)

11

Thank you so much Dave, I will let you know once I do this.

Edit: I found USER_SYNONYMS in 4 places, do I have to change in all the places?

Thanks a lot.

madhu@P (BOB member since 2008-08-04)

12

I would… but I didn’t test it. Just make a backup copy of the file before you do the edits, and you will most likely want to restart Designer after making the changes.

Dave Rathbun :us: (BOB member since 2002-06-06)

13

Hi Dave ,it worked …

But one more prob came up when I say insert that table , but I canot see the columns in it, it shows as blank table :frowning:

I dnt know what to do…

Thanks.

madhu@P (BOB member since 2008-08-04)

14

Hi ALL ,
I got a solution, I wanna share with you all ,if the columns doesnt appear on designer for a synonym , create a derived
table and select each column from the synonym in that derived table ,parse it and you can see all columns and you can use that table as
a regular table.

madhu@P (BOB member since 2008-08-04)

15

madhu@P,

[quote:3463192052=“madhu@P”]create a derived table and select each column from the synonym in that derived table.
[/quote]

I suggest

select * from synonym

does the same. You don’t have to specify each column. :mrgreen:

Hope this helps,
Johan

JdVries :netherlands: (BOB member since 2006-02-06)

16

Hi,
Thank you, well I did that , since I have got a CLOB column in that table I got an error message . So I had to do a select each column :frowning:

madhu@P (BOB member since 2008-08-04)

17

If it is of any help to anyone:

I tried the suggested changes in this post (and others) AND also some additional changes indicated in a KBASE article on SAPs tech support and was not able to get this to work.

I made the suggested changes to the oracle.stg file and it seemed that the change was not recognized. I changed the strategy in the universe parameters according to SAP tech support and still no success. I changed the oracle.prm file so that OWNER was set to N and Designer never came back with a list.

According to SAP tech support because we are using Oracle 10g, the recommended solution for displaying public synonyms does not work. Well it DOES work but takes way too long. WAAAYYYY too long. :blue:

According to SAP tech support, Oracle 10g has more stuff to list in the Insert Tables popup box and therefore takes an excessively long time to populate the list. So long in fact that this is not conducive to actually using the suggested change. :hb:

Therefore, the real, usable solution is to create PRIVATE synonyms to use when designing the universe and then drop the private synonyms when done with creating the universe. The public synonyms will then be used when creating documents.

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

18

HI Dave\Madhu,

Could you help me in where to find ConnectionServer folder.
I have a requirement to create Universe out of Synonyms(SQL Server 2008).

Thanks

BOB(J) The Builder :us: (BOB member since 2009-10-05)