BusinessObjects Board

automate descriptions and get from db

building semantic layer
#1

Hi All,

Instead of manually entering description about an object, is it possible to automate this by getting description from an excel file or a database.

If yes, how?

ajq

anushajq (BOB member since 2005-02-22)

getting metadata information
#2

This utility is waiting for you. :slight_smile:

Jansi :india: (BOB member since 2008-05-12)

#3

Thanks for this. However, I am not looking to create my own objects but to automatically extract universe descriptions from the oracle system tables…

anushajq (BOB member since 2005-02-22)

#4

You can’t; that’s why utilities like the one above have been created.

#5

Actually, you can. If the tables and columns have help text defined by your DBA and/or database architect, then they should come in with the proper strategy. It’s here in the strategy SQL:

<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>
	</Strategy>

However, this will only work upon initial import. If the help comments are later updated, they will not pass through to the universe, it will become a manual process at that point.

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

#6

That assumes an Oracle DB too doesn’t it Dave?

#7

Yes

It will only work on the first import. If the comment values are added after the tables are included in the universe, then the universe does not get updated. But it’s supposed to work for the initial import. I say “supposed to” because I have never tried it to confirm it. :slight_smile:

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

#8

Thanks Dave.So once you’ve created the strategy file and how do I use this? What if the database is not up-to-date or empty? Can I use an excel file?

anushajq (BOB member since 2005-02-22)

#9

There was a link to a utility hosted here in BOB’s Downloads entered in an earlier post. That’s your best bet.

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

#10

Hi,

How does this script works or how make use of Oracel.stg? Because this script is part of that file. My DB is oracle and I am connecting universe thru oracle client. Selecting either of the strategies (Built-in and external), object descriptions are not populated in universe. I am using BO R3.1 SP3

Please help me where I am missing.

Regards,
Chinnu

chinnu6666 :india: (BOB member since 2007-06-15)