How to download large data set from Business Objects

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!
LVL 24
mankowitzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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 ...
mankowitzAuthor Commented:
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?
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.