Sandor de Heij's blog

A couple of remarks with regard to SAP BusinessObjects (Advanced) Analysis for Microsoft Excel

2010-12-28T15:16:12Z

I had some spare time on my hands last week and thought it would be interesting to delve into (Advanced) Analysis for Microsoft Excel, the first product to come out of development after SAP’s acquisition of Business Objects (correct me if I’m wrong). My colleagues were quite impressed with what they had already seen of an early version at TechEd Las Vegas in 2009, so I was quite eager to get the installation going, so I could share some of my new-found insight with my colleagues (I’m in the lead when it comes to OLAP within our practice). Besides, I am a big fan of cubes and still think that, despite column-based storage and in-memory technology, they have a rightfully earned place in any Business Intelligence landscape.

Now, I don’t know about you, but I’m one of those people that tend to dive straight into things, without first reading through all of the documentation (of course only when it comes to non-production environments). This goes well most of the time, but sometimes it means I’ll run into problems and have to start from scratch and/or start reading the documentation after all. This was one of those times. Fortunately, I’d made a snapshot of my front end image (I use two, one for front end BOBJ applications and one for the back end stuff), so it was easy to revert to the state it was in originally.

The first thing I noticed when downloading the application was that since its initial release in June (probably a ramp-up release) five service packs have been released. The next thing that got my attention was the fact that you don’t get to choose whether you want the functionality for either PowerPoint or Excel. No, you either get both – if we’re talking about 2007 – or only Excel – in case we’re talking about 2003. In my situation, I only had Excel 2007 installed on my image to use in combination with Xcelsius. But it turns out that this situation isn’t really an option, as the add-in throws an error related to the fact that PowerPoint is not installed, when starting up the add-in for Excel. The only remedy, it seemed, was to also install PowerPoint on my machine (long live MSDN accounts). This was actually a bit of a *sigh* moment for me.

I then proceeded to start up the application and ran into the next problem (see above). Apparently a dll file, BiXllFunctions, failed to register, meaning the whole add-in simply didn’t show in Excel. In PowerPoint, however, the extra tab did show up. But when trying to connect to my BOE XI 3.1 system, none of the connections to my cubes were visible, whereas in InfoView they were (see below).

So, it wasn’t working at all for me. And even though Windows Server 2008 was supported, I decided to try over on Windows 7 64-bit, hoping it was some sort of glitch, but, alas, it wasn’t, because the same thing occurred.

Of course I proceeded to do a little search on Google, BOB and SAP Notes, but there wasn’t really anything to find, unfortunately. I also checked the new book that came out on SAP Press (which a colleague had brought in), to see if I’d done anything wrong during installation, but that didn’t seem the case, meaning I’m slowly starting to run out of options. I think I’ll just post a summary of what I’ve written here on BOB and, perhaps, SDN to see if someone else has experienced the same issue and has managed to solve it.

Welcome back ForceMerge() AKA MultiCube()!

2010-12-01T18:52:28Z

I have always been a big fan of the MultiCube() function that is available in Desktop Intelligence. I can still remember the first time I learned about the function. A colleague of mine at that time was struggling with a report with multiple data providers he had built. He was really annoyed, because he couldn’t get the measures to aggregate properly. Which is exactly the purpose of this function, so when he found out about the (proper) use of the function he was really excited he could make it work after all.

Unfortunately the same functionality wasn’t available in Web Intelligence until the introduction of XI 3.0. Why is beyond me, because having the ability to merge data providers on the document level and not being able to use the MultiCube function was a bit of nuisance. It is now, however, even though they gave the function a different name: ForceMerge.

To me the help text and especially the example that come with the MultiCube() function is much more helpful than the one that comes with the ForceMerge() function. The ForceMerge functions description states:

Includes synchronized dimensions in measure calculations when the dimensions are not in the measure’s calculation context

While the Desktop Intelligence description isn’t much better, the example is. So what I am going to try and do is recreate the example given in Desktop Intelligence in a Web Intelligence document. We’ll make use of the Island Resort Marketing Universe.

You have two queries. Query 1 returns city and country of origin of the customer.

Query 2 returns revenue per city.

Data Provider Results

Auto-merge dimensions is ticked by default, so the two city objects are automatically merged.

The results are as follows.

And combining the results is no problem either (there are obviously no customers living in certain cities).

The issue becomes evident when I remove the City object from the block. Keep in mind that the two data providers are merged by City. When that object is no longer present in the block, the measures should roll up (if set properly in the Universe and not disabled by the relevant property in the document).

That doesn’t seem right, right?

ForceMerge() to the rescue

Looking again at the description of the help text for ForceMerge we can see that this clearly is a situation where the dimension City definitely is not part of the measure’s calcuation context (because we deleted it from the block). So what do we need to do? Right, add the function to the measure.

=ForceMerge([Revenue])

And look what automagically happens to the results.

It seems about right, since the clients from the countries that don’t display any revenue are there in the database but never spent a dime in any of the resorts. Wondering why the block behaves as if an outer join is used? So am I, but perhaps that’s something for some other time.

In short

ForceMerge() AKA MultiCube() function) changes the behaviour of the, in this case Web Intelligence, report engine. Even though the merged dimension is no longer in the block, they are still taken into account by the measures that have the function wrapped around them.

Unfortunately it won’t fix every problem you’ll encounter, but it’s definitely worth taking a look at when you are forced to use several Universes and to combine the data to display in your document.

Attention

The help file also says:

ForceMerge returns #MULTIVALUE if applied to a smart measure because the grouping set necessary to calculate the smart measure does not exist.

SQL Server 2008 R2 is RTM

2010-04-27T20:33:52Z

A co-worker of mine told me the other day that SQL Server 2008 R2 had gone gold. A bit of research revealed that Ted Kummert, senior vice president of the Business Platform Division at Microsoft apparently announced the RTM status in a conference call. Customers will be able to get this latest version of SQL Server as of the 13th of May and MSDN adn TechNet will be able to download it the 3th of May. The free express edition can already be downloaded. Even though official prices haven’t been revealed yet, it is expected that they will rise by 15 to 25% in comparison to R1.

Now that SQL Server 2008 R2 has gone gold, finally more details about the different versions are becoming available about the different edtions, which makes it easier to determine which version one requires. It seem geographical maps are available in all version. Shared components, however, are available from the Standard edition onward. I’m still sifting through posts with information on what has changed (apart from the obvious). One thing is for sure, to Integration Services there really have not been that many features added.

Data Integrator part of the new SAP BusinessObjects certification programme after all

2010-03-22T11:01:15Z

Last week I ran into someone at an event and as the evening progressed we briefly discussed the new SAP BusinessObjects certification programme. I mentioned to him that it was a shame that the Data Integrator exam hadn’t been converted into an SAP exam, but he claimed that this indeed was case.

I figured that he was either mistaken, or that, since he works for a company that is in the SEP programme, he was in the know and I wasn’t. It turns out he was in the know and I wasn’t.

For me it doesn’t really make a difference as I already passed the first exam under the old programme and most probably won’t have the time to do the second exam before they are phased out, which means I’ll take the new-style exam somewhere this year. I guess that, just like with the other exams, my advice would be to wait with booking the exam until it can be booked from mid-March onwards, unless you’re in the process of becoming certified, but still one exam short, I would make sure to have booked and sat the exams by the end of April. If you’ve already booked your exam(s), but haven’t taken any yet, I would cancel the exam(s), get a refund and book the new exam as soon as it becomes available.

I’m secretly hoping that exam is also updated with a new set of exam questions, since the product has been updated to Data Services 3.2 recently, but I’m guessing that’s wishful thinking.

Business Objects Certified Professional certification soon to be retired

2010-02-16T16:37:05Z

The other day I was reading the latest posts in a topic of a forum I quite frequently visit and it came to my attention that the current SAP BusinessObjects certification programme will be retired by the end of April and replaced by a new SAP certification programme. One of the posts in said topic refers to a blog on the SAP Community Network, which explains that the exams will be replaced by the following three exams:

SAP Certified Application Associate – SAP BusinessObjects Enterprise XI 3.x (exam code C_BOE_30)
SAP Certified Application Associate – Crystal Reports 2008 (exam code C_BOCR_08)
SAP Certified Application Associate – BusinessObjects Web Intelligence XI 3.x (exam code C_BOWI_30)

Anyone who has ever spent some time investigating the certification possibilities for Business Objects will notice that, interestingly enough, there doesn’t seem to be a replacement for the Data Integrator certification (nor has the Migration Specialist accreditation been turned into something more substantial, but that was a long shot anyway). According to the blog post the new exams will be available via both Pearson VUE as well as the SAP Education Centres around the world.

I’m wondering, are any of you certified or planning to become certified? I guess my advice would be to wait with booking an exam until the new exams can be booked from mid-March onwards. If you’ve already booked your exam(s), but haven’t taken any yet, I would cancel. However, if you’re in the process of becoming certified, but still one or more exams short, I would make sure to have booked and sat the exams ASAP.

Update: After some people had posted replies, a FAQ was promised, which is now online. Additionally, some more information can be found at the SAP Education pages.

Adding system info to your desktop background

2009-11-20T12:21:56Z

I’d seen this in the past at a couple of clients, but never was able to figure out how their admins had accomplished it. I assumed it was some sort of setting hidden very far away in the bowels of Windows Server. I was wrong.

All my server's info in the background image.

It’s this tiny little program that does the trick. Simply place the executable somewhere on your system and start it. Once your satisfied with the result, simply Apply and Exit. If the information is prone to change, use the command-line options in combination with the Startup folder. Alternatively, if you want to use the same background for several servers, just Save As… and copy the .bgi file. You can then add a shortcut to the Startup folder with the following command

C:\bginfo.exe C:\bginfo.bgi /timer:0

to have the information displayed and refreshed at startup without a dialog box being presented (assuming the executable and configuration file are placed in the root of the C: drive.

Salmonella typhi font

2009-11-05T12:35:59Z

I love creativity and, admittedly, have a bit of a font fetish. Which is why I thought this experimental project which involved printing with bacteria is really interesting.

How many people are in space right now?

2009-10-13T10:40:10Z

Not much of a KPI, but there you go.

The harvest of an evening in a hotel with Web Designer magazine

2010-12-26T10:21:16Z

Philip Freriks ik ben je bitch niet

2009-08-14T11:51:32Z

Too funny this (unfortunately in Dutch).