8 datasources: best tool to extract and combine data from all into one?

(1) Share

Report

Posted on by Community Member

Is there one tool that will link to 8 (fairly large) datasources and bring the combined data out into a new file and/or report?

With Jet Reporting, you pay by datasource (a pretty high price in my opinion). Is there a more 'cost effective' solution available?

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Suggested answer

keoma 32,729 on at

Like (0)

Report
Copy link

Link copied!

what kind of datasources?

try using excel (linking the datasources, using vba)

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

I have been using Excel.

I can get the data down (675000++ records or so in each sheet).

But when I go to produce a Pivot Table on the records, it bombs.

Looking for something more robust (and the 1,000,000 row limit looks to be an issue with some of the files).

Thanks

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Have you looked at Azure at all?

You can easily pull data from each source and format it into reports how ever you want.

You do pay a bit for the "resources" but its only when they are used.

Was this reply helpful? Yes No
Suggested answer

keoma 32,729 on at

Like (0)

Report
Copy link

Link copied!

then try using ms access (link the datasources) or use stored procedurs in sql server (tsql).

links:

msdn.microsoft.com/.../ff772782.aspx

msdn.microsoft.com/.../ms156450.aspx

msdn.microsoft.com/.../ms156450.aspx

better way would be:

import/migrate all your base data (from csv files/excel docs/etc.) to a new sql server database. after that it would be easy to process and analyse the data.

sample tool: SQL Server Import Wizard

msdn.microsoft.com/.../ms140052.aspx

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Our implementation group is telling us we can use Power BI; they recommend AGAINST Jet Reporting?! Not a Microsoft product maybe?

Anyway, with massive and real security concerns, do we want to use a cloud-based system? Is it robust enough for analyzing multi-million record datasets across multiple systems and locations?

We are audited and must meet security requirements for our processing. Would something like Power BI or Tableau meet strict requirements (apparently they link to Facebook and other social media, a massive no-no here).

Thanks

**edit: on further investigation I ran across this from Microsoft's own site

"

there is a potential risk for these queries to contain malicious SQL code that could delete or modify content in a database when executed. Because of this risk, we have an existing security prompt whenever users try to run a native database query outside of the data source dialogs. It turns out that lots of customers are using this capability within Custom Columns or similar scenarios, so that they can dynamically build and execute native database queries. The downside of this is that they would get prompted for approval of every distinct SQL statement, which would make the experience very inconvenient.

In order to enable these customers to achieve their scenarios, we’re introducing an option to disable Native Database Query security prompts. Please use this under your own responsibility and beware of the potential risks mentioned above"

Was this reply helpful? Yes No
Suggested answer

Miguel Llorca Gómez 7,019 User Group Leader on at

Like (0)

Report
Copy link

Link copied!

You should try PowerBI (www.powerbi.com). You can create your Excel, define a PowerView report, use PowerPivot, etc. and then upload that :-)

It's really powerfull and easy.

Good luck!

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Is this essentially the same as downloading to a Excel spreadsheet and then creating a pivot table, because that doesn't work, not with 'small' downloads of 200,000 records, and definitely not with 2,000,000 row tables. With the 200,000 records, Excel just hangs, and there doesn't seem to be enough memory chips available to get it to finish the pivot table creation...it just hangs.

We'll try the free version on tables without security issues and see what happens.

Thanks

Was this reply helpful? Yes No