I am as new as they come to BOB and Business Objects and would like some advice. All i need to do is pull out data from Business Objects and dump it in an access Database then i will be happy. I only have front end access and have no idea as to the underlying structure of the source.
I am trying to find my way around and have been trying a few bits and bobs to familiarise myself but struggling. i have gone as far as epxorting a few odd records to Excel that just about it.
Since you say you are new to BO, first thing you have to know is that BO is not a database to pull data from
Your post subject is about exporting/saving data in Access.
But its not clear what you mean by WebI Infoview, is it the webI report you are talking about which you want to save into Access?
Please give more details
Haider,
Thank you for your reply and correction. I will try and clarify further.
Yes i think thats the 1 webI . Im in the Web Intelligence Document and i was wanting to save the results of my Query/Report in Access.
It would have been better if there was a way of running a Report of everything in the Universe (not sure if thats the right to put it)
Mark - Thank you jus realised you replied. The number of records is over 800 000 any ideas of how i can achieve this in excel without duplicating?
The db source i currently have is a very old one and i dont think its even normalised and at present even with the latest BO it takes years to a run a simple report. I would like to create a new DB in Sql Server thus Access and Excel are just for transporting.
You’d definitely be in a better position if you can access the existing datasource directly. If the database has an ODBC driver, you can import it in its entirety into Access.
With that said, the ability to migrate the data using only WebI depends on what’s available in the universe(*see note below). Your best bet would be to create a series of queries in the report, each query taking all available fields from each universe table. Save the report, then schedule it to CSV. You’ll get a separate CSV file for each query, which will more or less match the content of each table.
*You can modify the SQL in queries to include fields that don’t have associated universe objects.
Thanks again for your continued advice. I am only limited to front end access and Reports appear to be my own option. I am now exploring how based i can run reports in the universe (as Joe said)…
I’ll re-iterate only once that what you’re doing sounds a little ‘the wrong way around’ but then I can imagine there are constraints to what you are doing that prevent the ‘right’ solution going forward? Is your Business Objects system locked away behind a team that manages it and you have front-end access only?
In short you should not have a report that takes several hours - that needs fixing. You don’t need any more tools to get that done right, just the tools already in use applied properly
Don’t take no for an answer and badger your way into getting what you need - your employer will thank you in the end for saving money.
Lecture over, let’s see if your immediate question can be answered.
The report-CSV-Access-SQL Server part will work OK, but you can cut a long story short. Milk the report for it’s SQL, connect SQL Server directly to the source via OLEDB/ODBC (see OPENROWSET) then you are go. You will of course keep the badly performing query but you will at least get identical results.
An alternative would be to take the queries from the report, rebuild them in Query As A Web Service, then you can ‘consume’ them from wherever you like in Microsoft apps via VBA (a quick google will find you the code for doing just that). You can also do the same thing from SQL server, as to be honest it sounds like Access is a 5th wheel in what you’re attempting. (If you like the front end, then use it as a data project against your SQL server).
So, let’s assume that your starting point is Business Objects and your end point is SQL Server (completely ar$e about face but never mind!)
You could skip MS Access completely - SQL Server can import data from xls, csv, txt files with no problem at all, allowing you to create the table and reuse the transformations if you want to redo the extract.
Save you BO extracts as whatever format you choose and then is SQL Server Management Studio, right-click on the target database, choose tasks then import data and you’ll get the import wizard
Thanks Mark
A great thank you guys i think im beginning to see the light now.
Yes you are right philmorris i can only see the front end which appears limited.
You make it sound easy i will try and follow your advice and i know it will be a bumpy ride but i think i would have learnt a lot when this is done.
So am i right to assume once i decide on the objects and design my report i then save this and use the SQL behind it?
Yup, just click the SQL button in your reports, copy & paste.
That topic shows a nice example of OPENROWSET syntax, and it’s cool you can now use that kind of thing in a universe, but once copied & pasted you shouldn’t need your universe or Business Objects system anymore
So many times I hear about good people being locked out of using Business Objects properly. It was meant to open up things not lock ppl out . I’d recommend digging in and asking WHY a lot until you find the techie willing to help you out. You may even find another project already using BODI or something similar to extract your legacy DB into a better platform for analysis.
(BOB member since 2005-07-18)
(BOB member since 2002-08-29)
(BOB member since 2002-11-12)
(BOB member since 2002-11-12)