Loading adhoc files to database as tables
4 posters
Page 1 of 1
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
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
Re: Loading adhoc files to database as tables
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
Re: Loading adhoc files to database as 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
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
Re: Loading adhoc files to database as tables
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
Re: Loading adhoc files to database as tables
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).
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).
Re: Loading adhoc files to database as tables
We do use Aginity but never looked deep into it but looks like this could be the way to go.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.
.
I am assuming you're suggesting in house application to do that as another option.ngalemmo wrote:You can also write you own, more end-user friendly front-end to bring in such data on-the-fly.
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
Re: Loading adhoc files to database as tables
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.
And, yes, I was suggesting an in-house application as an option. It would not be terribly difficult to do.

» Automating dimension and fact loading ETL at the database level - Good or Bad?
» Creating Database View on Fact Table with Dimension tables
» Looking for advise on loading keys into factless fact tables
» Historical and Incremental Loading of Dimension and Fact tables
» BI Adhoc Reporting on OLTP model
» Creating Database View on Fact Table with Dimension tables
» Looking for advise on loading keys into factless fact tables
» Historical and Incremental Loading of Dimension and Fact tables
» BI Adhoc Reporting on OLTP model
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum