2

Following up on my previous post, I need to be able to query a database of 6M+ rows in the fastest way possible, so that this DB can be effectively used as a "remote" data source for a dynamic Excel report.

Like I said, normally I would store the data I need on a separate (perhaps hidden) worksheet and I would manipulate it through a second "control" sheet. This time, the size (i.e. number of rows) of my database prevents me from doing so (as you all know, excel cannot handle more than 1,4M rows).

The solution my IT guy put in place consists of holding the data on a txt file inside of a network folder. This far, I managed to query this file through ADO (slow but no mantainance needed) or to use it as a source to populate an indexed Access table, which I can then query (faster but requires more mantainance & additional software).

I feel both solutions, although viable, are sub-optimal. Plus it seems to me as all of this is but an unnecessary overcomplication. The txt file is actually an export from SAP BO, which the IT guy has access to through WEBI. Now, can't I just query the BO database through WEBI myself in a "dynamic" kind of way?

What I'm trying to say is, why can't I extract only bits of information at a time, on a need-to-know basis and directly from the primary source, instead of having all of the data transfered in bulk on a secondary/duplicate database?

Is this sort of "dynamic" queries even possible? Or will the "processing" times hinder the success of my approach? I need this whole thing to really feel istantaneuos, as if the data was already there and I'm not actually retrieving it all the times.

And most of all, can I do this through VBA? Unfortunately that's the only thing I will be having access to, I can't do this BO-side.

I'd like to thank you guys in advance for whatever help you can grant me!

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Bruder
  • 563
  • 3
  • 8
  • 18
  • of course you can , I have some code developed by vba to retrieve bo user information. – huangli Jun 25 '12 at 07:45
  • @huangli does that work with BO WEBI too? Can I see it? – Bruder Jun 25 '12 at 15:55
  • hi @Bruder, try this link http://kapilmalik.com/?p=116 and check the code , it absolutely work . Since it work with boe sdk , it could work with res sdk . Before that make sure you install boe client tool. – huangli Jun 27 '12 at 00:09
  • @huangli i tried your VBA code but I'm stuck. I know my user ID and password but I don't know what I'm supposed to type into the CMS box. Also I just realized I'm probably calling things by the wrong name. My IT guy keeps telling me that I have BO WEBI installed, but instead I think I only have BO Infoview, which I access through my web browser. I'm out of my element here, does that make sense to you? – Bruder Jul 03 '12 at 09:41

1 Answers1

2

Webi (short for Web Intelligence) is a front-end analytical reporting application from Business Objects. Your IT contact apparently has created (or has access to) such a Webi document, which retrieves data through a universe (an abstraction layer) from a database.

One way that you could use the data retrieved by Web Intelligence as a source and dynamically request bits instead of retrieving all information in one go, it to use a feature called BI Web Service. This will make data from Webi available as a web service, which you could then retrieve from within Excel. You can even make this dynamic by adding prompts which would put restrictions on the data retrieved.

Have a look at this page for a quick overview (or Google Web Intelligence BI Web Service for other tutorials).

Another approach could be to use the SDK, though as you're trying to manipulate Web Intelligence, your only language options are .NET or Java, as the Rebean SDK (used to talk to Webi) is not available for COM (i.e. VBA/VBScript/…).

Note: if you're using BusinessObjects BI 4.x, remember that the Rebean SDK is actually deprecated and replaced by a REST SDK. This could make it possible to approach Webi using VBA after all.

That being said, I'm not quite sure if this is the best approach, as you're actually introducing several intermediate layers:

  1. Database (holding the data you want to retrieve)
  2. Universe (semantic abstraction layer)
  3. Web Intelligence
  4. A way to get data out of Webi (manual export, web service, SDK, …)
  5. Excel

Depending on your license and what you're trying to achieve, Xcelsius or Design Studio (BusinessObjects BI 4.x) could also be a viable alternative to the Excel front-end, thereby eliminating layers 3 to 4 (and replacing layer 5). The former's back-end is actually heavily based on Excel (although there's no VBA support). Design Studio allows scripting in JavaScript.

DocZerø
  • 8,037
  • 11
  • 38
  • 66
  • 1
    @user1704812 Describing how to use VBA with a web service is out of scope for this question and really warrants a new question to be posted. If you're looking for information how to create a web service within Web Intelligence, have a look at the [Web Intelligence User's Guide](http://help.sap.com/businessobject/product_guides/sbo41/en/sbo41sp5_webi_user_guide_en.pdf) and check section 6.2: *Sharing content with other applications*. This manual is for BI 4.1 SP5, if you're using a different version, download the appropriate manual [here](http://help.sap.com/bowebi41?current=analytics). – DocZerø May 07 '15 at 06:51