Loading adhoc files to database as tables

View previous topic View next topic Go down

Loading adhoc files to database as tables

Post  sarah_id1 on Tue Dec 04, 2012 11:08 am

Hi,

We have requirement from end users to upload data from files to a database as tables so they could join that with the current operational data in the datawarehouse for ad hoc queries. These are not static structured files but ad hoc data sourced from different places which are of different structure, size. What is the best way to enable this uploading of file data as a tables with minimal manual effort of creating tables every time and then uploading them.

Sarah

sarah_id1

Posts : 9
Join date : 2010-11-18

View user profile

Back to top Go down

Re: Loading adhoc files to database as tables

Post  Jeff Smith on Tue Dec 04, 2012 3:35 pm

Is creating kind of a sand box for them an option? Create a database that they control and let them create and load tables.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Loading adhoc files to database as tables

Post  Mike Honey on Tue Dec 04, 2012 10:30 pm

Hi Sarah,

My favourite tool for this is the SQL Server 2012 Master Data Services component, in particular the Excel Add-In. You can start by quickly loading any Excel table of data into MDS, and then access that data via a generated view. As requirements mature you can add on security, validation, hierarchies (including ragged), ETL integration etc etc.

Mike
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: Loading adhoc files to database as tables

Post  sarah_id1 on Wed Dec 05, 2012 6:48 am

Jeff Smith wrote:Is creating kind of a sand box for them an option? Create a database that they control and let them create and load tables.

They want to join this file data (using SQL) with the mart data which is on the appliance box. We would given them an adhoc area but they want an intuitive way to upload this data without going through the pain of scripting every time. I really liked the other suggestion about SQL Server but we use the appliance box Netezza.

sarah_id1

Posts : 9
Join date : 2010-11-18

View user profile

Back to top Go down

Re: Loading adhoc files to database as tables

Post  ngalemmo on Wed Dec 05, 2012 1:21 pm

If you don't already have it, download and install Aginity's Netezza Workbench (aginity.com). It has support to import external files into tables. You can also write you own, more end-user friendly front-end to bring in such data on-the-fly.

To what Jeff is alluding to, the users should have their own area to load these tables. They should not coexist with 'real' data warehouse tables. On Netezza you do this by creating a separate database to server as the place to receive external data. You can join these tables with the data warehouse by using full path table names (database..table).
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Loading adhoc files to database as tables

Post  sarah_id1 on Thu Dec 06, 2012 7:08 am

ngalemmo wrote:If you don't already have it, download and install Aginity's Netezza Workbench (aginity.com). It has support to import external files into tables.
.
We do use Aginity but never looked deep into it but looks like this could be the way to go.

ngalemmo wrote:You can also write you own, more end-user friendly front-end to bring in such data on-the-fly.
I am assuming you're suggesting in house application to do that as another option.

ngalemmo wrote:
To what Jeff is alluding to, the users should have their own area to load these tables. They should not coexist with 'real' data warehouse tables. On Netezza you do this by creating a separate database to server as the place to receive external data. You can join these tables with the data warehouse by using full path table names (database..table).

Yes this is the idea. A separate Adhoc database to play with restricted resource.

Thanks that was really useful.

sarah_id1

Posts : 9
Join date : 2010-11-18

View user profile

Back to top Go down

Re: Loading adhoc files to database as tables

Post  ngalemmo on Thu Dec 06, 2012 4:37 pm

In Workbench, look under Tools/Import...

And, yes, I was suggesting an in-house application as an option. It would not be terribly difficult to do.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Loading adhoc files to database as tables

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum