Use IBM Rational Data Architect to model your Oracle databases

RDA is for more than IBM databases

Find out why IBM® Rational® Data Architect is gaining momentum as a database modeling tool that is optimized for IBM databases, yet also provides functionality to model other major database vendors such as Oracle. Look at alternatives to Oracle Designer, and see how keeping up with Oracle's major enhancements is setting RDA apart from the rest of the data modeling tools. [2009 Apr 17: Added note about Rational Data Architect changing product name to InfoSphere Data Architect. --Ed.]

Share:

Anson Kokkat (ansonk@ca.ibm.com), WorldWide Product Manager for Rational Data Architect, IBM

Photo: Anson KokkatAnson Kokkat works at IBM as product manager for Rational Data Architect. Most of Anson’s professional career has been spent working on application development technologies related to data servers. He has an extensive background in DB2 and WebSphere integration, JDBC technology, .NET, stored procedures, and other application development methodologies. Now he concentrates on Eclipse programming and data modeling and works with the development, sales, and marketing teams to make RDA a success.



17 April 2009 (First published 31 January 2008)

Also available in Russian Spanish

Introduction

Rational Data Architect provides tools that can dramatically reduce design and development time. This software, built on the open source Eclipse platform, helps data architects model, discover, map, and analyze data across multiple information sources, automating information integration in complex environments.

Product name change

On December 16th, 2008, IBM announced that as of Version 7.5.1, Rational Data Architect is renamed to InfoSphere Data Architect to feature its role in InfoSphere Foundation Tools.

Rational Data Architect is not just for IBM databases. If you are searching for just the right tool to do your data modeling with a bright future in terms of functionality, then have a look at Rational Data Architect. Here are a few reasons to consider RDA:

  • Now that Oracle Designer is going out of support, the Eclipse-based tooling available in RDA is a good alternate choice.
  • RDA's commitment to the major database vendors can be seen through the enhanced support that is coming in future versions of RDA.
  • RDA not only allows you to perform robust data modeling, but it also allows you to take advantage of the unique mapping capability, comparison and synchronization of two different types of models or objects within those models, strong reporting features (even stronger with the BIRT integration in the tool), and integrations with existing Rational tools that you may already have in your environment. (See Resources for more information about BIRT.) There is also a direct relation with the new IBM Data Studio offering that was announced recently.

Rational Data Architect is not just for IBM databases

One of the main features that Rational Data Architect can bring to your organization is its heterogeneous database support. Let's have a look at a real-life situation where different databases are used for different purposes in a company:

  1. Sales Department keeps track of sales information with an IBM DB2® database.
  2. Marketing Department keeps track of customer data in an Oracle database.
  3. Development uses an SQL server database to create various applications for the company.

Now, when it comes time to manage all the data that these three different parts of the organization are using, it becomes very confusing and time consuming. Rational Data Architect gives you the capability to use one tool to work with all of this data, no matter what the database.

Figure 1. View of the Database Explorer in Rational Data Architect . It can connect to many different data sources
View of the Database Explorer in Rational Data Architect

Let's have a look at what databases you can connect to in Rational Data Architect:

IBM databases:

  • Cloudscape®
  • DB2 for Linux®, UNIX®, and Windows®
  • DB2 for iSeries®
  • DB2 for z/OS®
  • Derby
  • Informix® Dynamic Server

Other Databases:

  • Oracle
  • Microsoft SQL Server Enterprise
  • MySQL
  • Sybase Adaptive Server Enterprise
  • Teradata
  • Generic JDBC connection

Since you can make a JDBC connection to most databases, you can really just use the JDBC driver to connect to any database of your choice.

Modeling an Oracle database

As mentioned, the connection in the Database Explorer is accomplished through the JDBC driver. So the first thing you need to do to connect to an Oracle database is download the JDBC driver for Oracle.

You can download it to anywhere on your machine, and then when you are connecting to the Oracle database for the first time, you can specify the driver location.

The next step is to look at the Database Explorer in Rational Data Architect.

Right-click on Connections > New Connection.

Figure 2. Creating a new connection in Rational Data Architect
Creating a new connection in Rational Data Architect

In the Connection Parameters wizard that comes up, pick the Oracle driver and the appropriate version of the Oracle database.

Figure 3. New Connection wizard for Oracle database
New Connection wizard for Oracle database

You then need to fill in the following values, as shown in Figure 4, as an example for connecting to Oracle 10g database:

Figure 4. Oracle connection Information
Oracle connection Information
  • Connection Name: Give the name of your connection a name. This is what will show up in the Database Explorer.
  • JDBC Driver: Choose the Oracle Thin Driver.
  • SID: Get this information from you administrator, if you don't know it.
  • Host: Get this information from you administrator, if you don't know it.
  • Port Number: Get this information from you administrator, if you don’t know it.
  • Class Location: This is the location of where you downloaded the Oracle JDBC driver, as per above.
  • Connection URL: When you fill in the above information, the Connection URL field should get filled in automatically.
  • User Information: Provide the userid and password that you use to connect to the Oracle database.

If you run into problems connecting, some common techniques to troubleshoot are to try to connect with the userid/password outside of RDA and see if you are successful.

The security of what you are allowed to perform on the database is determined by the privileges that are set with the userid and password that you use to connect to the database upon logon.

Figure 5. Security is set with userid and password that you specify at logon
Security is set with userid and password that you specify at logon

Once you are connected, you have the ability to perform all normal data modeling activities, as per RDA.

Let's have a look at a few data modeling activities performed through the database explorer.

Data visualization

Being able to view data in pictorial format is a lot stronger than most people think. Having pictures of your database really helps when you are dealing with complex database environments. It helps you to refine further design and maintain complex environments. Data visualization can take the form of the diagrams that you create in your data model and also the characteristics that are available in the database explorer.

Diagrams in RDA are rich. You can get the to the details of each entity in a logical data model:

Figure 6. Diagram capability available in RDA
Diagram capability available in RDA

The diagramming that you can perform with Oracle and other databases is rich, and even allows you to perform things like overview diagrams:

Figure 7. Overview diagram that shows grouped entities
Overview diagram that shows grouped entities

Within the Database Explorer as well, you have some strong pictorial capability that allows you to get a picture of the data in your database. You can sample the data in the Oracle (or other) database and even edit the data that is available.

Follow these instructions to see how to sample data in an Oracle database (Note: This applies to other database vendors as well):

  1. Connect to the Oracle database with the proper credentials, as per Figure 5.
  2. Drill down the database to the schema and table of the data that you would like to work with.
  3. Right-click on the table, select Data > Sample Contents. The data will show up in a data output of the properties window..
    Figure 8. Sample contents of the data in the Database Explorer
    Sample contents of the data in the Database Explorer
  4. You can also edit the data by right-clicking on the table and selecting Data > Edit. From here you can change any fields in the table that you want in the editor that becomes available.
    Figure 9. Edit the data that shows up in the Editor Window

Data modeling capabilities

The basic steps to create a data model from scratch involve realizing the business requirements that eventually get translated into a data model.

Figure 10. The flow of creating a data model from business requirements
The flow of creating a data model from business requirements

Rational Data Architect realizes the need to create logical data models and physical data models. You can easily create a logical data model that is derived from business requirements and transform that into a physical data model that reflects your database design.

The idea behind these data modeling activities is to allow you to create the following:

  • Logical data models – Canvas for design of your database; contains entities, attributes.
  • Physical data models – Blueprint of your physical tables and columns.
  • Domain Models – Allow you to define specific data types for your business. For example, a security number has some special meaning in your organization that you can define in a domain.

Creating database models using reverse engineering

Data models that are not created from scratch are usually created from an existing architecture of the database. You can reverse engineer your database into a model using a DDL file or from the database itself.

Let's look at an example of creating an Oracle database from reverse engineering. The wizard really helps make this simple.

  1. Once you have a new project created, select File > New > Physical Data Model, as shown in Figure 11:
    Figure 11. Creating a new physical data model
    Creating a new physical data model
  2. Fill in the following values, as shown in Figure 12:
    Figure 12. New Physical Data Model Wizard
    New Physical Data Model Wizard
    • Destination Folder: The folder pointing to the project you have created.
    • FileName: Give your new data model a name.
    • Database:Select Oracle.
    • Version: Version of the Oracle Database you are modeling.
    • Choose Create from Reverse Engineering.
  3. Select Next.
  4. Choose DDL script if you are creating a model from an existing DDL file that you have.
  5. Browse to the location of the DDL file, and select the options for how you want your diagram to look.
  6. Select Next, and then select Finish.

You will see your new data model created from the DDL file you gave the tool.

Figure 13. Create new Oracle database from reverse engineering DDL file
Create new Oracle database from reverse engineering DDL file

The features outlined above are not a complete list of what Rational Data Architect can do, but is intended to give you a taste of how feature rich RDA can be. It gives you full heterogeneous capability to model your Oracle and other databases.


Oracle Designer going out of support

There is a tool available specifically for modeling Oracle databases that is available from Oracle itself, but it should be noted that upon reading the Oracle Designer Web site, this tool is planning to be decommissioned. There are alternatives listed on the Oracle Designer site, but nothing that really meets the data modeling capabilities that were available in the Oracle Designer tool. The new suite of tools being recommended is mostly for the application development needs of the developer.

If you look at the different pieces of Oracle Designer and RDA, the concepts are pretty much the same. The only difference is in the way that activities are performed. For example, in Oracle Designer you can transform a model using the Database Design Transform that allows you to specify table mappings. RDA also allows you to choose your table mappings as well using the wizard when you do the transformation.

Figure 14. Transform to Physical Data Model options available in RDA
Transform to Physical Data Model options available in RDA

There are other options that you can specify in the Windows > Preferences part of the RDA tool as well.

Please note that specific Oracle data types are available in the data type mapping, accessible through Windows > Preferences > Data > Transform > Data Type Map > Logical to Physical.

Figure 15. Preferences for Oracle data types
Preferences for Oracle data types

For example, the VARCHAR2 data type is something specific to Oracle. When you create the logical data model upon transformation, you can automatically have RDA convert the VARCHAR data type that you specify in the logical data model to the VARCHAR2 data type in the physical data model.

Importing Oracle Designer files into RDA is possible since RDA has MITI bridges to bring in the data from Oracle Designer and directly import those into RDA. It is a matter of launching a wizard and specifying the location of the Oracle Designer file. RDA does the rest for you and allows you to start working with the model directly with a few mouse clicks.

Rational Data Architect provides the capability of strong data modeling activities, then goes a step further by giving you the capability for application development and integrations with other software products. This article discusses some of the key features RDA has to offer in a later section.


Future Oracle support and further enhancements

It is important that tools keep up with the newest of the features that are being added to particular database vendors. Database partitioning allows tables and indexes within a database to be subdivided into smaller pieces. A peek into the future shows us that RDA will provide Oracle partition and sub-partition support. The Oracle partition support that is planned for RDA will look similar to the partition support for RDA and DB2 that is available now.

Figure 16. DB2 partition support in RDA
DB2 partition support in RDA

As mentioned before, Oracle Designer will stop adding any new features after Oracle 10g, but looking forward, RDA does plan to provide support for Oracle 11g in the fixpack 5 time frame. Look for the enhanced partition support to be available at this time as well.

You can see that RDA commits itself to keeping up with database features no matter the vendor and targets to be a first class when it comes to database support.


RDA features

RDA has many features that cannot be covered in just one article. Here is a brief description of some of the features that are available to you when modeling your Oracle database. You can find many more details on these and other features by searching for "Rational Data Architect" in developerWorks. Remember, this article is targeted towards Oracle users, but the features listed are complacent for all database vendors. RDA is truly a heterogeneous product. Its key features include:

  • Eclipse platform – RDA is built on the open source Rational Software development platform, so it is feature rich with all common Eclipse features that you would find in any Rational product. Remember, RDA is part of the Information Management brand of software.
  • Robust data modeling – RDA is capable of doing rich and robust core data modeling. RDA can create logical, physical, and domain data models.
  • Mapping –The mapping feature is only found in RDA, and is a technique that allows you to investigate and develop how to turn one data solution into another data solution –- one schema into another schema. There are many situations that require such an investigation and solution: the development of a new version of an application, the acquiring of a company, and the evaluation of software packages are just a few of these situations.
  • Compare and Synchronize - RDA provides an editor that will compare two objects. After you decide what difference you want to merge into one of the data objects in the compare editor, you can generate a delta DDL script or export the changes to an XML file.
  • Reporting – RDA provides HTML and PDF reports for logical, physical, glossary, and mapping models. After fixpack 3 of RDA, you now have the added capability of BIRT (Business Intelligence Reporting Tool) that gives you flexible reporting options such as customizing reporting.

Let's now take a look at a couple of integrations that really set RDA apart from the other tools, such as Oracle Designer:

  • IBM Data Studio – RDA now falls under the IBM Data Studio set, which is IBM's newest database vendor tool that allows you to design, deploy, manage, and govern all your database activities in one tool. Since IBM Data Studio is also built on the Eclipse platform, you will see many more future integrations and closer ties between IBM Data Studio and RDA.
  • Integrations with Rational tool set – RDA is the only tool set that is fully built on top of the Rational platform. This means that you can do things like shell share -- install RDA on top of Rational Software Architect, Rational Application Developer, and more. This integration makes both products look like one. You can even transform from logical data model (RDA) to UML model (RSA).
  • IBM Information Server - The IBM Industry Models help clients define and describe a unified view of their analytical data that persists in a data warehouse. In order for the analytical solution to work, Information Server enables organizations to understand their existing data sources to cleanse, correct, and standardize information.
  • Industry Models - The IBM Industry Models provide structured and deployable business content for a growing number of industries, including banking, insurance, financial markets, health plans, telecommunications, and retail. The data models are defined in Rational Data Architect.

RDA is feature rich in the standard features that it provides, but there are numerous integrations that you will not get with any other tool set.


Conclusion

Do you have a complex environment with multiple database vendors in house? Rational Data Architect provides true heterogeneous database support.

  • Modeling your Oracle database and, in fact, any database is accomplished by RDA making an underlying connection to the database using the JDBC driver. After you have connected to the database you can create robust logical, physical, domain, and even glossary models in RDA.
  • The tool that was made by Oracle for database design, Oracle Designer, is going out of support, and no replacement is being announced, so looking at RDA to fill in the gaps for your database design needs may be just the tool that you are looking for. Importing existing Oracle Designer files into RDA is simple.
  • RDA does intend to keep up with all new functionality of Oracle by providing partition and sub-partition support, and also providing support for Oracle 11g.
  • RDA has many unique features such as its Eclipse foundation, strong data modeling, mapping, compare and synchronize, and integrations that truly set it apart from many of the other data modeling products available. If you already have some of these products or are looking to invest in them, then the fit with RDA will help you gain maximum benefit from the integrations. RDA falls under the IBM Data Studio umbrella as one tool for all your database tooling needs, which integrates with Rational Suite, Information Server, and Industry Models.

Rational Data Architect is a data modeling tool PLUS much more. Consider it for your data modeling needs.

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Rational
ArticleID=285045
ArticleTitle=Use IBM Rational Data Architect to model your Oracle databases
publish-date=04172009