My Presentation at VirtaThon 2011

I will be giving a presentation on the Oracle Data Miner New Features at the online conference VirtaThon, on Monday 18th July.

VirtaThon is a FREE 6 day conference with 2 parallel sessions with world leading speakers on Oracle Java and MySQL. 

Previously attendance at the conference cost $100, which was good value considering the quality of the speakers. But this year it is Free.

The VirtaThon conference runs from 16th July to 21st July

The schedule is available at

http://www.brainsurface.com/virtathon/sessions-schedule

To sign up to attend some or all of the sessions go to

http://bit.ly/ehlaV9

Attend4FREE! Jul 16-21: 6 Days of Expert+ Sessions #VirtaThon The Online Conference for the Oracle, Java & MySQL Domains http://bit.ly/ehlaV9

My first Oracle Scene planning meeting

I had my first meeting, as a deputy editor, with the Oracle Scene team on Tuesday 12th July. The meeting was organised by Suzanne Gaunt, who is leaving the UKOUG on Friday. She will be sadly missed. Lavinia and Karina are taking over the role of producing the magazine.

Also at the meeting was Neil Jarvis, the editor, and Gillian and Philip Adams from www.doggARTadams.com, who look after the whole production of the magazine.

The main topic of discussion was the production process for the magazine and an outline of the timelines involved.

There was some discussions on how to improve the magazine with new material. Some of the suggestions included

• From the editors section to be added back in. This is a half page from each of the deputy editors. For myself this will be an introduction/bio in the next edition
• Ask the editors section, where the readers can submit questions to the editorial team for them to attempt to answer and hopefully in a humorous way
• Articles from the different regions and SIGs. This is to allow the user group to get to know what the other parts of the group are up to
• A Blog summary. This will be a round up of some of the blogs from user group community
• Introduce a competition in each edition
• Unusual Photo competitions
• Book reviews, which maybe Book giveaways from the publishers

In addition to these suggestions the main content of articles from the user community. Everyone is invited to write an article. It does not matter how long or short it is. The main idea is for you to share some of your knowledge. An article can be of any length, for example it could be 1/4 page, 1/2 page, 1, 2, 3 or 5 pages.

Have you come across a new or unusual feature, did you do something interesting with a feature, how and why you implemented it, case studies, new tools, etc.

So articles could be technical or non-technical, or on anything that might be of interest to the user community.

The next deadline for submitting articles is 26th August and with all deadlines it is good to submit early!

The finished magazine will be ready for distribution around the end of October.

If you have any suggestions of changes, additions, articles or adverting, let me know.

Exporting & Importing Oracle Data Miner (11gR2) Workflows

As with all development environments there will be need to move your code from one schema to another or from one database to another.

With Oracle Data Miner 11gR2, we have the same requirement. In our case it is not just individual procedures or packages, we have a workflow consisting of a number of nodes. With each node we may have a number of steps or functions that are applied to the data.

Exporting an ODM (11gR2) Workflow

In the Data Miner navigator, right-click the name of the workflow that you want to export.

The Save dialog opens. Specify a location on you computer where the workflow is saved as an XML file.

The default name for the file is workflow_name.xml, where workflow_name is the name of the workflow. You can change the name and location of the file.

Importing an ODM (11gR2) Workflow

Before you import your ODM workflow, you need to make sure that you have access the the same data that is specified in the workflow.

All tables/views are prefixed with the schema where the table/view resides.

You may want to import the data into the new schema or ensure that the new schema has the necessary grants.

Open the connection in ODM.

Select the project under with you want to import the workflow, or create a new project.

Right click the Project and select Import Workflow.

Search for the XML export file of the workflow.

Preserve the objects during the import.

When you have all the data and the ODM workflow imported, you will need to run the entire workflow to ensure that you have everything setup correctly.

It will also create the models in the new schema.

Data encoding in Workflow

All of the tables and views used as data sources in the exported workflow must reside in the new account

The account from which the workflow was exported is encoded in the exported workflow e.g. the exported workflow was exported from the account DMUSER and contains the data source node with data MINING_DATA_BUILD. If you import the schema into a different account (that is, an account that is not DMUSER) and try to run the workflow, the data source node fails because the workflow is looking for USER.MINING_DATA_BUILD_V.

To solve this problem, right-click the data node (MINING_DATA_BUILD_V in this example) and select Define Data Wizard. A message appears indicating that DMUSER.MINING_DATA_BUILD_V does not exist in the available tables/views. Click OK and then select MINING_DATA_BUILD_V in the current account.

Video

I have created a video of this blog. It illustrates how you can Export a workflow and Import the workflow into a new schema.

ODM 11gR2 - Exporting and Importing ODM Workflows

Make sure to check out my other Oracle Data Miner (11gR2) videos.

http://www.youtube.com/user/btierney70

Installing Oracle Apex 4.0 on 11.2g

Over the past few days I’ve been trying to install Oracle Apex 4 on my 11.2g database.  I say trying as I’ve made a number of attempts with no success. I started with the install instructions that come with Apex 4. Generally Oracle installs and install instructions have improved greatly since the 6, 7 and 8i versions.

I had high hopes of an easy install (as indicated by the various Oracle Apex books), but no matter what version of the install instructions I found there always seemed to be a step missing.

I finally came across one set of instructions that worked for me. The following steps are what I performed to get Apex 4 working.

1. Download Apex 4 from OTN to the directory

c:\apex_download

2. Unzip the Apex 4 download file. It will create the directory

c:\apex_download\Apex

3. Login into SQL*Plus as SYS with SYSDBA

4. Run the Apex 4 install script

c:\apex_download\Apex\apexins.sql SYSAUX SYSAUX TEMP /i/

where SYSAUX is the tablespace for Apex, TEMP is the temporary tablespace and /i/ is needed for possible upward compatability

This steps can take up to 30 minutes to run

5. Load the Apex images into the database.

    - Got to the c:\apex_download\Apex directory.

    - Log into SQL*Plus as SYS with SYSDBA

    - run   @apxldimg.sql

    - You will be asked to enter the directory for the images. Make sure that you enter the correct directory, otherwise it will not work. In my case it is

              c:\apex_download

6. Run the Configuration script. This will set up the XDB HTTP connection details.

        @apxconf.sql

   - enter the port number : 8080

   - password

7. Unlock the required schema

SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;
SQL > ALTER USER XDB ACCOUNT UNLOCK;
SQL > ALTER USER APEX_040000 ACCOUNT UNLOCK;
SQL > ALTER USER FLOWS_FILES ACCOUNT UNLOCK;
SQL > ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;

8. Open Apex. Open your browser and enter

     http://localhost:8080/apex

     there is a default workspace created

      Workspace = internal

      Username = admin

       Password = admin

6. Change the password. The fist time you login you will be prompted to change the password. The new password needs to have a number, upper and lower case characters and one special character

7. To get the the Apex Admin page

     http://localhost:8080/apex/apex_admin

Irish BI SIG–23rd June 2011

On Thursday 23rd June the Irish BI SIG had a networking event on the Mv Cillairne boat. This is a former training boat that has been converted into a restaurant and bar. The boat is moored beside the new convention centre on the quays in Dublin near the O2.

There was a good turn out, with a mixture of people from An Post, ICON, Vertice, Fijitsu and some independent consultants/contractors.

There was a few fee drinks and some food provided by the UKOUG. Many thanks for these.

There was lots of sharing of what BI related projects people have worked. There was some discussions of how the SIG can progress in the future, with the consensus that people will need to be willing to present on their projects and experiences.

Tony Cassidy, the SIG Chair, is hoping to get a few volunteers to present at the next SIG or maybe have another social networking event.

I also did my bit for the Oracle Scene magazine, in asking people would they write an article (even if it is a short one) for the next edition. I’ve recently joined the editorial team of Oracle Scene.

OOW Oracle Mix 2011–End Result

After 3 weeks the voting on Oracle Mix has come to an end.  There was a record number of submissions and voting this year on Oracle Mix.

Based on the results from previous years anyone with a vote count over 30 seemed to have gotten they presentation accepted for Oracle Open World.

With this years increase interest, number of presentations and the total number of votes cast it looks like the you will need at least 87 votes to get into the top 20.

The organisers say that the winners are select based on the total number of votes case, but they can adjust things as this see fit – whatever that means.

I had submitted 3 presentations – it was my first attempt at submitting to Oracle Open World. 

My best place (or most popular) presentation was ‘Using Oracle Data Miner 11gR2 & SQL Dev. for Data Mining’. For this presentation I received 56 votes, which puts me in 52nd place. 

So it looks like I’m way outside making to short list for OOW11.  Maybe next year.

I’ve also submitted 2 of my presentations to the annual UKOUK conference in December. The closing date was the 5th June and the results should be out in August.

Fingers crossed.

Vote for my Oracle Open World Presentation on Oracle Mix

I’ve submitted the following 3 presentations on Oracle Mix, to be included in the voting process.

Voting finishes on 19th June.

Please vote for my presentations.  As they stay about elections in Ireland, Vote early and Often.

Upskilling a Data Developer for Data Mining

Oracle Data Mining has been available for many years now and has proven to be a powerful tool but seems to be over looked by longer established products that are a lot more expensive. Many companies have put significant work into development their BI environments. But what can they do now to improve their organisational knowledge. This presentation will look at how a database developer is more suited to doing data mining than someone with a PhD in statistics. Using the ODM tool and the CRIP-DM life-cycle it will be demonstrated how a data mining project can be conducted

See my YouTube channel for a my videos on Oracle Data Miner 11g R2

http://www.youtube.com/user/btierney70/

 

Using Oracle Data Miner 11gR2 & SQL Dev. for Data Mining

With the release of the new Oracle Data Mining tool and it being part of SQL Developer, this presentation will have a look at how these too tools can be used in combination. In particular the presentation will focus on the Data Understanding stage of the CRISP-DM Life Cycle. Using the key elements of the Data Understand stage the presentation will look at how a database developer can use the new features of the new Oracle Data Mining tool in conjunction with SQL Developer to explore the data with the aim of gaining a key insight into the data.

See my YouTube channel for my videos on Oracle Data Miner 11g R2

http://www.youtube.com/user/btierney70/

 

How to Deploy your Oracle Data Miner 11g R2 Workflows in a Live Environment

The new Oracle Data Miner 11g R2 tool is now easier to develop your Data Mining models and workflows. A Data Mining project has two main stages. This presentation will look at how you take your Data Mining workflow and Data Mining Model, that have been developed using the new Oracle Data Miner 11g R2 tool. It will show you have to extract the SQL code from the work flow to perform the Data Transformations, execute the Data Mining Model, how you can link these to your new data and finally how you can apply the model.

See my YouTube channel for my videos on the Oracle Data Miner 11g R2 tool

http://www.youtube.com/user/btierney70/

Irish Oracle BI SIG Meeting - 23rd June

The next Irish Oracle BI SIG meeting will be on Thursday 23rd June starting at 6:30pm.

The format of this SIG meeting is a bit different from the previous ones.

This time the SIG meeting will be an informal networking event and there will be no demos or presentations.

The SIG event will be in the River View Bistro Bar, which is on the the MV Cillairne boat, that is moored beside the new convenion center on the quays. Check out its website
http://www.mvcillairne.com/

Creating ODM Schemas & Repository for ODM 11g R2

Before you can start using the Oracle Data Miner features that are now available in SQL Developer 3, there are a few steps you need to perform. This post will walk you through these steps and I have put together a video which goes into more detail. The video is available on my YouTube channel.

http://www.youtube.com/user/btierney70

Oracle Data Miner 11g R2 : Creating ODM User & Repository video

I will be posting more How To type videos over the coming weeks and months. Each video will focus in one one particular feature within the new Oracle Data Mining tool.

So following steps are necessary before you can start using the ODM tool

Set up of Oracle Data Miner tabs

To get the ODM tabs to display in SQL Developer, you need to go to the View menu and select the following from the Data Miner submenu

• Data Miner Connections
• Workflow Jobs
• Property Inspector

Create an ODM Schema

There are two main ways to create a Schema. The first and simplest way is to use SQL Developer. To do this you need to create a connection to SYS. Right click on the Other Users option and select Create User.

The second option is to use SQL*Plus to create the user. Using both methods you need to grant Connect & Resource privileges to the user.

Create the Repository

Before you can start using Oracle Data Mining, you need to create an Oracle Data Miner Repository in the database. Again there are two ways to do this. The simplest is to use the inbuilt functionality in SQL Developer. In the Oracle Data Miner Connections tab, double click on the ODM schema you have just created. SQL Developer will check the database to see if the ODM Repository exists in the database. If it will create the repository for you. But you will need to provide the SYS password.

The other way to create the repository is to run the installodmr.sql script that in available in the ‘datamining’ directory.

@installodmr.sql <default tablespace> <temp tablespace>

example:   @installodmr.sql USER TEMP

Create another ODM Schema

It is typical that you would need to have more than one schema for your data mining work. After creating the default Oracle schema, the next step is to grant the schema the privileges to use the Data Mining Repository. This script is called

usergrants.sql <DM Schema>

example:    @usergrants.sql DMUSER

Hint: The schema name needs to be in upper case. 

IMPORTANT: The last grant statement in the script may give an error. If this occurs then it is due to an invalid hidden character on the line. If you do a cut and paste of the grant statement and execute this statement, everything should run fine.

If you want to demo data to be created for this new ODM schema then you need to run

@instdemodata.sql <DM Schema>

example:    @instdemodata.sql DMUSER

All of these scripts can be found in SQL developer directories

\sqldeveloper\dataminer\scripts

Great set of Data Design Articles

For anyone starting out on data and database design there are lots and lots of books and articles to help get them started.

But for those people who have been doing database design for a while, it is always good to reflect on your approaches and techniques.

I recently attended a presentation by Steve Hoberman. If you ever get a chance to attend a data design presentation by him, I would highly recommend it.

He addition to his presentations and database design courses, he also writes for the website Information Management.

His series of articles can be found at

http://www.information-management.com/authors/32163.html

and his company website is

http://www.stevehoberman.com/

Guest Post: Can Database Developers do Data Mining ?

I was recently invited by Sandro Saitta, who runs the Data Mining Research blog (http://www.dataminingblog.com/), to write a guest blog post for him. The topic for this guest post was Can Database Developers do Data Mining ?

The original post is available at Guest Post- Can Database Developers do Data Mining –

Here is the main body of the post

Over the past 20 to 30 years Data Mining has been dominated by people with a background in Statistics. This is primarily due to the type of techniques employed in the various data mining tools. The purpose of this post is to highlight the possibility that database developers might be a more suitable type of person to have on a data mining project than someone with a statistics type background.

Lets take a look at the CRISP-DM lifecycle for data mining (Figure 1). Most people involved in data mining will be familiar with this life cycle.

Figure 1 – CRoss Industry Standard Process for Data Mining.

It is will documented that the first three steps in CRISP-DM can take up to 70% to 80% of the total project time. Why does it take so much time. Well the data miner has to start learning about the business in question, explore the data that exists, re-explore the business rules and understand etc. Then can they start the data preparation step.

Database developers within the organisation will have gathered a considerable amount of the required information because they would have been involved in developing the business applications. So a large saving in time can be achieved here as this will already have most of the business and data understanding. They are well equipped at querying the data, getting to the required data quicker. The database developers are also best equipped to perform the data preparation step.

If we skip onto the deployment step. Again the database developers will be required to implement/deploy the selected data mining model in the production environment.

The two remaining steps, Modelling and Evaluation, are perhaps the two steps that database developers are less suited too. But with a bit of training on Data Mining techniques and how to evaluate data mining models, they would be well able to complete the full data mining lifecycle.

If we take the stages of CRISP-DM that a database developer is best suited to, Business Understanding, Data Understanding, Data Preparation and Deployment, this would equate to approximately 80% to 85% of the total project. With a little bit of training and up skilling, database developers are the based kind of person to perform data mining within their organisation.

Oracle Data Miner Comes of Age

I’ve recently had an article titled Oracle Data Miner Comes of Age accepted for the June edition of the UKOUG Oracle Scene article.

I’ve been thinking of ways to try to promote this article and I’ve decided I would create two videos and post them on YouTube.

The first video is a short 1 minute introduction to the article. A taster kind of video. I’ve learned from my initial attempts at producing the video that

• It is more difficult than it looks
• The camera on my laptop is not install straight. That is why I’m looking to one side
• I need a better quality microphone

But perhaps the most interesting thing was that within a couple of hours of posting it up on YouTube (and not telling anyone about it), it was found and tweeted by Charlie Burger. Charlie is the Senior Director in charge of the Oracle Data Miner tool. He also very kindly tweeted about one of my blog postings on the New Features of Oracle Data Miner 11g R2.

You can find the introduction video to the article at

I will be posting an much long view, which will be based on the full article over the next couple of weeks