Incremental Server Generation - Designer

We are evaluating Server Generation technologies and trying to find out if Designer supports incremental releases of the physical database model.
We don't want to generate the complete DDL every time, only what has changed.
Also, will it create any meta data, apert from the standard Oracle meta data to define what tables and Views are supportting which ERD Entities from the Database Design Transformation.
Thanks
Pat 

1. Yes, Designer can create "alter table" etc. scripts. I don't really remember how. It's a long time ago I work with Designer.
2.What else do you want apart from the standard Oracle meta data? Designer keeps track of the entity-table and attribute-column mapping. 

Generating incremental scripts is done like this:
- in the generate dialog-box choose "database" in the "Target for Generation" section.
- enter username/password#database of the database you want incremental scripts for.
- enter "file Prefix" and directory. Remember this, because this is where you'll find the increment scripts after generation.
- click "start".
- when Designer is ready a dialog will appear with 4 options. At this point Designer has created the increment-scripts but has NOT executed them yet. In the dialog you can choose to execute them immediately... or you can click cancel: the scripts will then be available in the directory you specified before. They can then be executed at a later time using SQL*Plus.
Hope this helps.
Tim 

I need to know the transformation mapping between Entity and Tables.
We are tring to generate EJB definitions using the logical model, not the database model. To do this we need to generate against views that represent the Entities.
Does the Database Transformer store these mappings and create the logical views. 

Thanks Tim, this is just what I need.
As long as designer is kept as the master model, then this should always work.

Related

How to get "Synchronize with database" to compare/generate changes for database 'contexts'?

Hi Using SDDM 3.3. How does one get to synchronise changes/differences in 'contexts' (defined in physical model - Oracle DB)? I have tried to synchronize both ways, i.e. model to db and db to model, but never does it show the DDL necessary to create the missing context. Yes, Context is selected/ticked under preferences for Oracle DB synchronization preferences. Thank you & Regards
Hi, DDL is not generated for context, Definition in physical model is updated with definition from database. Philip
Hi Philip If I use the "generate DDL" option from the toolbar, it does generate DDL for the context I have in the physical model.  I can select/deselect them in the DDL Generation Options window. Why then would it not generate DDL when comparing the model with the database, i.e. using the "synchronise data dictionary to model" option?  It successfully generates a 'patch'/change script for tables, views, packages, etc.? Thank you & Regards PS.  I am asking for a way to get it to generate me a complete 'patch'/change script.
Hi Hans,
Why then would it not generate DDL when comparing the model with the database, i.e. using the "synchronise data dictionary to model" option?
It's not implemented. I also want Data Modeler to be able to generate complete patch and that for all supported databases not only for Oracle .We are small team and we can act according our resources. Philip
Hi Philip I did not mean to say you are bad or anything.  Just trying to understand the difference in behaviour between the two options/functions. I also work in a small team and can appreciate the need to juggle work amongst few resources.  (That is why I cannot even test/use SDDM extensively and provide as much feedback as I would want to.) So how do I go about logging the above as an outstanding item (enhancement request) then? Thank you & Regards
I logged enhancement request for that Philip
Thank you, Philip. 

Clarification on Repository and Designer

Hi
These are my understandings on the oracle repository and oracle designer.
An oracle repository consists data about data and it is stored in the database and we inturn use the oracle designer to maintain the repository.Pl confirm me whether my understanding is correct.
My other question is why should we maintain a repository.What is the necessity to maintain information on metadata?
Thanks & Regards
Suji Iyer 
Fair question, and it goes to the heart of why we Designer users prefer Designer to the design tools that are built into JDeveloper. You see, while JDeveloper can draw pretty class diagrams, database diagrams, and several of the other UML based diagrams, it has no repository behind it - only XML files. Here are my top five reasons that a metadata repository is IMPORTANT:
1. You can maintain the relationships between objects: Modules AND the tables the modules reference. Entities and the tables that are the physical implementation of the entities. Relationships that go both ways, even many to many.
2. If I change something, the change gets filtered down to everything that uses that thing. For instance, if I change a column name, all of the modules that use that column IMMEDIATELY get the change. That's because they don't have a copy of the column information with the old data, they have a reference to the column I just changed.
3. Easy reporting, cross referenced any way I need it.
4. All members of the team can immediately see the changes I've made, as soon as I commit the changes. Yes, I know that I could put the XML files on a shared file system, or in a version control system, and we could share them that way, but my experience has been that this works better.
5. I don't have a versioned repository, but I know people who do, and they love the fact that a Designer versioned repository can version table definitions. I have versioned the DDL files that create and alter tables, and it isn't the same thing. This is mostly because you use a different DDL command to change a table than you do to create it the first time. You would have to run the create command, then each alter command in the correct order to get an identical result.

Import model from OWB repository

We have an OWB repository with several 100 tables, mappings, etc. what we try to import but SQL Modeler seems to import nothing although it gives no errors.
We've found that before importing all the OWB environment variables must be set.
Which we did.
Then it seems to connect to the repository, do some processing and returns but without any changes in the model.
Did anyone import model from OWB successfully?
We are using version 10.2 OWB and database.
Bye,
Gabor 
Gabor,
Can you post the content of the log file (installation directory, log directory, logfile).
Hopefully we can see in the logfile what went wrong.
Kind regards,
René De Vleeschauwer
SQL Developer Data Modeling team 
Hi Gabor,
thank you for trying Oracle Data Modeling.
About your question - ODM doesn't import these objects - tables, mappings, dimensions, cubes ....
OWB has two kind of objects:
1) predefined (listed above and more) - these objects are used in OWB built in (or regular) functionality
2) OWB has metadata facilities - you can define metadata about your data (user defined objects - UDO). As example - Oracle has table and snapshot as different kind of objects. In oWB you can define class "YourTable" that can be defined using either list of columns or query that defines it as extract or summary of other tables. You can define your set of classes and also can define their responsibility. In terms of OWB - you have classes, associations between them and their roles (modules, folders, first class objects (FCO) and second class objects (SCO)). UDO can have associations with predefined (OWB) objects.
3) You can create instances of these classes after you create your User Defined Objects (metadata) in OWB repository. UDO are created using OMB scripts. Instances can be created using OWB user interface (UI) or using scripts.
What can be done with Oracle Data Modeling:
1) you can define your UDOs using dedicated diagrammer in ODM - your classes with their properties, associations and roles. After that you can create your UDO directly in OWB repository or OMB script can be generated.
2) these definitions can be imported from OWB repository, changed and updated back in repository.
You can use OWB UI or OMB scripts to create instances.
At the end you can do Lineage and impact analysis using OWB.
You can look at OWB resources for more information about UDO.
Philip 
Hi
OSDM 1.5.1 build 518 Wont even connect to an OWB 10.1.0.5 build.
We have no plans on going to 10.2 anytime soon so I am keen to see when it can connect to this version of OWB.
No reasonable message returned from the dialog apart from unable to connect to repository ;)
Oh and there was no section for import owb meta data in the feedback app too.
Regards
Richard 
Hi I was just reading Phillips post.
So it cant read OWB to import any of the Relational models? Or OLAP models?
ERM I kinda figured that um you would want this to do um you know modelling...:)
I guess I could point out that I use designer for current modelling to drive our OWB environment.
I can import into odm from designer (ill get around to trying that soonish) and possilby push into owb (depending on when it can connect to 10.1.5)
Great I can see the Point of UDO etc but seriously there has to be more functionality than that. Most of your users will never go near UDO they dont understand it and probably have no need too.
Regards,
Richard 
Hi
OK ive imported from Designer into OSDM just fine.
However things I dont like so far ...;)
The properties palete can you make it like owb in that when i open it and change context in the browser it updates? I dont want to have to keep going show properties.
Pushing the OK button does not appear to do anything the properties window stays open so how is it different to apply?
Things I dont like... the number of clicks to see information ( no hotkeys or shortcuts :( )
No drag n drop columns from 1 table to another. No right click on browser nodes to create keys etc No CTL drop no SHIFT drop
Why cant I reorder columns by moving them in the browser? Then I could move them visually. This also applies to property paletes if I have to go to the effort of highlighting a current row then why cant I just grab it and move it?
Appears to be no ability to set common properties over a number of attributes. If I select a number of columns I cant pull up a property pallete to set a common attribute on them.
I could not get the enginer to logical model to enable :(
Otherwise looks promising
Regards
Richard. 
Thank you for looking at SQL Developer Data Modeling.
We will create an enhancement request for a more informative message when you can't create a connection to OWB.
Kind regards,
René De Vleeschauwer
SQL Developer Data Modeling team 
OK ive imported from Designer into OSDM just fine.
Good. Glad this works.
However things I dont like so far ...;)
The properties palete can you make it like owb in that when i open it and change context in the browser it updates? I dont want to have to keep going show properties.
In future releases User Interface will be adresses to be more intuitive and to be more SQL developer alike
Pushing the OK button does not appear to do anything the properties window stays open so how is it different to apply?
Sorry, but we don't understand. Pushing OK should work and should close the property window.
Things I dont like... the number of clicks to see information ( no hotkeys or shortcuts  )
Agree. In future releases User Interface will be adresses to be more intuitive and to be more SQL developer alike
Next Early Adopter will have new dialog for entities and tables
No drag n drop columns from 1 table to another. No right click on browser nodes to create keys etc No CTL drop no SHIFT drop
You can drag columns
an enhancement request will be created
Why cant I reorder columns by moving them in the browser? Then I could move them visually. This also applies to property paletes if I have to go to the effort of highlighting a current row then why cant I just grab it and move it?
You can reorder columns/attributes by the "up" and "down" arrows after you highlighted the column or attribute in their respective property window.
an enhancement request will be created for doing this from the browser and the grabbing
Appears to be no ability to set common properties over a number of attributes. If I select a number of columns I cant pull up a property pallete to set a common attribute on them.
an enhancement request will be created
I could not get the enginer to logical model to enable
Should work. when you have the relational model, use the engineering to logical model icon (2 red arrows) or the design menu option and do engineer to logical model
Otherwise looks promising
Thank you!!
Kind regards,
René De Vleeschauwer
SQL Developer Data Modeling team 
richard.harrison1#corrections.govt.nz wrote:
No drag n drop columns from 1 table to another.Hi Richard,
you can copy columns from browser and paste them into another table on diagram.
Regards,
Philip 
richard.harrison1#corrections.govt.nz wrote:
Hi
OSDM 1.5.1 build 518 Wont even connect to an OWB 10.1.0.5 build.
We have no plans on going to 10.2 anytime soon so I am keen to see when it can connect to this version of OWB.We rely on OWB 10.2 to connect to repository and to extract UDO metadata - public API some scripts. UDO definitions can be exported to OMB script and you can use your OWB installation to create them into repository.
"Oh and there was no section for import owb meta data in the feedback app too."
Very suspicious - may be you are right about usage of UDOs and we have to remove UDO related functionality :).
Thanks for feedback,
Philip 
Hi All
OH Dear. I wanted to continue my road test but I am unable to open the model i created yesterday :(
I did a save all at the end of my play session. However trying to open some of it says old version yada yada (how can that be ? ;) )
I cant seem to open the whole state of my model environment (as i saved it) however I am expecting it to work like designer in that i have a workspace and every thing loads up nicely at all functional areas but this does not seem so :(
OK there appears to be a master xml doc that gives tries to give me what I am expecting but it generates errors. Unable to load all objects see the log file.
WHERE IS THE LOG FILE? WHATS IT CALLED? Add the info to the dialog you have it in the app cough it up please.
Better yet add button show log file ;) Think about adding button with email log to Oracle feature :P
Some general questions now. How do you plan to do security? Versioning? Branching? Multi Users?
Viewers, Tinkerers, Admins etc. Ability to cast a major version of the model (configuration idea?)
Typical SDLC handling of a model in its various states between internal and external vendors?
Rollback, a problem occurs with a build and you need to pull and revert to pervious known good build?
Ability to record which version of a Model is in which cycle of the SDLC
How do you plan to make it Enterprise worthy?
Regards
Richard. 
richard.harrison1#corrections.govt.nz wrote:
However trying to open some of it says old version yada yada (how can that be ? ;) )Richard,
in order to have such message you need two versions with change in persistence. We have published only one version of the product.
Really how can that be?
Philip 
Hi Philip
Simple really I just tried to load something you did not expect me to :)
Instead of loading the main xml doc at the root level of model folders i jumped into one of the sub folders and loaded the first xml file I saw which gives this error.
Regards,
Richard. 
Hi All
Another thing I have noticed is the way the create connection dialogs vary depending on what your doing.
Conn to DB
Name
Host
Port
SID
USER
PASSWORD
Cant enter password unless choose save password.
Conn to OWB
Username
Password
Host
Port
Service Name
Repository
Just an observation but I would expect them to be consistent in the way they are layed out.
Regards,
Richard. 
Hi Philip,
Do you know of any plans to include these object in the OWB import/export?
Our OWB developer guys do not use UDOs only the 'plain' database objects (tables, views, etc.)
For us the most valuable part of using ODM would be to have everything in one place.
I'm a little bit disappointed that it is not possible with this version.
Bye,
Gabor

Capture schema design

Hi,
I've been using Oracle SQL Data Modeler to generate a physical diagram of a schema we have had to develop quickly. Now I need to get the objects into Oracle designer but when I choose "Generate" -> Capture Design of Server Model" I'm not able to enter enough details to enable the "Start" button. I figure it's the "Capture Implementations into" field I need to populate?
Under Global Shared WorkArea I already have an Application folder HPS. Normally we would just create table definitons manually under this folder so I think there is enough there to import into , but how do I import into it?
Is it because I am logged into Designer as HPS?
Designer is quite confusing. Is there any place with lots of examples and screenshots on how to use it. 
You could generate your design from SQL Developer Data Modeler inside a schema in the database. And then capture the design of back into designer. 
To capture a database design, you not only need the container, but also a database.
On the DB Admin tab, check if there is a database associated with your container HPS. This database will be the "Capture Implementations into" field. 
Okay thanks for that.
I hope it doesn't actually create a database/schema. We tend to have a whole bunch of small applications/schemas on a database (right term?). I'm surprised I have to create a database in Designer rather than be able to query for it, but that's got the job. 
I hope it doesn't actually create a database/schema. Just like a table definition doesn't create a table, defining a database doesn't create a database, until you generate and run the scripts.
I'm surprised I have to create a database in DesignerYou define the technical structure of the database here. When you generate a table script from Designer, you also need to know table spaces to use for data and indexes, grants to roles or users, storage parameters etc. These are properties of a database, so Designer needs to know for which database to generate script.
When capturing a schema design, Designer needs to know for which database it is capturing the design, so it can assign the correct properties. When using several schemas in one database, you also have to assign the tables to the correct schema.

Storage Clauses and using Designer

Hi there,
I am doing another OWB course and the following questions were asked:
1. How do I manage the storage clause on the DDL scripts in OWB without manually editing the scripts? (This one has been een around for a while)
2. Would it be possible to design the objects in Designer & pull in the definitions - with storage clauses?
3. If you follow this route (2), does Designer allow the creation of dimensions (I beleive the previous version did not)?
4. If you cannot create dimensions in Designer, would you be able to import the underlying table and define only the dimension (hierarchy) structure on it?
5. Previous versions of OWB could not import dimensions - is there a way around this problem?
Thanks in advance!
Marlene Raats 
Marlene,
See the answers below:
Hi there,
I am doing another OWB course and the following questions were asked:
1. How do I manage the storage clause on the DDL scripts in OWB without manually editing the scripts? (This one has been een around for a while) At the moment (version 9.0.2) there is a limited functionality on this - for example, you can define the tablespace and some other parameters but not a complex storage clause. You are limited to whatever you can define during the configuration phase of the object. If you have particularly complex storage clauses, the best way would be to deploy them separately from OWB and then import their definition into OWB. Object deployment is a one-time process.
2. Would it be possible to design the objects in Designer & pull in the definitions - with storage clauses? You can import the table definitions, but I am afraid that the storage clauses will not be imported completely (see coment above).
3. If you follow this route (2), does Designer allow the creation of dimensions (I beleive the previous version did not)? Dimensional models have to be designed in OWB, current version of designer does not support them.
4. If you cannot create dimensions in Designer, would you be able to import the underlying table and define only the dimension (hierarchy) structure on it? Yes, it is possible to import a dimension table with an already defined hierarchy structure, and then define the hierarchy inside OWB.
5. Previous versions of OWB could not import dimensions - is there a way around this problem?
Thanks in advance!As mentioned, it is possible to import dimensional tables but not dimensional objects at the moment.
Regards:
Igor
Marlene Raats 
I can provide some more information on questions 3-5:
3. In Designer you can register a table being a dimension. However, Designer does not enable you to define the levels, level attributes and hierarchies. As a result... in Designer this property is only there for documenation purposes. You create dimensions in Warehouse Builder.
4. In the current version of Warehouse Builder unfortunately you can only import the table structure in which case you get a table object inside Warehouse Builder. You cannot define a hierarchy on top of this table structure... but we are currently rebuilding the dimensional modeling area which will eventually enable you to do this. Unfortunately it will at least not be available until summer 2003 (don't have any release dates yet).
5. At the moment there is not really a way around the lack of support to import dimensions... unless you want to explore scripting to access the repository through the back end (generate a script yourself based on existing database objects).
Hope this helps,
Mark. 
I have a much better idea. Add this dimension modelling area plus the mapping modelling area to Oracle Designer and Oracle will have a far far better datawarehouse design and development tool than Oracle Warehouse Builder will ever be.
If they added the functionality of OWB to Designer two years ago, instead of developing OWB, we won't have to discuss about these kind of problems and can spent more time to actually design and develop a datawarehouse.
Maybe that somebody can tell me why Oracle still is trying to make OWB work? It isn't even capable to create datawarehouse example in the demo schema SH that comes with the Oracle 9i database.
Is it possible now to import tables (flagged as dimensions) from Oracle Designer into OWB as dimension objects?
Alternatively, is it possible to copy attributes from a table object into a dimension object? Any workarounds so I do not have to enter all of the table object information into the Dimension and Fact object editors to recreate them?
I can provide some more information on questions
3-5:
3. In Designer you can register a table being a
dimension. However, Designer does not enable you to
define the levels, level attributes and hierarchies.
As a result... in Designer this property is only
there for documenation purposes. You create
dimensions in Warehouse Builder.
4. In the current version of Warehouse Builder
unfortunately you can only import the table structure
in which case you get a table object inside Warehouse
Builder. You cannot define a hierarchy on top of this
table structure... but we are currently rebuilding
the dimensional modeling area which will eventually
enable you to do this. Unfortunately it will at least
not be available until summer 2003 (don't have any
release dates yet).
5. At the moment there is not really a way around the
lack of support to import dimensions... unless you
want to explore scripting to access the repository
through the back end (generate a script yourself
based on existing database objects).
Hope this helps,
Mark.

Categories

Resources