Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Loading adhoc files to database as tables

Go down

Loading adhoc files to database as tables Empty Loading adhoc files to database as tables

Post  sarah_id1 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

Back to top Go down

Loading adhoc files to database as tables Empty Re: Loading adhoc files to database as tables

Post  Jeff Smith 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

Back to top Go down

Loading adhoc files to database as tables Empty Re: Loading adhoc files to database as tables

Post  Mike Honey 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
Mike Honey
Mike Honey

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

http://www.mangasolutions.com

Back to top Go down

Loading adhoc files to database as tables Empty Re: Loading adhoc files to database as tables

Post  sarah_id1 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

Back to top Go down

Loading adhoc files to database as tables Empty Re: Loading adhoc files to database as tables

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

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

http://aginity.com

Back to top Go down

Loading adhoc files to database as tables Empty Re: Loading adhoc files to database as tables

Post  sarah_id1 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

Back to top Go down

Loading adhoc files to database as tables Empty Re: Loading adhoc files to database as tables

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Loading adhoc files to database as tables Empty Re: Loading adhoc files to database as tables

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top


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