Pulling Data from Two Sources

[lucyv]

Does anybody know the best way to pull data from two different data sources (i.e. Access & SQL Server) and then merge the data together.

I've successfully done this by connecting to the Access database, pull the necessary information over and store it into a DataTable. I then did the same thing for the SQL Server database, and then add both DataTables into a Dataset.

I then used the DataRelation object to link the two together via a related field to show the related records.

Even though this is working, I was wondering if I'm making things too complicated. If this is not the most efficient way, can somebody tell me what is?

Thanks in advance.

-lucyv

 

[lucyv]

Also, is my previous post on the same lines as pulling data in a "cubed" format? I know I should probably be posting this in another forum, but I was hoping some gurus from this forum could help me out.

At work we have a huge database that has all sort of normalized data. My boss has asked me to look into pulling data into some kind of "cube" form. I've done a little bit of research but I'm fairly new to OLAP and Cubed data.

Any insight would also be greatly appreciated.

-lucyv

 

[ThatRickGuy]

cubed" data seems to be some new whizbang name for calc or agregate tables from what I can tell. With a highly normalized database, there is little redundant data, and everything is linked through relationships. This works well for low speed requirement, single item loading systems (ie: your standard desktop office app). This doesn't work so good for mass reporting, and highly complexe joins and processes. To help make those processes faster, you can compile a set of data into a denormalized table. That way, you can load a large number of records at runtime with only using one table that was built over night, or on some other schedule. Much faster for processing, but you lose the live data aspect. (Take that with a grain of salt, my understanding that cube data is the same as calc/agregate data as I know it is based off of a few minutes searching on google)

That said, your way of pulling the SQL Server and Access data is correct. You'll either have to pull them local and create a relation between two tables, or you can pull one table local, add columns to it and populate those columns with the other source's data.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[chrissie1]

you could also setup a linked server in sqlserver to the access database.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[lucyv]

Thank you for both of your answers. This helps out alot.

-lucyv