Donal Daly

Subscribe to Donal Daly feed
Random thoughts on Big Data Analytics and the Big Data Marketplace in GeneralUnknownnoreply@blogger.comBlogger29125
Updated: 2 weeks 8 hours ago

Consolidation is the key driver for Migration from Microsoft Access to Oracle Application Express

Thu, 2007-02-15 14:01
I have just been reviewing the survey results and feedback received from early adopters of the Application Migration Workshop a new feature of Oracle Application Express to assist with migration of Microsoft Access Forms and Reports to Oracle Application Express.

Looking at the data, consolidation is the key reason. In fact, it was twice as popular as the next given reasons of company direction and performance.

So what does consolidation really mean in this context. It is 1 to 1, i.e. one Access database to a unique schema in Oracle or is it a many to 1, removing duplication and increasing the shared access to a "single source of truth". I think it is a mixture of both.

In talking with customers over the last year or so, some customers have told me that they have thousands of access applications and refer to it as their "access problem". Clearly it would be a stretch to think that they have thousands of unique applications, so there must be a lot of duplication and redundant access databases.

So how do you define an appropriate access migration strategy. Well I believe firstly, you need more information about your access databases. This is really an assessment phase. In doing an assessment of a large number of access databases, I would split it into two parts:
  1. Technical Assessment
  2. Business Assessment
Technical Assessment

In the technical assessment phase, you are trying to build up some key data about these access databases. I refer to it as a technical assessment, as hopefully you could have a utility to collect this information. Data to collect would include:
  • Name, Owner, Size
  • Date created, Date last modified
  • Version
  • # of Tables, Forms, Reports, Queries, Pages, Modules,
  • Links
    • links to other mdb files
    • linked data e.g. link tables via ODBC to Oracle, SQL Server, ...
  • Location (server)
  • ...
Business Assessment (Business Level Discovery)

This requires human involvement, and is critical for a subsequent analysis phase. Data points here would include:

  • Business Owner (by department, group)
  • Description (purpose)
  • Application Type (data entry, reporting, ...)
  • Priority (Business Critical to Redundant)
  • Action (migrate, ignore, duplicate, remove, ...)
  • # of users
I can see this being the basis for a nice Oracle Application Express Application, that would support the data entry of business level data, and the generation of useful reports and charts. This would then facilitate the next level of analysis and allow you to ask some interesting questions:
  • What is my spread of versions of Microsoft Access?
  • How many copies of project.mdb or tasklist.mdb do I have? :-)
  • What's my largest / average access database size?
  • Which access databases are most heavily used?
  • ...
Now I think that would be a very useful data collection utility and Oracle APEX application.

Oracle APEX Application Migration Workshop is now on OTN!

Thu, 2006-11-30 17:10
While the road to release has not been as smooth as I would have liked (down to me in parts :-) ) , we have finally published the early adopter version on OTN.

We very much welcome your feedback on it. Our plans is to have this as a standard feature of Oracle APEX 3.0, so you have the chance of your feedback making a difference in the final product in a short timeframe. I look forward to receving your feedback and making the product better because of it.

You will find more details about it here.

Enjoy, Donal

Design Goals for the Oracle APEX Application Migration Workshop

Wed, 2006-11-29 10:18
We are in the home stretch now, to make the early adopter version of the Oracle APEX Application Migration Workshop available on OTN. We have released some more information about it on OTN and have created a new OTN forum to host discussions about migration from Microsoft Access to Oracle Application Express.

I thought it would be an opportune time, to outline some of our design goals behind this development. Firstly, we want to promote Database Refactoring, once you have gotten your schema and data migrated using the Oracle Migration Workbench. The better your Data Model is, the easier it will be to create good Oracle APEX applications. Currently we identify tables without primary or foreign keys and also promote the usage of UI defaults, a feature of Oracle APEX. We hope to expand on this in the future, based on feedback. A possibility could be to identify tables that could be leveraged as List of Values (LOV's) or try to identify incorrect usage of data types, e.g. dates being stored as varchar.

Our approach is one of Design Recovery. We want to provide as much information about the original Access Application that we could recover from the Access mdb file. We have taken a workshop approach, to provide the developer a productive environment to decide what Access objects they are interested in and should be in scope. You deal with any errors reported, so additional objects can also be included. By default all Access objects which we analyze and determine are valid are included in the scope your migration project by default.

Our goal is to only generate a native Oracle APEX application. We are not interested in emulating unique Access functionality. Access Applications could be classed as "rich client" applications whereas Oracle APEX applications are Web (Browser) based. It's a different UI paradigm, and this needs to be respected if you are to create, intuitive and useful applications. We don't migrate any VBA code, but we will show you the VBA code contained in your modules. You could then determine if this logic is still necessary and if so, could be reimplemented as PL/SQL procedures/functions.

We can generate either an application based on selected Access Forms or Reports or generate a maintenance style application which is a Report and a Form on each selected Tables and Views.
Our application generation leverages the Application Model behind the create application wizard.

I am looking forward to feedback on this new feature of Oracle APEX. I believe we have built a good foundation, to address additional feature requests we might receive. With input from the community we can ensure that the Application Migration Workshop increases the productivity of developers who seek to migrate Access applications to Oracle APEX, when it becomes a standard feature of Oracle APEX 3.0

Now back to the last round of testing...

Database Tools Talks at UKOUG next week

Thu, 2006-11-09 14:03
Next week I'll be in Birmingham at the UK Oracle User Group Conference. I am looking forward to it, as it gives me another opportunity to talk about our new tools. This year, I think the database tools team is well represented.

Mike Hichwa, VP of Database Tools (main architect behind Oracle Application Express & my boss!) will be giving a presentation on Thursday morning @ 9am titled - An Insight into SQL Developer and Application Express

Sue Harper, PM for SQL Developer is giving a presentation on Wednesday on SQL Developer: Using Oracle's Graphical Database Development Tool. As you probably know by now, we have released an evaluation copy of SQL Developer 1.1 on OTN, so I am sure Sue will cover all the new cool features in 1.1 and answer any questions you might have.

Alex Keh, a PM whose focus is on our windows technologies is giving a presentation on Tuesday about .NET and Oracle Best Practices: Performance, Deployment and PL/SQL Tips. So if your preferred development environment is .NET be sure to attend his talk. I think we do a lot to make a .NET developers experience productive with Oracle database technology.

For myself, lucky me has 3 presentations!

On Tuesday I will be talking about: End the Microsoft Access Chaos - Your simplified path to Oracle Application Express. I will be demoing our new Oracle APEX Application Migration Workshop. This should also be making an appearance on OTN real soon...

Then on Thursday, I have two presentations: Oracle Application Express: Features, Futures and Customer Tales In this presentation, I give a brief overview of Application Express, What's in 2.2, what's coming in 3.0 and some demos and finally Oracle Migration Workbench: Taking Database migration to the next level where I will demo the new Oracle Migration Workbench integrated into SQL Developer. This should be making an appearance on OTN in 2007.

I am also looking forward to hearing customer feedback at the Oracle Development Tools Roundtable on Wednesday afternoon.

Having had a look at the agenda there are many talks which I want to attend. Looks like it is going to be a good week. Now to put the finishing touches to my presentations...

I won't get much work done this weekend....

Sat, 2006-11-04 16:22
Not a work related post, but...

I was asked by the Porsche club of Ireland (of which I am a member) would I be willing to write a car review for their newsletter as the Irish Porsche dealership offered them a car for the weekend. I could pick what I wanted, so I went for a 997 C4S manual.

In the picture is my son Daragh, and Alan from the Porsche dealership. In Ireland cars have high taxes , so this car costs €160K/$203K.... The car is on my insurance, so I have to be careful :-)

I had promised Julie I would sort out the garden this weekend and deal with all the leaves, sorry not this weekend!