How to download large data set from Business Objects

mankowitz
mankowitz used Ask the Experts™
on
Hi-

Looking for some practical advice. I'd like to download about 10 million rows of data from a SAP BusinessObjects data warehouse. I don't have direct access to the underlying database(s). The problem is that when I build queries, the result usually times out before I can download any data. It seems that it chokes at about a million rows, so I'd like to divide the data into manageable chunks. I DO have the capability of writing queries in SQL directly, but I don' t think I am allowed to make temporary tables, So....

How do I write a query that downloads a fraction (say 5%) of the database each time I run it? Is there any way to automate this process and then cause it to download the results automatically? For that matter, is there any way to determine the underlying database?

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I DO have the capability of writing queries in SQL directly, but I don' t think I am allowed to make temporary tables, So....
If you have access to the SQL Server then you can also create temporary tables. Temporary tables doesn't really exists in the database and will be deleted as soon you close your connection.


How do I write a query that downloads a fraction (say 5%) of the database each time I run it?
SELECT TOP 5 PERCENT *
FROM TableName
WHERE ...
ORDER BY ...
I'll have to see if I can do that, but what do I do about the next 5 percent?  In MySQL, I can do LIMIT x,y to select a certain number of rows.

Also, Is there a way to automate the process?

Does this work in SAP Business Objects, or only in MSSQL?
IT Engineer
Distinguished Expert 2017
Commented:
what do I do about the next 5 percent?
You can add a WHERE clause to filter out the processed rows so you'll deal only with the remain records.

Also, Is there a way to automate the process?
Sure. Create a Job in SQL Agent.

Does this work in SAP Business Objects, or only in MSSQL?
This is purely MSSQL but I think SAP can call MSSQL scripts and jobs.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial