Clarification on Repository and Designer - 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.

Related

capture Server Model of an existing Data base

I am using Designer 10.1.
Is it possible to capture the design of the whole database.
Atleast I want to capture all the schema objects. I have been struggling with the FK constraints.
Can somebody help me out ?
What is the right approach to go about it..Starting from creating containers. I am confused between folders, application systems. where should I create my database. And shall I create users first corresponding to the schemas in my database whose design I am capturing and then capture the objects into them.
I tried different application systems corresponding to the schemas. But I have problem with inter schema FKs. I don't think that is the right approach.
Thanks a lot 
Possible to capture entire database? Yes, probably, but you'd probably have to do it in pieces, at the very least schema by schema.
Which brings us to the cross-schema foreign keys. I don't have many of these in my databases, so I don't remember trying to capture the with Designer, but I have seen reports from people on this forum who have had trouble with it. All I can say is, do a search of the forum for more information and/or try it and see.
To do a "Capture Server Model", you need to set up your repository as follows:
1. Create an "Application System" to own the metadata. An application system is one of two kinds of "containers" in Designer. The other is a "Folder" which is mostly used for files. Application systems contain the structured data, Folders the unstructured.
2. Create a "Database Definition", probably an "Oracle" database.
3. Under the database definition, create one or more Users to hold schema definitions.
When you run the capture, you will choose the application, database, and user under which the new metadata will be stored.
Finally, let me say that I don't think it is a good idea to try to capture an entire database. Capture only what you need to use right now. Capture more later if you need to. If you are trying to document your database, there are probably better tools than Designer. For instance, JDeveloper has some very good database diagramming capabilities, and so does the new SQL Developer design tool that is now in early access.

Preserving Access Spec. when copy&paste an ext. table between projects?

Dear all,
Our company has a number of distinct Oracle projects (about 2 dozen) in our OWB dev repository, and each project is named according to its business function. A few years ago, we implemented a new business model which uses a number of core concepts (such as "Product", "Arrangement" and "Involved Party").
I have been tasked to create a single project for each of the core concepts (i.e "CONCEPT_ARRANGEMENT"). The current layout of the projects needs to be changed so that all mappings, external tables and so on, must be split so that all "Arrangement"-related mappings/tables etc. must go into the "CONCEPT_ARRANGEMENT" project, product-related tables and stuff must go into "CONCEPT_PRODUCT", and so on.
The idea is that each project-per-concept will only contain tables and mappings that apply to that core concept. The target physical model has already been implemented, it's the ETL conversion process that is suffering.
My problem is that, while I am able to copy and paste most objects from the source project to the target project, some metadata is thrown away in the process.
Specifically, external tables lose their file access specifications. All of the source files have been sampled and then used to create the associated external tables. When the external table is copied and pasted, all other metadata is preserved except the access spec, and it isn't possible to recapture it (using OWB) except by re-importing the file or manually setting the access specifier in the ext. table properties.
Since many of these projects are many years old, the original test files are not available, and the hundreds of files makes manually specifying the access params an intractable problem. Configuring the external table via the key/value editor doesn't help either. All other settings, locations and other stuff is identical between source and target project.
Is there any way I can go about accomplishing this without lots of hassle? We are using Oracle 10gR2 and OWB 10.1.0.5.0.
Please help! Any insights would be very much appreciated.
Regards,
Riaan 
If you used scripting, could you read the ACCESS_SPECIFICATION from one external table and set it in the copied table? The property is names ACCESS_SPECIFICATION (I think), if you could you could create a script to do all of your work that is tedious to do. The bottom line is that the copy/paste sounds like a bug, but the scripting route might be useful to automate what you are doing.
Cheers
David

How to connect to existing schema and create ER diagram?

Hello,
I installed the Designer repository in an existing database. I can connect as repo user (which is my repository user). I want to create ER-Diagram etc for an existing schema in this database where the repository exists. I chose repository object navigator and can see my workarea. From here how do I create ER diagrams for existing schema for a particulkar database?
Please advice.
Thanks a lot... 
First, you need to capture the server design. This will create Table Definitions and Foreign Key definitions, NOT Entities and Relationships. These can be put in a server model diagram, NOT an E/R diagram. But for some people, that's all they need or want. Just drag the tables from the Design Editor navigator onto the blank area to the right, and it will create a diagram. If you want to create true E/R diagrams, you will use the Table to Entity Retrofit utility.
These threads ought to help:
capture Server Model of an existing Data base
Re: How to create ER diagrams of an existing schema in Designer 
Flack,
I did what you have told me to do, and followed the links that you sent me, works fine but i cannot see the entity relationships. I have all the entity's on the diagram but without relationships. I want to see the relationships how can i see them...
Please advice.
Thank you. 
Are you sure that there were constraints in the database, in the first place? Designer will not magically produce relationships when retro-fitting to entities; the foreign key constraints have to be there. Try producing a schema diagram of all the tables and see if there are foreign key relationships. If none, you need to define them yourself 
I am still having problem in creating an ER diagram from an existing database. First of all is there any good documentation step by step directions how to creat e an ER diagram for existing schemas? in server model constraints grayed out. i have Primary, foreign keys etc on all the tables, still dont see the relationships..
Please advice. 
Even though you may have foreign keys defined in your database, the Capture Server Model may not have captured them. Check the table definitions to make sure that they are there. JohnReardon suggested creating a server model diagram (you don't have to save it) as a good way to check - did you do this?
If the foreign key constraints are not there (you said they are grayed out-which seems to say that they aren't), did you check "Capture Constraints" in the Capture Server Model from Database wizard? You can always delete all the table definitions and try the capture again. You can also do the capture again without deleting first and it is supposed to add what it missed the first time, but I've found this to be a bit flakey sometimes.
Get the Capture Server Model right first, the Table to Entity Retrofit won't work right until you do. 
Hi,
I dont see constraints, when i try to capture the server model, i see tables, views etc.... but not constraints. How do I get these please help me, I spent almost from 3 days on this and moving no where.
Please help.
Thank you 
I just did a "Capture Server Model" myself just to remind myself how it works. This wizard is a bit flaky, and it helps to uncheck "Stop on Error" so that it keeps going even if it gets an error. But it DOES work and it DOES capture constraints. I suggest that you delete everything and start over. This time - first do it in small batches of related tables, it tends not to like doing a lot at one time. Second, make sure that you check the box labeled "Capture Constraints". Third, when I did it, it reported an error and failed to capture one of the tables. It worked fine when I tried that one table again, but any foreign key referencing that table had to be added manually. It isn't hard to add foreign keys manually, as long as you don't have to do too many, especially if the primary key (or unique) constraints were captured. 
Can you tell which version of Oracle Desginer you are working with including patchsets etc. And which version of the Oracle Database you have your Oracle Designer repository in. And from which version of database you want to capture your design? 
Hi Jflack,
I exactly did as per your instructions capturing the server model, then can see all the entity's without any relational arrows. I did check the constraints box as you said. I am new to designer, I am absolutely freaking out with this tool. I just want to get all the tables that already in my schema to see the relationships between the tables with PK's and FK's. Seem I am out of luck. I am sorry to bug you but could please let me know what else I am missing or will it generate just the entity's without relations. I even try to create pk's and fks manually still doesn't show on the diagram with arrow kind relations between the tables.
Is there step by step good documentation?
Any good books to follow? any body have any idea..........
I am using the 10g database and 10.1.2. designer version.
Please advise me...
Thanks a lot.......... 
At this point, I'm out of ideas, seeing as I can't look over your shoulder and see what you see, and browse around in your environment. Sorry, I couldn't be more help.
There is a good book on Designer, Oracle Designer Handbook by Paul Dorsey and Peter Koletzke. It's pretty old now, and references Designer 2.1 rather than 10g, but then, Designer hasn't changed much since then, except for adding version control, which probably isn't the issue here.
You may have seen from the earlier post that I referenced at the beginning of this thread, and other posts of mine in this forum, that I don't recommend Designer to new users, especially if all you want to do is E/R diagrams and database design. I only use it to support systems that were originally generated from Designer. Don't get me wrong, it's a great tool, but the only upgrades it has gotten in the past several years are bug fixes and making sure it continues to work with the latest Oracle databases. And that doesn't mean that it supports new database features.
If all you want to do is draw E/R diagrams, if there are no other Designer users in your shop, and you don't intend to generate modules from Designer, I'd use something else. SQL Developer Data Modeler is very good, with many of the same design tools that Designer has. SDDM isn't free, though. Oracle JDeveloper has a good UML based design tool, and Dai Clegg and Susan Duncan have done some good presentations to show how you can use its class modeler as a good substitute for E/R. JDeveloper is free. Then there is ER Win - I haven't used it for a while, but it is very popular, and TOAD Data Modeler - limited, but not bad. 
What is the exact version of the 10g database do you use? 10.2.0.4 or 10.2.0.5? I ask this because I had some trouble using the ADT and DDT inside those versions. I had to set the following settings inside the oracle database:
alter system set "_optimizer_cost_based_transformation"=off scope=both; I can be that you experience the same problems and that the relations from you tables are not loaded when you recapture the model.

Creating data warehouse using script file

Hi,I'm trying to create a data warehouse using script file. Since i'm beginner, i'm looking for some examples to understand how DDL files can be created. I'm working with OWB. Any help please?
You don't say what version you're using. Is it safe to assume you actually have something to export, or are you asking how to do that too. What are you using the DDLs for? Most of the things you'll probably be creating with OWB can be deployed directly to the database from the tool. If you need them for version control, you'll probably want to look at generating model metadata, not DDL. To actually generate stuff (at least using most/all versions I've used) right click the object you want to generate scripts for and hit Generate. That should pop up a Results - Log window somewhere. In that window you should be able to expand the tree and find scripts. You can select multiple objects, or entire groups. But like I mentioned earlier, make sure generating DDLs is what you really need. At a minimum, read the OWB Concepts guide.
Hi,Sorry I didn't eplain exactly my question. I'm working with OWB 11g R2. Concerning hw to generate the warehouse using scripts (dimensions, facts,...) , I found the language OMB+. When I read about implementing warehouse, I found that exist two ways: relational tables and OLAP cubes. I'm confused a little bit. Please any help?Thanks in advance.
Dylan gave you good feedback.   In Design Center, generate DDL for whatever objects you need to create in your EDW.   The single most important thing here will be that your fact tables are going to have foreign keys to your dimension tables, so make sure your script is creating the dimensions first, then the facts, just as we load dimensions first, then load facts.

Incremental Server Generation

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.

Categories

Resources