best data transfer methods into SQL

[maudedo]

Hello All
Apologies in advance for this ambiguous request, but I am sure someone will have an idea of some solutions to this issue..please??

we have a database designed in New Era, and the data is backed up to a Unix server overnight. This database and data is held on a Novell network.

We also have a SQL back end database with Access front end, this is held on a different server (Windows).

We transfer the data from the Unix server every morning to this SQL database using the only method we have available:

5 Business objects reports are run using Automate and the results exported to csv files on the Novell network (at about 9mb each). this process starts about 2am and completes about 530 am.

SQL enterprise manager then runs scheduled DTS jobs which pick up these text/csv files into temporary tables, runs data clean up and then appends/updates the SQL database with the information held on those cleaned temporary tables. The whole process finishing about 7am.

This database is then copied over to a back up server, and is also copied to a data warehouse version of the software (still in progress of creating). Therefore all data movement between software is sometimes still taking place at 9am affecting users.

I know I know, I hear you shouting at me already about this convoluted exercise, but i promise, this was the only solution available to us! Business objects is the only access we have to the data from the main new era database, and then the universe design is so restrictive that we have to have 5 files to pick up Key IDs.. its like having inner links across the board. nightmare!
anyway..

Every now and again this process fails due to Business objects access, or comma delimitted issues etc, so any break in the above process impacts on the main SQL database availability/performance.

We are therefore considering alternative solutions to obtaining data from the Unix server straight into the SQL Database, but I am unsure what to suggest, or what methods are the most stable?

Our IT department are very protective of what tools they provide and also the time scales in which they deliver solutions (for example any suggestions we make now are considered for delivery in 12 months or more!)

We have to get rid of the Business Objects route as a. business objects is a reporting tool not a data transfer tool, and also b. we would gain a few hours for data warehousing functions and still be comfortable that data will be ready as it should for 9am start.

I would like to suggest an XML solution, however, based on the amount of information we update/change/append then is this a viable solution for batch of information?

are there any other suggestions which are quite viable and our IT department would encourage with minimal development costs (as they charge the earth for the most simple of solutions)?

I would also like to suggest more than 1 option and also ask them to provide a solution, but as it goes with our IT department we always get a lot further if we show that we have considered more than one solution (isnt that the job of the business analyst??)

I would therefore be very grateful for any suggestions.

thanks ever so

 

[Crowley16]

not really heard of new era or used Novell network so not sure about that area, however I would expect any reasonable database engine nowadays to be ODBCable, so maybe it's worth while trying to see if you can get some sort of direct querying happening, instead of doing this text file stuff.

also, why not have a centra data warehouse where you dump all the data to, do the processing, and just get everyone using the cleaned data?
It seems to me that having to dump to multiple servers is a waste of time...

--------------------
Procrastinate Now!

 

[maudedo]

Hi Crowley
I use odbc links when accessing the SQL database through Access or excel for MI/reporting, however, I am unsure if we can odbc link to Unix to source the primary data, have i picked this up correctly? I will ask if this is possible. Thanks.

also, the purpose of the SQL database is that the New era database (of which I am also unfamiliar) was not designed to full spec so a lot of the working parts of the database we needed didnt get done. Every request in 7 years to have 1 working database has gone unheaded. Therefore the SQL database was designed as a multi functional application. 1. to manage the missing module which is a working database (this is read write), and 2. to concatenate/merge the information from the New era db (read only), for MI as well as a more overall picture of where our data stands (due to universe design in BO, Access is more versatile). The datawarehouse is primarily for the purpose of MI (as running any complex report in the SQL database on the 'live' tables cause users to timeout).

This is therefore why we were advised to have SQL as another working database but have an Access data warehouse which houses a datadump of the information of the SQL database.

 

[Crowley16]

I would certainly shy away from access for what you want since it's not really designed as a enterprise level database engine.

There is no reason why you cannot have all your bespoke tables on sql server, either split across databases or merged in one single database, but organised so that some tables are read only while others are used for both.
Certainly having only one data dump will save you a lot of time because you're not having to re-send the data anywhere else, which I believe is your main problem at the moment.

Also, I suspect, with the complex reports, the reason your users are timing out is that you are locking the tables being reported on. You can in-fact change the views/sprocs/udfs that the reports are based on to not hold any locks (although do consider the implications here...) which will allow both complicated reporting and user interactions simultaneously.
Of course, you can just have 2 sets of tables (or 2 databases), with replication between them or triggers or whatever, and reports happen on one, and transactions happens on another...

--------------------
Procrastinate Now!

 

[maudedo]

Hi
yes the two underlying problems is the business objects download and then resending the data to different databases. I think that is what our IT support person was attempting to achieve with having 2 databases but instead of replication or triggers he went down the option of copy table/queries option in DTS which is causing me no end of trouble.

The IT manager attempted to change the locks property on procedures etc but ended up people just grinding to a halt in refreshing of their screens or flicking through tabs or simple searches on IDs which is where the 'copy of database on one server and read only copy on another' idea came from. So far I have not yet found this to be beneficial as, as soon as I have to run an update query on the backup database, people complain of performance issues on the live database.

so, if we get the data from the unix server in a more efficiient manner we can then concentrate on getting the SQL database and its replica part sorted accordingly. The CSV files are causing more issues than that of the different versions of the databases, both in time and errors experienced.

 

[MollyAnne]

http://ww2.pervasive.com/Integration/Products/Pages/PervasiveDataIntegrator.aspx

It used to be called Data Junction until it was purchased by Pervasive. I've used the product off and on since 1999. Have Data Integrator (formerly known as Data Junction)ftp the files from the Unix server to the SQL server and convert them to SQL or whatever. It is the fastest data conversion tool I have ever seen - faster than SQL Server DTS.