Skip to Content

Well, I’ll write some posts for people like me, VBA’s nostalgics.

Remember, there was a great time for older versions of Business Objects, when you can get or create wonderful tools with Excel and VBA. These tools were open, and we could improve it easily, with a minimum of VBA’s knowledge. But VBA didn’t evolve with BI4. And I haven’t found specific Excel tools for BI4, to describe an unx file for example.

But … with RESTful API you can use any langage … Java, Powershell, Python … and why not Excel ? Excel can work on Big Data, why not RESTful API ?

I asked myself how we can use Excel with RESTful API, I made some tests/scripts and I’ll share my feedback in this blog, following these posts :

  1. Introduction (this post)
  2. Logon/logoff sample
  3. List universes
  4. List universe’s content
  5. List documents using a universe
  6. Change universe in documents

Notes :

  • I’m not a VBA guru, the code isn’t perfect …
  • There are some missing features, because i’ve no time to develop it … The code is open, I’ll give my todo list, you can improve the scripts if you want !
  • Sorry for the misspellings … I skipped some English class hours at school 😉

Some advantages of RESTful Excel Tools :

I think Excel is not the easiest tool when you want manipulate a RESTful API, but like someone said, “yes you can” !

And Excel has the great advantage to be on most pc …

With the older VBA SDK the BI4 Clients are needed. And sometimes the version of dll was in conflict with the downloaded excel script … With RESTful API the BI4 Clients are not more needed. You just have to reference two libraries :

dependances dll.png


These dlls are out-of-the-box with Excel (tested with Office 2010 and +), it’s so easy !

Some disadvantages of RESTful Excel Tools :

But with RESTful API you can’t do everything like VBA. You can’t get all informations on universe for example …

And if you call the RESTful web service a lot of time, Excel will crash …

I’ll explain this later.

Next Step : let’s start with logon/logoff !

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Anthony MULLER

    Hello Stéphane,

    Thanks a lot for this series of articles about VBA and BI4 RESTful API.

    Please, could you elaborate about limitations you discovered about what you could do before and you can’t anymore? (you told about informations of universe?)

    Best regards,

    Anthony

    (0) 
    1. Stéphane DUPONT Post author

      Hi Anthony,

      Well, i’ve said “I’ll explain this later.” 😉


      But for example i can’t get sql definition of objects with RESTful SDK (in Request GET /universes/<universeID>?aggregated=<aggregated>) … Neither database tables …


      Perhaps it’s possible, but I didn’t found howto do this in the documentation (in 4.1 SP7).


      Or i’ve not be precise ? I wanted to talk about difference between RESTful SDK and BO/BI4 VBA libraries. Like this tool : Document a universe using Excel and the Designer SDK


      Best Regards,

      Stéphane

      (0) 

Leave a Reply