Loading adhoc files to database as tables

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

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

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

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.

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).

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.

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.