1/ Describe excel file (Physical & logical) in Topology...see ODI help for exemple
2/ Create a model in designer
3/ Create an interface with excel datastore in source, oracle datastore in target and use a LKM like LKM SQL to Oracle to load data...and the IKM you need
your xls file is it heavy?
1/ sqlloader is in fact an opportunity for heavy file. If it's your case, use a LKM SQL to ORACLE (SnpsSqlUnload, loader) witch unload data in flat file and load it. or use csv format and load directly your data with LKM File to Oracle (SqlLoader)
2/but if your file is light, you must use LKM presented before...
To simply transfer from Excel to Oracle, If you are looking at a one time load scenario or a very infrequent load, you can try Application Express (A GUI tool that can not only load but also create applications).
else you can also try sqlloader...
If you are looking at an periodic load then ODI/OWB would be the choices you have...
Apex could be used if your excel-files aren't using any macro's, pivot tables, ... otherwise it would be time consuming to get the excel files sorted properly so can import them using Apex.
I would say the options for importing complex excell files one-time-only (migration from excel to oracle), would be:
- external tables
- open source ETL tool
Correct me if I'm wrong but this would be the options I have wright?
I'm wondering if you have to do this step just ones, what would be the preferred solution? I have complex excel-files and a oracle db to transform to migrate to oracle 10g, new db model, new schema and new front-end.
The ODI seems to be a powerful tool to transform from source to target in a visual way, without having to write pl/sql procedures ;o), but is it more time-consuming to set everything up than to actually perform the migration?
If your need is to upgrad 9i db to a 10g. you should use Oracle migration tool. But the migration tool will not make any transformation on your data. If you have many transformations, ODI will help you in your development even if it is for one shot.
Which guide did you follow to get started With Oracle Data Integrator? I've followed the Oracle Data Integrator Users' Guide & Knowledge Module Developer's Guide - Fundamentals but I didn't find any starting points on defining my own Migration path in the ODI?
I'm keeping a blog about my experiences with ODI to let the community know how everything works, how I got started for my project, etc.
I'll keep you posted via this blog on my thoughts, experience, way of work, etc.
Feel free to comment and advice on my blog's so I know I'm keeping the right track !
I have a question conerning the definition of an Oracle Physical Schema in my Toplogy and there's no example of an Oracle DataStore in the 'getting started'-project (which is weard also ;o), no Oracle DB used).
When I define a Physcial Schema for my Oracle Db Schema I have 2 dropdowns in the definition-tab to define the Schema and Work Schema.
Why do I need to define these 2 schema's and what's the difference between the work schema and the schema?
Do I need to define Work-repository for the work schema or do I need to define a different schema so the different system-views used by ODI won't be created in my own user-defined schema?
Don't forget ODI is more an ELT than an ETL...and then need a workspace in genarally on target.
For Oracle, If you have a schema called TARGET, create another one for ODI staging area (workspace), with his own tablespace if you want...
In topology (physical schema), declare TARGET as your schema, ODI_SA (for example) as the workspace associated to TARGET.
Then the logical schema can be TARGET or ODI_SA....all standard KM are developed to create temporary tables in the workspace then in ODI_SA...
I'm sorry I didn't post the question to you to, because I've already noticed you are answering a lot of posts on this forum.
Just to make sure I understand you correctly, the work schema will hold the different work tables used by ODI and that's it.
This work-schema doesn't need to have my user-defined tables, triggers, db objects etc. this is just an empty schema that will hold the work objects, correct?
I've already created a master and a work repository (= snpw, as suggested in the install guide), can I use the work repository as the work schema, is that the way to go or is this work schema another schema?
You're correct...work-schema is an empty schema dedicated to ODI work objects (C$/I$/E$/J$/JV$...tables,views, triggers...coming from ODI and used for load, forCDC,...)
Your personnal DB objects could be in your schema
and called by the same ODI function ...getObjectName( "","","" )...
The last parameter is the physical schemain the logical schema:
D for Data...your schema
W for Work....ODI work schema