Populating Universes w/ERWin metadata

building semantic layer
1

Hi All,

Our DB design team uses Platinum ERWin 3.5 as a data modeling tool. We would like to pull in at least the Class and Object descriptions from the ERWin metadata so that we don’t have to re-type them in BusObj.

I know there is a way to do this using an external strategy, but no one including Tech Support has been able to tell me how to do so. I know it involves exporting from ERWin as .dbf files and writing SQL to pull into the universe.

Does anyone have specifics on how to do this? What I really need is to know the proper source (ERWin) table and destination (BO Repository) table for the descriptions.

If anyone has details on this, would you please share? We are using BO 4.1.3, SQLServer 6.5 and NT 4.0.

Regards,
Randy Scarborough
rscarborough@sarofim.com

Listserv Archives (BOB member since 2002-06-25)

2

We recently created several universes from ERWIN models against an Oracle database. The instructions for feeding the metadata shouldn’t vary too much, however. Most of this information can be found in the Designer’s guide, pp 75 through 82.

The easiest method involves creating a flat file (text) extract from ERWIN. If you prefer, you can create dBase files and convert them to text. The process involves two steps - creating the flat file, then updating the BusinessObjects strategy file that points to your flat file.

Step 1: Creating the flat file extract

The structure of each record is very specific. You must provide the following information for each line. Use tabs to seperate each value. You’ll need seperate flat files for objects, joins, and tables. The name of the file is arbitrary.

The flat file layout for an object strategy would be:

Filename: METADATA_OBJECTS.txt

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.

For example:

ORDER_HEADER BASE_QTY Order Header Base Quantity ORDER_HEADER.BASE_QTY N The quantity of material consumed. D

Part 2: Referencing your flat file

In Oracle, the file to update was named “STORA7EN.TXT”. I think the file you’ll need to update for MS SQL Server 6.5 is “STSRVEN.TXT” or “STODBCEN.TXT”. I’m guessing at the file name, here, since we did not work on SQL Server. This would work if you were using ODBC to connect to your database.

Once you find the file to update, include the following section to add your strategy to the list of BusinessObjects strategies:

[STRATEGY]
TYPE=OBJECT
NAME= Custom Object Strategy

[FILE]
NAME=C:\METADATA\METADATA_OBJECTS.txt

[HELP]
HELP=Custom Object strategy generated from ERWIN.

I hope this helps. It sure accelerated our development efforts. Remember, you will need to repeat the process to import joins and table definitions from ERWIN.

Regards,

Alan Mayer
amayer@islink.com
Integra Solutions, Inc.

Listserv Archives (BOB member since 2002-06-25)

3

RScarborough@SAROFIM.COM wrote:

I know there is a way to do this using an external strategy, but no one including Tech Support has been able to tell me how to do so. I know it involves exporting from ERWin as .dbf files and writing SQL to pull into the universe.
There should be another way to do this:

  1. Import the ERWin model with ModelMart into the ModelMart-Repository which is relational database. 2. Create a SQL-based strategy to query the ModelMart-tables e.g. [STRATEGY]
    TYPE=OBJECT
    NAME=ERWIN Object Strategy

[SQL]
SQL=SELECT …
[CONNECTION]
NAME=ModelMart_Connection … which is the name of an BusinessObjects Connection

So, you don’t need the flat file. However, I haven’t done this with ERWin /ModelMart but I’ve implemented it for PowerDesigner DataArchitect/Metaworks.

the proper source (ERWin) table and destination (BO Repository) table for >>the descriptions.
You probably don’t wanna know what destination tables there are in the BO-Repository :slight_smile: Querying the BO-Repository-tables is hard enough so inserting is a pain. For example, the descriptions of classes/objects are stored in slices of 256 bytes. Let BO do this job using external strategies…

Regards Patrick

Listserv Archives (BOB member since 2002-06-25)

4

X-cc: “RScarborough@SAROFIM.COM” RScarborough@SAROFIM.COM

We’re also in the process of trying to automate the population of the “Help-Text”
via CA/Platinum’s ERWin or CA/Platinum’s Repository. Anyone else had any luck?

I’d be curious to hear how other organizations have chosed to handle this. Especially if you’ve built something turnkey.

Also: Can the Business Objects Developer’s Suite be used to update the repository?

TIA,

Christopher J. Pohl
Mellon Financial Corporation
Corporate Information Systems Group/SED

Our DB design team uses Platinum ERWin 3.5 as a data modeling tool. We
would
like to pull in at least the Class and Object descriptions from the ERWin metadata so that we don’t have to re-type them in BusObj.

I know there is a way to do this using an external strategy, but no one including Tech Support has been able to tell me how to do so. I know it involves exporting from ERWin as .dbf files and writing SQL to pull into
the
universe.

Listserv Archives (BOB member since 2002-06-25)

5

We have built an application(VB access) to update and generate documentation from a repository.

We are editing list of hidden Object, joint etc, documentation of the universe

Metadata On univers is export on a repository, only used for this.

We also use this repository for make change in the table descriptions

Thierry DOUZAL
Consultant DÈcisionnel
MIDI Partners
75, voie du TOEC
31076 Toulouse Cedex - FRANCE

TÈl. +33 (0)5 61 15 84 55

Listserv Archives (BOB member since 2002-06-25)

6

X-cc: “tdouzal@MIDIPARTNERS.FR” tdouzal@MIDIPARTNERS.FR

I’d love to hear how you use the table to populate changes in the table descriptions.
And what platform your Repository is located on. Are your end users access the help text via BO or the VB Access database?

Thanks,

Christopher J. Pohl
Mellon Financial Corporation
Corporate Information Systems Group/SED

From: Thierry Douzal [SMTP:tdouzal@MIDIPARTNERS.FR]

We have built an application(VB access) to update and generate documentation from a repository.

We are editing list of hidden Object, joint etc, documentation of the universe

Metadata On univers is export on a repository, only used for this.

We also use this repository for make change in the table descriptions

Thierry DOUZAL
Consultant DÈcisionnel

We’re also in the process of trying to automate the population of the “Help-Text” via CA/Platinum’s ERWin or CA/Platinum’s Repository. Anyone else had any
luck?

I’d be curious to hear how other organizations have chosed to handle this. Especially if you’ve built something turnkey.

Also: Can the Business Objects Developer’s Suite be used to update the repository?

TIA,

Christopher J. Pohl
Mellon Financial Corporation
Corporate Information Systems Group/SED

Listserv Archives (BOB member since 2002-06-25)

7

Q1) All the fields ( with the Flag H) is updated in a query, you can use the metadata of the SGBD
Q2 )SGBD=Access
Q3 )user -->via BO, the access database is only used by the Datawarehouse manager

the most difficult is to retrive the shema of the repository.

Listserv Archives (BOB member since 2002-06-25)

8

X-cc: “tdouzal@MIDIPARTNERS.FR” tdouzal@MIDIPARTNERS.FR

Thanks for the response.
Our Repository is located on MVS/DB2 so we’re going to have to develop a slightly different approach to populating the “Help Text” We did find the managero universe and some of the repository documentation on the BO install disks quite helpful in figuring out the schema of the repository.

Regards,

Christopher J. Pohl
Mellon Financial Corporation
Corporate Information Systems Group/SED

From: Thierry Douzal [SMTP:tdouzal@MIDIPARTNERS.FR]

Q1) All the fields ( with the Flag H) is updated in a query, you can use the metadata of the SGBD
Q2 )SGBD=Access
Q3 )user -->via BO, the access database is only used by the Datawarehouse manager

the most difficult is to retrive the shema of the repository.

Listserv Archives (BOB member since 2002-06-25)

9

Hi,
I had a similar requirement for my project. we are using Powerdesigner as the modeling tool and Business Objects XI version 2. we are required to add Business Names, Column Names and desciption to our universe. My queries are as following:
1.0 how do we access external strategies option. was going through Universe-parameters-strategies. I asm getting only built in strategies as an option.
shall be grateful for any help in this regard.

apoorv (BOB member since 2005-08-30)