Migrate your MS Access data to an Oracle database using the ETL Tool Talend

APEX is promoted as the perfect replacement for MS Access applications. One thing you should consider though is how you migrate your data to the Oracle database. In APEX there is a handy tool called the Data Workshop that can be used for this. You first export your Excel files from the MS Access database, and then follow the data upload wizard to import the data into identical tables. Since you are not always working with a 1-1 relationship, you will most likely have to write some PL/SQL to get all the data in the right tables.

dataworkshop

The downside is that you will need to repeat this process when you go into production. This is not a big problem if you only have one table to migrate. But if you have multiple tables and/ or your users also want new data during tests and trainings, you will spend a lot of time exporting and importing Excel files.

A recent APEX project for a client required a large data migration from MS Access Databases to the Oracle database. Because we would require fresh data on several points in the development process we decided to use the ETL Open Source Tool Talend. We got impressed of how intuitive the tool is, it only took a few days before we were familiar with the tool. Once you get the hang of it, you can write (or should I say draw) migrations of tables in no time. We needed to migrate from an MS Access database but the tool supports a wide range of databases and documents to import your data from. In total we migrated around 30-40 tables to our Oracle database.

Let’s have a closer look at one of our migration jobs.

talendmsaccessoracle

At the left we see our MS Access database. Each tAccessInput component will get data from one table. After that we join the tables in our tMap_1 component. The reason we don’t just write our joins in one component, is because this way we can really see how many rows every table returns.

On the bottom we have some Oracle Database input connections. They will join the persons of our MS Access Database with the persons in our Oracle Database based on the National registration number. After that we write our data to our Oracle Database. You may notice that we have two lines going to Excel files. This is our error logging; we use this to log the rows that did not find a match. In our first Excel for example we write persons that did not find a match in our Oracle Database.

This is just one example, in total about 20 jobs were built. During the development we also had to deal with certain calculations or convert data. For most things there was a component ready to use and if there wasn’t you could always write a Java expression in the tMap items.

I hope I convinced you of the benefits of using Talend as a migration tool for APEX projects, because we will certainly use this tool again!

All Things Oracle article: “What’s your choice for Oracle Forms?”

Last week on Oracle Open World we(iAdvise together with Grant Ronald) did a presentation on Oracle Forms Modernization.
We presented the possible options for an Oracle Forms application: modernize, upgrade, integrate or migrate.

In case you couldn’t attend the session or just want to know more about those options, you can read the following article on All Things Oracle: “What’s Your Choice for Oracle Forms?”

Seminar: “What’s your choice for Oracle Forms” – recap

A great turnout for the seminar: more than 40 interested attendees, a mix audience of IT managers, project managers and developers.
Hof ter Delft was a nice location!

Grant Ronald, Oracle Senior Group Product Manager for the development tools division, opened the seminar with a keynote presentation.
He started with the Oracle Statement of Direction: Oracle has no plans to desupport Forms and Reports.
After this he gave an high level overview of what is possible with an Oracle Forms application: modernize, upgrade, integrate and migrate.
Grant ended with an overview of ADF.

My session was next, same subject as Grant, but a bit deeper into detail with demos and customer cases.
I showed how you could modernize an application using Pluggable Java Components and JavaBeans.
Upgrade will bring you some nice features, eg. javascript integration and external events in 11g.
Those new features were shown in the integration demo, together with web service calls from Forms.
I ended with migration:  reasons to migrate, strategy(eg. a customer case of  redesign/rebuild in Apex), tools that  can be used(eg. JHeadstart ), …
One lesson: migration is not an easy path…

The following session walked this path of migration, the one of a phased migration.
Wilfred van er Deijl(Commit Consulting)presented OraFormsFaces.
A presentation about where OraFormsFaces can fit in and how it works.
When you choose for a migration and you don’t want a big bang, OraFormsFaces can help you in doing new development in a new technology(eg. Oracle ADF) and keep your Forms investment.
OraFormsFaces let you integrate a form in a webapplication and passes info between those two technologies.

To show that OraFormsFaces works with other technologies, Tom Bauwens(SmartApps) showed the integration of Oracle Forms with Eclipse RCP using OraFormsFaces.

After a nice lunch Grant Ronald gave an introduction training to ADF:

  • Building ADF Business Components
  • Business validation
  • Shaping your data
  • Application Flow
  • Building UI pages

This really showed the power of ADF to Forms developers.


iAdvise Seminar: What’s your choice for Oracle Forms?

There are a lot of questions about the future of Oracle Forms and applications that were built in Oracle Forms.
During this seminar we will answer these questions and give an overview of the (possible) future of such applications.

The seminar will kick off with a keynote by Grant Ronald, about the Oracle Forms strategy.
Next we show the following possibilities: modernize, upgrade, integrate and migrate.

To end the morning session we will show how change can go nice and smooth.

After lunch Grant Ronald will give an introduction to Oracle ADF Development:

  • Building ADF Business Components
  • Business Validation
  • Shaping your data
  • Application Flow
  • Building UI pages

Attendees of the afternoon session “Introduction to Oracle ADF” will receive a copy of the book “The Quick Start Guide to Fusion Development” by Grant Ronald!

When: Monday, June 6 2011
Where: http://www.hofterdelft.be (Ekeren – Antwerp)
More info

OOW 2010: Moving forms to ADF

When working with Oracle Forms these days and you’re not satisfied with the application anymore, there are some possibilities you can do:

  • upgrade
  • modernize
  • integrate
  • migrate

On our OOW session tomorrow(Oracle Forms in the Middle of Middleware, 1pm, Marriott Marquis Room: Salon 9), we will talk about the first three possibilities, upgrade, modernize and integrate.

But today I went to the session of Grant Ronald: Moving from Oracle Forms to Java and Oracle Application Development Framework
A session about migrating Oracle Forms to ADF.
The strategy of oracle is NOT desupporting Oracle Forms, on the contrary, they’re working on new features for 11g R2.

But when you consider migrating, do it for the right reasons.
Three kinds of reasons: the good, the bad and the ugly

Reasons to choose for migration can be

  • forms doesn’t meet the requirements anymore
  • there’s need for re-development
  • adopt leading edge, modern technologies

Reasons NOT to choose for migration:

  • there’s a heavy forms investment you don’t want to throw away
  • happy with data entry (and to my opinion forms is one of the best choices for data entry applications)

Wrong reasons:

  • forms will be desupported -> A clear answer of Grant Ronald: THIS IS NOT THE CASE!
  • upgrading your forms application will result in big problems
  • rewriting the application will save $$$

So migration is an option for your forms application, but Grant stated it several times in his session: DO IT FOR THE RIGHT REASON.

About migrating forms to ADF…
The technologies look similar…
Grant made a comparison between a dish washer and a washing machine.
Both have the same measurements, do similar things(wash something and dry it), etc.
But who puts his clothing in a dish washer?  Or cups and glasses in a washing machine?
So thechnologies look similar, but are different:

  • Java applet <> HTML/javascript
  • PL/SQL <> Java
  • Stateful <> stateless
  • No separation of UI and data elements <> seperate UI and data elements

Do not ignore those differences when looking at migration!

ADF is a framework and does a lot of things for you(like log on to the database, you don’t have to write the code) which is pretty nice.
But hey, Forms does also things for you, it’s also a framework.

You can build applications in ADF that look like forms application and have the same behaviour, but is that the reason to migrate, to work the same way?

When migrating there are some more challenges, eg reusability of table/views, procedures/functions, PLL, triggers.  What about forms built-in functions?

So, of course migration is an option for your Oracle Forms application, but ask yourself a question: Why migrate?
Take a look at all the options, before going to migrate.  It’s not an easy path to walk…

Check also the paper Grant wrote about migrating: Migrating Oracle Forms to Fusion: myth or magic bullet

Oracle Data Integrator – Part 2 – Working with Excel Data

What have I learned today …

Today I took a closer look at declaring or using my Excel file as a source model for the ODI for which I’ve used the ODI User Guide and the help-documentation in ODI itself.


What do I need to do to get ODI to talk to my Excel file?

  • Create a new ODBC datasource for my Excel files using the Microsoft ODBC Data Source Administrator (run from the Configuration Panel)
  • Make sure you’ve defined a name for the data in the Excel spreadsheet. You can accomplish this by defining a name for the data shown in every worksheet. You have to mark the data in a specific worksheet (select all the rows and columns with data) and choose to insert a name for this data using the insert-menu.

For the configuration of ODI I’ve used different kinds of guidance, help then only the User Guide. To define my ODBC Datasource in the ODI I’ve followed the topic ‘Create a Microsoft Excel Data Server’ (written in the help-documentation of ODI) . This topic has a more detailed approach then the topic ‘Creating a Data Server’ explained in the User Guide.

The steps I’ve performed:

  • Connect to the Topology Manager (first open up the Designer from your Oracle Data Integrator menu)

  • Select Topology > Physical Architecture > Technologies > Microsoft Excel in the tree

  • Right click and select Insert Data Server
  • Fill in the necessary fields in the Definition and JDBC tab, such as the name and the JDBC Driver (sun.jdbc.odbc.JdbcOdbcDriver) and JDBC URL (jdbc:odbc: => the name you defined for your ODBC data source)

  • You can test your connection, test if the connection was succesfull by clicking the test-button in your Data Server properties screen.

The next step we need to perform is to create the physical schema for our Excel file to be able to map this ‘source’ schema to the ‘target’ schema.

  • Right-click on your newly created Data Server and choose to insert a new physical schema:
  • Leave the defaults as is for the Physical schema and choose the wright context in the context-tab which means you need to specify the logical schema you want to use. Specify a name and click apply and ok.

  • Let’s reverse engineer the Excel file into a new model. Go to the designer and choose to insert a new model. In the technology-dropdown choose ‘Microsoft Excel’ and choose your Logical Schema.
  • In the reverse-tab make sure to select the context you want to define the model in.
  • In the ‘Selective Reverse’ tab choose all checkboxes and the data you’ve defined names for in the spreadsheet will be listed. Click OK to reverse-engineering the defined data into your model.
  • In the model-tree you can see the different tables that where created inside the model depending on the data you’ve defined in the excel sheet.

  • In the different DataStores that were created (depending on the data you’ve defined in the spreadsheet) you can check the colums that were defined in your spreadsheet.

The next step is to start creating Interfaces to define the transformation that’s needed from our source-model to our target-model.

Stay Tuned

Oracle Data Integrator

For my project one of the tasks I need to accomplish is to work out the migration path from an oracle db and excell files to a new oracle db.
I need to design a new application in adf with a process-driven data-model to replace the existing ‘application’, which are excell files with a lot of pivot tables, redundancy, …
The first application, before the excell files, was written in Oracle Forms, but isn’t currently used anymore.

One of the challenges I’m faced with now is the need to migrate the old oracle schema and the excell files to my new oracle schema. The data model has changed a lot according to the previous one, because the data-model is set-up in a process-driven manner.

The past weeks we’ve analysed the existing data and the different strategies we can can follow for this migration path. The migration has to be done only once for all the data that’s stored in the old schema and in the excell files and the best way to do is … indeed that’s the question?

I’ve been reading a lot on technet, on different blogs, in whitepapers, … about the different possibilities there are, the ones I came up with:

  • external tables
  • sqlloader
  • Oracle Migration Workbench (SQLDeveloper plug-in)
  • Oracle Data Integrator
  • Open Source ETL Tools
  • After taking a look at the strengths and weaknesses of every technology/possibility I’ve chosen to have a closer look at ODI (Oracle Data Integrator).

Some of the features I’m really interested in and which are very useful for me are the following:

  • Transformation between source and target
      My source system, the excel files, have a different data structure than my target database. To be able to map the source-data to the target-data I will need a mapping-tool such as ODI to do this mapping in a declarative way.
      E.G. In the excell file the address of a person is stored in one cell, this needs to be mapped to different fields in my target-datasource, …

  • Ability to check the data-flows (via Data Flow Control) between the source- and target-destination and have error-handling and logging for each needed step during the process
    • Data flow control consists of checking the source data of an interface compared to the consistency level required on the target. Data is checked before the data is integrated in the target.
      Detection of an error can give rise to either non-integration of all the data (even correct), or integration of only the correct data. In any case, it is useful to isolate and/or recycle the erroneous data (after any necessary correction).

  • Use existing Knowledge Modules which hold the necessary tasks to extract and load data according to the pre-defined steps in this Module
  • The following important question is … how to get started?

    Firstly I’ve installed ODI after reading the installation guide and then I’ve taken a tour of the ‘getting started guide’ and the demo material that’s included in the ODI install.

    After reading through the ODI User’s guide I’ve noted down the following important decision points:

    • I need a ‘One Shot Interface’ for my migration path because I only need to perform this migration one time, before going to production
    • I need an LKM (Loading Knowledge Module) to extract data from the source database and my excell files
    • When designing Interfaces in Oracle Data Integrator, these Interfaces contain several phases, including data loading, data check, data integration, etc. For each of these phases, you will define:
      - The functional rules (mappings, constraints, etc.) for this phase
      - The Knowledge Module to be used for this phase.

    Secondly I’ve read the threads on ‘otn’ to be able to have an idea of the community that’s using this tool and the people I can asks questions to ;o)

    Thirdly it’s time to get started …

    I will keep you posted on the progress and the ODI-experience … so stay tuned !