I am trying to write an external strategy that will import the table names, column names, and their descriptions from the user_col_comments table in our data warehouse on Oracle 7.3. I have edited the Stora7en.txt file several times using the information from the Designer’s Guide but I either get no “candidate objects” or it stack dumps and closes BOI when I try to apply it via the universe wizard. The documentation I have found so far is skimpy. Does anyone have any information or examples of such a strategy that can help me out?
I am trying to write an external strategy that will import the table names, column names, and their descriptions from the user_col_comments table in our data warehouse on Oracle 7.3. I have edited the Stora7en.txt file several times using the information from the Designer’s Guide but I either get no “candidate objects” or it stack dumps and closes BOI when I try to apply it via the universe wizard. The documentation I have found so far is skimpy. Does anyone have any information or examples of such a strategy that can help me out?
I use this feature quite alot, and only in rare situations had problems with it. Never dumped…
What you should consider is that maybe not all users can access the dictionary views in oracle…
Walter
DI Walter Muellner
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna / Austria Tel: +43-1-8151456-12, Fax: +43-1-8151456-21 e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at
Linda,
Try running the SQL from SQLPlus and see if you receive the expected results.
The SQL that is required for the classes and objects section of the external strategy to pull in the descriptions is something like : 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,900),’|’,
‘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,900),’|’,
‘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
;
Best Regards,
Lori S. Furda
Sage Solutions, Inc. Lori_Sage@solution4u.com
Can anyone guide me on how to create external strategies and you need to go for the external strategies.
Chapter 4 of the Designers guide has some information on creating external strategies, however, you may find it is not 100% accurate!
The easiest way to create an external strategy is to use an existing strategy as the basis for your new one, which you will find in the STxxxxxx.prm file located in you RDBMS folder (the Oracle one is called stora7en.prm). Personally, I leave the supplied strategies intact and append my ‘creations’ to the end of this file.
Hi Listers ,
I am trying to read ERWIN Data Model directly into Universe .I know this can
done by External Strategies. Can anyone share the sample External Strategies
‘File’ (may be to read Erwin model or for Informatica ).
Sorry I don’t have the one for Erwin but here is the structure it should be
in and an Oracle example
Filename: METADATA_OBJECTS.txt (This is if you specify an external flat
file as source but the columns are the same for the SQL part)
Value 1: Table Name
Value 2: Column Name
Value 3: BusinessObjects Class Name
Value 4: BusinessObjects Object Name
Value 5: Column SQL (Table Name.Column Name)
Value 6: Datatype (C - Character, D - Date, N - Number, L - Long text, B -
blob). Default is C.
Value 7: Help text for the object
Value 8: Type of object (D - dimension, M - measure, I - detail). Default
is D.
Or change the following files
In Ora7en.prm
STG=stora7en
In Stora7en.txt
[STRATEGY]
TYPE=STRUCT
NAME= Struct : Tables
[SQL]
SQL=SELECT
OWNER,‘|’,
O.OWNER,‘|’,
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,ALL_OBJECTS O
WHERE
U1.table_name=U2.table_name
and U1.column_name=U2.column_name
and O.object_name=U1.table_name
UNION
SELECT
O.OWNER,‘|’,
O.OWNER,‘|’,
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
;
[HELP]
HELP= External struct.
Good luck,
Klaus Binder
ebiexperts
The builders of Business Objects Full Client auditing
Your going to Forward Engineer your data model first into your database …
Then use an external strategy to import your metadata and joins into the
Universe?
Or do you want to do something like File Open an ERWin model directly into
Business Objects Designer? Basically skip the Database import part?
Dave ,
You are right .I am done with Forward Engineering and trying to create a
external strategy. I can do manual design but I think it is worth trying to
import the Erwin model directly into BO Designer…
Thanks
Shivaji
Dave Wrote :
Your going to Forward Engineer your data model first into your database …
Then use an external strategy to import your metadata and joins into the
Universe?
Or do you want to do something like File Open an ERWin model directly into
Business Objects Designer? Basically skip the Database import part?